signals — new schema (agents-v2/v3 build, Phase 4 of 6, HIGHEST RISK)

11 logical tables, 111 columns — schema-locked 2026-07-15 (PROJECT_DECISIONS #65). signals is a brand-new foundation-layer schema with no v1 antecedent — the feature/forecast/outcome store the not-yet-built agents module (Phase 5) will read from and write to, with bitemporal reads enforced at the function level rather than left to caller discipline. This is the highest-risk phase of the 6-phase agents-v2/v3 build authorization (Phase 1 platform, PROJECT_DECISIONS #62; Phase 2 ai, PROJECT_DECISIONS #63; Phase 3 semantics, PROJECT_DECISIONS #64; this is Phase 4), per the build's own explicit designation — it combines 3 mechanisms none of the prior 3 phases combined in one schema: PARTITION BY RANGE, a split-authority pattern for an invariant a native partitioned index cannot express, and SECURITY DEFINER tenant-isolation functions.

signals is observational, high-volume, bitemporal, append-only, partitioned — the opposite operational profile from semantics (definitional, low-volume, human-curated, zero partitioning). Design of record: vrida-agents-v2-design-amendment-2026-07-11.md (B2/B3/B4/B5/B6/A6) as amended by vrida-agents-v3-correction-pass-2026-07-12.md (BLOCKER 1/2/3/8, v3 wins on conflict). Migration: packages/db/migrations/20260715000000_signals_new_schema.sql. Drizzle schema files: packages/db/src/schema/signals/{_schema,feature,forecast,anomaly,outcome,experiment,index}.ts.

Groups: Feature/Experiment catalog family (4 tables, standard A2b definition/version pattern: feature_definition, feature_version, experiment, experiment_version) / Bitemporal value family — PARTITIONED weekly by recorded_at (3 tables: feature_value, forecast, anomaly_score) / Experiment causal-inference family — NOT partitioned (2 tables: experiment_assignment, experiment_exposure_event) / Outcome split-authority family (2 tables: outcome_observation — PARTITIONED monthly by created_at — and outcome_authority — deliberately NOT partitioned).

PROJECT_DECISIONS entry: #65.

Which tables are partitioned

Table	Partitioned?	Strategy
feature_value	YES	PARTITION BY RANGE (recorded_at), weekly — 12 weekly partitions (2026-06-29 through 2026-09-21) + 1 DEFAULT catch-all = 13 partitions
forecast	YES	Same shape as feature_value — 13 partitions
anomaly_score	YES	Same shape as feature_value — 13 partitions
outcome_observation	YES	PARTITION BY RANGE (created_at), monthly — 13 monthly partitions (2026-06 through 2027-06) + 1 DEFAULT catch-all = 14 partitions
outcome_authority	NO	Deliberately small, unpartitioned — the split-authority pattern (see "Design Patterns Summary" below)
feature_definition / feature_version / experiment / experiment_version / experiment_assignment / experiment_exposure_event	NO	Standard catalog/transactional tables, native PRIMARY KEY

The partition key column (recorded_at or created_at) is required in every UNIQUE/PRIMARY KEY constraint on the 4 partitioned tables — Postgres's own structural rule. All 4 partitioned tables therefore carry no PRIMARY KEY at all, only a UNIQUE(id, tenant_id, <partition_key>) composite constraint, which is the identity shape any FK into them must resolve against (outcome_authority's own FK into outcome_observation uses exactly this shape).

Partition-GRANT independence (Phase 2's own hard-won lesson, applied proactively here). Postgres GRANTs and ALTER DEFAULT PRIVILEGES on a partitioned PARENT table do not propagate to child partitions — each partition gets its own copy of ALTER DEFAULT PRIVILEGES IN SCHEMA signals GRANT SELECT ON TABLES TO authenticated at CREATE TABLE time, independent of whatever REVOKE the parent later receives. This migration closes that gap immediately rather than leaving it for a later audit to find (as Phase 2's own ai.agent_execution/agent_memory partitioning did): a pg_inherits-driven DO block runs REVOKE ALL ON signals.<partition> FROM authenticated against every feature_value/forecast/anomaly_score partition right after creation, and a second block re-applies outcome_observation's own exact access shape (REVOKE ALL then GRANT SELECT, INSERT then the scoped UPDATE grant) to every one of its own partitions individually.

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

Column	Target	Notes
feature_value.tenant_id, forecast.tenant_id, anomaly_score.tenant_id, experiment_assignment.tenant_id, experiment_exposure_event.tenant_id, outcome_observation.tenant_id, outcome_authority.tenant_id	platform.tenant	NOT NULL on all 7 tenant-scoped tables, enforced FK.
feature_version.feature_definition_id	signals.feature_definition	NOT NULL, intra-schema.
feature_value.feature_version_id, forecast.feature_version_id, anomaly_score.feature_version_id	signals.feature_version	NOT NULL, intra-schema, all 3 bitemporal value tables.
experiment_version.experiment_id	signals.experiment	NOT NULL, intra-schema.
experiment_assignment.experiment_version_id	signals.experiment_version	NOT NULL, intra-schema.
experiment_exposure_event.experiment_assignment_id + .tenant_id	signals.experiment_assignment (id, tenant_id)	NOT NULL, composite FK (experiment_exposure_event_assignment_id_tenant_fkey) — the structural first half of the assigned_at <= first_eligible_exposure_at invariant: an exposure event cannot exist without a pre-existing, already-committed assignment row.
outcome_observation.attribution_model_version_id	semantics.attribution_model_version	NOT NULL. Real, enforced cross-schema FK — replaces v2's original free-text attribution_method column, exactly as semantics' own doc (Phase 3) predicted this table would do.
outcome_observation.experiment_assignment_id	signals.experiment_assignment	Nullable, intra-schema — links an outcome measurement back to the experiment assignment (if any) that produced it.
outcome_authority.experiment_assignment_id	signals.experiment_assignment	Nullable, intra-schema.
outcome_authority.authoritative_observation_id + .tenant_id + .authoritative_observation_created_at	signals.outcome_observation (id, tenant_id, created_at)	NOT NULL, composite FK (fk_outcome_authority_observation) — the split-authority pattern's own structural link back to the one observation row it currently trusts.
outcome_observation.agent_action_id, outcome_authority.agent_action_id	(none — DISCLOSED FORWARD-REF)	Plain uuid NOT NULL, no FK. agents.agent_action does not exist yet — confirmed live: zero tables in the agents schema as of this phase (Phase 5 builds it). Logged to docs/open-items/OPEN_ITEMS.md (signals | FK) — wired as a real composite FK when Phase 5 lands.
outcome_observation.supersedes_observation_id	(none — informational lineage only, no FK)	Nullable uuid. A self-referencing composite FK into a partitioned table would need the identical shadow-column treatment outcome_authority.authoritative_observation_id already carries, for a column that was never the enforcement mechanism — deliberately not done (v2's own A6 correction, restated by BLOCKER 2).
experiment_assignment.exposure_event_id	(none — informational pointer, no FK)	Nullable uuid, set by update_first_eligible_exposure() to the exposure event currently treated as first-eligible. Not an FK — the trigger is the only writer.

---

signals.feature_definition (6 cols)

Standard A2b definition/version pattern — the stable, versioned concept a feature_version row is one revision of. Global catalog, platform/service_role-managed.

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
feature_code	text	NOT NULL	—	UNIQUE
description	text	NOT NULL	—
lifecycle_status	text	NOT NULL	'active'	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_feature_definition_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired').

Indexes: PK on id; feature_definition_feature_code_unique — UNIQUE btree (feature_code).

Triggers: set_updated_at (BEFORE UPDATE) — platform.set_updated_at(), shared/reused.

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

---

signals.feature_version (7 cols)

The versioned revision of a feature_definition — how the feature is actually computed.

Global, non-tenant-scoped catalog table. No RLS. No soft delete, no created_at/updated_at — authorship is platform-only, matching every other version-row family in this build.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
feature_definition_id	UUID	NOT NULL	—	FK → signals.feature_definition
version	integer	NOT NULL	—	UNIQUE per (feature_definition_id)
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_feature_version_status — status IN ('draft','published','deprecated','retired').

Indexes: PK on id; feature_version_definition_version_unique — UNIQUE btree (feature_definition_id, version); feature_version_feature_definition_id_idx — plain index on feature_definition_id.

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

---

signals.experiment (6 cols)

Standard A2b pattern, mirrors feature_definition exactly — the stable, versioned concept an experiment_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
experiment_code	text	NOT NULL	—	UNIQUE
description	text	NOT NULL	—
lifecycle_status	text	NOT NULL	'active'	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_experiment_lifecycle_status — lifecycle_status IN ('planned','designed','in_build','active','deprecated','retired').

Indexes: PK on id; experiment_experiment_code_unique — UNIQUE btree (experiment_code).

Triggers: set_updated_at (BEFORE UPDATE) — platform.set_updated_at().

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

---

signals.experiment_version (9 cols)

The versioned revision of an experiment — the immutable treatment logic. Carries B4's own 2 required-decision columns: allows_reentry (default false, the safer default per B4's own rationale) and washout_days (nullable, service-layer-enforced).

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
experiment_id	UUID	NOT NULL	—	FK → signals.experiment
version	integer	NOT NULL	—	UNIQUE per (experiment_id)
status	text	NOT NULL	'draft'	CHECK IN (draft,published,deprecated,retired)
allows_reentry	boolean	NOT NULL	false	B4's own required decision — may a subject be re-randomized into this experiment
washout_days	integer	nullable	—	B4's own required decision — service-layer-enforced, no DB mechanism
published_at	timestamptz	nullable	—
deprecated_at	timestamptz	nullable	—
retired_at	timestamptz	nullable	—

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

Indexes: PK on id; experiment_version_experiment_version_unique — UNIQUE btree (experiment_id, version); experiment_version_experiment_id_idx — plain index on experiment_id.

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

---

signals.feature_value (10 cols) — PARTITIONED

Bitemporal, append-only feature-value store. as_of = business truth time; recorded_at = ERP knowledge time (the partition key — B5's own "knowledge time is sacred" rule means queries filtering recorded_at <= knowledge_cutoff are the hot query path). entity_type/entity_ref are a polymorphic, no-FK pointer, the same precedent ai.agent_execution.target_module/target_row_id already established.

Tenant-scoped, PARTITIONED BY RANGE(recorded_at), weekly. tenant_id NOT NULL FK → platform.tenant. No PRIMARY KEY — UNIQUE(id, tenant_id, recorded_at) is the identity constraint (Postgres requires the partition key in the constraint). REVOKE ALL from authenticated (B3 rule 1) — the ONLY sanctioned read path is signals.get_feature_as_of() (SECURITY DEFINER, owned by the dedicated signals_function_owner role — see "Design Patterns Summary" below). No RLS on this table — the as-of function's own platform.current_tenant_id()-derived WHERE clause is the actual tenant boundary; REVOKE ALL already fully closes direct access for authenticated, so RLS would need signals_function_owner to bypass it for no added guarantee. Disclosed as a deliberate choice (Section 4 item O), not an oversight. Written by an elevated (non-authenticated) service connection — matching every other backend-computed, non-tenant-writable table in this codebase.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	Part of the identity UNIQUE constraint, no standalone PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
entity_type	text	NOT NULL	—	Polymorphic, no FK
entity_ref	UUID	NOT NULL	—	Polymorphic, no FK
feature_version_id	UUID	NOT NULL	—	FK → signals.feature_version
value	numeric	nullable	—
as_of	timestamptz	NOT NULL	—	Business-truth time
recorded_at	timestamptz	NOT NULL	now()	Partition key — ERP knowledge time
computed_by	text	nullable	—
lineage	jsonb	nullable	—	Example shape: {"source_table": "pos.sale_line", "source_row_ids": ["<uuid>"], "computation_run_id": "<uuid>", "inputs": {"lookback_days": 30}} — added during the Section 4 self-audit fix (item J)

Constraints: feature_value_id_tenant_recorded_unique — UNIQUE (id, tenant_id, recorded_at).

Indexes: feature_value_tenant_entity_idx — composite on (tenant_id, entity_type, entity_ref); feature_value_feature_version_id_idx — plain index on feature_version_id.

Partitions: 12 weekly (2026-06-29 through 2026-09-21) + 1 DEFAULT = 13 total. Each partition individually REVOKE ALL FROM authenticated (partition-GRANT independence).

Grants: REVOKE ALL ON signals.feature_value FROM authenticated (parent + every partition).

---

signals.forecast (11 cols) — PARTITIONED

Mirrors feature_value's exact shape plus confidence_interval (a Postgres numrange, via a customType — the same pattern inventory.item's own tsvector column established, since Drizzle has no first-class numrange column builder). Same REVOKE ALL / function-only-read / no-RLS treatment as feature_value.

Tenant-scoped, PARTITIONED BY RANGE(recorded_at), weekly. Same access-control shape as feature_value — see that table's own header for the full rationale.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	Part of the identity UNIQUE constraint, no standalone PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
entity_type	text	NOT NULL	—	Polymorphic, no FK
entity_ref	UUID	NOT NULL	—	Polymorphic, no FK
feature_version_id	UUID	NOT NULL	—	FK → signals.feature_version
value	numeric	nullable	—
confidence_interval	numrange	nullable	—	Postgres numrange, Drizzle customType
as_of	timestamptz	NOT NULL	—	Business-truth time
recorded_at	timestamptz	NOT NULL	now()	Partition key
computed_by	text	nullable	—
lineage	jsonb	nullable	—	Example shape: {"model_name": "prophet_v2", "model_run_id": "<uuid>", "training_window_days": 90, "feature_inputs": ["<feature_version_id>"]} — Section 4 self-audit fix (item J)

Constraints: forecast_id_tenant_recorded_unique — UNIQUE (id, tenant_id, recorded_at).

Indexes: forecast_tenant_entity_idx; forecast_feature_version_id_idx.

Partitions: 12 weekly + 1 DEFAULT = 13 total, same shape as feature_value.

Grants: REVOKE ALL ON signals.forecast FROM authenticated (parent + every partition).

---

signals.anomaly_score (10 cols) — PARTITIONED

Mirrors feature_value's shape (no confidence_interval). Leaf table — nothing FKs into it. Corrected from the design doc's own literal reconciliation-appendix row, which specified this table's uniqueness as (id, tenant_id) — live-verified during this build that Postgres rejects any UNIQUE/PK on a partitioned table omitting the partition key (the exact class of error BLOCKER 2 itself documents for outcome_observation.is_authoritative, just not caught by that section's own adversarial pass for this sibling table). Corrected to UNIQUE(id, tenant_id, recorded_at), matching feature_value/forecast's own already-correct shape, even though nothing currently FKs into anomaly_score — for structural consistency, and because Postgres requires it regardless.

Tenant-scoped, PARTITIONED BY RANGE(recorded_at), weekly. Same access-control shape as feature_value.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	Part of the identity UNIQUE constraint, no standalone PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
entity_type	text	NOT NULL	—	Polymorphic, no FK
entity_ref	UUID	NOT NULL	—	Polymorphic, no FK
feature_version_id	UUID	NOT NULL	—	FK → signals.feature_version
value	numeric	nullable	—
as_of	timestamptz	NOT NULL	—	Business-truth time
recorded_at	timestamptz	NOT NULL	now()	Partition key
computed_by	text	nullable	—
lineage	jsonb	nullable	—	Example shape: {"detector": "isolation_forest_v1", "threshold": 0.95, "baseline_window_days": 30, "contributing_features": ["<feature_version_id>"]} — Section 4 self-audit fix (item J)

Constraints: anomaly_score_id_tenant_recorded_unique — UNIQUE (id, tenant_id, recorded_at). Corrected shape — see note above.

Indexes: anomaly_score_tenant_entity_idx; anomaly_score_feature_version_id_idx.

Partitions: 12 weekly + 1 DEFAULT = 13 total.

Grants: REVOKE ALL ON signals.anomaly_score FROM authenticated (parent + every partition).

---

signals.experiment_assignment (19 cols) — NOT partitioned

Which subject got which treatment arm, at what randomization probability, and what its causal-inference state is. subject_type/subject_ref resolve a genuine internal inconsistency in the design doc itself (its own column-list prose named the column assignment_unit, while its own "Uniqueness" line specified subject_type, subject_ref) — treated as the same concept, named per the more specific, typed, structurally-consistent form, matching this codebase's established polymorphic-pointer convention (feature_value.entity_type/entity_ref, ai.agent_execution.target_module/target_row_id). A disclosed judgment call, not an oversight.

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

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	gen_random_uuid()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
experiment_version_id	UUID	NOT NULL	—	FK → signals.experiment_version
subject_type	text	NOT NULL	—	Polymorphic, resolves v2's own naming inconsistency — see above
subject_ref	UUID	NOT NULL	—	Polymorphic
randomization_key	text	NOT NULL	—
hash_salt	text	nullable	—
randomization_version	integer	nullable	—
treatment_arm	text	NOT NULL	—
assignment_probability	numeric(5,4)	nullable	—
stratification_attributes	jsonb	nullable	—	Example shape: {"tenant_tier": "pro", "region": "us-east", "account_age_bucket": "1-2yr"} — the covariates randomization was stratified against
eligibility_evaluated_at	timestamptz	nullable	—
first_eligible_exposure_at	timestamptz	nullable	—	Maintained exclusively by update_first_eligible_exposure() — see "Design Patterns Summary"
exposure_event_id	UUID	nullable	—	Informational pointer, no FK — set by the same trigger
exclusion_reason	text	nullable	—
contamination_status	text	NOT NULL	'clean'	CHECK IN (clean,concurrent_experiment,recent_exposure,washout_active,exposure_revised_post_lock)
assignment_hash	text	nullable	—
assigned_at	timestamptz	NOT NULL	now()	Unconditionally overwritten by force_assignment_timestamp() on every INSERT — the column default is cosmetic, never actually reached once the trigger fires
causal_basis_locked_at	timestamptz	nullable	—	Set exactly once, by lock_experiment_causal_basis() — see disclosure below

CHECK constraints: chk_experiment_assignment_contamination_status — contamination_status IN ('clean','concurrent_experiment','recent_exposure','washout_active','exposure_revised_post_lock').

Indexes: PK on id; experiment_assignment_tenant_id_idx; experiment_assignment_experiment_version_id_idx; experiment_assignment_id_tenant_unique — UNIQUE (id, tenant_id), the composite-identity shape experiment_exposure_event's own FK resolves against; experiment_assignment_version_subject_unique — UNIQUE (tenant_id, experiment_version_id, subject_type, subject_ref) — at most one assignment per subject per experiment version.

Triggers: trg_experiment_assignment_force_timestamp (BEFORE INSERT) — signals.force_assignment_timestamp(), overwrites NEW.assigned_at := clock_timestamp() unconditionally (GUARD 3, see "Design Patterns Summary").

Grants: REVOKE UPDATE ON signals.experiment_assignment FROM authenticated; GRANT UPDATE (contamination_status, first_eligible_exposure_at, exposure_event_id, causal_basis_locked_at) TO authenticated — only these 4 columns are tenant-writable via UPDATE; every other column is set once at INSERT time (subject to the schema's default SELECT-only privilege for INSERT/base access, since this table receives no bespoke GRANT INSERT).

---

signals.experiment_exposure_event (5 cols) — NOT partitioned

Append-only exposure log. The mandatory, NOT NULL FK into experiment_assignment is the first half of the assigned_at <= first_eligible_exposure_at structural guarantee (BLOCKER 3) — an exposure event cannot exist without a pre-existing, already-committed assignment row.

Tenant-scoped, NOT partitioned, append-only. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy experiment_exposure_event_tenant_isolation. No soft delete, no updated_at. REVOKE UPDATE, DELETE FROM authenticated.

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
experiment_assignment_id	UUID	NOT NULL	—	Composite FK → signals.experiment_assignment (id, tenant_id)
occurred_at	timestamptz	NOT NULL	—	Validated >= assigned_at by validate_exposure_after_assignment()
exposure_type	text	NOT NULL	—

Indexes: PK on id; experiment_exposure_event_tenant_id_idx; experiment_exposure_event_assignment_id_idx.

Constraints: experiment_exposure_event_assignment_id_tenant_fkey — composite FK (experiment_assignment_id, tenant_id) → signals.experiment_assignment (id, tenant_id).

Triggers:

1. trg_experiment_exposure_event_validate_timing (BEFORE INSERT) — signals.validate_exposure_after_assignment(). Rejects any occurred_at < assigned_at (the TIMESTAMP half of BLOCKER 3's structural guarantee — FK existence alone doesn't prevent a backfill inserting an old occurred_at against a newly-created assignment).
2. trg_experiment_exposure_event_update_first_eligible (AFTER INSERT) — signals.update_first_eligible_exposure(). Converges experiment_assignment.first_eligible_exposure_at to the true-earliest qualifying exposure event on every event, not first-write-wins by commit order — see "Design Patterns Summary" for the retroactive-contamination branch.

Grants: REVOKE UPDATE, DELETE ON signals.experiment_exposure_event FROM authenticated (append-only, SELECT via schema default).

---

signals.outcome_observation (19 cols) — PARTITIONED

Append-only, remeasurable outcome-measurement ledger. is_authoritative is now a denormalized CONVENIENCE flag only — outcome_authority's own PRIMARY KEY is the real enforcement (a native unique partial index on a partitioned table cannot include all partitioning columns without silently defeating the guarantee — live-verified, BLOCKER 2, see "Design Patterns Summary"). agent_action_id is a disclosed forward-ref (see the Cross-Module FK table above). attribution_model_version_id and experiment_assignment_id ARE real FKs today — both targets already exist.

Tenant-scoped, PARTITIONED BY RANGE(created_at), monthly. tenant_id NOT NULL FK → platform.tenant. No PRIMARY KEY — 2 UNIQUE constraints instead (see below). RLS enabled — permissive policy outcome_observation_tenant_isolation. No soft delete. Append-only by convention: REVOKE UPDATE ON signals.outcome_observation FROM authenticated; GRANT UPDATE (status, is_authoritative, validated_at) TO authenticated — earlier observations are never overwritten; a later observation is a NEW row (observation_version + 1, supersedes_observation_id = prior.id).

Column	Type	Nullable	Default	Notes
id	UUID	NOT NULL	platform.uuid_generate_v7()	Part of the identity UNIQUE constraint, no standalone PK
tenant_id	UUID	NOT NULL	—	FK → platform.tenant
agent_action_id	UUID	NOT NULL	—	DISCLOSED FORWARD-REF, no FK — see Cross-Module FK table
outcome_type	text	NOT NULL	—
measurement_window	text	NOT NULL	—
attribution_model_version_id	UUID	NOT NULL	—	FK → semantics.attribution_model_version — real cross-schema FK
measurement_run_id	UUID	nullable	—
observation_version	integer	NOT NULL	1	Incremented on each remeasurement of the same scope tuple
supersedes_observation_id	UUID	nullable	—	Informational lineage only, no FK — see Cross-Module FK table
status	text	nullable	—	CHECK IS NULL OR IN (active,superseded)
is_authoritative	boolean	NOT NULL	false	Denormalized convenience flag — NOT the real enforcement, see table intro
validated_at	timestamptz	nullable	—	Tenant-updatable
observed_value_cents	bigint	nullable	—
observed_effect	jsonb	nullable	—
confidence	numeric(5,4)	nullable	—
is_incremental	boolean	NOT NULL	true
experiment_assignment_id	UUID	nullable	—	FK → signals.experiment_assignment
baseline_value_cents	bigint	nullable	—
created_at	timestamptz	NOT NULL	now()	Partition key

CHECK constraints: chk_outcome_observation_status — status IS NULL OR status IN ('active','superseded').

Constraints (both include created_at, the partition key — see the corrected-shape note below):

• uq_outcome_observation_id_tenant_created — UNIQUE (id, tenant_id, created_at) — the composite-identity shape outcome_authority's own FK resolves against.
• uq_outcome_observation_scope_version — UNIQUE (tenant_id, agent_action_id, outcome_type, measurement_window, attribution_model_version_id, observation_version, created_at).

Corrected from the design doc's own literal SQL — the design specified uq_outcome_observation_scope_version WITHOUT created_at; live-verified during this build that Postgres rejects it (identical error class to BLOCKER 2's own documented is_authoritative case, just not caught by that section's own adversarial pass for this second unique constraint on the same table). created_at was added to satisfy Postgres's structural requirement — but the resulting per-partition-month-scoped constraint alone would silently weaken "one row per (tenant, action, type, window, method, version) ever" to "...per month," the exact bug class Phase 2 (ai reopen) already solved for agent_execution.idempotency_key. Resolved identically: a genuine cross-partition trigger performs the real check (see below).

Note on naming: the Drizzle schema file (packages/db/src/schema/signals/outcome.ts) names these 2 indexes outcome_observation_id_tenant_created_unique / outcome_observation_scope_version_unique; the migration's own literal SQL — what actually ran against the live DB — uses uq_outcome_observation_id_tenant_created / uq_outcome_observation_scope_version. This doc uses the migration's names, since that is what is live. Same shape either way; a naming-only discrepancy between the Drizzle file and the hand-written migration, not a functional one.

Indexes: outcome_observation_tenant_id_idx; outcome_observation_agent_action_id_idx; outcome_observation_experiment_assignment_id_idx — partial, WHERE experiment_assignment_id IS NOT NULL.

Partitions: 13 monthly (2026-06 through 2027-06) + 1 DEFAULT = 14 total.

Triggers:

1. trg_outcome_observation_no_duplicate_version (BEFORE INSERT) — signals.check_outcome_observation_no_duplicate_version(). A genuine cross-partition duplicate-version check, using pg_advisory_xact_lock + a real SELECT across the whole logical table (not just the target partition) — closes the residual gap the partition-scoped UNIQUE constraint above cannot cover on its own. Matches Phase 2's own established pattern for the identical bug class (ai.agent_execution.idempotency_key).
2. trg_outcome_observation_promote (AFTER INSERT) — signals.promote_authoritative_observation(). BLOCKER 2's own promotion mechanism — see "Design Patterns Summary".

---

signals.outcome_authority (9 cols) — NOT partitioned

The split-authority pattern. Deliberately small (one row per scope, not per observation event), NOT partitioned, NOT time-ranged — matches the same operational profile semantics tables have. The PRIMARY KEY itself is the enforcement: exactly one authoritative observation per (tenant, agent_action, outcome_type, measurement_window), full stop, independent of how many outcome_observation rows or partitions exist beneath it. Populated exclusively via promote_authoritative_observation()'s native INSERT ... ON CONFLICT ... DO UPDATE (a genuine, atomic, native upsert — Postgres's own race-free primitive, no advisory lock needed) — never a direct application write in the intended design, though the DB does not structurally forbid one (a workflow convention, disclosed, matching this design's own "mutable (native upsert only)" framing).

Tenant-scoped, NOT partitioned. tenant_id NOT NULL FK → platform.tenant. RLS enabled — permissive policy outcome_authority_tenant_isolation. No soft delete, no updated_at — finalized_at is overwritten by every promotion instead.

Column	Type	Nullable	Default	Notes
tenant_id	UUID	NOT NULL	—	FK → platform.tenant, part of the composite PK
agent_action_id	UUID	NOT NULL	—	DISCLOSED FORWARD-REF, no FK — part of the composite PK
outcome_type	text	NOT NULL	—	Part of the composite PK
measurement_window	text	NOT NULL	—	Part of the composite PK
authoritative_observation_id	UUID	NOT NULL	—	Composite FK (with tenant_id, authoritative_observation_created_at) → signals.outcome_observation
authoritative_observation_created_at	timestamptz	NOT NULL	—	The shadow half of the composite FK into the partitioned outcome_observation
experiment_assignment_id	UUID	nullable	—	FK → signals.experiment_assignment
remeasurement_required	boolean	NOT NULL	false	Set true by update_first_eligible_exposure()'s retroactive-contamination branch
finalized_at	timestamptz	NOT NULL	now()	Overwritten on every promotion

Constraints:

• PRIMARY KEY (tenant_id, agent_action_id, outcome_type, measurement_window) — the real enforcement mechanism, not a UUID surrogate.
• fk_outcome_authority_observation — composite FK (authoritative_observation_id, tenant_id, authoritative_observation_created_at) → signals.outcome_observation (id, tenant_id, created_at).

Indexes: PK (composite, above); outcome_authority_tenant_id_idx.

Grants: GRANT SELECT, INSERT, UPDATE ON signals.outcome_authority TO authenticated — the one table in this schema where authenticated has a genuine write grant beyond a scoped column list, since the promotion trigger runs as the inserting session's own privileges (not SECURITY DEFINER). The intended access pattern is via the trigger's own upsert only — a disclosed workflow convention, not a DB-enforced restriction.

---

signals — Design Patterns Summary

The split-authority pattern (outcome_observation + outcome_authority)

The problem (BLOCKER 2, live-verified during the design phase): a native UNIQUE/partial-unique index enforcing "exactly one authoritative observation per scope" cannot survive PARTITION BY RANGE(created_at) — Postgres requires the partition key in every unique constraint on a partitioned table, and naively adding it (created_at) silently defeats the guarantee: 2 simultaneously-authoritative rows in 2 different monthly partitions would both pass, since the constraint is now scoped per-partition-month rather than globally.

The fix: a genuinely separate, unpartitioned table, outcome_authority (PK = (tenant_id, agent_action_id, outcome_type, measurement_window)), maintained by promote_authoritative_observation() — a native INSERT ... ON CONFLICT ... DO UPDATE upsert. outcome_observation.is_authoritative becomes a denormalized convenience flag only; outcome_authority's own PK is the real, structural enforcement, independent of how many outcome_observation rows or partitions exist beneath it.

Live-reproduced: an observation in June (partition 1) with is_authoritative=true correctly creates 1 outcome_authority row; a second observation in July (partition 2, same scope tuple) also is_authoritative=true correctly leaves exactly 1 outcome_authority row, now pointing at the July observation — both underlying history rows remain intact in outcome_observation, proving the native upsert works correctly across partitions (test section E, signals-schema.spec.ts).

The SECURITY DEFINER as-of-function tenant-isolation mechanism

The problem (BLOCKER 1, live-verified during the design phase): a p_tenant_id uuid parameter on a SECURITY DEFINER function is spoofable by any caller regardless of their real tenant — no version of "check the parameter" closes this, since the parameter itself is attacker-controlled.

The fix: signals.get_feature_as_of() / get_forecast_as_of() / get_anomaly_score_as_of() derive tenant scope from a new helper, platform.current_tenant_id() (SELECT current_setting('app.current_tenant_id', true)::uuid), with no tenant argument in the function signature at all — there is no argument position left to spoof. Each function:

• is owned by a new, minimal role signals_function_owner (NOLOGIN, NOINHERIT, NOT superuser, NOT the table owner) — its own narrow SELECT grant on exactly feature_value/forecast/anomaly_score, nothing else;
• has search_path pinned to 'signals, pg_catalog';
• has REVOKE EXECUTE FROM PUBLIC.

2 privilege-mechanics gaps found live during the migration-apply pass itself (not the design):

1. ALTER FUNCTION ... OWNER TO signals_function_owner requires the migration-running role to be a member of the target role AND for the target role to hold CREATE on the containing schema — live-verified this local Supabase stack's own postgres role is NOT a true superuser (rolsuper = false), so both grants were genuinely required. CREATE is REVOKEd again immediately after the 3 ownership transfers, since signals_function_owner has no ongoing need to create anything.
2. The SECURITY DEFINER function body calls platform.current_tenant_id() — the executing role (signals_function_owner) needed explicit USAGE on schema platform to even reference it, a genuinely separate requirement from EXECUTE on the function itself (which is PUBLIC-granted by default) — found only when live-reproducing GUARD 1 for the first time.

Live-reproduced: a session scoped to tenant 1 correctly sees its own row (value=42); the identical call from a session scoped to tenant 2, for the identical entity_ref/feature_version_id, correctly returns nothing — there is no argument to substitute another tenant's ID into (test section C).

Enforced bitemporal read (B3 mandatory test 4a, GUARD 4). Live-reproduced exactly as the design specifies: a feature_value row with as_of = T-30days (business truth) but recorded_at = T+1day (the ERP learned this fact AFTER a decision made at time T) — calling get_feature_as_of() with p_business_as_of=T, p_knowledge_cutoff=T correctly returns nothing; the identical call with knowledge_cutoff advanced past recorded_at correctly returns the value (test section D).

2026-07-17 addendum (Phase 6 of the agents-v2/v3 build): GRANT EXECUTE on all 3 functions (get_feature_as_of() / get_forecast_as_of() / get_anomaly_score_as_of(), all (uuid, uuid, timestamptz, timestamptz)) is now wired to the new agent_reader role — previously only the REVOKE EXECUTE FROM PUBLIC half existed, with no corresponding GRANT to any role at all (see Open Items item 2 below, now closed). Live-reproduced: a call via the new agentReaderDB() connection helper succeeds with no permission error; no additional GRANT USAGE ON SCHEMA platform was needed for agent_reader itself, since all 3 functions are SECURITY DEFINER owned by signals_function_owner, which already holds that grant to call platform.current_tenant_id() internally. Grant-only — no table or column change. See PROJECT_DECISIONS #67 and packages/db/migrations/20260717000001_agent_reader_role.sql.

The assigned_at forgery-prevention trigger

The problem: DEFAULT now() alone does not stop an explicit backdated INSERT — a default only fires when the column is omitted, it is not a validation rule.

The fix: force_assignment_timestamp(), an unconditional BEFORE INSERT trigger on experiment_assignment, overwrites NEW.assigned_at := clock_timestamp() regardless of what the caller supplied — clock_timestamp() (true wall-clock instant), not now() (frozen at transaction start, indistinguishable from a value a caller could construct by holding a transaction open).

Live-reproduced: an INSERT with an explicit 2020-01-01 assigned_at value is silently overwritten with the real insert time (test F1).

The retroactive-exposure-contamination race fix

A second, more subtle race — a genuinely-earlier exposure event arriving AFTER the causal basis is locked — was live-reproduced by the design's own independent review and closed via matching SELECT ... FOR UPDATE row-locks on experiment_assignment in both lock_experiment_causal_basis() (the function is built this phase; its own CREATE TRIGGER ... ON agents.decision_context_snapshot is deferred to Phase 5, which builds that table — see Open Items below) and update_first_eligible_exposure(). Both branches row-lock the assignment FIRST, before reading or writing causal_basis_locked_at/first_eligible_exposure_at — this is what makes the two functions serialize correctly against each other, the actual fix for the live-reproduced race, not the branching logic alone.

The two branches of update_first_eligible_exposure():

• Normal case (no lock yet, or the new event isn't earlier than the current first-eligible): first_eligible_exposure_at/exposure_event_id converge to the true-earliest qualifying event, re-evaluated on every event — not first-write-wins by commit order.
• Retroactive-contamination case (causal_basis_locked_at IS NOT NULL and the new event is earlier than the current first_eligible_exposure_at): the original measurement history is preserved — first_eligible_exposure_at is never silently moved — contamination_status flips to 'exposure_revised_post_lock', and outcome_authority.remeasurement_required is flipped true for the linked scope, flagging remeasurement rather than silently invalidating history.

Live-reproduced end-to-end: exposure-before-assignment correctly rejected by validate_exposure_after_assignment(); a genuine exposure sets first_eligible_exposure_at; after simulating the causal-basis lock, a genuinely-earlier exposure event (after assigned_at, before the current first_eligible_exposure_at) correctly leaves first_eligible_exposure_at unchanged and flips contamination_status to 'exposure_revised_post_lock' (test F3).

---

Column-count reconciliation

Table	Cols	Partitioned?
feature_definition	6	No
feature_version	7	No
experiment	6	No
experiment_version	9	No
feature_value	10	Yes (weekly, recorded_at)
forecast	11	Yes (weekly, recorded_at)
anomaly_score	10	Yes (weekly, recorded_at)
experiment_assignment	19	No
experiment_exposure_event	5	No
outcome_observation	19	Yes (monthly, created_at)
outcome_authority	9	No
Total	111	4 of 11 tables partitioned

Verified live via information_schema.columns (logical basis — excludes partition-child relations, which inherit their parent's columns and add none of their own), schema signals: 11 logical tables, 111 columns — matches the regression suite's own (A2) count assertion exactly. The physical table count including partition children is higher: 11 logical tables + 39 feature_value/forecast/anomaly_score partition children (13 × 3) + 14 outcome_observation partition children = 64 physical relations in information_schema.tables, the same logical-vs-physical distinction ai.agent_execution/agent_memory already established in Phase 2.

2026-07-17 addendum (Phase 6 of the agents-v2/v3 build): the agent_reader GRANT EXECUTE wiring (see "The SECURITY DEFINER as-of-function tenant-isolation mechanism" above) is grant-only — no table or column change. Still 11 logical tables / 111 columns.

Open items carried forward

Both logged to docs/open-items/OPEN_ITEMS.md (signals | FK):

1. outcome_observation.agent_action_id / outcome_authority.agent_action_id are bare uuid, no FK — forward-refs to agents.agent_action, which does not exist until Phase 5 (confirmed live: zero tables in the agents schema as of this phase). The design of record's own SQL specifies these as composite FKs → agents.agent_action(id, tenant_id); wired for real once Phase 5 lands.
2. CLOSED 2026-07-17 (Phase 6 of the agents-v2/v3 build). get_feature_as_of() / get_forecast_as_of() / get_anomaly_score_as_of() now have GRANT EXECUTE wired to agent_reader (all 3, (uuid, uuid, timestamptz, timestamptz) signature) — previously only REVOKE EXECUTE FROM PUBLIC existed with no corresponding GRANT to any role, and agent_reader (the intended caller, per BLOCKER 1's own design) didn't exist until this phase. Live-reproduced via the new agentReaderDB() connection helper. See "The SECURITY DEFINER as-of-function tenant-isolation mechanism" above and PROJECT_DECISIONS #67 / packages/db/migrations/20260717000001_agent_reader_role.sql.

Also disclosed, not logged as a separate OPEN_ITEMS row (informational only): the Drizzle schema file names 2 of outcome_observation's indexes differently than the migration's own literal SQL — see that table's own note above.

No SignalsService yet — schema-only this phase, matching every phase of the agents-v2/v3 build so far (every phase stays schema-only until Phase 5 lands the agents module itself). Downstream code would query signals.* directly via Drizzle, or via the 3 as-of functions through the now-wired agent_reader role (Phase 6, 2026-07-17 — see Open Items item 2 above), until a service layer is built.

Regression tests

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

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

Section	Covers	Tests
A	Table existence (exactly 11 logical), column count (exactly 111), partition shape (13 weekly × 3, 14 monthly for outcome_observation)	4
B	Partition-level GRANT isolation — zero grants on any feature_value/forecast/anomaly_score partition; a direct SELECT on a partition (bypassing the as-of function) is rejected (42501)	2
C	GUARD 1 (BLOCKER 1) — tenant-spoof via SECURITY DEFINER as-of function	2
D	GUARD 4 (B3 mandatory test 4a) — enforced bitemporal read	2
E	GUARD 2 (BLOCKER 2) — partition/authority split, native upsert across partitions, cross-partition duplicate-version rejection	2
F	GUARD 3 (BLOCKER 3) — assigned_at forgery, exposure-before-assignment rejection, retroactive-post-lock contamination race	3
G	RLS cross-tenant isolation (experiment_assignment, outcome_observation)	2
H	GRANT shape — 4 global catalog tables SELECT-only, zero table-level grants on the 3 bitemporal parents, experiment_assignment's restricted-column UPDATE	3
Total		20

A companion stale-assertion fix landed in the pre-existing platform-module-registry.spec.ts (G3): signals now correctly bumps to 'in_build' alongside semantics in platform.module_catalog, while agents alone stays 'designed'.

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