Schema Evolution

The core schema and the enforcement boundary: enums, core tables, RLS, and the event state-machine RPC + gate evaluator.

• +pgcrypto extension
• +Enums in lockstep with @uploz/shared-types (operational_state, node_state, severity, ack_level, dependency_type)
• +Core event tables (events, event_nodes, event_roles, …) with row-level security
• +fn_transition_event_state — the only write path for an event lifecycle
• +fn_evaluate_gates — shared by the write path and the read-only "why blocked" preview (R2)

0001_set_a_foundation.sql

The substrate the rest of the platform builds on: resources, schedules, run-of-show, the dependency graph, comms, tasks, and instruction versioning.

• +node_resources + schedules (Resource Capacity / Schedule Conflict gate sources)
• +nuggets (run-of-show), dependencies (polymorphic graph), channels + messages
• +tasks, instruction_versions, acknowledgments, alerts, presence, Health Timeline feed
• +idempotency-key ledger
• +Extends fn_evaluate_gates (Resource Capacity + Schedule Conflict); adds fn_compute_readiness

0002_set_b.sql

The single write path for node_state, mirroring the event transition RPC with optimistic locking and a data-driven legal-transition table.

• +node_state_transitions — explicit legal edges, with auto_only system-only edges (DEGRADED/OFFLINE)
• +fn_transition_node_state — authority check, SELECT … FOR UPDATE, optimistic lock (PT409)
• +Appends to state_transition_log (scope "node")
• +Raises the canonical Error Contract (PT403/PT409/PT422)

0003_node_state_machine.sql

The instruction publish / supersede write path, with content-addressed versions and a conflicting-instruction blocker.

• +Unique (channel_id, content_hash) index — duplicate content is a hard DB invariant
• +fn_publish_instruction — content-addressed via md5(content)
• +fn_supersede_instruction — replace the canonical instruction on a channel
• +PT409 VERSION_CONFLICT (duplicate hash) · PT422 INSTRUCTION_CONFLICT (<24h, different author)

0004_vendor_governance.sql

Append-only per-node readiness snapshots, plus a thin activity-logging helper over activity_events.

• +readiness_checks — append-only snapshots (weighted score, gate ceiling, category breakdown, blocker count)
• +fn_snapshot_readiness — reuses fn_compute_readiness + fn_evaluate_gates per node
• +fn_log_activity — appends to activity_events

0005_readiness_activity.sql

INSERT-only audit / event / override logs with immutability enforced at the DB and member-only read access.

• +audit_log, event_log, override_log (INSERT-only)
• +prevent_mutation trigger — raises on any UPDATE or DELETE
• +RLS: SELECT restricted to event members (is_event_member)

0006_set_c_immutability.sql

Idempotent alert raise/resolve plus the escalation ladder the worker drives on a timer.

• +escalation_log (append-only history) + active_escalations (one row per escalating alert)
• +fn_raise_alert — idempotent on (event, type, source); re-raising an OPEN alert is a no-op
• +fn_resolve_alert — marks RESOLVED, stops escalation
• +fn_due_escalations — escalations the worker should fire now

0007_escalation.sql

At-most-once notification delivery keyed by an idempotency key, plus Web Push subscription storage.

• +notification_log — per-delivery record keyed by idempotency_key (append-only)
• +push_subscriptions — Web Push subscriptions per user
• +fn_enqueue_notification — idempotent insert (duplicate key silently ignored)
• +RLS: users manage their own push_subscriptions

0008_notifications.sql

Liveness pings per source and the system path that degrades silent nodes.

• +heartbeats — current liveness ping per (source_id, source_type), upserted
• +fn_heartbeat — upsert last_ping
• +fn_stale_presence — sources older than a per-kind threshold
• +fn_mark_node_degraded — system write path to DEGRADED + raises a CRITICAL alert

0009_presence_heartbeat.sql

The dedupe + locking primitives the write-path RPCs use to stay correct under concurrent / retried requests.

• +idempotency_keys — request_hash dedupe; retries replay the stored response jsonb
• +live_locks — coarse advisory-style lock rows serializing vendor-channel writes
• +fn_acquire_lock / fn_release_lock
• +PT409 IDEMPOTENCY_CONFLICT · PT423 RESOURCE_LOCKED

0010_concurrency.sql

Automatic audit_log triggers on the key business tables — auditing that never blocks the originating write.

• +AFTER INSERT/UPDATE triggers on events, event_roles, instruction_versions, alerts
• +Each appends an immutable audit_log row (actor, action, entity, NEW snapshot)
• +Errors swallowed (raise notice) so a failed append degrades gracefully
• +SECURITY DEFINER with a pinned search_path

0011_audit_wiring.sql

Governance overrides recorded into the INSERT-only override_log under per-category policy.

• +fn_apply_override — writes override_log under category policy
• +AESTHETIC / LOGISTIC: reason ≥ 20 chars · OPERATIONAL_SAFETY: second approver required
• +SECURITY / CONTENT: always rejected (PT403)
• +Unknown category → PT422 BAD_REQUEST

0012_override_engine.sql

A typed change feed over the INSERT-only event_log, plus an ordered replay function per event.

• +AFTER INSERT/UPDATE triggers on events, event_nodes, alerts, instruction_versions
• +Each appends a typed event_log row (event_type, source_object_type/id, NEW snapshot)
• +fn_replay_event — ordered slice of the log for one event, optionally time-bounded
• +Errors swallowed so emitting the feed never blocks the write

0013_event_replay.sql

Multi-node vendor-channel governance: channel modes plus a sub-lead recommendation → lead forward flow.

• +channels.mode (MULTI_NODE | PER_NODE)
• +sub_lead_recommendations — drafted recommendations on a channel
• +fn_recommend_instruction (member write) / fn_forward_recommendation (authority write)
• +PT404 NOT_FOUND · PT409 VERSION_CONFLICT (already forwarded)

0014_vendor_multinode.sql

Critical-path flagging over the dependency graph, mirroring the validation-engine critical-path module.

• +fn_recompute_critical_path — flags is_on_critical_path on the longest DEPENDS_ON / REQUIRES_SYNC chain
• +Downstream dependency cascade helper
• +Bounded traversal: hard depth cap + cycle guard (terminates on large / cyclic graphs)

0015_critical_path.sql

Read-only reporting helpers over the append-only readiness_checks history.

• +fn_readiness_trend — ordered time-series for one node within a trailing window
• +fn_readiness_summary — latest snapshot per node (score, ceiling, blocker_count)
• +SECURITY INVOKER + STABLE: pure SELECTs governed by existing RLS

0016_readiness_trend.sql

Links a task to the dependency edge it satisfies and cascades readiness when fulfillment flips.

• +tasks.dependency_id — additive FK to dependencies(id)
• +AFTER UPDATE trigger — mirrors is_fulfilled to the dependency and re-snapshots touched nodes
• +fn_verify_task / fn_reopen_task — authority-gated (PT403), idempotent
• +Cascade never aborts the originating write (degrades to a notice)

0017_task_fulfillment.sql

Filename-derived entry: 0018_event_clusters.sql

0018_event_clusters.sql

Filename-derived entry: 0019_notification_templates.sql

0019_notification_templates.sql

Filename-derived entry: 0020_analytics_views.sql

0020_analytics_views.sql

Filename-derived entry: 0021_retention.sql

0021_retention.sql

Filename-derived entry: 0022_metrics.sql

0022_metrics.sql

Filename-derived entry: 0023_rls_refine.sql

0023_rls_refine.sql

Filename-derived entry: 0024_rate_limit.sql

0024_rate_limit.sql

Filename-derived entry: 0025_full_text_search.sql

0025_full_text_search.sql

Filename-derived entry: 0026_performance_indexes.sql

0026_performance_indexes.sql

Filename-derived entry: 0027_mv_node_readiness.sql

0027_mv_node_readiness.sql

Filename-derived entry: 0028_event_export.sql

0028_event_export.sql

Filename-derived entry: 0029_outbound_webhooks.sql

0029_outbound_webhooks.sql

Filename-derived entry: 0030_cluster_report.sql

0030_cluster_report.sql

Filename-derived entry: 0031_presence_location.sql

0031_presence_location.sql

Filename-derived entry: 0032_event_templates.sql

0032_event_templates.sql

Filename-derived entry: 0033_phase3_ai_stub.sql

0033_phase3_ai_stub.sql

Filename-derived entry: 0034_archive_completed_event.sql

0034_archive_completed_event.sql

Filename-derived entry: 0035_silent_channels.sql

0035_silent_channels.sql

Filename-derived entry: 0036_event_report.sql

0036_event_report.sql

Filename-derived entry: 0037_audit_search.sql

0037_audit_search.sql

Filename-derived entry: 0038_ops_metrics_views.sql

0038_ops_metrics_views.sql

Filename-derived entry: 0039_alert_sla.sql

0039_alert_sla.sql

Filename-derived entry: 0040_fn_export_csv.sql

0040_fn_export_csv.sql

Filename-derived entry: 0041_fn_capacity_plan.sql

0041_fn_capacity_plan.sql

Filename-derived entry: 0041_user_notification_prefs.sql

0041_user_notification_prefs.sql

Filename-derived entry: 0042_seed_event_templates.sql

0042_seed_event_templates.sql

Filename-derived entry: 0043_presence_sessions.sql

0043_presence_sessions.sql

Filename-derived entry: 0044_fn_staffing_suggest.sql

0044_fn_staffing_suggest.sql

Filename-derived entry: 0045_fn_event_risk_score.sql

0045_fn_event_risk_score.sql

Filename-derived entry: 0046_fn_readiness_history.sql

0046_fn_readiness_history.sql

Filename-derived entry: 0046_fn_silent_channels.sql

0046_fn_silent_channels.sql

Filename-derived entry: 0046_fn_submit_feedback.sql

0046_fn_submit_feedback.sql

Filename-derived entry: 0047_fn_role_vacancies.sql

0047_fn_role_vacancies.sql

Filename-derived entry: 0048_fn_reorder_nugget.sql

0048_fn_reorder_nugget.sql

Filename-derived entry: 0049_v_presence_now.sql

0049_v_presence_now.sql

Filename-derived entry: 0050_fn_event_analytics.sql

0050_fn_event_analytics.sql

Filename-derived entry: 0051_fn_sla_breaches.sql

0051_fn_sla_breaches.sql

Filename-derived entry: 0052_v_node_health.sql

0052_v_node_health.sql

Filename-derived entry: 0053_fn_event_feedback_score.sql

0053_fn_event_feedback_score.sql

Filename-derived entry: 0054_escalation_log.sql

0054_escalation_log.sql

Filename-derived entry: 0054_fn_channel_stats.sql

0054_fn_channel_stats.sql

Filename-derived entry: 0054_incidents.sql

0054_incidents.sql

Filename-derived entry: 0055_fn_export_event_json.sql

0055_fn_export_event_json.sql

Filename-derived entry: 0055_fn_readiness_band.sql

0055_fn_readiness_band.sql

Filename-derived entry: 0055_override_log.sql

0055_override_log.sql

Filename-derived entry: 0056_audit_log.sql

0056_audit_log.sql

Filename-derived entry: 0056_fn_incident_summary.sql

0056_fn_incident_summary.sql

Filename-derived entry: 0057_event_log.sql

0057_event_log.sql

Filename-derived entry: 0057_fn_ack_coverage.sql

0057_fn_ack_coverage.sql

Filename-derived entry: 0058_fn_incident_candidates.sql

0058_fn_incident_candidates.sql

Filename-derived entry: 0058_fn_replay_event.sql

0058_fn_replay_event.sql

Filename-derived entry: 0059_fn_blocker_digest.sql

0059_fn_blocker_digest.sql

Filename-derived entry: 0059_notification_log.sql

0059_notification_log.sql

Filename-derived entry: 0060_kpi_views.sql

0060_kpi_views.sql

Filename-derived entry: 0060_v_event_blocker_summary.sql

0060_v_event_blocker_summary.sql

Filename-derived entry: 0061_fn_mark_event_needs_work.sql

0061_fn_mark_event_needs_work.sql

Filename-derived entry: 0061_perf_indexes.sql

0061_perf_indexes.sql

Filename-derived entry: 0062_fn_capture_global_kpis.sql

0062_fn_capture_global_kpis.sql

Filename-derived entry: 0062_readiness_rollup_view.sql

0062_readiness_rollup_view.sql

Filename-derived entry: 0063_activity_indexes.sql

0063_activity_indexes.sql

Filename-derived entry: 0064_fn_event_summary.sql

0064_fn_event_summary.sql

Filename-derived entry: 0065_seed_reference.sql

0065_seed_reference.sql

Filename-derived entry: 0082_fn_vendor_detail.sql

0082_fn_vendor_detail.sql

Filename-derived entry: 0083_incident_view.sql

0083_incident_view.sql

Filename-derived entry: 0084_fn_presence_rollup.sql

0084_fn_presence_rollup.sql

Filename-derived entry: 0085_message_indexes.sql

0085_message_indexes.sql

Filename-derived entry: 0086_fn_export_roles_json.sql

0086_fn_export_roles_json.sql

Filename-derived entry: 0087_notification_indexes.sql

0087_notification_indexes.sql

Filename-derived entry: 0088_role_grants.sql

0088_role_grants.sql

Filename-derived entry: 0089_fn_log_escalation_service_role.sql

0089_fn_log_escalation_service_role.sql

Filename-derived entry: 0090_quality_findings.sql

0090_quality_findings.sql

Filename-derived entry: 0091_quality_ledger.sql

0091_quality_ledger.sql

Filename-derived entry: 0092_fn_quality_rpcs.sql

0092_fn_quality_rpcs.sql

Filename-derived entry: 0093_fn_quality_snapshot.sql

0093_fn_quality_snapshot.sql

Filename-derived entry: 0094_createrz_user_group_taxonomy.sql

0094_createrz_user_group_taxonomy.sql

Filename-derived entry: 0095_fix_auth_uid_jwt_fallback.sql

0095_fix_auth_uid_jwt_fallback.sql

Filename-derived entry: 0150_agent_runs.sql (max migration id)

0150_agent_runs.sql