# Aether V4 Architecture Standards

**Status:** Draft / Strategic Intent (January 2026)
**Scope:** Universal standards for Aether (AE) Core Objects and Database Logic.

---

## 1. Core Identity & Relationships
All Aether V4 objects must implement the following identity fields to support "Vision" string IDs and polymorphic relationships.

| Field Name | Type | DB Column | Description |
| :--- | :--- | :--- | :--- |
| `id` | `int` | `obj_id` | Internal Primary Key (AI). |
| `id_random` | `str` | `id_random` | Public "Vision" ID (URL-safe string). |
| `code` | `str` | `obj_code` | Human-friendly unique identifier. |
| `account_id` | `int/str` | `obj_account_id` | Multi-tenancy context. |
| `parent_type` | `str` | `obj_parent_type` | Polymorphic parent type (e.g., 'event'). |
| `parent_id` | `int` | `obj_parent_id` | Polymorphic parent ID. |
| `ext_uid` | `str` | `obj_ext_uid` | External system unique ID (GUID/UUID). |
| `ext_id` | `str` | `obj_ext_id` | External system human ID. |

---

## 2. Standardized Lifecycle Fields
V4 expands beyond simple creation/update timestamps to support complex moderation and archival workflows.

| Field Name | Type | Description |
| :--- | :--- | :--- |
| `status` | `int` | Numerical status code (0=Draft, 1=Active, etc.). |
| `approve` | `bool` | Approval state. |
| `approved_on` | `datetime` | Timestamp of approval. |
| `enable` | `bool` | Global availability toggle. |
| `enable_on` | `datetime` | Scheduled activation time. |
| `archive` | `bool` | Archival state. |
| `archive_on` | `datetime` | Timestamp of archival. |
| `created_on` | `datetime` | Automatic creation timestamp (DB). |
| `updated_on` | `datetime` | Automatic update timestamp (DB). |

---

## 3. Search Optimization Pattern (`default_qry_str`)
To provide high-performance search without complex application-side logic, searchable objects implement a "Search Indexing" pattern.

### SQL Implementation
1. **Column:** `default_qry_str` (TEXT/VARCHAR, NULL) with a `FULLTEXT` index.
2. **Function:** A deterministic SQL function `{table}_default_qry_str(...)` that returns a `CONCAT_WS` of key fields (ID, Name, Email, Code, etc.).
3. **Trigger:** `BEFORE INSERT` and `BEFORE UPDATE` triggers to call the function and populate the column.

**Example Function:**
```sql
CREATE OR REPLACE FUNCTION `event_badge_default_qry_str`(
    id_random VARCHAR(22),
    full_name VARCHAR(200),
    email VARCHAR(255)
) RETURNS TEXT CHARSET utf8mb4 DETERMINISTIC
BEGIN
    RETURN REPLACE(TRIM(CONCAT_WS(' ', id_random, COALESCE(full_name,''), COALESCE(email,''))), '  ', ' ');
END;
```

---

## 4. Database Normalization Standards
Aether V4 favors "Database-First" normalization via triggers. This ensures data integrity even when accessed by multiple services.

### Automatic Logic Patterns:
- **Identity Generation:** Trigger checks if `id_random` is missing and calls `gen_rand_pattern('4C-2N-2N-2N')`.
- **Name Concatenation:** Trigger calls `name_for_full_name(...)` to merge informal/given/family names into a single `full_name` field.
- **Location Stringify:** Trigger calls `make_location_str(...)` to build human-readable locations from city/state/country.

---

## 5. Metadata & JSON Structures
To support real-time schema evolution without migrations, AE objects include four distinct JSON buckets:

| Field | Purpose |
| :--- | :--- |
| `data_json` | Object-specific business data. |
| `meta_json` | System-level metadata and processing logs. |
| `cfg_json` | UI/Frontend configuration (e.g., display themes). |
---

## 6. SQL Orchestration Pattern (The "Triple-Threat")
To maintain high performance and data integrity while keeping the API layer thin, Aether V4 uses a standardized orchestration pattern across all core objects (Badges, Journals, Events, etc.).

### 6.1 Deterministic Functions
Used to centralize logic that would otherwise be duplicated in the API and UI.
- **Purpose:** Concatenation (Names, Addresses), Search Indexing (`default_qry_str`), and ID Generation.
- **Standard:** Must be `DETERMINISTIC` to allow use in generated columns and triggers.

### 6.2 Automation Triggers
Enforce data consistency at the point of entry/modification.
- **`BEFORE INSERT`:** Auto-generates IDs, normalizes raw strings into standard fields (e.g., atomic names to `full_name`), and populates search strings.
- **`BEFORE UPDATE`:** Recalculates all derived fields to ensure they never drift from their source components.

### 6.3 Standardized Views (`v_`)
The API strictly consumes Views rather than raw Tables for `GET` and `SEARCH` operations.
- **Purpose:** Join related data (e.g., joining a Journal with its Owner's name), handle null-coalescing, and provide stable aliases for the API.
- **Standard:** Every table `X` should have a corresponding view `v_X` which serves as the default `tbl_default` in `ae_obj_types_def.py`.

### 6.4 Journal-Specific Extensions (Reference Implementation)
Journals and Journal Entries represent a high-complexity implementation of the Triple-Threat pattern, incorporating aggregated counts and cross-table aliasing.

#### View Aggregation (`v_journal`)
The Journal view joins account, person, and user context while calculating entry counts in a subquery to avoid N+1 issues in the API.
- **Stable Aliasing:** `journal.id` is aliased to `journal_id` and `journal.id_random` to `journal_id_random` within the view to ensure API stability across refactors.
- **Context Enrichment:** Joins `person.full_name` and `user.username` so the frontend can display owner info without secondary lookups.

#### Complex Search (`journal_entry_default_qry_str`)
The Journal Entry search function demonstrates indexing deep into JSON and history fields:
- **Concatenation:** Includes `id_random`, `name`, `summary`, `content`, `history`, `category_code`, `tags`, and even `data_json`.
- **FULLTEXT:** A dedicated `FULLTEXT INDEX` is applied to the resulting `default_qry_str`.

#### Pattern-Based Identity
Journals use specific random patterns via triggers to distinguish between object types:
- **Journal:** `gen_rand_pattern('4C-2N-2N-2N')`
- **Journal Entry:** `gen_rand_pattern('3CN-2CN-2CN-2CN')`

---

### 6.5 Cleanup & Cascading Logic
To maintain referential integrity without relying solely on DB-level constraints (which can be opaque), Aether V4 uses explicit `AFTER DELETE` triggers.

#### Cascading Deletes
When a parent object (like a Journal) is deleted, a trigger ensures all children (Journal Entries) are scrubbed.
```sql
CREATE TRIGGER journal_after_delete AFTER DELETE ON `journal`
FOR EACH ROW
BEGIN
    DELETE FROM journal_entry WHERE journal_entry.journal_id = OLD.id;
END;
```