9c0aae9a6d
- Create ARCH__V4_CORE_STANDARDS.md to document V4 identity, lifecycle, and search patterns. - Standardize default_qry_str in Event_Badge_Base. - Clean up obsolete .snapshot files. - Update README baseline to v3.0.99.
133 lines
6.5 KiB
Markdown
133 lines
6.5 KiB
Markdown
# 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;
|
|
```
|