116 lines
5.5 KiB
Markdown
116 lines
5.5 KiB
Markdown
# Project: V3 Uniform Lookup & Identity Agnostic Resolution
|
|
> **Status:** 🏗️ Implementation (Phase 2 & Whitelist Complete - Feb 20, 2026)
|
|
> **Goal:** Standardize all `lu_*` tables into a hierarchical, identity-agnostic system supporting Global Defaults, Account Overrides, and Object Overrides.
|
|
|
|
## 1. Executive Summary
|
|
The Aether V3 Lookup System solves the problem of varied data standards (e.g., ISO-2, ISO-3, Numeric codes) and multi-tenant customization needs. It adopts a "Deduplicated Inheritance" pattern:
|
|
- **Global Defaults:** System-wide standard records (provided by Aether).
|
|
- **Account Overrides:** Client-specific labels, sorting, or disabled states.
|
|
- **Object Overrides:** Context-specific overrides (e.g., unique labels for a specific Event or Person).
|
|
|
|
## 2. Theoretical Architecture
|
|
|
|
### 2.1 Standardized Schema Components
|
|
To ensure consistency across the ecosystem, all lookup tables will migrate toward a baseline "V3 Minimal Structure."
|
|
|
|
**Minimal Baseline Fields:**
|
|
- `id`: Internal Integer (Primary Key).
|
|
- `id_random`: Public String (ID Vision compliance).
|
|
- `account_id`: Account context (NULL = Global).
|
|
- `for_type` / `for_id`: Polymorphic Context (Optional).
|
|
- `group`: The primary business key/cluster key (e.g., 'US', 'ACTIVE'). *Note: Must be populated for hierarchy to work.*
|
|
- `name`: Primary display label.
|
|
- `description`: Detailed explanation.
|
|
- `enable`: Binary (1 = Active, 0 = Disabled). *Crucial for Negative Overrides.*
|
|
- `hide`: UI Visibility flag.
|
|
- `priority`: Boolean/TinyInt (1 = High priority, 0 = Normal).
|
|
- `sort`: Ordering priority.
|
|
- `created_on` / `updated_on`: Audit timestamps.
|
|
|
|
### 2.2 Hierarchical Selection (The "Ranked" Query)
|
|
To return a complete list where overrides replace defaults without duplication, we use **Window Functions** (`ROW_NUMBER() OVER`).
|
|
|
|
**Finalized SQL Pattern:**
|
|
```sql
|
|
SELECT * FROM (
|
|
SELECT *,
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY `group`
|
|
ORDER BY
|
|
(for_type = :for_type AND for_id = :for_id) DESC,
|
|
(account_id = :account_id) DESC,
|
|
created_on DESC
|
|
) as rank_priority
|
|
FROM `v_lu_v3_example`
|
|
WHERE (for_type = :for_type AND for_id = :for_id)
|
|
OR account_id = :account_id
|
|
OR account_id IS NULL
|
|
) AS ranked
|
|
WHERE rank_priority = 1
|
|
AND enable = 1 -- Negative Override enforcement
|
|
ORDER BY sort ASC, name ASC;
|
|
```
|
|
|
|
### 2.3 Identity Agnostic Resolution
|
|
To support external systems using different coding standards, the V3 API implements a multi-field "Any-Match" resolver.
|
|
|
|
**Resolution Logic:**
|
|
- Accepts a query string `q`.
|
|
- Iterates through `searchable_fields` defined in the lookup registry.
|
|
- Returns the highest-priority hierarchical match.
|
|
|
|
### 2.4 Negative Overrides (The "Shadowing" Pattern)
|
|
To "delete" a global default for a specific account, the account creates an override record with the same `group` but `enable = 0`. The ranking query assigns `rank_priority = 1` to the account record, and the outer `WHERE enable = 1` then excludes it, effectively hiding the item for that account.
|
|
|
|
---
|
|
|
|
## 3. Implementation Standards
|
|
|
|
### 3.1 Normalization Rules
|
|
- **Underscore Naming:** All database columns must use underscores (e.g., `alpha_3_code`) instead of hyphens to ensure clean Python attribute access.
|
|
- **Group Population:** The `group` field must be physically populated in the table with a stable identity string (ISO codes, standard names).
|
|
- **Physical Name Field:** While specialized fields (like `english_short_name`) may exist, a physical `name` field is required for Generic CRUD compatibility.
|
|
|
|
### 3.2 View Requirements
|
|
- **Standard Joins:** All `v_lu_v3_*` views must join the `account` table to provide `account_id_random`.
|
|
- **Hybrid Naming:** Views may use `COALESCE` to provide a default `name` from specialized fields if the physical `name` column is empty.
|
|
|
|
### 3.3 Whitelist Policy (Phase 2)
|
|
To avoid manual management of hundreds of negative overrides, accounts can define an opt-in whitelist.
|
|
- **Storage:** `site.cfg_json` -> `lookup_policy`.
|
|
- **Schema:** `{ "lookup_policy": { "country": ["US", "CA"] } }`
|
|
- **Logic:** When `site_id` is passed to the lookup API, results are filtered to only include groups present in the whitelist.
|
|
|
|
---
|
|
|
|
## 4. Implementation Roadmap
|
|
|
|
### Phase 1: Normalization & Infrastructure
|
|
- [x] Audit first batch of `lu_*` tables.
|
|
- [x] Establish `Lookup_Base` Pydantic models.
|
|
- [x] Implement hierarchical `get_lookup_list_v3` logic.
|
|
- [x] Register `/v3/lookup/` router.
|
|
|
|
### Phase 2: Migration (Batch 1: High Priority)
|
|
- [x] `lu_v3_country` (Group: `alpha_2_code`)
|
|
- [x] `lu_v3_country_subdivision` (Group: `code`)
|
|
- [x] `lu_v3_time_zone` (Group: `name`)
|
|
|
|
### Phase 3: Migration (Batch 2: Contextual) - **ON HOLD FOR V3.1**
|
|
- [ ] `lu_post_topic`
|
|
- [ ] `lu_user_status`
|
|
- [ ] `lu_file_purpose`
|
|
|
|
### Phase 4: Advanced Features
|
|
- [x] Implement **Whitelist Policies** via `site.cfg_json`.
|
|
- [ ] Implement Batch Update tools for managers.
|
|
|
|
## 5. Key Learnings & Decisions
|
|
- **Deduplication:** The `PARTITION BY group` is the cornerstone of the system. Without a populated `group` field, the hierarchy collapses.
|
|
- **Generic CRUD Compatibility:** We decided to maintain a physical `name` field to allow the Aether V3 UI to handle all lookups with zero custom code.
|
|
- **Fail-Closed:** Account isolation is enforced at the query level via the `account_id` filter.
|
|
- **Type-Safe Auth:** Discovered that `load_site_obj` returns Random IDs for accounts; updated router context comparison to use `account_id_random` strings for 403 authorization checks.
|
|
|
|
---
|
|
*Created by Gemini CLI for Scott Idem*
|