semantics — new schema (agents-v2/v3 build, Phase 3 of 6)

14 logical tables, 114 columns — schema-locked 2026-07-14 (PROJECT_DECISIONS #64). semantics is a brand-new foundation-layer schema with no v1 antecedent (confirmed: v1 had no semantic-layer concept at all) — the shared business ontology the not-yet-built agents (Phase 5) and signals (Phase 4) modules will read from, rather than each inventing their own metric/goal/constraint vocabulary. It answers "what does a business fact (e.g. SKU margin) actually MEAN and how is it computed" and "what are a tenant's own priorities (e.g. protect cash over margin)" as real, queryable data instead of free text buried in an agent_task.goal_description string.

Design of record: vrida-agents-v2-design-amendment-2026-07-11.md (B1) as amended by vrida-agents-v3-correction-pass-2026-07-12.md (I2/I3/I4 — v3 wins on any conflict with v2). Migration: packages/db/migrations/20260714000000_semantics_new_schema.sql. Drizzle schema files: packages/db/src/schema/semantics/{_schema,registry,metric,entity,dimension,goal,attribution,index}.ts.

Groups: Function Registry (1 table: approved_function_registry) / Metric family (4: metric_definition, metric_version, tenant_metric_binding, metric_dependency) / Entity family (2: entity_definition, entity_alias) / Dimension (1: dimension_definition) / Goal & Constraint family (4: goal_definition, constraint_definition, tenant_goal_binding, tenant_constraint_binding) / Attribution family (2: attribution_model_definition, attribution_model_version).

PROJECT_DECISIONS entry: #64.

Global rules for this schema:

• Mixed tenant-scoping, sharply split. Exactly 3 of 14 tables (tenant_metric_binding, tenant_goal_binding, tenant_constraint_binding) carry tenant_id NOT NULL and RLS enabled with a permissive <table>_tenant_isolation policy (USING/WITH CHECK on current_setting('app.current_tenant_id')::uuid). The other 11 are global, non-tenant-scoped definition/catalog tables with no tenant_id column and no RLS at all — verified live: exactly 3 rows return true for pg_class.relrowsecurity in this schema.
• GRANT shape mirrors the RLS split exactly. The 11 global tables get GRANT SELECT only to authenticated plus an explicit REVOKE INSERT, UPDATE, DELETE — matching ai's own Phase 2 registry-table precedent (this is platform/service_role-authored ontology content; tenants read it, never write it). The 3 tenant-scoped binding tables get full SELECT, INSERT, UPDATE, DELETE (RLS does the isolation) — verified live via information_schema.role_table_grants.
• updated_at is trigger-maintained via platform.set_updated_at() on 7 of the 14 tables: metric_definition, tenant_metric_binding, entity_definition, goal_definition, constraint_definition, and — as of the Section 4 self-audit fix below — tenant_goal_binding/tenant_constraint_binding. entity_alias carries created_at only (write-once, no updated_at). The remaining 6 tables (approved_function_registry, metric_version, metric_dependency, dimension_definition, attribution_model_definition, attribution_model_version) carry no created_at/updated_at at all — the two version-row-family tables (metric_version, attribution_model_version) instead carry published_at/deprecated_at/retired_at (authorship is platform-only, no UPDATE grant to authenticated on either).
• No soft delete anywhere in this schema — no deleted_at column exists on any of the 14 tables. This is a purely definitional/ontology schema, not a transactional one; the Section 4 self-audit confirmed no nullable-tenant_id+UNIQUE combination and no partial-unique soft-delete trap exists to check for.
• No JSONB columns anywhere in this schema (Section 4 item J — N/A) and no money-derivation columns (Section 4 item I — N/A, not a transactional schema).
• Agent-as-actor attribution, continuing the canonical pattern. Every *_actor_id column (approved_function_registry.approved_by_actor_id, tenant_metric_binding.owner_actor_id, tenant_goal_binding.approved_by_actor_id, tenant_constraint_binding.approved_by_actor_id) targets identity.actor, the polymorphic root — never identity.identity_user directly.
• I2 approved-function-registry re-verification pattern — the load-bearing mechanism of this phase. See "Design Patterns Summary" below.
• EXCLUDE USING gist overlap prevention, HARD-REJECT on all 3 tenant-scoped binding tables. See "Design Patterns Summary" below.
• metric_dependency cycle detection is a real, firing trigger (semantics.check_metric_dependency_no_cycle(), BEFORE INSERT OR UPDATE, a recursive CTE walking the dependency graph) — a direct self-reference is separately rejected by a CHECK (chk_metric_dependency_no_self_reference), not the trigger. v3's own disclosed finding was that an earlier draft of this exact logic was dead code (no CREATE TRIGGER statement anywhere); this build confirmed the trigger is genuinely present and live-reproduced it firing.
• tenant_goal_binding.module_id/.site_id and tenant_constraint_binding.module_id/.site_id are deliberately BARE (no FK). See "Design Patterns Summary" below.
• Section 4 self-audit — 2 real GAPs found and fixed before lock, both same-pass. See the dedicated subsection below.
• A schema-usage GRANT gap found only by the regression test suite, not the read-only Section 4 pass. The migration's original draft never ran GRANT USAGE ON SCHEMA semantics TO authenticated — every prior new-schema migration in this codebase (ai, crm, approvals, returns, files, receiving, consumer/rewards/offers) does this explicitly, and this one silently omitted it. The RLS cross-tenant-isolation test failed with permission denied for schema semantics — table-level GRANTs are meaningless without schema-level USAGE. Fixed live and in the migration file (GRANT USAGE ON SCHEMA semantics TO authenticated; immediately after CREATE SCHEMA).
• No pre-migration audit needed — brand-new schema, zero pre-existing rows anywhere, not a reopen of a v1 table.

Cross-Phase / Cross-Module Foreign Keys (semantics)

Column	Target	Notes
approved_function_registry.approved_by_actor_id	identity.actor	NOT NULL.
metric_version.metric_definition_id, tenant_metric_binding.metric_definition_id, metric_dependency.metric_definition_id/.depends_on_metric_definition_id	semantics.metric_definition	All NOT NULL, intra-schema, enforced.
metric_version.resolved_function_reference	semantics.approved_function_registry	Nullable. The I2 pointer a metric version's expression resolves through — never raw SQL stored in a data row.
tenant_metric_binding.tenant_id	platform.tenant	NOT NULL.
tenant_metric_binding.metric_version_id	semantics.metric_version	NOT NULL.
tenant_metric_binding.owner_actor_id	identity.actor	Nullable.
entity_alias.entity_definition_id	semantics.entity_definition	NOT NULL, intra-schema.
goal_definition.applies_to_domain	platform.module_catalog	Nullable. First real consumer of module_catalog as an FK target outside platform itself (module_catalog built Phase 1, PROJECT_DECISIONS #62). Scopes a goal to a business domain/module, e.g. "protect cash" applying to purchasing/billing.
constraint_definition.goal_definition_id	semantics.goal_definition	Nullable — a constraint may exist independent of any named goal.
tenant_goal_binding.tenant_id	platform.tenant	NOT NULL.
tenant_goal_binding.goal_definition_id	semantics.goal_definition	NOT NULL.
tenant_goal_binding.approved_by_actor_id	identity.actor	Nullable.
tenant_goal_binding.module_id, .site_id	(none — deliberately bare)	Plain nullable uuid, NO FK. See "Design Patterns Summary" below.
tenant_constraint_binding.tenant_id	platform.tenant	NOT NULL.
tenant_constraint_binding.constraint_definition_id	semantics.constraint_definition	NOT NULL.
tenant_constraint_binding.approved_by_actor_id	identity.actor	Nullable.
tenant_constraint_binding.module_id, .site_id	(none — deliberately bare)	Plain nullable uuid, NO FK. Same disclosure as tenant_goal_binding above.
attribution_model_version.attribution_model_definition_id	semantics.attribution_model_definition	NOT NULL, intra-schema.
attribution_model_version.computation_reference	semantics.approved_function_registry	Nullable. Same I2 pointer pattern as metric_version.resolved_function_reference — signals.outcome_observation (not yet built) will FK to this table, replacing v2's original free-text attribution_method column.

---

semantics.approved_function_registry (11 cols)

The allowlist a metric/attribution-model VERSION's "expression" resolves through — a FUNCTION REFERENCE, never raw SQL stored in a data row. Every SQL text in this repo already goes through migration-file human review before it ever runs; this table just means a metric's expression is a POINTER to a function that itself lives in a normal, reviewed migration (Option 3, the architect's own lean).

Global, non-tenant-scoped catalog table. No RLS. No soft delete. No created_at/updated_at (has approved_at instead — a re-approval of a changed function is recorded as a distinct, new row's own act of registration, not an in-place edit tracked by a maintained timestamp).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
schema_name	text	NOT NULL	—	e.g. 'semantics'
function_name	text	NOT NULL	—
argument_signature	text	NOT NULL	—	MUST be bare types only (e.g. 'numeric, numeric'), never the parameter-name-included form a human would naturally copy from a CREATE FUNCTION statement — ::regprocedure casts require bare type lists and throw a hard syntax error otherwise. A REQUIRED registry-population discipline, deliberately NOT DB-enforced by a CHECK/regex (a wrong regex would be worse than none, silently rejecting valid bare-type signatures with unusual spacing)
return_type	text	NOT NULL	—
definition_hash	text	NOT NULL	—	md5(pg_get_functiondef(...)) of the function at approval time — the value verify_function_still_matches_approval() re-checks against on every call
owner_role	text	NOT NULL	—
security_mode	text	NOT NULL	—	CHECK IN (definer,invoker)
volatility	text	NOT NULL	—	CHECK IN (immutable,stable,volatile)
approved_at	timestamptz	NOT NULL	now()
approved_by_actor_id	UUID	NOT NULL	—	FK → identity.actor

CHECK constraints (verified live):

Name	Condition
chk_approved_function_registry_security_mode	security_mode IN ('definer','invoker')
chk_approved_function_registry_volatility	volatility IN ('immutable','stable','volatile')

Indexes: PK on id; approved_function_registry_identity_unique — UNIQUE btree (schema_name, function_name, argument_signature).

Functions owned by this table's own logic: semantics.verify_function_still_matches_approval(p_registry_id uuid) RETURNS boolean — re-resolves schema.function(bare_arg_types)::regprocedure fresh from stored TEXT columns on every call (never trusting a cached OID, which is not guaranteed stable across pg_dump/restore or major-version upgrades) and compares md5(pg_get_functiondef(...)) against the stored definition_hash. See "Design Patterns Summary" below for the full I2 mechanism.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE — only service_role/platform registers approved functions.

---

semantics.metric_definition (6 cols)

Standard A2b definition/version pattern — "what does SKU margin mean." The stable, versioned concept a metric_version row is one revision of.

Global, non-tenant-scoped catalog table. No RLS. No soft delete. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
metric_code	text	NOT NULL	—	UNIQUE
description	text	NOT NULL	—
lifecycle_status	text	NOT NULL	'planned'	CHECK IN (planned,designed,in_build,active,deprecated,retired)
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_metric_definition_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired'). This table's own CHECK was correctly built from the start — it is the pattern the 3 Section-4-gap tables below (dimension_definition, goal_definition, attribution_model_definition) should have matched from the start but initially didn't.

Indexes: PK on id; metric_definition_metric_code_unique — UNIQUE btree (metric_code).

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.metric_version (8 cols)

The versioned revision of a metric_definition — how it's actually computed. resolved_function_reference is a REAL FK into approved_function_registry (I2), not free text.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at — authorship is platform-only, authenticated gets no UPDATE on version rows (REVOKE, matching every other version-row family in this build).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
metric_definition_id	UUID	NOT NULL	—	FK → semantics.metric_definition
version	integer	NOT NULL	—	UNIQUE per (metric_definition_id)
resolved_function_reference	UUID	nullable	—	FK → semantics.approved_function_registry — the I2 pointer
status	text	NOT NULL	'draft'	CHECK IN (draft,published,deprecated,retired)
published_at	timestamptz	nullable	—
deprecated_at	timestamptz	nullable	—
retired_at	timestamptz	nullable	—

CHECK constraints: chk_metric_version_status — status IN ('draft','published','deprecated','retired').

Indexes: PK on id; metric_version_definition_version_unique — UNIQUE btree (metric_definition_id, version); metric_version_metric_definition_id_idx — plain index on metric_definition_id.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.tenant_metric_binding (12 cols)

Which metric_version is AUTHORITATIVE for a tenant, for a time range — a tenant's own choice of "which revision of SKU margin do I use, and since when." Overlap prevention reuses platform.accounting_period's own EXCLUDE USING gist precedent — but HARD-REJECT, not the flag-not-reject variant accounting_period pairs with. See "Design Patterns Summary" below.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy tenant_metric_binding_tenant_isolation on authenticated, USING/WITH CHECK against current_setting('app.current_tenant_id')::uuid. No soft delete. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
metric_definition_id	UUID	NOT NULL	—	FK → semantics.metric_definition
metric_version_id	UUID	NOT NULL	—	FK → semantics.metric_version
effective_from	timestamptz	NOT NULL	—
effective_to	timestamptz	nullable	—	NULL = open-ended
override_or_default	text	NOT NULL	—	CHECK IN (tier_default,tenant_override)
approval_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected)
owner_actor_id	UUID	nullable	—	FK → identity.actor
change_reason	text	nullable	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_tenant_metric_binding_override_or_default	override_or_default IN ('tier_default','tenant_override')
chk_tenant_metric_binding_approval_status	approval_status IN ('pending','approved','rejected')

Indexes: PK on id; tenant_metric_binding_tenant_id_idx; tenant_metric_binding_metric_definition_id_idx; excl_tenant_metric_binding_no_overlap — EXCLUDE USING gist (tenant_id WITH =, metric_definition_id WITH =, tstzrange(effective_from, effective_to, '[)') WITH &&).

Triggers:

1. set_updated_at (BEFORE UPDATE) — platform.set_updated_at(), shared/reused.
2. trg_tenant_metric_binding_verify_function (BEFORE INSERT OR UPDATE) — semantics.validate_metric_binding_function_hash(). Calls verify_function_still_matches_approval() against the referenced metric_version.resolved_function_reference on every binding write and rejects if the live function definition no longer matches what was approved. The I2 supply-chain-hole close — see "Design Patterns Summary" below.

Grants: GRANT SELECT, INSERT, UPDATE, DELETE to authenticated (RLS does the isolation).

---

semantics.metric_dependency (4 cols)

Which metrics depend on which other metrics (e.g. a ratio metric depends on its numerator and denominator metrics) — cycle detection via a real, firing trigger.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
metric_definition_id	UUID	NOT NULL	—	FK → semantics.metric_definition
depends_on_metric_definition_id	UUID	NOT NULL	—	FK → semantics.metric_definition
dependency_type	text	NOT NULL	—	CHECK IN (direct,derived,ratio_component)

CHECK constraints (verified live):

Name	Condition
chk_metric_dependency_type	dependency_type IN ('direct','derived','ratio_component')
chk_metric_dependency_no_self_reference	metric_definition_id != depends_on_metric_definition_id

Indexes: PK on id; metric_dependency_metric_depends_on_unique — UNIQUE btree (metric_definition_id, depends_on_metric_definition_id); metric_dependency_depends_on_metric_definition_id_idx — plain index on depends_on_metric_definition_id, added proactively for the reverse lookup a future dependent-check needs (Phase 1's own lesson about supporting reverse-FK lookups, applied here before an audit had to ask for it).

Triggers: trg_metric_dependency_no_cycle (BEFORE INSERT OR UPDATE) — semantics.check_metric_dependency_no_cycle(), a recursive CTE walking the dependency graph forward from the proposed depends_on_metric_definition_id and rejecting if it can reach back to metric_definition_id. See "Design Patterns Summary" below.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE — only service_role/platform writes the dependency graph.

---

semantics.entity_definition (6 cols)

Names a business entity concept (e.g. "customer") and its canonical table. Makes an already-existing ambiguity — e.g. crm.customer.consumer_id was deferred (no FK) when crm locked because consumer didn't exist yet, and the FK was never retrofitted after consumer was later built — a queryable, named fact instead of two unreconciled prose comments in two schema docs. Does not retrofit the FK itself — out of this phase's own scope.

Global, non-tenant-scoped catalog table. No RLS. No soft delete. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
entity_code	text	NOT NULL	—	UNIQUE
canonical_table_ref	text	NOT NULL	—	e.g. 'crm.customer' — a text seam, not an FK (points at a schema.table name, not a row)
description	text	NOT NULL	—
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

Indexes: PK on id; entity_definition_entity_code_unique — UNIQUE btree (entity_code).

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.entity_alias (5 cols)

An alternate table/view that represents the same (or a related) entity as its parent entity_definition — e.g. naming that consumer.consumer and crm.customer are same_as/related_to/subset_of one another.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, write-once — carries created_at only, no updated_at.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
entity_definition_id	UUID	NOT NULL	—	FK → semantics.entity_definition
alias_table_ref	text	NOT NULL	—	Text seam, not an FK
relationship_type	text	NOT NULL	—	CHECK IN (same_as,related_to,subset_of)
created_at	timestamptz	NOT NULL	now()

CHECK constraints: chk_entity_alias_relationship_type — relationship_type IN ('same_as','related_to','subset_of').

Indexes: PK on id; entity_alias_definition_alias_ref_unique — UNIQUE btree (entity_definition_id, alias_table_ref); entity_alias_entity_definition_id_idx — plain index on entity_definition_id.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.dimension_definition (5 cols)

Names a dimension (e.g. "site", "customer segment", "plant category") a metric can be sliced/grouped by.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at at all — the leanest table in this schema alongside attribution_model_definition.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
dimension_code	text	NOT NULL	—	UNIQUE
canonical_table_ref	text	nullable	—	Text seam, not an FK
description	text	NOT NULL	—
lifecycle_status	text	NOT NULL	'active'	CHECK IN (planned,designed,in_build,active,deprecated,retired) — Section 4 self-audit fix, added before lock (originally had the default but no CHECK at all)

CHECK constraints: chk_dimension_definition_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired').

Indexes: PK on id; dimension_definition_dimension_code_unique — UNIQUE btree (dimension_code).

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.goal_definition (8 cols)

So an agent_task.goal_description (free text in v1) can additionally carry a real goal_definition_id, letting agents read cash-protection/service-level/margin-floor priorities from data instead of prose.

Global, non-tenant-scoped catalog table. No RLS. No soft delete. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
goal_code	text	NOT NULL	—	Plain index, not UNIQUE — multiple goal-definition revisions may legitimately share a code lineage
description	text	NOT NULL	—
priority_order	integer	NOT NULL	—
applies_to_domain	UUID	nullable	—	FK → platform.module_catalog — scopes the goal to a business domain/module
lifecycle_status	text	NOT NULL	'active'	CHECK IN (planned,designed,in_build,active,deprecated,retired) — Section 4 self-audit fix, added before lock
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_goal_definition_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired').

Indexes: PK on id; goal_definition_goal_code_idx — plain index on goal_code.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.constraint_definition (7 cols)

A hard or soft limit associated with (optionally) a goal — e.g. "never let a single order discount exceed 30%" (hard) or "prefer vendors with on-time delivery > 90%" (soft).

Global, non-tenant-scoped catalog table. No RLS. No soft delete. updated_at: trigger-maintained via platform.set_updated_at().

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
constraint_code	text	NOT NULL	—	Plain index, not UNIQUE
description	text	NOT NULL	—
constraint_type	text	NOT NULL	—	CHECK IN (hard,soft)
goal_definition_id	UUID	nullable	—	FK → semantics.goal_definition — a constraint may exist independent of any named goal
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained via platform.set_updated_at()

CHECK constraints: chk_constraint_definition_type — constraint_type IN ('hard','soft').

Indexes: PK on id; constraint_definition_constraint_code_idx — plain index on constraint_code; constraint_definition_goal_definition_id_idx — plain index on goal_definition_id.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.tenant_goal_binding (15 cols)

Which goal_definition a tenant has bound, at what target value and priority, for a time range — same EXCLUDE USING gist overlap-prevention precedent as tenant_metric_binding (B1) / platform.accounting_period.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy tenant_goal_binding_tenant_isolation. No soft delete. updated_at: trigger-maintained via platform.set_updated_at() — Section 4 self-audit fix, added before lock (originally missing both created_at/updated_at and the trigger its sibling tenant_metric_binding already had).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
goal_definition_id	UUID	NOT NULL	—	FK → semantics.goal_definition
target_value	numeric	nullable	—
priority_order	integer	NOT NULL	—
effective_from	timestamptz	NOT NULL	—
effective_to	timestamptz	nullable	—	NULL = open-ended
module_id	UUID	nullable	—	Deliberately BARE, no FK. See "Design Patterns Summary" below
site_id	UUID	nullable	—	Deliberately BARE, no FK. See "Design Patterns Summary" below
mode	text	NOT NULL	—	CHECK IN (hard,soft)
approval_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected)
approved_by_actor_id	UUID	nullable	—	FK → identity.actor
version_ref	integer	NOT NULL	1
created_at	timestamptz	NOT NULL	now()	Section 4 self-audit fix, added before lock
updated_at	timestamptz	NOT NULL	now()	Section 4 self-audit fix, added before lock — trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_tenant_goal_binding_mode	mode IN ('hard','soft')
chk_tenant_goal_binding_approval_status	approval_status IN ('pending','approved','rejected')

Indexes: PK on id; tenant_goal_binding_tenant_id_idx; tenant_goal_binding_goal_definition_id_idx; excl_tenant_goal_binding_no_overlap — EXCLUDE USING gist (tenant_id WITH =, goal_definition_id WITH =, tstzrange(effective_from, effective_to, '[)') WITH &&).

Triggers: set_updated_at (BEFORE UPDATE) — platform.set_updated_at(), added by the Section 4 self-audit fix.

Grants: GRANT SELECT, INSERT, UPDATE, DELETE to authenticated (RLS does the isolation).

---

semantics.tenant_constraint_binding (15 cols)

The constraint_definition counterpart to tenant_goal_binding — identical shape, same EXCLUDE USING gist overlap-prevention pattern, same Section 4 self-audit fix.

Tenant-scoped. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy tenant_constraint_binding_tenant_isolation. No soft delete. updated_at: trigger-maintained via platform.set_updated_at() — Section 4 self-audit fix, added before lock, identical rationale to tenant_goal_binding above.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
constraint_definition_id	UUID	NOT NULL	—	FK → semantics.constraint_definition
target_value	numeric	nullable	—
priority_order	integer	NOT NULL	—
effective_from	timestamptz	NOT NULL	—
effective_to	timestamptz	nullable	—	NULL = open-ended
module_id	UUID	nullable	—	Deliberately BARE, no FK. See "Design Patterns Summary" below
site_id	UUID	nullable	—	Deliberately BARE, no FK. See "Design Patterns Summary" below
mode	text	NOT NULL	—	CHECK IN (hard,soft)
approval_status	text	NOT NULL	'pending'	CHECK IN (pending,approved,rejected)
approved_by_actor_id	UUID	nullable	—	FK → identity.actor
version_ref	integer	NOT NULL	1
created_at	timestamptz	NOT NULL	now()	Section 4 self-audit fix, added before lock
updated_at	timestamptz	NOT NULL	now()	Section 4 self-audit fix, added before lock — trigger-maintained via platform.set_updated_at()

CHECK constraints (verified live):

Name	Condition
chk_tenant_constraint_binding_mode	mode IN ('hard','soft')
chk_tenant_constraint_binding_approval_status	approval_status IN ('pending','approved','rejected')

Indexes: PK on id; tenant_constraint_binding_tenant_id_idx; tenant_constraint_binding_constraint_definition_id_idx; excl_tenant_constraint_binding_no_overlap — EXCLUDE USING gist (tenant_id WITH =, constraint_definition_id WITH =, tstzrange(effective_from, effective_to, '[)') WITH &&).

Triggers: set_updated_at (BEFORE UPDATE) — platform.set_updated_at(), added by the Section 4 self-audit fix.

Grants: GRANT SELECT, INSERT, UPDATE, DELETE to authenticated (RLS does the isolation).

---

semantics.attribution_model_definition (4 cols)

A methodological definition alongside metric_definition — "how do we interpret a business fact" (e.g. last-touch vs. linear vs. time-decay attribution) rather than "what does it mean."

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at — the leanest table in this schema alongside dimension_definition.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
model_code	text	NOT NULL	—	UNIQUE
description	text	NOT NULL	—
lifecycle_status	text	NOT NULL	'planned'	CHECK IN (planned,designed,in_build,active,deprecated,retired) — Section 4 self-audit fix, added before lock

CHECK constraints: chk_attribution_model_definition_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired').

Indexes: PK on id; attribution_model_definition_model_code_unique — UNIQUE btree (model_code).

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics.attribution_model_version (8 cols)

The versioned revision of an attribution_model_definition. computation_reference is the same I2 pointer pattern as metric_version.resolved_function_reference — a real FK into approved_function_registry, not free text. signals.outcome_observation (not yet built, Phase 4) is expected to FK here, replacing v2's original free-text attribution_method column.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at — same version-row-family shape as metric_version.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
attribution_model_definition_id	UUID	NOT NULL	—	FK → semantics.attribution_model_definition
version	integer	NOT NULL	—	UNIQUE per (attribution_model_definition_id)
computation_reference	UUID	nullable	—	FK → semantics.approved_function_registry
status	text	NOT NULL	—	CHECK IN (draft,published,deprecated,retired) — no default (unlike metric_version.status's DEFAULT 'draft'), every row must state its status explicitly
published_at	timestamptz	nullable	—
deprecated_at	timestamptz	nullable	—
retired_at	timestamptz	nullable	—

CHECK constraints: chk_attribution_model_version_status — status IN ('draft','published','deprecated','retired').

Indexes: PK on id; attribution_model_version_definition_version_unique — UNIQUE btree (attribution_model_definition_id, version); attribution_model_version_definition_id_idx — plain index on attribution_model_definition_id.

Grants: GRANT SELECT to authenticated; REVOKE INSERT, UPDATE, DELETE.

---

semantics — Design Patterns Summary

The I2 approved-function-registry re-verification pattern

approved_function_registry is not a simple allowlist that trusts its own row forever. Once a metric_version.resolved_function_reference (or attribution_model_version.computation_reference) points at a registry row, semantics.verify_function_still_matches_approval(p_registry_id uuid) re-resolves schema.function(bare_arg_types)::regprocedure fresh, from the stored TEXT columns, on every call — never from a cached OID, which is not guaranteed stable across pg_dump/restore or major-version upgrades — and recomputes md5(pg_get_functiondef(...)) against the stored definition_hash. A dedicated trigger, semantics.validate_metric_binding_function_hash() (BEFORE INSERT OR UPDATE on tenant_metric_binding — trg_tenant_metric_binding_verify_function), calls this on every binding write and rejects the write if the live function definition no longer matches what was approved.

This closes a real supply-chain hole: "approve a function, quietly redefine it later via CREATE OR REPLACE FUNCTION, existing and new bindings keep trusting it" — without this check, a metric's computation could be silently swapped out from under every tenant binding it, without touching semantics at all. Live-reproduced during the build: a scratch function (SELECT $1 + $2) was registered and hashed; a tenant_metric_binding referencing it via metric_version.resolved_function_reference succeeded; the function was then redefined (SELECT $1 * $2) via CREATE OR REPLACE FUNCTION; verify_function_still_matches_approval() immediately flipped to false; a NEW binding write against the same (now-tampered) metric_version was correctly REJECTED with "references a function whose live definition no longer matches its approved hash".

Implementation detail confirmed critical during the build: argument_signature must store BARE TYPES ONLY ('numeric, numeric'), never the parameter-name-included form a human would naturally copy out of a CREATE FUNCTION statement — ::regprocedure throws a hard syntax error on the latter form. This is a required registry-population discipline, deliberately not DB-enforced by a CHECK/regex (a wrong regex would silently reject valid bare-type signatures with unusual spacing — worse than no guard at all).

The EXCLUDE USING gist hard-reject overlap pattern, contrasted with platform.accounting_period's flag-not-reject

tenant_metric_binding, tenant_goal_binding, and tenant_constraint_binding all use EXCLUDE USING gist (tenant_id WITH =, <definition>_id WITH =, tstzrange(effective_from, effective_to, '[)') WITH &&) — reusing platform.accounting_period's own precedent of preventing two overlapping date ranges for the same key (btree_gist already installed in this database from that earlier build).

The genuine, disclosed departure is the failure mode: accounting_period pairs its own EXCLUDE with a FLAG-NOT-REJECT trigger (flag_closed_period_business_date()) on pos.sale/sale_refund/register_cash_entry, because offline-sync needs a late-arriving sale to still land even if it falls inside an already-closed fiscal period — rejecting the write outright would break POS's offline-first contract. The 3 semantics binding tables have no such reason to be lenient: setting an authoritative metric/goal/constraint binding is an online, synchronous admin action, not a device replaying a queued offline transaction. So all 3 EXCLUDE constraints here are genuine HARD-REJECT — the write simply fails, no flag-and-continue path exists or is needed.

Live-reproduced for all 3 tables: a first binding for a (tenant, definition) pair over [2026-01-01, 2026-06-01) succeeds; an overlapping second [2026-03-01, 2026-09-01) is rejected by the table's own excl_*_no_overlap constraint; a non-overlapping, adjacent third [2026-06-01, ∞) succeeds.

The deliberately-bare module_id/site_id disclosure

tenant_goal_binding.module_id/.site_id and tenant_constraint_binding.module_id/.site_id are plain uuid columns with no REFERENCES clause — confirmed directly against the migration's own literal DDL, not inferred. This is a genuine judgment call, not an oversight or a deferred-FK debt of the ordinary kind this codebase usually logs (e.g. ai.routing_policy.workload_class_id, which is bare because its target, agents.workload_class, genuinely doesn't exist yet).

Here, platform.module_catalog already exists (built Phase 1, PROJECT_DECISIONS #62) — wiring module_id → platform.module_catalog(id) is technically possible today. But the design of record (v3's own literal SQL) does not specify doing so, and no reconciliation-table row in the design names these columns as intended FKs. The build's own Section 4 self-audit deliberately left them exactly as specified rather than over-interpreting the design — a site_id composite-FK wiring (mirroring identity.user_site_assignment.site_id's own precedent, PROJECT_DECISIONS #54) was never even attempted, since no such prerequisite check was run for this column either. This is disclosed, not silently fixed and not silently ignored — logged to docs/open-items/OPEN_ITEMS.md (semantics | FK) with an explicit trigger: "if a future reopen decides these SHOULD be real FKs — module_id → platform.module_catalog(id) is immediately wireable today; site_id → multi_loc.site(id, tenant_id) needs the same composite-FK treatment already applied to identity.user_site_assignment.site_id."

Section 4 self-audit — 2 real GAPs found and fixed before lock

1. [FIXED, item F — CHECK completeness] 3 lifecycle_status columns (dimension_definition, goal_definition, attribution_model_definition) carried an enum-like default ('active'/'planned') but had no CHECK constraint at all — the exact "CHECK completeness" trap this runbook's own Section 4 checklist exists to catch, and a bug class this build had already hit once elsewhere in the same schema (metric_definition's own CHECK, which was built correctly from the start and became the template the 3 gap tables should have matched). Fixed: all 3 now carry chk_<table>_lifecycle_status CHECK (lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired')), matching metric_definition's own vocabulary. Live-reproduced rejecting an invalid value ('bogus') on all 3 tables before lock.
2. [FIXED, item T — trigger/structural consistency] tenant_metric_binding has created_at/updated_at plus a maintaining set_updated_at trigger; the sibling tenant_goal_binding/tenant_constraint_binding (same I3 binding pattern, same mutable approval_status lifecycle) had neither — an asymmetry with no disclosed rationale, found by comparing the 3 binding tables' own shapes against each other rather than against the design doc text alone. Since this is a brand-new schema with zero live rows anywhere (confirmed via SELECT count(*) before the fix), the safer and more disciplined choice was to fix the inconsistency immediately rather than defer it to OPEN_ITEMS.md — both tables now carry created_at/updated_at plus the same set_updated_at trigger tenant_metric_binding already had. Column count grew 110 → 114 (+4) as a direct result of this fix; the regression suite's own count assertion carries an explanatory comment referencing this exact fix.

All other A–P+T+U Section 4 items PASS or N/A: no nullable-tenant_id+UNIQUE combinations (all 3 tenant-scoped tables have tenant_id NOT NULL); no soft-delete columns anywhere (no partial-unique traps); no JSONB columns at all (item J N/A); no money-derivation columns (item I N/A — a purely definitional/ontology schema, not transactional); tenant_id present as a leading index column on all 3 tenant-scoped tables; the reverse-lookup index on metric_dependency.depends_on_metric_definition_id was added proactively; all cross-schema FK targets (platform.tenant, platform.module_catalog, identity.actor) verified against the actual live table names. Non-RLS access control (item O) is documented via migration comments + GRANT SELECT-only to authenticated on all 11 global catalog tables — matching ai's own registry-table precedent from Phase 2. Item U (Design-Phase Integrity 4-block requirement) is N/A — brand-new schema with no v1 predecessor to diff against.

Live-reproduced guards (3 named, all via real psql sessions against the live dev DB)

• EXCLUDE overlap rejection — see above, all 3 binding tables.
• I2 supply-chain-hole guard — see above.
• metric_dependency cycle detection — A depends_on B, B depends_on C both succeed; C depends_on A (would close the cycle) is rejected by the trigger, not a CHECK, with the message "metric_dependency: <A> -> <C> would create a cycle". A direct self-reference (A depends_on A) is separately rejected by chk_metric_dependency_no_self_reference.

Column-count reconciliation

Table	Cols
approved_function_registry	11
metric_definition	6
metric_version	8
tenant_metric_binding	12
metric_dependency	4
entity_definition	6
entity_alias	5
dimension_definition	5
goal_definition	8
constraint_definition	7
tenant_goal_binding	15
tenant_constraint_binding	15
attribution_model_definition	4
attribution_model_version	8
Total	114

Verified live via information_schema.columns, schema semantics: 14 tables, 114 columns — matches the regression suite's own (A2) count assertion exactly (110 base + 4 from the Section 4 self-audit fix adding created_at/updated_at to tenant_goal_binding/tenant_constraint_binding).

Service layer

No SemanticsService yet — schema-only this phase, matching every other phase of this build (every phase stays schema-only until Phase 5 lands the agents module itself). Downstream code would query semantics.* directly via Drizzle until one exists.

Regression tests

apps/api/src/platform/__tests__/semantics-schema.spec.ts — 19/19 passing. Run with:

cd apps/api && npx jest src/platform/__tests__/semantics-schema.spec.ts --forceExit

Section	Covers	Tests
A	Table existence (exactly 14), column count (exactly 114), RLS shape (exactly the 3 tenant-scoped binding tables)	3
B	tenant_metric_binding EXCLUDE overlap — first succeeds, overlapping rejected, adjacent non-overlapping succeeds	3
C	I2 supply-chain-hole guard — register, verify true, redefine, verify false, new binding write rejected	4
D	metric_dependency cycle detection — A→B succeeds, B→C succeeds, C→A rejected, self-reference rejected	4
E	tenant_goal_binding / tenant_constraint_binding EXCLUDE overlap	2
F	RLS cross-tenant isolation on tenant_metric_binding	1
G	GRANT shape — 11 global tables SELECT-only, 3 binding tables full CRUD	2
Total		19

A companion stale-assertion fix landed in the pre-existing platform-module-registry.spec.ts (G3): Phase 1 had asserted agents/semantics/signals all stay 'designed' in platform.module_catalog — now that semantics has landed, this migration correctly bumps its lifecycle_status to 'in_build', so the test was updated to assert agents/signals stay 'designed' while semantics is 'in_build', rather than loosening or deleting the original assertion.

Full apps/api suite green: 1150/1150, typecheck clean on packages/db and apps/api.

Open items carried forward

tenant_goal_binding.module_id/.site_id and tenant_constraint_binding.module_id/.site_id stay deliberately bare — see "Design Patterns Summary" above and docs/open-items/OPEN_ITEMS.md (semantics | FK). No SemanticsService exists yet — the accepted-function-registry population workflow, the metric/goal/constraint binding approval flow, and any future agents/signals consumption are all still to be designed at the service layer.