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.sqlGitHub ↗
-- ============================================================
-- 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)

APPENDIX A - Database Governance Metadata and Table Maintenance Standard

This section defines how ZAYAZ, EcoWorld, Academy, and ECO registry tables should be documented, governed, classified, audited, and maintained.

The purpose is to make every important table traceable, accountable, auditable, and safe to use across operational systems, ESG reporting, supplier intelligence, verifier workflows, Academy participation data, and public E-C-O-Number™ registries.

Good table governance is not administrative overhead. It is part of the trust infrastructure of the platform.

A.1. Why this matters

ZAYAZ and EcoWorld are building systems that will hold data used for:

CSRD / ESRS reporting
supplier assessments
training and competence evidence
E-C-O-Number™ entity identity
verifier and audit workflows
sustainability risk models
public lookup services
company and employee participation records
policy acknowledgement records
future AI-assisted reporting and assurance

For this to be credible, every important table must answer:

Who owns this data?
Where did it come from?
Who is responsible for quality?
Is it sensitive?
How long should it be kept?
Who can access it?
How are changes audited?
Can the data be used in reporting, AI, public lookup, or assurance?

A.2. Recommended metadata model

Create a shared metadata layer, preferably in:

core_metadata

Recommended tables:

core_metadata.table_registry
core_metadata.column_registry
core_metadata.data_lineage
core_metadata.data_stewards
core_metadata.classification_policies
core_metadata.audit_policies
core_metadata.retention_policies
core_metadata.data_quality_rules

This avoids scattering governance data across comments only.

PostgreSQL COMMENT ON TABLE and COMMENT ON COLUMN should still be used, but the registry tables provide structured, queryable governance metadata.

A.3. Ownership metadata

Purpose

Ownership metadata defines who is accountable for a table, domain, or dataset.

This is essential for platform governance because technical ownership and business ownership are not always the same.

What to capture

Field	Meaning
`schema_name`	Schema where the table lives
`table_name`	Table being governed
`business_owner`	Person/team accountable for data meaning and use
`technical_owner`	Person/team accountable for implementation and reliability
`domain`	Product, Academy, ECO registry, ZAYAZ reporting, risk engine, etc.
`system_of_record`	Whether this table is authoritative
`criticality`	low / medium / high / mission_critical
`status`	draft / active / deprecated / archived

Example

example-ownership-metadata.sqlGitHub ↗
insert into core_metadata.table_registry (
  schema_name,
  table_name,
  business_owner,
  technical_owner,
  domain,
  system_of_record,
  criticality,
  status
)
values (
  'eco',
  'entities',
  'EcoWorld Registry Governance',
  'ZAYAZ Platform Engineering',
  'eco_identity',
  true,
  'mission_critical',
  'active'
);

Importance

Ownership prevents orphaned data. Every critical table needs a clear owner before it becomes part of ESG reporting, verification, or client-facing workflows.

A.4. Lineage metadata

Purpose

Lineage metadata explains where data comes from, how it moves, and what downstream systems depend on it.

This is critical for auditability and future AI governance.

What to capture

Field	Meaning
`source_system`	HubSpot, LearnWorlds, Cognito, Academy UI, ECO API, manual upload, etc.
`source_table_or_endpoint`	Origin table, file, or API endpoint
`target_schema`	Destination schema
`target_table`	Destination table
`transformation_logic`	Summary of transformation
`load_method`	API, batch import, webhook, manual migration
`refresh_frequency`	real_time / hourly / daily / manual
`lineage_status`	active / deprecated / experimental

Example lineage

HubSpot Company
→ academy company sync
→ prodreg.academy_companies
→ eco.entities
→ eco.numbers
→ public E-C-O-Number™ lookup

Importance

Lineage helps answer:

Can this data be trusted?
Which system is authoritative?
What breaks if this table changes?
Which reports depend on this field?
Can this data be used as audit evidence?

A.5. Steward metadata

Purpose

Data stewards are responsible for the quality, completeness, and interpretation of data.

Ownership answers “who is accountable?”
Stewardship answers “who maintains quality?”

What to capture

Field	Meaning
`steward_name`	Named person or team
`steward_email`	Contact point
`schema_name`	Schema scope
`table_name`	Table scope
`responsibility_type`	quality / compliance / taxonomy / access / operations
`review_frequency`	monthly / quarterly / annually
`active`	Whether steward assignment is active

Examples

Typical steward assignments:

Dataset	Steward
`core_dim.countries`	Data Governance / Reference Data Steward
`eco.entities`	ECO Registry Steward
`eco.numbers`	ECO Number Issuance Steward
`prodreg.product_variants`	Product Operations
`prodreg.academy_enrollments`	Academy Operations

Importance

ESG and audit systems require clear human accountability. Stewardship is especially important for reference data, country risk indicators, ECO-number issuance, and client/supplier identity data.

A.6. Classification and sensitivity metadata

Purpose

Classification metadata defines how sensitive a table or column is and what protections apply.

This is essential for GDPR, company confidentiality, supplier data, employee participation data, and audit evidence.

Recommended classification levels

Classification	Meaning
`public`	Can be shown publicly
`internal`	Internal platform data
`confidential`	Client/company-sensitive
`restricted`	High-risk sensitive data
`personal_data`	GDPR/PII applies
`audit_evidence`	Evidence used in reporting or assurance
`public_registry`	Intended for lookup/public identity registry

Column-level examples

Table	Column	Classification
`eco.numbers.eco_number`	public or restricted depending on visibility
`eco.entity_contacts.email`	personal_data
`prodreg.academy_learners.email`	personal_data
`prodreg.academy_enrollments.completion_pct`	confidential / personal_data
`eco.supplier_survey_responses.response_payload`	confidential / audit_evidence
`core_dim.countries.iso_3166_1_numeric`	public

Importance

This controls:

API exposure
frontend visibility
AI model access
exports
retention
logging
role-based permissions
public registry behavior

No sensitive dataset should be exposed through an API until classification metadata exists.

A.7. Audit policies

Purpose

Audit policies define how changes are tracked.

For ZAYAZ, this is essential because many records may become part of ESG reporting, supplier qualification, or assurance evidence.

What to capture

Field	Meaning
`audit_enabled`	Whether changes are audited
`audit_level`	none / row / field / full_payload
`tracked_operations`	insert / update / delete
`actor_column`	`created_by`, `updated_by`, etc.
`timestamp_columns`	`created_at`, `updated_at`, etc.
`audit_table`	Where audit logs are stored
`evidence_grade`	operational / compliance / assurance

Recommended table pattern

All operational tables should include:

example-audit-policies.sqlGitHub ↗
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
created_by text,
updated_by text

For high-value tables, add audit log triggers later.

High priority audit tables:

eco.entities
eco.numbers
eco.entity_contacts
eco.supplier_survey_responses
prodreg.academy_companies
prodreg.academy_learners
prodreg.academy_enrollments
prodreg.product_variants
prodreg.product_public_codes

Importance

Audit policies protect against disputes such as:

Who issued this ECO-number?
Who changed supplier status?
When was a certificate issued?
Who updated a product price?
When was a supplier response reviewed?
Which evidence existed at reporting time?

A.8. Retention policies

Purpose

Retention policies define how long data is kept, archived, anonymized, or deleted.

This is critical for GDPR, HR data, learning records, supplier evidence, and regulatory evidence.

What to capture

Field	Meaning
`retention_period`	e.g. 3 years, 7 years, indefinite
`retention_basis`	contract, legal obligation, audit evidence, operational need
`delete_action`	hard_delete / anonymize / archive / retain
`archive_location`	Optional archive storage
`review_frequency`	How often policy is reviewed
`legal_hold_supported`	Whether deletion can be suspended

Recommended defaults

Data type	Recommended retention
Public ECO-number registry	indefinite unless revoked
Supplier ESG evidence	7–10 years
Academy completion evidence	7 years
Employee training records	3–7 years depending on contract/legal basis
Product registry data	indefinite / archive old versions
Temporary imports	delete after validation
Debug logs	30–90 days
Security logs	1–7 years depending on sensitivity

Importance

Retention policies reduce legal risk and keep the platform clean while preserving evidence needed for ESG assurance.

A.9. Additional table maintenance standards

A.9.1 Table and column comments

Every production table should have:

production-table-requirements.sqlGitHub ↗
comment on table schema.table_name is '...';
comment on column schema.table_name.column_name is '...';

This improves:

developer onboarding
AI-assisted coding
data catalog quality
audit review
reporting reliability

A.9.2 Naming standards

Recommended conventions:

Object	Convention
schemas	lowercase snake_case
tables	plural nouns
primary keys	`<entity>_id`
foreign references	`<entity>_ref`
timestamps	`created_at`, `updated_at`
statuses	`status` with CHECK constraint
JSON columns	`_payload`, `_metadata`, `_config` suffix
booleans	`is_`, `has_`, `allow_`, `requires_`

Examples:

eco.entities
eco.numbers
eco.entity_contacts
prodreg.product_variants
core_dim.countries

A.9.3 Constraints

Use constraints to protect data quality.

Recommended:

recommended-constraints.sqlGitHub ↗
not null
unique
check (...)
foreign key where in same database

Examples:

example-recommended-constraints.sqlGitHub ↗
constraint chk_eco_numbers_status
check (status in ('provisional', 'active', 'suspended', 'revoked', 'archived'));

Do not use cross-database foreign keys. Use stable UUID references and API-level integrity instead.

A.9.4 Indexes

Add indexes for:

primary lookup fields
foreign references
frequently filtered statuses
external IDs
public identifiers
lowercased email

Examples:

example-indexes.sqlGitHub ↗
create index if not exists idx_eco_numbers_eco_number
  on eco.numbers(eco_number);

create index if not exists idx_eco_entity_contacts_email
  on eco.entity_contacts(lower(email));

Avoid over-indexing early; add indexes based on query patterns.

A.9.5 Status lifecycle fields

Important operational tables should use explicit lifecycle states.

Examples:

pending
active
suspended
archived
revoked
verified
submitted
reviewed
accepted
rejected

Avoid vague states like done, ok, or valid unless clearly defined.

A.9.6 Soft delete vs hard delete

For audit-sensitive systems, prefer:

preferred-delete.sqlGitHub ↗
status = 'archived'

instead of deleting.

Use hard delete only for:

test data
temporary imports
duplicate failed records
legally required erasure

For GDPR deletion, consider anonymization where audit evidence must remain.

A.9.7 Versioning

Version important data where meaning may change.

Examples:

survey schemas
ECO-number verification rules
composite score formulas
course syllabus versions
policy acknowledgements
supplier assessment questionnaires

Recommended fields:

version integer not null default 1
version_id text
effective_from date
effective_to date

A.9.8 Data quality rules

Create explicit data quality rules for critical datasets.

Examples:

country numeric code must be 3 digits
ECO-number must match prefix-country-sequence format
email must be lowercase
completion percentage must be 0–100
active ECO-number must have issued_at
revoked ECO-number must have revoked_at

These can be enforced through:

CHECK constraints
validation functions
scheduled quality jobs
metadata rules

A.9.9 External ID mapping

For integrated systems, always store external IDs explicitly.

Examples:

hubspot_company_id
hubspot_contact_id
cognito_user_id
learnworlds_user_id
learnworlds_course_id
academy_company_id
zayaz_client_id

This prevents fragile matching based only on names or emails.

A.9.10 Data lineage for AI use

Before AI models use data for summarization, scoring, recommendations, or report generation, the table should have:

source system
owner
classification
freshness
quality score
permitted AI use
prohibited AI use
retention policy

Recommended metadata field:

ai_use_classification

Example values:

allowed_internal_summary
allowed_reporting_assist
restricted_no_training
restricted_no_external_llm
public_safe

A.10. Suggested metadata tables

A.10.1 `core_metadata.table_registry`

core_metadata.table_registry.sqlGitHub ↗
create table if not exists core_metadata.table_registry (
  table_registry_id uuid primary key default gen_random_uuid(),
  schema_name text not null,
  table_name text not null,
  table_description text,
  business_owner text,
  technical_owner text,
  data_steward text,
  domain text,
  system_of_record boolean not null default false,
  criticality text not null default 'medium',
  classification text not null default 'internal',
  retention_policy_key text,
  audit_policy_key text,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique(schema_name, table_name)
);

A.10.2 `core_metadata.column_registry`

core_metadata.column_registry.sqlGitHub ↗
create table if not exists core_metadata.column_registry (
  column_registry_id uuid primary key default gen_random_uuid(),
  schema_name text not null,
  table_name text not null,
  column_name text not null,
  column_description text,
  data_type text,
  classification text not null default 'internal',
  contains_personal_data boolean not null default false,
  contains_audit_evidence boolean not null default false,
  allowed_public_exposure boolean not null default false,
  ai_use_classification text,
  source_field text,
  quality_rule text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique(schema_name, table_name, column_name)
);

A.10.3 `core_metadata.retention_policies`

core_metadata.retention_policies.sqlGitHub ↗
create table if not exists core_metadata.retention_policies (
  retention_policy_key text primary key,
  description text not null,
  retention_period interval,
  retention_basis text,
  delete_action text not null,
  legal_hold_supported boolean not null default true,
  review_frequency text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

A.10.4 `core_metadata.audit_policies`

core_metadata.audit_policies.sqlGitHub ↗
create table if not exists core_metadata.audit_policies (
  audit_policy_key text primary key,
  description text not null,
  audit_enabled boolean not null default true,
  audit_level text not null default 'row',
  tracked_operations text[] not null default array['insert','update','delete'],
  audit_table text,
  evidence_grade text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

A.11. Minimum checklist for every new production table

Before a table is considered production-ready, it should have:

Table comment
Important column comments
Primary key
Required indexes
Status lifecycle if operational
created_at
updated_at
Owner metadata
Steward metadata
Classification metadata
Retention policy
Audit policy
Lineage entry
Data quality rules
External IDs where relevant
API exposure review
AI-use classification review

A.12. Priority implementation order

Recommended order:

Add comments to all foundational tables.
Create core_metadata.table_registry.
Create core_metadata.column_registry.
Create retention and audit policy tables.
Register eco.entities, eco.numbers, eco.entity_contacts, and core_dim.countries.
Register Academy learning tables.
Add classification to personal/company/supplier data.
Add lineage for HubSpot, Cognito, LearnWorlds, Academy, and ECO API.
Add audit triggers for high-value tables.
Add retention jobs and archival workflows.

A.13. Strategic importance for ZAYAZ

This metadata layer becomes the foundation for:

ESG auditability
CSRD evidence traceability
verifier workflows
supplier transparency
public ECO lookup confidence
data governance
safe AI use
multi-tenant access control
future white-label deployments
enterprise procurement acceptance

Without metadata governance, the platform risks becoming a collection of useful tables.

With metadata governance, it becomes a trusted sustainability intelligence infrastructure.

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​

APPENDIX A - Database Governance Metadata and Table Maintenance Standard​

A.1. Why this matters​

A.2. Recommended metadata model​

A.3. Ownership metadata​

A.4. Lineage metadata​

A.5. Steward metadata​

A.6. Classification and sensitivity metadata​

A.7. Audit policies​

A.8. Retention policies​

A.9. Additional table maintenance standards​

A.9.1 Table and column comments​

A.9.2 Naming standards​

A.9.3 Constraints​

A.9.4 Indexes​

A.9.5 Status lifecycle fields​

A.9.6 Soft delete vs hard delete​

A.9.7 Versioning​

A.9.8 Data quality rules​

A.9.9 External ID mapping​

A.9.10 Data lineage for AI use​

A.10. Suggested metadata tables​

A.10.1 core_metadata.table_registry​

A.10.2 core_metadata.column_registry​

A.10.3 core_metadata.retention_policies​

A.10.4 core_metadata.audit_policies​

A.11. Minimum checklist for every new production table​

A.12. Priority implementation order​

A.13. Strategic importance for ZAYAZ​

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

APPENDIX A - Database Governance Metadata and Table Maintenance Standard

A.1. Why this matters

A.2. Recommended metadata model

A.3. Ownership metadata

A.4. Lineage metadata

A.5. Steward metadata

A.6. Classification and sensitivity metadata

A.7. Audit policies

A.8. Retention policies

A.9. Additional table maintenance standards

A.9.1 Table and column comments

A.9.2 Naming standards

A.9.3 Constraints

A.9.4 Indexes

A.9.5 Status lifecycle fields

A.9.6 Soft delete vs hard delete

A.9.7 Versioning

A.9.8 Data quality rules

A.9.9 External ID mapping

A.9.10 Data lineage for AI use

A.10. Suggested metadata tables

A.10.1 `core_metadata.table_registry`

A.10.2 `core_metadata.column_registry`

A.10.3 `core_metadata.retention_policies`

A.10.4 `core_metadata.audit_policies`

A.11. Minimum checklist for every new production table

A.12. Priority implementation order

A.13. Strategic importance for ZAYAZ