notifications — module #29 (2026-07-19), the first STALE-BUT-UNBUILT module revival

18 tables, 252 columns (up from v1's stale 11/166 placeholder, never migrated — locked 2026-06-10, schema-locked-but-never-built until now) — schema built, migration applied, live-reproduced, and locked 2026-07-19. This build is the first application of SCHEMA_DESIGN_RUNBOOK.md Section 2.1a ("Reopening or Reviving a Design-Locked-But-Never-Built Module") — a runbook subsection this build's own Part 0 authored and applied to itself, since no prior module had ever sat schema-locked without a real migration for this long. Full lineage: v1 (2026-06-10) → design review (2026-07-19, validated against 6 current requirements + live codebase reality) → v2 proposal → 16 architect rulings (vrida-notifications-design-review-2026-07-19-v2.md) → RULED design (vrida-notifications-design-RULED-2026-07-19.md) → an 11-finding independent adversarial verification pass (3 BLOCKERs, 4 MAJORs, 4 MINORs — 10 accepted-and-fixed, 1 deferred as a genuine human/architect decision) → this build. See PROJECT_DECISIONS (Notifications entry) for the full record.

notifications is the event-driven delivery orchestrator: transactional/marketing sends across email/SMS/push/in-app, a durable print signal for POS receipts, campaigns with pre-send audience snapshots, two-way SMS, and a per-tenant + platform-wide suppression list. Fully tenant-scoped except the 3 deliberately platform/global-scoped tables (provider_event_log, provider_event_dead_letter — nullable tenant_id, mirroring payments.stripe_event_log/stripe_event_dead_letter exactly; platform_suppression — no tenant_id column at all).

Depends on platform (tenant, outbox, processor_catalog), crm (customer, customer_group, customer_segment_definition), identity (actor), offers (offer), purchasing (vendor), orders/billing (companion-reopen prerequisites only, no live FK from this schema). No NotificationsService exists yet — schema-only this pass.

5 companion reopens (one migration each, all additive-only, applied before the main 18-table migration):

• platform (20260719000003_platform_reopen_notifications_prereqs.sql) — outbox gains UNIQUE(id, tenant_id) (outbox_id_tenant_id_unique, prerequisite for notification.outbox_event_id's composite FK); processor_catalog.kind's CHECK widened to add 'notification_provider' (chk_processor_catalog_kind); resend/twilio seeded as active notification_provider rows.
• orders (20260719000004_orders_reopen_order_fulfillment_unique.sql) — order_fulfillment gains UNIQUE(id, tenant_id) (order_fulfillment_id_tenant_id_unique) — prerequisite for the pickup-ready notification seam to later become a real composite FK (currently a polymorphic source_module/source_ref, no reciprocal column).
• billing (20260719000005_billing_reopen_ar_statement_unique.sql) — ar_statement gains UNIQUE(id, tenant_id) (ar_statement_id_tenant_id_unique). Convention note, no further schema change: ar_statement.sent_at/.delivery_method become a thin cache written by NotificationsService once real notification/delivery_attempt rows exist for these sends (Ruling 15).
• purchasing (20260719000006_purchasing_reopen_po_vendor_ack.sql) — purchase_order gains vendor_acknowledged_at timestamptz + vendor_acknowledgement_note text — the write-back half of the PO-acknowledgement seam (send is already served by purchase_order.status='sent').
• crm (20260719000007a_crm_reopen_customer_group_unique.sql) — emergency 6th companion reopen, discovered mid-build (the RULED design's own Seams table had named this as Part B Finding #8 but the build's own Phase 1 planning missed executing it until the main migration failed on it): customer_group gains UNIQUE(id, tenant_id) (customer_group_id_tenant_id_unique) — prerequisite for campaign.customer_group_id's composite FK.

pg_cron extension also installed codebase-wide (20260719000007_pg_cron_extension.sql, the ruled scheduler) — no jobs created, job definitions are service-build work (OPEN_ITEMS row 244 — the inventory sweep — inherits the same ruling, row stays open).

Global rules for this schema:

• Uniform tenant-scoping on 16 of 18 tables — tenant_id NOT NULL FK → platform.tenant, standard RLS <table>_tenant_isolation policy FOR ALL TO authenticated. provider_event_log/provider_event_dead_letter have a deliberately nullable tenant_id (a webhook can arrive before tenant resolution, mirroring the Stripe precedent exactly — confirmed live via pg_get_functiondef that stripe_event_log/stripe_event_dead_letter carry zero triggers, neither append-only). platform_suppression has no tenant_id column at all — platform-owned, not tenant data.
• updated_at trigger-maintained via platform.set_updated_at() on 11 tables (notification_template, campaign, notification, delivery_attempt, notification_preference, in_app_notification, notification_quota_usage, inbound_message, sender_identity, notification_frequency_tracker, and the 3 dormant journey tables). Not on campaign_recipient (insert-once, 2 columns legitimately fill in later — see below), provider_event_log/provider_event_dead_letter (raw evidence ingestion, no update lifecycle beyond status/processed_at/resolved_at), suppression (no update lifecycle beyond soft-delete), platform_suppression (immutable by design).
• UUIDv7 PKs (platform.uuid_generate_v7()) on the 7 ledger/evidence-shaped tables: notification, delivery_attempt, campaign_recipient, notification_frequency_tracker, provider_event_log, provider_event_dead_letter, suppression. The other 11 tables keep gen_random_uuid() (config/definition-shaped, no future partitioning need).
• Soft delete (deleted_at) on 13 tables. Not on campaign_recipient (immutable targeting snapshot), notification_frequency_tracker/notification_quota_usage (mechanical counters), provider_event_log/provider_event_dead_letter (raw evidence — a dead-letter row's own lifecycle is resolved_at, not soft-delete), platform_suppression (no deletion path at all — belt-and-suspenders lockdown).
• Agent-as-actor — every *_actor_id column targets identity.actor, not a legacy identity_user/*_user_id shape. (4 v1 *_user_id columns — notification_preference.user_id, in_app_notification.recipient_user_id, inbound_message.handled_by_user_id, campaign.created_by_user_id — were retargeted to actor_id/recipient_actor_id/handled_by_actor_id/created_by_actor_id in this build; journey_enrollment.recipient_user_id was retargeted the same way as recipient_actor_id, alongside its existing recipient_customer_id.)
• Autonomy tiers, non-uniform by design — see the Autonomy Pack Mapping table below (full pack on 4 tables, none on the other 14).
• Append-only tables: zero. notification/delivery_attempt are both deliberately MUTABLE (Ruling 1 — no delivery_event split); provider_event_log/provider_event_dead_letter mirror the Stripe precedent's own MUTABLE shape (status/processed_at/resolved_at legitimately update, confirmed live via pg_get_functiondef neither table carries any trigger at all). campaign_recipient is a genuine "insert-once, 2 columns fill in later" shape (notification_id/suppression_reason legitimately populate post-insert) — disclosed honestly per Recurring Bug Class #2 rather than mislabeled append-only or given the shared reject_append_only_mutation() trigger.
• platform_suppression belt-and-suspenders lockdown — RLS enabled + zero policies + explicit REVOKE ALL ON notifications.platform_suppression FROM authenticated, mirroring identity.operator's own established precedent for Vrida-internal-only tables. Confirmed live: the authenticated role gets a genuine Postgres permission denied for table platform_suppression error (42501), not just an empty RLS-filtered result set — the REVOKE, not RLS, is what rejects the read.
• A schema-wide GRANT gap, self-caught and fixed mid-build. The initial 18-table migration omitted GRANT USAGE ON SCHEMA notifications TO authenticated / GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA notifications TO authenticated / ALTER DEFAULT PRIVILEGES ... — the blanket per-schema grant pattern every new module schema needs (established at the 2026-07-08 RLS-wiring pass, repeated by every schema built since, e.g. consumer/rewards/offers). Caught live during Phase 5 verification as a genuine permission denied for schema notifications Postgres error when attempting to SET LOCAL ROLE authenticated. Fixed via 20260719000010_notifications_grant_authenticated.sql, which re-applies the platform_suppression REVOKE afterward (the blanket grant would otherwise re-open it).

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

Column	Target	Notes
*.tenant_id (16 of 18 tables)	platform.tenant	NOT NULL except provider_event_log/provider_event_dead_letter (nullable); platform_suppression has no tenant_id at all
campaign.customer_group_id	crm.customer_group (id, tenant_id)	nullable, composite — needed the emergency crm companion reopen
campaign.customer_segment_definition_id	crm.customer_segment_definition.id	nullable, BARE (not composite) — customer_segment_definition.tenant_id is nullable (genuine global "builtin" segment rows exist); a composite FK would be a MATCH SIMPLE trap. Enforced instead by trg_campaign_validate_segment (see below)
campaign.offer_id	offers.offer (id, tenant_id)	nullable, composite
campaign.template_id	notifications.notification_template.id	nullable, bare (same-schema, no cross-tenant risk beyond RLS)
campaign.created_by_actor_id	identity.actor	nullable, bare
notification.template_id	notifications.notification_template.id	nullable, bare
notification.campaign_id	notifications.campaign.id	nullable, bare (same-schema)
notification.journey_enrollment_id / .journey_step_id	notifications.journey_enrollment.id / .journey_step.id	nullable, bare — dormant tables
notification.recipient_customer_id	crm.customer.id	nullable (XOR with recipient_actor_id), bare
notification.recipient_actor_id	identity.actor	nullable (XOR with recipient_customer_id), bare
notification.created_by_actor_id	identity.actor	nullable, bare
notification.outbox_event_id	platform.outbox (id, tenant_id)	nullable, composite — the companion outbox reopen's own prerequisite
notification.reprint_of_notification_id	notifications.notification (id, tenant_id)	nullable, composite self-FK
campaign_recipient.campaign_id	notifications.campaign (id, tenant_id)	NOT NULL, composite
campaign_recipient.customer_id	crm.customer (id, tenant_id)	NOT NULL, composite
campaign_recipient.notification_id	notifications.notification (id, tenant_id)	nullable, composite
notification_frequency_tracker.customer_id	crm.customer (id, tenant_id)	NOT NULL, composite
provider_event_log.provider_code / provider_event_dead_letter.provider_code	platform.processor_catalog.code	NOT NULL, bare (global catalog, no tenant scoping)
delivery_attempt.notification_id	notifications.notification (id, tenant_id)	NOT NULL, composite (Part B Finding #9 — zero marginal cost since notification gains UNIQUE(id,tenant_id) in this same migration)
delivery_attempt.provider	platform.processor_catalog.code	nullable, bare
suppression.customer_id	crm.customer (id, tenant_id)	nullable, composite
suppression.source_event_id / platform_suppression.source_event_id	notifications.provider_event_log.id	nullable, BARE (not composite — provider_event_log.tenant_id is nullable, same MATCH SIMPLE trap reasoning as campaign.customer_segment_definition_id; provider_event_log.id is a globally-unique PK regardless, so a bare FK loses nothing structurally)
in_app_notification.notification_id	notifications.notification (id, tenant_id)	nullable, composite
in_app_notification.recipient_customer_id / .recipient_actor_id	crm.customer.id / identity.actor	nullable (XOR), bare
inbound_message.customer_id	crm.customer.id	nullable (≤1 with vendor_id), bare
inbound_message.vendor_id	purchasing.vendor (id, tenant_id)	nullable (≤1 with customer_id), composite
inbound_message.related_notification_id	notifications.notification (id, tenant_id)	nullable, composite
inbound_message.handled_by_actor_id	identity.actor	nullable, bare
suppression.created_by_actor_id	identity.actor	nullable, bare

---

1. notifications.notification_template (16 cols, autonomy pack)

v1's 13 columns retained verbatim + the 3-column autonomy pack.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at	timestamptz	NOT NULL	now()
updated_at	timestamptz	NOT NULL	now()	Trigger-maintained
deleted_at	timestamptz	nullable	—	Soft delete
name	text	NOT NULL	—
notification_type	text	NOT NULL	—
is_transactional	boolean	NOT NULL	true
audience	text	NOT NULL	'customer'	CHECK IN (customer,staff,system)
channel_variants	jsonb	NOT NULL	—	Per-channel copy variants
variables	jsonb	nullable	—	Declared template variable slots
version	integer	NOT NULL	1
is_active	boolean	NOT NULL	true
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
decision_provenance	jsonb	nullable	—	Set only when automation_source='agent'

Constraints: notification_template_tenant_name_version_unique UNIQUE(tenant_id, name, version). Indexes (3): PK; notification_template_tenant_id_idx; notification_template_notification_type_idx.

---

2. notifications.notification (33 cols, autonomy pack) — the convergence point

All v1 columns retained (2 retargeted): id (UUIDv7), tenant_id, created_at, updated_at, deleted_at, template_id, notification_type, is_transactional, audience, origin_type, source_module, source_type, source_ref, campaign_id, journey_enrollment_id, journey_step_id, recipient_customer_id, recipient_actor_id (retargeted from recipient_user_id), recipient_contact (jsonb), requested_channels (jsonb), status, dedup_key, scheduled_for, priority, template_data (jsonb), created_by_actor_id (retargeted).

Added (4): outbox_event_id, is_test, printed_at, reprint_of_notification_id. Autonomy pack (+3): automation_source, review_status, decision_provenance — exists for the rarer case of a directly agent-proposed one-off manual send; a campaign-origin notification inherits its gate from the parent campaign at launch time.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	see notes	now() / now() / nullable	Standard triple
template_id	uuid	nullable	—	FK → notification_template
notification_type	text	NOT NULL	—
is_transactional	boolean	NOT NULL	—
audience	text	NOT NULL	—	CHECK IN (customer,staff,system)
origin_type	text	NOT NULL	—	CHECK IN (transactional_event,campaign,journey,manual)
source_module	text	nullable	—	CHECK IN (pos,orders,billing,purchasing,crm,audit,inventory,offers,returns,platform) or NULL — value list flagged as Build-time-finalizable against real producers
source_type	text	nullable	—
source_ref	uuid	nullable	—	Polymorphic, no FK (paired with source_module)
outbox_event_id	uuid	nullable	—	Composite FK → platform.outbox. Set for origin_type='transactional_event' sends riding the outbox
campaign_id	uuid	nullable	—	FK → campaign
journey_enrollment_id / journey_step_id	uuid	nullable	—	FK → dormant journey tables
recipient_customer_id	uuid	nullable (XOR)	—	FK → crm.customer
recipient_actor_id	uuid	nullable (XOR)	—	FK → identity.actor
recipient_contact	jsonb	nullable	—	Immutable after insert (see trigger below) — e.g. {"email":"x@example.com"}
requested_channels	jsonb	NOT NULL	—	e.g. ["email","sms"]
status	text	NOT NULL	'pending'	CHECK IN (pending,consent_blocked,preference_blocked,quota_blocked,suppressed,queued,sent,partially_sent,failed,cancelled)
dedup_key	text	nullable	—	NULL-safe partial-unique per tenant
scheduled_for	timestamptz	nullable	—
priority	text	NOT NULL	'normal'	CHECK IN (low,normal,high)
template_data	jsonb	nullable	—
created_by_actor_id	uuid	nullable	—	FK → identity.actor
is_test	boolean	NOT NULL	false	Excluded from frequency-cap increments, quota increments, and — orthogonal to origin_type (a campaign test send is still origin_type='campaign', is_test=true)
printed_at	timestamptz	nullable	—	The durable print-execution signal for POS receipts — no delivery_attempt row is ever created for print
reprint_of_notification_id	uuid	nullable	—	Composite self-FK
automation_source	text	NOT NULL	'human'	CHECK IN (human,agent,system,seed)
review_status	text	NOT NULL	'not_required'	CHECK IN (not_required,pending,approved,rejected)
decision_provenance	jsonb	nullable	—

CHECK constraints (9): chk_notification_audience; chk_notification_origin_type; chk_notification_origin_refs (4-branch — each origin_type requires exactly its own ref columns set, forbids the others); chk_notification_recipient (num_nonnulls(recipient_customer_id, recipient_actor_id) = 1); chk_notification_source_module; chk_notification_status; chk_notification_priority; chk_notification_automation_source; chk_notification_review_status.

Constraints: notification_id_tenant_id_unique UNIQUE(id, tenant_id) (the composite-FK prerequisite for delivery_attempt/in_app_notification/inbound_message, all upgraded to composite in this same migration since notification is brand-new here); notification_tenant_dedup_key_unique — a NULL-safe partial unique index UNIQUE(tenant_id, dedup_key) WHERE dedup_key IS NOT NULL AND deleted_at IS NULL (not a plain UNIQUE, so NULL dedup_key rows coexist freely); notification_reprint_of_notification_id_tenant_fkey; notification_outbox_event_id_tenant_fkey.

Indexes (7): PK; the composite-FK-prerequisite unique above; notification_tenant_id_idx; notification_status_idx (partial, pending/queued); notification_campaign_id_idx (partial); notification_journey_enrollment_id_idx (partial); notification_recipient_customer_id_idx; notification_source_module_ref_idx (partial); notification_scheduled_for_idx (partial).

Triggers (2):

• trg_notification_recipient_contact_immutable (BEFORE UPDATE OF recipient_contact, fires only WHEN (NEW.recipient_contact IS DISTINCT FROM OLD.recipient_contact)) — rejects any post-insert change to recipient_contact, closing a real suppression-bypass path an independent adversarial pass found (Part B Finding #2): without this, a row could be inserted with a clean address, then corrected to a suppressed one post-insert with zero re-check.
• trg_notification_guard_insert (BEFORE INSERT, function notifications.guard_notification_insert()) — the combined 3-stage structural guard, see "The combined notification-insert guard" below.

---

3. notifications.delivery_attempt (21 cols, no autonomy pack)

All 19 v1 columns retained (2 modified): id (UUIDv7), tenant_id, created_at, updated_at, deleted_at, notification_id (composite FK), channel, provider (FK → processor_catalog), provider_message_ref, status (widened CHECK), sent_at, delivered_at, opened_at, clicked_at, failed_at, bounced_at, failure_reason, retry_count, idempotency_key.

Added (2): next_retry_at, max_attempts.

No autonomy pack — dispatch/delivery execution is deterministic system execution once a send is authorized, not an independent agent decision; provenance for why a send happened lives on the parent notification row.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
notification_id	uuid	NOT NULL	—	Composite FK → notification
channel	text	NOT NULL	—	CHECK IN (email,sms,push) — print is deliberately excluded (Ruling 5)
provider	text	nullable	—	FK → platform.processor_catalog.code — nullable, a queued row may not yet know its provider
provider_message_ref	text	nullable	—
status	text	NOT NULL	'queued'	CHECK IN (queued,sending,sent,delivered,failed,bounced,complained) — 7 values. opened/clicked are never status values — they live only on opened_at/clicked_at
sent_at/delivered_at/opened_at/clicked_at/failed_at/bounced_at	timestamptz	nullable	—	opened_at/clicked_at are first-write-wins (see trigger below)
failure_reason	text	nullable	—
retry_count	integer	NOT NULL	0
next_retry_at	timestamptz	nullable	—	The retry-sweep scheduling signal, entirely absent in v1
max_attempts	integer	nullable	—	Nullable = falls back to a tenant/channel-default constant (Build-time decision)
idempotency_key	text	nullable	—	NULL-safe partial unique per tenant

Constraints: chk_delivery_attempt_channel; chk_delivery_attempt_status; delivery_attempt_notification_id_tenant_fkey; delivery_attempt_tenant_idempotency_key_unique — NULL-safe partial unique index UNIQUE(tenant_id, idempotency_key) WHERE idempotency_key IS NOT NULL AND deleted_at IS NULL.

Indexes (7): PK; delivery_attempt_tenant_id_idx; delivery_attempt_notification_channel_idx; delivery_attempt_status_idx (partial); delivery_attempt_provider_message_ref_idx (partial); delivery_attempt_opened_at_idx (partial); delivery_attempt_next_retry_at_idx (partial).

Trigger — trg_delivery_attempt_guard_status (BEFORE UPDATE OF status, opened_at, clicked_at, function notifications.guard_delivery_attempt_status()) — the monotonic status guard (Ruling 1, corrected per Part B Findings #1/#6/#7/#11):

1. First-write-wins on opened_at/clicked_at, independent of the status transition — IF NEW.x IS NOT NULL AND OLD.x IS NOT NULL THEN NEW.x := OLD.x. A second or third open must not destroy the first-open timestamp.
2. No-op, not reject, on NEW.status IS NOT DISTINCT FROM OLD.status — a duplicate/retried webhook redelivering the identical terminal status is expected, routine at-least-once provider behavior (both Resend and Twilio), not a regression.
3. Reject only a genuine ordinal decrease. Ordinal scheme: queued(0) → sending(1) → sent(2) → delivered(3) → {failed,bounced,complained}(4). A late 'delivered' arriving after 'bounced' is rejected; a duplicate 'bounced' after 'bounced' is caught by clause 2 instead.

Deliberately coarse cache, disclosed. delivery_attempt.status does not attempt to represent every real provider event granularly — Resend's email.delivery_delayed and Twilio's undelivered (carrier-side, distinct from Twilio's own failed) are both real, non-identical outcomes with no ordinal slot. provider_event_log.event_type is the lossless record; delivery_delayed-class events are logged there but never mutate delivery_attempt.status at all.

---

4. notifications.notification_preference (13 cols, unchanged shape + 1 retarget)

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	—		Standard triple
customer_id	uuid	nullable (XOR)	—	FK → crm.customer
actor_id	uuid	nullable (XOR)	—	FK → identity.actor — retargeted from v1's user_id
channel	text	NOT NULL	—	CHECK IN (email,sms,push,in_app,all)
category	text	nullable	—
is_enabled	boolean	NOT NULL	true
quiet_hours_start/quiet_hours_end	time	nullable	—
timezone	text	nullable	—

Constraints: chk_notification_preference_channel; chk_notification_preference_recipient (num_nonnulls(customer_id, actor_id) = 1); notification_preference_tenant_customer_channel_category_unique (partial unique, customer-side); notification_preference_tenant_actor_channel_category_unique (partial unique, actor-side). Indexes (4): PK + 2 uniques above + notification_preference_tenant_id_idx/_customer_id_idx/_actor_id_idx.

---

5. notifications.in_app_notification (20 cols, autonomy pack + 1 retarget)

All 17 v1 columns retained (recipient_user_id retargeted to recipient_actor_id) + the 3-column autonomy pack.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	—		Standard triple
notification_id	uuid	nullable	—	Composite FK → notification
recipient_customer_id/recipient_actor_id	uuid	nullable (XOR)	—	FK → crm.customer / identity.actor
title/body	text	NOT NULL	—
action_url/action_label	text	nullable	—
is_read	boolean	NOT NULL	false
read_at	timestamptz	nullable	—
category	text	nullable	—
priority	text	NOT NULL	'normal'	CHECK IN (low,normal,high)
expires_at	timestamptz	nullable	—	e.g. "ready for pickup, valid 4 hours"
automation_source/review_status/decision_provenance	—	—	—	Standard autonomy pack

Constraints: chk_in_app_notification_recipient; chk_in_app_notification_priority; chk_in_app_notification_automation_source; chk_in_app_notification_review_status; in_app_notification_notification_id_tenant_fkey. Indexes (5): PK; in_app_notification_tenant_id_idx; in_app_notification_recipient_customer_unread_idx (partial); in_app_notification_recipient_actor_unread_idx (partial); in_app_notification_expires_at_idx (partial).

---

6. notifications.notification_quota_usage (9 cols, unchanged)

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	—		Standard triple
channel	text	NOT NULL	—	CHECK IN (email,sms,push)
period_start/period_end	date	NOT NULL	—
sent_count	integer	NOT NULL	0	Incremented atomically on successful send (service-layer, Build phase) — must exclude is_test sends

Constraints: chk_notification_quota_usage_channel; notification_quota_usage_tenant_channel_period_unique.

---

7. notifications.inbound_message (15 cols, +1 ADD, +1 retarget)

All 14 v1 columns retained (handled_by_user_id retargeted to handled_by_actor_id) + vendor_id.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	—		Standard triple
channel	text	NOT NULL	—	CHECK IN (sms,email)
from_contact	text	NOT NULL	—
body	text	nullable	—
customer_id	uuid	nullable (≤1)	—	FK → crm.customer
vendor_id	uuid	nullable (≤1)	—	Composite FK → purchasing.vendor — closes the vendor-reply-attribution gap
related_notification_id	uuid	nullable	—	Composite FK → notification
status	text	NOT NULL	'new'	CHECK IN (new,handled,ignored)
handled_by_actor_id	uuid	nullable	—	FK → identity.actor
handled_at	timestamptz	nullable	—
received_at	timestamptz	NOT NULL	—

Constraints: chk_inbound_message_channel; chk_inbound_message_status; chk_inbound_message_recipient (num_nonnulls(customer_id, vendor_id) <= 1 — deliberately <= 1, not = 1: an inbound reply from an unrecognized address is a real, expected case); inbound_message_vendor_id_tenant_fkey; inbound_message_related_notification_id_tenant_fkey. Indexes (5): PK; inbound_message_tenant_id_idx; inbound_message_status_new_idx (partial); inbound_message_customer_id_idx; inbound_message_related_notification_id_idx.

---

8–10. Journey tables — 40 cols total, KEPT verbatim from v1, dormant (Ruling 14)

notification_journey (11), journey_step (14), journey_enrollment (15, with recipient_user_id retargeted to recipient_actor_id alongside its existing recipient_customer_id). Out of v1 capability scope — no journey service, no journey UI, no seed rows. No structural guard added — confirmed nothing outside these 3 tables' own FKs reference them beyond notification.journey_enrollment_id/.journey_step_id, both nullable and correctly unpopulated while dormant.

Table	Column	Type	Nullable	Default	Notes
notification_journey	id,tenant_id,created_at,updated_at,deleted_at	—	—	—	Standard quintet
	name	text	NOT NULL	—
	description	text	nullable	—
	trigger_type	text	NOT NULL	—	CHECK IN (event,manual,segment)
	trigger_config	jsonb	nullable	—
	status	text	NOT NULL	'draft'	CHECK IN (draft,active,paused,archived)
	audience	text	NOT NULL	'customer'	CHECK IN (customer,staff)
journey_step	id,tenant_id,created_at,updated_at,deleted_at	—	—	—	Standard quintet
	journey_id	uuid	NOT NULL	—	FK → notification_journey
	step_order	integer	NOT NULL	—	UNIQUE(journey_id, step_order)
	step_type	text	NOT NULL	—	CHECK IN (send,wait,branch,exit)
	template_id	uuid	nullable	—	FK → notification_template
	channel_policy/delay_config/branch_condition_json	jsonb	nullable	—	Required per step_type (CHECK)
	next_step_id/fallback_step_id	uuid	nullable	—	Self-FK
journey_enrollment	id,tenant_id,created_at,updated_at,deleted_at	—	—	—	Standard quintet
	journey_id	uuid	NOT NULL	—	FK → notification_journey
	recipient_customer_id/recipient_actor_id	uuid	nullable (XOR)	—	FK → crm.customer / identity.actor
	current_step_id	uuid	nullable	—	FK → journey_step
	status	text	NOT NULL	'active'	CHECK IN (active,completed,exited,paused)
	enrolled_at/entered_step_at/next_action_at/completed_at	timestamptz	see notes	now() for enrolled_at
	exit_reason	text	nullable	—

---

11. notifications.campaign (21 cols, autonomy pack)

All 15 v1 columns retained (created_by_user_id retargeted to created_by_actor_id) + 3 new FK columns + the 3-column autonomy pack.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
created_at/updated_at/deleted_at	timestamptz	—		Standard triple
name	text	NOT NULL	—	UNIQUE(tenant_id, name)
description	text	nullable	—
template_id	uuid	nullable	—	FK → notification_template
audience_criteria	jsonb	nullable	—
customer_group_id	uuid	nullable	—	Composite FK → crm.customer_group
customer_segment_definition_id	uuid	nullable	—	Bare FK → crm.customer_segment_definition.id (validated by trigger, see below)
offer_id	uuid	nullable	—	Composite FK → offers.offer
channels	jsonb	NOT NULL	—
status	text	NOT NULL	'draft'	CHECK IN (draft,scheduled,sending,sent,paused,cancelled)
scheduled_for/sent_at	timestamptz	nullable	—
recipient_count	integer	nullable	—	Should always equal COUNT(campaign_recipient) — a D9-named reconciliation candidate, not built this pass
created_by_actor_id	uuid	nullable	—	FK → identity.actor
automation_source/review_status/decision_provenance	—	—	—	Standard autonomy pack

CHECK constraints (3): chk_campaign_status; chk_campaign_automation_source; chk_campaign_review_status; chk_campaign_draft_gate (status = 'draft' OR review_status IN ('not_required','approved')) — the flagship autonomy-boundary tie: an agent-drafted campaign cannot leave 'draft' (i.e., cannot reach 'scheduled'/'sending') while review_status != 'approved'.

Constraints: campaign_tenant_name_unique; campaign_id_tenant_id_unique UNIQUE(id, tenant_id); campaign_customer_group_id_tenant_fkey; campaign_offer_id_tenant_fkey; campaign_customer_segment_definition_id_fkey (bare).

Indexes (4): PK + the tenant-scoped composite-FK-prerequisite unique; campaign_tenant_id_idx; campaign_status_idx (partial); campaign_scheduled_for_idx (partial).

Trigger — trg_campaign_validate_segment (BEFORE INSERT OR UPDATE OF customer_segment_definition_id, tenant_id, function notifications.validate_campaign_segment()) — mirrors offers.validate_offer_targeting_rule_segment() exactly: rejects any customer_segment_definition_id that doesn't resolve to a row with tenant_id = NEW.tenant_id OR tenant_id IS NULL, since crm.customer_segment_definition.tenant_id is nullable by design (genuine global "builtin" segments exist) and a composite FK would be a functional trap.

---

12. notifications.campaign_recipient (8 cols, NEW)

Closes the pre-send audience-snapshot gap and defines the campaign-grain dedup mechanism (Ruling 8).

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
campaign_id	uuid	NOT NULL	—	Composite FK → campaign
customer_id	uuid	NOT NULL	—	Composite FK → crm.customer
notification_id	uuid	nullable	—	Composite FK → notification — set once the actual send is created; NULL between targeting and send
targeted_at	timestamptz	NOT NULL	now()
suppression_reason	text	nullable	—	Non-NULL if targeted then excluded before send
created_at	timestamptz	NOT NULL	now()

Constraints: campaign_recipient_tenant_campaign_customer_unique UNIQUE(tenant_id, campaign_id, customer_id) — the campaign-grain dedup mechanism: one customer targeted at most once per campaign, full stop, regardless of how many times the audience-resolution query re-selects them. Indexes (3): PK; campaign_recipient_tenant_id_idx; campaign_recipient_campaign_id_idx. No updated_at trigger — a genuine "insert-once, 2 columns fill in later" shape (notification_id/suppression_reason), disclosed honestly rather than mislabeled append-only.

notification.dedup_key composition for campaign-origin sends: dedup_key := campaign_id::text || ':' || recipient_customer_id::text — the two dedup layers compose, not overlap: campaign_recipient's UNIQUE prevents a customer from being targeted twice; notification.dedup_key's partial unique prevents a send from being created twice for the same targeting.

---

13. notifications.notification_frequency_tracker (8 cols, NEW)

The atomic per-customer marketing-frequency cap, mirroring offers.offer_redemption_reversal_tracker's own lazily-created-tracker precedent.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
customer_id	uuid	NOT NULL	—	Composite FK → crm.customer
period_start/period_end	date	NOT NULL	—
sent_count	integer	NOT NULL	0	Incremented atomically inside guard_notification_insert() (see below)
created_at/updated_at	timestamptz	NOT NULL	now()	updated_at trigger-maintained

Constraints: notification_frequency_tracker_tenant_customer_period_unique UNIQUE(tenant_id, customer_id, period_start). Applies to marketing-class sends only (is_transactional=false), excludes test sends (AND is_test=false).

---

14. notifications.provider_event_log (9 cols, NEW)

Mirrors payments.stripe_event_log exactly (live-confirmed via pg_get_functiondef: zero triggers, neither append-only).

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	nullable	—	A webhook can arrive before tenant resolution
provider_code	text	NOT NULL	—	FK → platform.processor_catalog.code
provider_event_id	text	NOT NULL	—	The provider's own event ID
event_type	text	NOT NULL	—	Provider's own vocabulary, not CHECK-constrained (Build-time finalization item)
received_at	timestamptz	NOT NULL	now()
processed_at	timestamptz	nullable	—
status	text	NOT NULL	'received'	CHECK IN (received,processed,ignored)
payload	jsonb	nullable	—	The raw provider webhook body, retained verbatim for later re-verification

Constraints: chk_provider_event_log_status; provider_event_log_code_event_id_unique UNIQUE(provider_code, provider_event_id) — global idempotency key, not tenant-scoped, mirroring stripe_event_log's own precedent exactly. Indexes (3): PK + the unique above; provider_event_log_tenant_id_idx; provider_event_log_event_type_idx. No trigger — RLS naturally hides NULL-tenant rows from every tenant session (confirmed live: not a cross-tenant leak).

---

15. notifications.provider_event_dead_letter (10 cols, NEW)

Mirrors payments.stripe_event_dead_letter exactly.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	nullable	—	Same reasoning as provider_event_log
provider_code	text	NOT NULL	—	FK → platform.processor_catalog.code
provider_event_id	text	NOT NULL	—
event_type	text	NOT NULL	—
error	text	NOT NULL	—
retry_count	integer	NOT NULL	0
last_retry_at/resolved_at	timestamptz	nullable	—
payload	jsonb	nullable	—

Constraints: provider_event_dead_letter_open_unique UNIQUE(provider_code, provider_event_id) WHERE resolved_at IS NULL — open dead-letters only. Indexes (3): PK + the partial unique above; provider_event_dead_letter_tenant_id_idx; provider_event_dead_letter_open_idx (partial).

---

16. notifications.suppression (10 cols, NEW) — the per-tenant list

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant — suppression IS tenant-scoped, unlike its optional source event
channel	text	NOT NULL	—	CHECK IN (email,sms,push)
address_normalized	text	NOT NULL	—	Lowercased email or E.164 phone — the load-bearing key
customer_id	uuid	nullable	—	Composite FK → crm.customer — fast resolved-lookup path
reason	text	NOT NULL	—	CHECK IN (hard_bounce,spam_complaint,manual,provider_block)
source_event_id	uuid	nullable	—	Bare FK → provider_event_log.id. NULL for reason='manual'
created_at	timestamptz	NOT NULL	now()
created_by_actor_id	uuid	nullable	—	FK → identity.actor — NULL for automatic, set for manual
deleted_at	timestamptz	nullable	—	A false-positive suppression can be manually reversed

Constraints: chk_suppression_channel; chk_suppression_reason; suppression_customer_id_tenant_fkey; suppression_tenant_channel_address_unique UNIQUE(tenant_id, channel, address_normalized) WHERE deleted_at IS NULL. Indexes (3): PK + the partial unique above; suppression_tenant_id_idx; suppression_customer_id_idx (partial).

---

17. notifications.sender_identity (13 cols, NEW)

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	gen_random_uuid()	PK
tenant_id	uuid	NOT NULL	—	FK → platform.tenant
channel	text	NOT NULL	—	CHECK IN (email,sms)
identity_value	text	NOT NULL	—	From-address or E.164 SMS sender number
domain_mode	text	NOT NULL	'shared'	CHECK IN (shared,byo) — every v1 row is system-seeded 'shared'; 'byo' is a structurally-ready, un-exercised v1.1 path
verification_status	text	NOT NULL	'verified'	CHECK IN (pending,verified,failed)
verified_at	timestamptz	nullable	—
is_default	boolean	NOT NULL	true	One default per (tenant, channel) — partial unique
dkim_domain	text	nullable	—	Email-only; NULL for domain_mode='shared'
can_spam_postal_address	text	nullable	—	NOT enforced NOT NULL at column level — CHECK requires non-NULL when channel='email'
created_at/updated_at/deleted_at	timestamptz	—		Standard triple

Constraints: chk_sender_identity_channel; chk_sender_identity_domain_mode; chk_sender_identity_verification_status; chk_sender_identity_can_spam (channel <> 'email' OR can_spam_postal_address IS NOT NULL — the concrete CAN-SPAM schema anchor); chk_sender_identity_byo_verified (domain_mode = 'shared' OR verification_status <> 'verified' OR verified_at IS NOT NULL); sender_identity_tenant_channel_default_unique UNIQUE(tenant_id, channel) WHERE is_default = true AND deleted_at IS NULL.

---

18. notifications.platform_suppression (6 cols, NEW — architect ruling on Part B Finding #10)

Platform-owned, cross-tenant, service-role-only. Closes the disclosed shared-domain-suppression-poisoning risk: per-tenant-only suppression does nothing to stop one shared-domain tenant's hard-bounce/complaint history from being repeated by another tenant against the same address.

Column	Type	Nullable	Default	Notes
id	uuid	NOT NULL	platform.uuid_generate_v7()	PK
channel	text	NOT NULL	—	CHECK IN (email,sms,push)
address_normalized	text	NOT NULL	—
reason	text	NOT NULL	—	CHECK IN (hard_bounce,spam_complaint,provider_block) — deliberately NO 'manual' value: a tenant-initiated manual suppression must never propagate to the platform-wide list
source_event_id	uuid	nullable	—	Bare FK → provider_event_log.id
created_at	timestamptz	NOT NULL	now()

No tenant_id column at all. No updated_at/deleted_at — immutable by design, no deletion path.

Constraints: chk_platform_suppression_channel; chk_platform_suppression_reason; platform_suppression_channel_address_unique UNIQUE(channel, address_normalized).

Security — belt-and-suspenders lockdown: ALTER TABLE ... ENABLE ROW LEVEL SECURITY with zero policies created + REVOKE ALL ON notifications.platform_suppression FROM authenticated (applied twice — once in the main migration, re-applied in the GRANT-fix migration since the schema-wide blanket grant would otherwise re-open it). Confirmed live: authenticated gets a genuine permission denied for table platform_suppression (Postgres code 42501) — the REVOKE, not RLS, is what rejects the read; a real Postgres permission error, not just an empty result set.

---

The combined notification-insert guard

notifications.guard_notification_insert() — a single BEFORE INSERT trigger on notification, merging 3 structural checks in sequence, mirroring the atomic lock-then-check-then-increment idiom offers.check_and_sync_offer_budget() established:

1. Campaign-recipient presence guard (campaign-origin only) — origin_type='campaign' requires EXISTS(SELECT 1 FROM campaign_recipient WHERE campaign_id = NEW.campaign_id AND customer_id = NEW.recipient_customer_id AND tenant_id = NEW.tenant_id), else RAISE EXCEPTION. Without this, nothing enforced that campaign_recipient — the whole justification for building the table — is actually populated before a send exists (Part B Finding #5).
2. Suppression gate — fires unconditionally, regardless of is_transactional (transactional sends bypass marketing consent but never suppression). Resolves address_normalized from recipient_contact->>'email' or ->>'phone', checks BOTH the per-tenant suppression table AND the platform-wide platform_suppression table; on a match, forces NEW.status := 'suppressed' and returns early (short-circuiting the frequency-cap check below — a suppressed send is never also frequency-cap-evaluated).
3. Frequency cap — marketing-class only (is_transactional=false), excludes test sends (is_test=false), only when recipient_customer_id IS NOT NULL. Upserts (INSERT ... ON CONFLICT (tenant_id, customer_id, period_start) DO UPDATE SET sent_count = sent_count + 1) the current month's notification_frequency_tracker row atomically, returning the post-increment count. Fixed mid-build (self-caught, not from the design doc): the trigger as first written only demonstrated the atomic-increment mechanism with no actual numeric cap check — a real fix (20260719000009_notifications_frequency_cap_enforcement.sql) rewrote it to a genuine lock-then-check-then-increment: the tracker row is ensured, the per-tenant cap is read from admin.tenant_setting (category='notifications', key='marketing_frequency_cap_per_month', defaulting to 999999 — no behavioral change — if unset), and the increment UPDATE ... WHERE sent_count < v_cap only succeeds under the cap; a NULL result (cap already hit) sets NEW.status := 'quota_blocked'. Live-verified via a genuine 3-way concurrent psql & race at cap=2: exactly 2 rows landed pending, 1 quota_blocked, tracker sent_count=2.

Suppression-guard hardening (found by the mandatory independent lock-gate verification pass, 2026-07-19, fixed via 20260719000011_notifications_suppression_guard_hardening.sql): the adversarial verification pass (a genuinely separate agent, own fresh fixtures) found 1 BLOCKER and 2 MAJOR bugs in step 2's address-matching logic, all fixed:

• BLOCKER — whitespace bypass. The guard lowercased the incoming address but never trimmed it; a single leading/trailing space in recipient_contact->>'email' defeated a correctly-normalized suppression entry. Fixed: trim() added alongside lower() at the comparison point.
• MAJOR — case-mismatch on the stored side. suppression.address_normalized/platform_suppression.address_normalized had no enforcement forcing lowercase+trimmed storage — a mixed-case or padded stored row silently failed to match a correctly-normalized incoming address. Fixed: a new BEFORE INSERT OR UPDATE OF address_normalized normalization trigger on both tables (notifications.normalize_suppression_address()) forces NEW.address_normalized := lower(trim(NEW.address_normalized)) at write time — a bad value can no longer be stored at all, not just tolerated at read time.
• MAJOR — case-sensitive JSONB key lookup. The guard only checked the exact-case keys 'email'/'phone'; a caller violating the documented lowercase-key convention (e.g. {"Email": "..."}) silently skipped the suppression check entirely rather than failing closed. Fixed: the guard now falls back to a case-insensitive scan of the JSONB object's own keys via jsonb_each_text() when the exact-case key isn't present.

A 4th finding — ALTER DEFAULT PRIVILEGES IN SCHEMA notifications still grants authenticated full read/write to any FUTURE table created in this schema (the same latent-risk class independently found and fixed for platform.polymorphic_target_registry, PROJECT_DECISIONS #68) — is disclosed as an accepted residual risk, not fixed: platform_suppression itself is correctly locked down TODAY (0 grants, confirmed live); the risk only materializes if this specific table were ever dropped and recreated, which default privileges cannot selectively exempt. Logged to OPEN_ITEMS rather than papered over with a schema-wide REVOKE that would break every other table's legitimate access.

Design Rationale

• campaign.customer_segment_definition_id is a bare FK + trigger, not composite — crm.customer_segment_definition.tenant_id is nullable (genuine global "builtin" segment rows exist); a composite (id, tenant_id) FK cannot express "same-tenant OR global." Mirrors offers.offer_targeting_rule.segment_definition_id's own established precedent exactly.
• suppression.source_event_id/platform_suppression.source_event_id are bare FKs, not composite — the identical MATCH SIMPLE reasoning as above, applied to provider_event_log's own deliberately-nullable tenant_id. provider_event_log.id is a globally-unique PK regardless, so a bare FK loses nothing. Confirmed live: zero FKs anywhere in this codebase reference payments.stripe_event_log compositely either — the same trap would apply there too, making this the established (if previously implicit) precedent, now made explicit.
• delivery_attempt and notification stay MUTABLE, not append-only — Ruling 1: no delivery_event split. Real state evolves in place (status transitions, opened_at/clicked_at set post-facto), with the monotonic guard as the sole integrity backstop rather than an append-only ledger reconstructing "current state" via aggregation.
• provider_event_log/provider_event_dead_letter are genuinely NOT append-only — confirmed live via pg_get_functiondef that the Stripe tables this design mirrors carry zero triggers of any kind; status/processed_at/resolved_at legitimately update in place. The task's own explicit instruction ("verify live, don't assume strict append-only") was honored rather than defaulting to the codebase's more common append-only pattern.
• campaign_recipient is disclosed as "insert-once, 2 columns fill in later," not append-only or immutable — notification_id/suppression_reason are legitimately written post-insert as targeting resolves into an actual send or an exclusion. Naming this honestly (per Recurring Bug Class #2's own warning against append-only mislabeling) avoids either over-restricting the table or under-disclosing its actual mutability.
• Retention posture (Ruling 16) — stated once, no decision made. notification/delivery_attempt/in_app_notification (transactional-record tables) likely warrant a longer retention horizon than provider_event_log/provider_event_dead_letter (raw provider events, shorter-value once ingested). No horizon is picked here; all 7 ledger-shaped tables are UUIDv7-keyed, keeping time-range partitioning open at near-zero cost whenever a horizon is actually decided.

Agent Authority Mapping

No new authority mechanism — pure consumer of identity.agent_duty_grant.

Table	automation_source	review_status/gate	decision_provenance	Rationale
notification_template	✅	✅	✅	AI-drafted copy = draft_only; goes live only via is_active
notification	✅	✅ (rare — manual-origin only)	✅	Campaign/journey-origin rows inherit their gate from the parent
in_app_notification	✅	✅	✅	Same reasoning as notification
campaign	✅	✅	✅	The flagship draft_only case (chk_campaign_draft_gate)
delivery_attempt	❌	❌	❌	Deterministic system execution — no independent decision per attempt
notification_preference	❌	❌	❌	Pure human/customer product setting
notification_quota_usage / notification_frequency_tracker	❌	❌	❌	Mechanical counters
inbound_message	❌	❌	❌	Inbound data capture
notification_journey / journey_step / journey_enrollment	N/A (dormant)	N/A	N/A	Out of v1 scope
campaign_recipient	❌	❌	❌	A derived snapshot
provider_event_log / provider_event_dead_letter	❌	❌	❌	Raw external evidence ingestion
suppression	❌ (but created_by_actor_id distinguishes automatic vs. manual)	❌ — deliberately may_act_alone, never gated	❌	A human-in-the-loop delay here IS the compliance risk — the opposite of the usual caution
sender_identity	❌	❌	❌	Verification is a deterministic DNS/provider check, never agent-guessed
platform_suppression	N/A — service-role-only, no autonomy surface	N/A	N/A	Platform-owned infrastructure

Open items carried forward (see OPEN_ITEMS.md for the full, current wording)

• notification_preference.actor_id/in_app_notification.recipient_actor_id/inbound_message.handled_by_actor_id/campaign.created_by_actor_id — retargeted in THIS build (not deferred; listed here only because the RULED design had flagged them as pending).
• source_module CHECK's exact producer list and provider_event_log.event_type's exact Resend/Twilio vocabulary — both need Build-time finalization against real producers/webhooks.
• delivery_attempt.max_attempts's actual default/tenant-override mechanism — a config-value decision, not a schema-shape one.
• pg_cron job definitions (retry sweeps, sender_identity re-verification, campaign scheduling) — extension installed, zero jobs created; service-build work.
• A malicious/careless manual suppression of a legitimate address — audit-trailed via created_by_actor_id, not fully preventable at the schema layer.
• POS↔Notifications offline-print integration shape — notification.printed_at exists; no offline-sync quintet (client_uuid/sync_status/etc.) proposed on notification itself.
• notification_quota_usage.sent_count/campaign.recipient_count reconciliation view — mirrors inventory.stock_reconciliation_shell's "minimal shell now" precedent, not built this pass.
• sender_identity 'byo' verification-lifecycle machinery — the column exists (domain_mode), the DNS/provider verification workflow does not.
• Retention horizon (Ruling 16) — posture stated, no horizon picked; UUIDv7 keeps partitioning open.