DB-TAB

Database Tables & Relationships

ZAYAZ Platform – Data Architecture Guide

1. Purpose of this document

This document explains how database tables and relationships are structured in ZAYAZ, and how developers should read, interpret, and design new tables going forward.

It is intended for:

Developers onboarding to ZAYAZ
Engineers creating new modules, engines, or pipelines
Architects reviewing data model changes
Anyone reading dbdiagram.io diagrams or registry tables

ZAYAZ uses a registry-driven architecture. Instead of relying on implicit conventions, all tables, columns, and relationships are explicitly described in registries that serve as the single source of truth.

2. Core registries (single source of truth)

ZAYAZ uses three primary registries to describe the database:

2.1. table_overview

A list of all tables in the platform.

Describes:

table name
prefix (what kind of table it is)
schema (where it lives)
owning module
lifecycle status
purpose

Think of this as the catalog of tables.

2.2. signal_registry (SSSR)

A row-level registry of all columns (signals) across all tables.

Describes:

table + column name
semantic meaning of the column
data type / role
whether it is a key, metric, attribute, or derived value
row_type (concept vs binding)
read/write characteristics

Think of this as the data dictionary and semantic layer.

2.3. relationship_registry

A registry of how tables relate to each other.

Describes:

which table references which
cardinality
relationship type (FK, derivation, aggregation, dependency)
enforcement strategy
documentation notes

Think of this as the graph of the data model.

3. How to read a ZAYAZ table

To fully understand a table, you should always read it through four lenses:

Prefix → what kind of table is this?
Schema → what layer does it belong to?
Signals → what does each column mean?
Relationships → how does it connect to other tables?

No table should be interpreted in isolation.

4. Table prefixes (what kind of table is this?)

Table prefixes make the role of a table immediately visible.

Prefix	Meaning
data_	Legacy or raw general tables (avoid for new design)
dim_	Dimensions (countries, units, sectors, classifications)
fact_	Facts / events (emissions, executions, indicators)
ref_	Reference data (registries, taxonomies, method catalogs)
stg_	Staging tables (raw Excel / API ingestion)
int_	Intermediate tables (engine merge or transformation outputs)
agg_	Aggregates (summarized or rolled-up metrics)
mrt_	Data marts (domain- or consumer-specific views)
tmp_	Temporary tables (pipeline intermediates only)
rl_	Pure relation tables (many-to-many joins only)
eng_	Engine outputs (algorithmic results, scores, models)
mod_	Module-owned business objects (user-facing state)
sig_	Signal registry tables (definitions, metadata)

Rule of thumb If you can’t explain why a table has its prefix, the prefix is wrong.

5. Database schemas (where does the table live?)

Schemas express data lifecycle and responsibility, not just organization.

Schema	Purpose
core_data	Core transactional / reference data
core_dim	Dimension tables
core_telemetry	Logs, metrics, operational telemetry
core_metadata	Registries, configuration, descriptors
core_staging	Raw ingestion
core_intermediate	Transformation outputs
core_aggregates	Aggregated metrics
core_marts	Domain-specific data marts
core_temp	Temporary pipeline data
core_relations	Join tables
core_engine	Engine computation outputs
core_module	Module-owned state
core_signals	Signal registry
bronze	Lakehouse raw layer (optional)
silver	Lakehouse cleaned layer
gold	Lakehouse curated layer

Prefix + schema must agree Example:

ref_* belongs in core_metadata
fact_* belongs in core_data or core_engine
agg_* belongs in core_aggregates

6. Table lifecycle status

Every table has a status to communicate stability.

Status	Meaning
draft	Initial definition, not ready for use
experimental	Working but unstable
stable	Fully supported
legacy	Old but still required
deprecated	Replacement exists
retired	Removed; kept only for history

Only stable tables should be used for new dependencies.

7. Signals (columns) and signal_registry

Every column is a signal with meaning.

Key ideas:

A signal is not just a column — it’s a semantic contract
Signals are defined once, then reused
Tables bind signals to physical storage

7.1. row_type

Type	Meaning
concept	Canonical definition of a signal
binding	Physical storage of a signal

Example:

method_id (concept)
ref_compute_method_registry.method_id (binding)

7.2. connection_type

Value	Meaning
r	Read-only
w	Write-only
rw	Read + write

This is critical for engines and governance.

7.3. Relationships between tables

All relationships are defined in relationship_registry.

Cardinality

Type	Meaning
one-to-one	Unique pairing
one-to-many	Parent → multiple children
many-to-one	Many rows point to one
many-to-many	Requires join table (rl_)

7.4. Relationship types

Type	Meaning
references	Classic foreign-key relationship
derives_from	Table is calculated from another
feeds	Data flow (e.g. staging → model)
aggregates	Summarization
joins	Many-to-many join
depends_on	Logical dependency (engine needs metadata)

Not all relationships are enforced as database FKs — some are logical but still first-class.

8. Reading dbdiagram.io diagrams

dbdiagram.io diagrams are generated views, not the source of truth.

Rules:

Tables reflect table_overview
Columns reflect signal_registry
Lines reflect relationship_registry
Composite keys are explicit
Join tables (rl_) are always shown as intermediates

If a diagram surprises you, the registry is wrong, not the diagram.

9. Designing new tables (rules you should follow)

When adding a new table:

Choose the correct prefix
Place it in the correct schema
Register the table in table_overview
Register every column in signal_registry
Define all relationships in relationship_registry
Assign a status
Ensure naming is consistent and deterministic

If you skip a registry, the table is incomplete.

10. Final principle

ZAYAZ does not rely on convention alone. Everything is explicit, inspectable, and automatable.

This is what enables:

automated migrations
generated documentation
consistent engine behavior
long-term scalability

11. The relationship_registry Table

11.1. relationship_registry - Column Description Table

Column name	Type	Description
relationship_id	UUID	Unique internal identifier for the relationship record. Can be auto-generated.
relationship_key	TEXT	Deterministic, human-readable unique key for the relationship. Used by tooling, documentation, and to ensure stability across environments.
constraint_name	TEXT	Explicit database constraint name for the foreign key. If empty, tooling can generate one automatically.
availability_status	TEXT	Lifecycle status of the relationship. Use active, draft, deprecated, pending, or future. Only active relationships are enforced or emitted by default.
version	INTEGER	Version number of this relationship definition. Increment when meaningfully changing behavior or structure.
change_reason	TEXT	Short explanation of why this relationship was changed (optional but recommended).
module_name	TEXT	Official ZAYAZ module name that owns this relationship (used for sorting diagrams, documentation, and responsibility).
owner_team	TEXT	Team or group responsible for this relationship (optional governance metadata).
created_by	TEXT	Who originally defined this relationship (person or system).
approved_by	TEXT	Who approved the relationship for enforcement or publication (optional).
approved_at	TIMESTAMPTZ	Timestamp of approval (optional).
child_db_engine	TEXT	Database engine where the child (referencing) table lives (e.g. aurora_postgres, postgres, dynamodb).
child_db_name	TEXT	Logical database or cluster name for the child table (e.g. zayaz_core).
child_schema	TEXT	Schema name of the child table (usually public).
child_table	TEXT	Name of the child table where the foreign key column(s) exist.
child_columns	TEXT[]	Column or columns in the child table that reference the parent. Order matters for composite keys.
parent_db_engine	TEXT	Database engine where the parent (referenced) table lives.
parent_db_name	TEXT	Logical database or cluster name for the parent table.
parent_schema	TEXT	Schema name of the parent table.
parent_table	TEXT	Name of the parent table being referenced.
parent_columns	TEXT[]	Column or columns in the parent table being referenced. Order must match child_columns.
relationship_type	TEXT	Type of relationship: foreign_key (enforceable), logical (documented only), or join_bridge (many-to-many helper).
enforcement_mode	TEXT	How the relationship is enforced: auto (tool decides), db_fk (database FK), app_validated (application logic), or doc_only.
cardinality	TEXT	Documentation hint describing relationship shape: 1:many, 1:1, or many:many.
is_required	BOOLEAN	If true, the relationship must exist for valid data. If false, the relationship is optional.
enforce_child_unique	BOOLEAN	If true, child columns should be unique (used to enforce true one-to-one relationships).
match_type	TEXT	Foreign key match type: simple (normal) or full (rare, composite keys only).
deferrable	BOOLEAN	Whether the foreign key can be deferred until transaction commit (used for cyclic dependencies).
initially_deferred	BOOLEAN	If true, the constraint starts deferred by default (only valid if deferrable is true).
on_delete	TEXT	Action when a parent row is deleted: restrict, cascade, set_null, set_default, or no_action.
on_update	TEXT	Action when a parent key is updated (same options as on_delete).
create_child_index	BOOLEAN	Whether tooling should ensure an index exists on the child foreign key column(s).
child_index_name	TEXT	Explicit name for the generated child index (optional).
child_index_method	TEXT	Index method for the child index (typically btree).
create_parent_index	BOOLEAN	Whether to create an index on the parent columns (usually false, since PKs are indexed).
tenant_scope_column	TEXT	Name of a tenant or scope column that must match on both sides of the relationship (optional).
require_tenant_match	BOOLEAN	If true, validates that both child and parent rows belong to the same tenant or scope.
generator_flags	JSONB	Free-form JSON used to control generators (e.g. suppress FK, suppress index, hide from docs).
notes	TEXT	Human-readable explanation of the relationship and its intent.
created_at	TIMESTAMPTZ	Timestamp when this relationship definition was created.
updated_at	TIMESTAMPTZ	Timestamp when this relationship definition was last updated.

11.2. relationship_registry - SQL

relationship_registry.sql
-- ============================================================
-- 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);

12. DB Diagaram

We use dbdiagram.io to quickly create database diagrams (ERDs) using DBML to visualize database structures, and define relationships, all without relying heavily on a mouse—perfect for those who prefer coding over dragging and dropping.

The database diagrams are password protected to avoid poblic viewing. Use ZYZ-2026 to enable view.

Due to the large number of tables we group them (TableGroup) to allow easier overview. See dbdiagram.io for more information.

dbdiagram.io Code example

Compute Method Versoning (DBML)GitHub ↗
// ==========================================
// ZAYAZ - Compute Method Versioning
// ==========================================

TableGroup "Compute Method Registry" [color: #011B4E] {
  ref_compute_method_registry
  ref_compute_method_latest
  fact_compute_executions
}

Table ref_compute_method_registry {
  method_id                 varchar
  version                   varchar
  method_name               varchar
  status                    varchar
  description               text
  inputs_schema_json        json
  options_schema_json       json
  output_schema_json        json
  implementation_ref        varchar
  dataset_requirements      text
  acl_tags                  varchar
  created_at                datetime
  updated_at                datetime

  Indexes {
    (method_id, version) [pk] // canonical identity of a method-version
  }
}

Table ref_compute_method_latest {
  method_id                 varchar
  version                   varchar
  updated_at                datetime
  note                      text

  // One row per method_id (designates the latest version)
  Indexes {
    (method_id) [unique]
  }
}

Table fact_compute_executions {
  exec_id                   varchar [pk]
  method_id                 varchar
  version                   varchar
  tenant_id                 varchar
  inputs_hash               varchar
  options_hash              varchar
  output_hash               varchar
  dataset_hashes            text
  provenance_id             varchar
  latency_ms                int
  status                    varchar
  error_code                varchar
  created_at                datetime
  region                    varchar
  storage_ref               varchar
  caller_ip                 varchar

  Indexes {
    (method_id, version)
    (tenant_id)
    (created_at)
  }
}

// ==========================================
// Relationships (composite FK syntax: table.(col1, col2))
// ==========================================

// Latest pointer -> Registry method-version (many-to-one in relational terms)
Ref: ref_compute_method_latest.(method_id, version) > ref_compute_method_registry.(method_id, version)

// Execution -> Registry method-version (many-to-one)
Ref: fact_compute_executions.(method_id, version) > ref_compute_method_registry.(method_id, version)

Example (of the above tables)

GitHub Repo Request for Change (RFC)

1. Purpose of this document​

2. Core registries (single source of truth)​

2.1. table_overview​

2.2. signal_registry (SSSR)​

2.3. relationship_registry​

3. How to read a ZAYAZ table​

4. Table prefixes (what kind of table is this?)​

5. Database schemas (where does the table live?)​

6. Table lifecycle status​

7. Signals (columns) and signal_registry​

7.1. row_type​

7.2. connection_type​

7.3. Relationships between tables​

7.4. Relationship types​

8. Reading dbdiagram.io diagrams​

9. Designing new tables (rules you should follow)​

10. Final principle​

11. The relationship_registry Table​

11.1. relationship_registry - Column Description Table​

11.2. relationship_registry - SQL​

12. DB Diagaram​

1. Purpose of this document

2. Core registries (single source of truth)

2.1. table_overview

2.2. signal_registry (SSSR)

2.3. relationship_registry

3. How to read a ZAYAZ table

4. Table prefixes (what kind of table is this?)

5. Database schemas (where does the table live?)

6. Table lifecycle status

7. Signals (columns) and signal_registry

7.1. row_type

7.2. connection_type

7.3. Relationships between tables

7.4. Relationship types

8. Reading dbdiagram.io diagrams

9. Designing new tables (rules you should follow)

10. Final principle

11. The relationship_registry Table

11.1. relationship_registry - Column Description Table

11.2. relationship_registry - SQL

12. DB Diagaram