Jira progress: loading…
DB-TAB
Database Tables & Relationships
-- ============================================================
-- ZAYAZ: relationship_registry (Postgres registry table)
-- Purpose:
-- Canonical registry of intended relationships between tables/entities.
-- Drives:
-- 1) FK + index migration generation (when enforceable in same RDBMS)
-- 2) App/validator integrity rules (cross-db / non-relational)
-- 3) Auto-generated documentation + diagrams (grouped by module_name)
-- ============================================================
CREATE TABLE IF NOT EXISTS relationship_registry (
-- ----------------------------------------------------------
-- Identity & lifecycle
-- ----------------------------------------------------------
relationship_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
relationship_key TEXT NOT NULL UNIQUE, -- stable slug/key used by tooling and references
constraint_name TEXT, -- optional explicit FK constraint name (else generated)
availability_status TEXT NOT NULL DEFAULT 'active', -- active | deprecated | draft | pending | future
version INTEGER NOT NULL DEFAULT 1,
change_reason TEXT,
-- ----------------------------------------------------------
-- Ownership / grouping (use this for sorting docs/diagrams)
-- ----------------------------------------------------------
module_name TEXT NOT NULL, -- e.g., FOGE, DVE, FIRM, CPI, PEF-ME, Billing, CoreIdentity
owner_team TEXT,
created_by TEXT,
approved_by TEXT,
approved_at TIMESTAMPTZ,
-- ----------------------------------------------------------
-- Child (referencing) side location (multi-database aware)
-- ----------------------------------------------------------
child_db_engine TEXT NOT NULL DEFAULT 'aurora_postgres',
child_db_name TEXT, -- logical DB/cluster identifier (e.g., zayaz_core, zayaz_analytics)
child_schema TEXT NOT NULL DEFAULT 'public',
child_table TEXT NOT NULL,
child_columns TEXT[] NOT NULL, -- supports composite FK (ordered)
-- ----------------------------------------------------------
-- Parent (referenced) side location (multi-database aware)
-- ----------------------------------------------------------
parent_db_engine TEXT NOT NULL DEFAULT 'aurora_postgres',
parent_db_name TEXT,
parent_schema TEXT NOT NULL DEFAULT 'public',
parent_table TEXT NOT NULL,
parent_columns TEXT[] NOT NULL, -- supports composite key refs (ordered)
-- ----------------------------------------------------------
-- Enforcement + semantics
-- ----------------------------------------------------------
relationship_type TEXT NOT NULL DEFAULT 'foreign_key', -- foreign_key | logical | join_bridge
enforcement_mode TEXT NOT NULL DEFAULT 'auto', -- auto | db_fk | app_validated | doc_only
cardinality TEXT, -- 1:many | 1:1 | many:many (docs/validation hint)
is_required BOOLEAN NOT NULL DEFAULT TRUE, -- if false, treat as optional (docs/validation/generator hint)
enforce_child_unique BOOLEAN NOT NULL DEFAULT FALSE, -- for 1:1 patterns (UNIQUE on child fk cols)
match_type TEXT NOT NULL DEFAULT 'simple', -- simple | full (PG supports both; full is rare)
deferrable BOOLEAN NOT NULL DEFAULT FALSE,
initially_deferred BOOLEAN NOT NULL DEFAULT FALSE,
-- ----------------------------------------------------------
-- Actions (for RDBMS FKs; ignored for logical-only relations)
-- ----------------------------------------------------------
on_delete TEXT NOT NULL DEFAULT 'restrict', -- restrict | cascade | set_null | set_default | no_action
on_update TEXT NOT NULL DEFAULT 'restrict', -- restrict | cascade | set_null | set_default | no_action
-- ----------------------------------------------------------
-- Index / performance hints (for RDBMS relations)
-- ----------------------------------------------------------
create_child_index BOOLEAN NOT NULL DEFAULT TRUE,
child_index_name TEXT,
child_index_method TEXT NOT NULL DEFAULT 'btree', -- btree | hash | gin | gist | brin (use selectively)
create_parent_index BOOLEAN NOT NULL DEFAULT FALSE, -- usually false (parent PK/UK already indexed)
-- ----------------------------------------------------------
-- Tenant scoping (optional but useful in multi-tenant systems)
-- ----------------------------------------------------------
tenant_scope_column TEXT, -- e.g., tenant_id / normalized_client_id / eco_number
require_tenant_match BOOLEAN NOT NULL DEFAULT FALSE,
-- ----------------------------------------------------------
-- Generator control + freeform metadata
-- ----------------------------------------------------------
generator_flags JSONB NOT NULL DEFAULT '{}'::jsonb, -- e.g. {"suppress_fk":true,"emit_index":false}
notes TEXT,
-- ----------------------------------------------------------
-- Timestamps
-- ----------------------------------------------------------
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-- ----------------------------------------------------------
-- Sanity checks
-- ----------------------------------------------------------
CONSTRAINT rr_columns_nonempty_chk
CHECK (array_length(child_columns, 1) >= 1 AND array_length(parent_columns, 1) >= 1),
CONSTRAINT rr_composite_len_match_chk
CHECK (array_length(child_columns, 1) = array_length(parent_columns, 1)),
CONSTRAINT rr_deferrable_logic_chk
CHECK ((initially_deferred = FALSE) OR (deferrable = TRUE)),
-- Controlled vocabularies (prevents generator-breaking typos)
CONSTRAINT rr_availability_status_chk
CHECK (availability_status IN ('active','deprecated','draft','pending','future')),
CONSTRAINT rr_relationship_type_chk
CHECK (relationship_type IN ('foreign_key','logical','join_bridge')),
CONSTRAINT rr_enforcement_mode_chk
CHECK (enforcement_mode IN ('auto','db_fk','app_validated','doc_only')),
CONSTRAINT rr_cardinality_chk
CHECK (cardinality IS NULL OR cardinality IN ('1:many','1:1','many:many')),
CONSTRAINT rr_match_type_chk
CHECK (match_type IN ('simple','full')),
CONSTRAINT rr_on_delete_chk
CHECK (on_delete IN ('restrict','cascade','set_null','set_default','no_action')),
CONSTRAINT rr_on_update_chk
CHECK (on_update IN ('restrict','cascade','set_null','set_default','no_action')),
CONSTRAINT rr_child_index_method_chk
CHECK (child_index_method IN ('btree','hash','gin','gist','brin'))
);
-- ------------------------------------------------------------
-- Indexes for common usage:
-- - docs/diagrams grouped by module_name
-- - generator queries by child/parent tables
-- ------------------------------------------------------------
CREATE INDEX IF NOT EXISTS idx_rr_module_status
ON relationship_registry (module_name, availability_status);
CREATE INDEX IF NOT EXISTS idx_rr_child_lookup
ON relationship_registry (child_db_engine, child_db_name, child_schema, child_table);
CREATE INDEX IF NOT EXISTS idx_rr_parent_lookup
ON relationship_registry (parent_db_engine, parent_db_name, parent_schema, parent_table);
CREATE INDEX IF NOT EXISTS idx_rr_type_enforcement
ON relationship_registry (relationship_type, enforcement_mode);
-- Optional: if you frequently diff “what changed recently”
CREATE INDEX IF NOT EXISTS idx_rr_updated_at
ON relationship_registry (updated_at);