Governed Identity Database Migration

1. Purpose

This migration specification classifies legacy physical identity vocabulary before any PostgreSQL rename or data transformation. It implements the ZPDV rule that existing fields are classified before migration and the UOM rule that generic entity and hierarchy semantics are not canonical domain identities.

The migration covers the generated schema catalogs:

config/database/zar-registry-db/catalog.json
config/database/zayaz-dev-aurora-pg/catalog.json

Generated catalogs are read-only evidence. They MUST NOT be hand-edited.

2. Non-negotiable Academy protection

The academy schema is operational and is explicitly excluded from this migration.

The migration tooling MAY inventory matching names under academy so that dependencies are visible, but it MUST NOT emit or execute DDL, DML, constraint changes, index changes, comments, table renames, column renames, backfills, triggers, views, or compatibility shims against any academy.* object.

This exclusion applies even when an Academy object contains tenant_id, entity_id, entity_type, parent_entity_id, or child_entity_id, or references an object outside the Academy schema.

Any future Academy migration requires a separate change set and explicit operational review.

3. Search surface

The inventory MUST search for these substrings, including occurrences embedded inside longer names:

tenant_id
entity_id
entity_type
parent_entity_id
child_entity_id

The search applies to relation names and comments, column names/comments/default expressions, constraint names/definitions, foreign-key source and target columns, and index names/definitions.

For example, idx_external_opcode_binding_registry_tenant_id is a migration candidate even though the legacy token is only one part of the index name.

Run the read-only inventory with:

node scripts/database/audit-governed-identity-migration.mjs
node scripts/database/audit-governed-identity-migration.mjs --json

4. Semantic classification matrix

Every non-Academy hit MUST be classified before DDL is authored.

Classification	Meaning	Typical physical target
eco_number	E-C-O family identity or governed organizational/business scope	eco_number or role-qualified *_eco_number where two E-C-O roles coexist
user_id	Individual ZAYAZ user	user_id or role-qualified user identifier
uom_number	Independently identified UOM-owned object	uom_number or role-qualified UOM identifier
uom_classification	UOM classification rather than identity	uom_type / uom_category_code
relationship	Graph endpoint or relationship assertion semantics	relationship_id, object_a, object_b, uom_relationship
local-id-keep	Local surrogate/domain identifier whose meaning is not a governed cross-domain identity	retain local identifier with explicit domain name/comment
external-provider-native	Provider-owned identifier	retain provider-qualified name, for example microsoft_entra_tenant_id
remove	Redundant or obsolete field after governed identity resolution	remove only through forward migration after dependency/backfill analysis
review-required	Catalog evidence is insufficient to determine semantics	no DDL until canonical owner is resolved
ACADEMY-PROTECTED	Any match in the Academy schema	inventory only; no mutation

tenant_id is never classified by mechanical substitution. A ZAYAZ-owned tenant_id may become an E-C-O scope, a user/UOM identity, a relationship/context binding, or disappear entirely depending on the canonical object represented.

5. UOM physical migration

The current UOM target model is:

core_metadata.uom_registry
core_metadata.uom_graph_nodes
core_relations.uom_relationships

The canonical mapping is:

Legacy semantic	Target semantic
UOM-owned entity_id	uom_number
E-C-O-backed organizational/business entity_id	eco_number
entity_type	uom_type and, where applicable, uom_category_code
parent_entity_id / child_entity_id	neutral object_a / object_b endpoints plus uom_relationship
generic graph surrogate	internal graph_node_id only when required for referential integrity; never a business identity

The graph-node projection preserves identity_namespace and identity_value. It must not mint a second business identity.

The migration MUST include dependent foreign keys, indexes, constraints, views, functions, triggers, application queries, engines, and documentation. Renaming only the three core tables is insufficient.

6. White-label identity model

White-label deployment isolation remains multi-tenant, but tenant_id is not a ZAYAZ business identity.

A White-Label Partner and each client are identified through their governed E-C-O family identities. The canonical API resource model is:

POST /v1/white-label-partners
GET  /v1/white-label-partners/{partner_eco_number}
POST /v1/white-label-partners/{partner_eco_number}/clients
GET  /v1/white-label-partners/{partner_eco_number}/clients/{client_eco_number}

Partner-to-client association is relationship/context semantics; it MUST NOT introduce a parallel tenant_id or client_id business-identity namespace.

7. Forward PostgreSQL migration requirements

Physical changes MUST be delivered as forward migrations. Applied historical migrations are immutable.

Before a destructive rename/drop, the migration design MUST establish:

1. semantic classification and canonical owner;
2. current PostgreSQL type and nullability;
3. data-shape/backfill strategy without exposing row data to repository artifacts;
4. incoming/outgoing foreign keys;
5. indexes, constraints, views, functions and triggers;
6. application/API/SDK consumers;
7. rollback or compatibility strategy;
8. PostgreSQL comments aligned to canonical semantics;
9. post-migration catalog regeneration and diff;
10. proof that no academy.* object is mutated.

Where the legacy physical type is incompatible with the governed identity representation, a column rename alone is prohibited. Use an explicit add/backfill/validate/cut-over/drop sequence or another reviewed online-migration pattern.

8. Validation gates

A database migration is not complete until all of the following are true:

• no unclassified non-Academy hit remains for the five legacy tokens;
• every emitted DDL statement is schema-qualified;
• an automated guard rejects any statement targeting academy;
• provider-native identifiers remain provider-qualified;
• UOM graph endpoints use neutral relationship semantics;
• E-C-O/UOM/user identities are not conflated;
• dependent code and canonical documentation use the same semantic vocabulary;
• generated database catalogs are regenerated from PostgreSQL after migration;
• PostgreSQL comments describe the governed semantics;
• ZPDV validation and documentation integrity checks pass.

9. Current governance defect

config/system/registry-manifest.json declares config/system/identifier-types.json as the canonical identifier-type registry, but that file is currently absent from the repository. This migration MUST NOT invent replacement identifier types to compensate. Existing canonical ZPDV/UOM semantics may be used; any new governed identifier type requiring that registry remains blocked until the registry defect is resolved.