Schema Browser

Every table the Supabase migrations 0001–0034 create, grouped by layer with its columns and row-level-security note. The Supabase CLI owns the schema (R5); Prisma is types-only. The DB is the enforcement boundary — event-scoped tables read via is_event_member and write via has_event_authority, the INSERT-only ledgers add a prevent_mutation trigger, and the infra tables default-deny (reached only by the SECURITY DEFINER write RPCs).

Tables

Core

Pipeline

Governance

Operational

MVP02 / Phase 3

Core

Set A foundation — events, nodes, roles, and the state-machine config.

profiles0001

Self-managed (per user)

One row per auth user — display name, language, theme.

Column	Type	Note
id	uuid	pk → auth.users
display_name	text	default ''
lang	text	check en/he/hi
theme	text	check system/light/dark
created_at	timestamptz

RLS: a user selects/inserts/updates only their own row (id = auth.uid()).

events0001

Members read · authority write

The top-level event aggregate; operational_state is driven only by fn_transition_event_state.

Column	Type	Note
id	uuid	pk
code	text	unique
name	text
operational_state	operational_state	enum, default DRAFT
version_number	integer	optimistic lock
created_by	uuid	fk → auth.users
cluster_id	uuid	fk → event_clusters (added 0018)

RLS: members select; creator inserts (created_by = auth.uid()); PRODUCER/ADMIN update.

event_nodes0001

Members read · authority write

A node within an event; node_state advances via fn_transition_node_state (system-only DEGRADED/OFFLINE).

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events, cascade
name	text
node_state	node_state	enum, default DRAFT
readiness_score	numeric(5,2)
is_mandatory	boolean
last_heartbeat	timestamptz
version_number	integer	optimistic lock

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

event_roles0001

Members read · authority write

Role slots on an event/node — the ROLE_UNFILLED gate source for STAFFING → VENDOR_ALIGNMENT.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
app_role	app_role	enum, default MEMBER
role_category	role_category	enum, default GENERAL
is_required	boolean
assignee_id	uuid	fk → auth.users
fulfillment_status	fulfillment_status	enum, default EMPTY

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

invitations0001

Members read · authority write

Single-use invite tokens binding an email to a role slot, expiring after 72h.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
role_id	uuid	fk → event_roles
token	uuid	unique, single-use
email	text
expires_at	timestamptz	default now()+72h
accepted_at	timestamptz

RLS: members select; PRODUCER/ADMIN/NODE_LEAD write.

state_transition_log0001

Append-only (immutable)

Append-only audit of every event/node state transition the RPCs commit.

Column	Type	Note
id	bigint	identity pk
event_id	uuid	fk → events
scope	text	check event/node
scope_id	uuid
from_state	text
to_state	text
triggered_by	uuid

RLS: members select via is_event_member(event_id). Full UPDATE/DELETE lock-down via Set C.

state_transitions0001

Config / lookup

Config table making legal event transitions explicit; auto_only edges are system-driven (LIVE → NEEDS_WORK).

Column	Type	Note
from_state	operational_state	pk part
to_state	operational_state	pk part
auto_only	boolean	system-only edge

Lookup table seeded by the migration; read by fn_transition_event_state.

node_state_transitions0003

Config / lookup

Legal node-state edges; health edges (DEGRADED/OFFLINE) are auto_only — driven by heartbeats, not manual RPC.

Column	Type	Note
from_state	node_state	pk part
to_state	node_state	pk part
auto_only	boolean	system-only edge

Lookup table seeded by the migration; read by fn_transition_node_state.

Pipeline

Set B substrate that feeds the gate evaluator and readiness scoring.

node_resources0002

Members read · authority write

Required vs available quantities per resource — the RESOURCE_CAPACITY gate source.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
resource_type	text
required_qty	integer
available_qty	integer

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

role_schedules0002

Members read · authority write

Time windows per role/node — the SCHEDULE_CONFLICT gate source (overlapping assignee bookings).

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
role_id	uuid	fk → event_roles
node_id	uuid	fk → event_nodes
start_at	timestamptz
end_at	timestamptz

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

nuggets0002

Members read · authority write

Run-of-show units — hierarchical, sequenced, with assigned nodes / required roles / mood tags.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
title	text
estimated_duration	integer
assigned_nodes	uuid[]
required_roles	text[]
parent_nugget_id	uuid	fk → nuggets (self)
sequence_order	integer

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

dependencies0002

Members read · authority write

Polymorphic cross-object edge graph; is_on_critical_path is recomputed by fn_recompute_critical_path.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
source_type / source_id	text / uuid
target_type / target_id	text / uuid
dependency_type	dependency_type	enum
is_fulfilled	boolean
is_on_critical_path	boolean

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

channels0002

Members read · authority write

Comms channels per event/node; mode (MULTI_NODE/PER_NODE) added by 0014 for vendor governance.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
type	text	default GENERAL
is_vendor	boolean

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

messages0002

Members insert own rows

Channel messages; members may post their own, authority has full write.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
channel_id	uuid	fk → channels
author_id	uuid	fk → auth.users
body	text

RLS: members select; members insert with author_id = auth.uid(); authority full-write.

tasks0002

Members read · authority write

Work items; dependency_id (0017) links a task to the edge it satisfies and cascades readiness on fulfillment.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
assignee_id	uuid	fk → auth.users
status	text	default open
is_fulfilled	boolean
dependency_id	uuid	fk → dependencies (added 0017)

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

instruction_versions0002

Members read · authority write

Content-addressed instruction history; unique (channel_id, content_hash) makes duplicate content a hard invariant.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
channel_id	uuid	fk → channels
content_hash	text	md5(content); unique w/ channel
is_canonical	boolean
superseded_by	uuid	fk → instruction_versions

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

acknowledgments0002

Members insert own rows

Polymorphic acks at a given ack_level; members may insert their own.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
user_id	uuid	fk → auth.users
object_type / object_id	text / uuid
acknowledgment_level	ack_level	enum

RLS: members select; members insert with user_id = auth.uid(); authority full-write.

alerts0002

Members read · authority write

The source of truth for an alert; raised idempotently by fn_raise_alert on (event, type, source).

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
severity	severity	enum, default WARNING
blocker_code	text	idempotency type
status	text	default OPEN
source_object_type / _id	text / uuid

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

presence_events0002

Members read · authority write

Per-user presence status changes within an event/node.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
user_id	uuid	fk → auth.users
status	text
changed_at	timestamptz

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

activity_events0002

Members read · authority write

The Health Timeline feed — readiness_before/after deltas plus a jsonb payload per occurrence.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
type	text
readiness_before	numeric(5,2)
readiness_after	numeric(5,2)
payload	jsonb

RLS: members read via is_event_member(event_id); writes require has_event_authority (PRODUCER/ADMIN/NODE_LEAD).

readiness_checks0005

Append-only (immutable)

Append-only per-node readiness snapshots — weighted score, gate ceiling, category breakdown, blocker count.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
node_id	uuid	fk → event_nodes
weighted_score	numeric(5,2)
readiness_ceiling	numeric(5,2)
category_scores	jsonb
blocker_count	integer

RLS: members select via is_event_member(event_id); written by fn_snapshot_readiness.

Governance

Vendor / instruction governance and the immutable override flow.

override_log0006

Append-only (immutable)

INSERT-only record of governance overrides; reason_text ≥ 20 chars is a DB-level check.

Column	Type	Note
id	bigint	identity pk
event_id	uuid
user_id	uuid
user_role_at_time	text
action_type / entity_type / entity_id	text / text / uuid
reason_text	text	check length ≥ 20

RLS: members select via is_event_member(event_id); prevent_mutation blocks UPDATE/DELETE. Written by fn_apply_override.

sub_lead_recommendations0014

Members insert own rows

Drafted sub-lead recommendations on a vendor channel; forwarded flips once when a Lead publishes the canonical instruction.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
channel_id	uuid	fk → channels
author_id	uuid	fk → auth.users
forwarded	boolean
instruction_id	uuid	fk → instruction_versions

RLS: members select; members insert with author_id = auth.uid(); authority full-write.

Operational

Immutable ledgers, escalation, notifications, presence, concurrency.

audit_log0006

Append-only (immutable)

INSERT-only audit trail wired by triggers on events, event_roles, instruction_versions, alerts (0011).

Column	Type	Note
id	bigint	identity pk
event_id	uuid
actor	uuid
action_type	text
entity_type / entity_id	text / uuid
payload	jsonb

RLS: members select; prevent_mutation blocks UPDATE/DELETE.

event_log0006

Append-only (immutable)

INSERT-only typed change feed (0013); fn_replay_event returns an ordered slice per event.

Column	Type	Note
id	bigint	identity pk
event_id	uuid
event_type	text
source_object_type / _id	text / uuid
payload	jsonb
processed_at	timestamptz

RLS: members select; prevent_mutation blocks UPDATE/DELETE.

escalation_log0007

Append-only (immutable)

Append-only history of every escalation notification fired against an alert.

Column	Type	Note
id	bigint	identity pk
alert_id	uuid	fk → alerts
escalation_level	integer
notified_user_id	uuid
notification_channel	text
acknowledged_at	timestamptz

RLS: members select (via the alert’s event); prevent_mutation blocks DELETE.

active_escalations0007

Members read · authority write

Live ladder state — one row per still-escalating alert; fn_due_escalations drives the worker.

Column	Type	Note
alert_id	uuid	pk → alerts
current_level	integer
next_escalation_at	timestamptz
escalation_history	jsonb

RLS: members select (via the alert’s event); written by the escalation RPCs.

notification_log0008

Append-only (immutable)

At-most-once delivery record keyed by idempotency_key; fn_enqueue_notification dedupes on conflict.

Column	Type	Note
id	uuid	pk
user_id	uuid
event_id	uuid	fk → events
alert_id	uuid	fk → alerts
channel	text
idempotency_key	text	unique

RLS: members select via is_event_member(event_id); prevent_mutation blocks UPDATE/DELETE.

push_subscriptions0008

Self-managed (per user)

Web Push subscriptions registered per user; unique (user_id, endpoint).

Column	Type	Note
id	uuid	pk
user_id	uuid
endpoint	text	unique w/ user
p256dh	text
auth	text

RLS: a user selects/inserts/deletes only their own (user_id = auth.uid()).

heartbeats0009

Members read · authority write

Current liveness ping per source (node/user), upserted by fn_heartbeat; fn_stale_presence detects silence.

Column	Type	Note
id	uuid	pk
source_id	uuid
source_type	text	check node/user
event_id	uuid	fk → events
last_ping	timestamptz

RLS: members select via is_event_member(event_id); writes via SECURITY DEFINER fn_heartbeat.

idempotency_keys0010

Infra · definer-only

Request dedupe / response replay; a key reused with a different request_hash is rejected (PT409).

Column	Type	Note
id	bigint	identity pk
key	text	unique
request_hash	text
response	jsonb	replayed on retry

RLS on with no permissive policy → default-deny; reached only by definer write RPCs / service role.

live_locks0010

Infra · definer-only

Coarse advisory-style locks; unique (resource_type, resource_id) is the mutual-exclusion mechanism (PT423).

Column	Type	Note
id	bigint	identity pk
resource_type / resource_id	text / uuid	unique together
locked_by	uuid
lock_reason	text

RLS on with no permissive policy → default-deny; claimed/released via fn_acquire_lock / fn_release_lock.

MVP02 / Phase 3

Clusters, templates, metrics, webhooks, AI stubs, and archive tables.

event_clusters0018

Members read · authority write

Groups events into a cluster; is_cluster_member / has_cluster_authority reuse the per-event predicates.

Column	Type	Note
id	uuid	pk
name	text
created_by	uuid	fk → auth.users

RLS: cluster members select; cross-event reads gated by is_cluster_member. events.cluster_id added here.

notification_templates0019

Append-only (immutable)

Append-only localized templates; a new revision is a fresh (key, locale) row. fn_render_template does {{var}} substitution.

Column	Type	Note
id	uuid	pk
key	text	unique w/ locale
locale	text	check en/he/hi
subject	text
body	text

Append-only (prevent_mutation). unique (key, locale); locale check en/he/hi.

kpi_snapshots0022

Append-only (immutable)

Append-only KPI time series; event-scoped or global (event_id null).

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events (nullable = global)
scope	text	default event
key	text
value	numeric
captured_at	timestamptz

RLS: event-scoped rows visible to members; global rows visible to any authenticated caller. prevent_mutation.

rate_limit_hits0024

Infra · definer-only

Fixed-window counter buckets per key; fn_rate_limit does an atomic check-and-increment.

Column	Type	Note
key	text	pk part
window_start	timestamptz	pk part
count	integer

RLS on with no permissive policy → default-deny; touched only by definer fn_rate_limit / service role.

outbound_webhooks0029

Members read · authority write

Per-event webhook registrations — target url, signing secret, and the event types to deliver.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
url	text
secret	text	HMAC signing key
events	text[]
is_active	boolean

RLS: event-scoped; members read, authority write.

outbound_webhook_deliveries0029

Append-only (immutable)

Append-only delivery log; the worker advances state by inserting fresh rows. unique (webhook_id, idempotency_key).

Column	Type	Note
id	uuid	pk
webhook_id	uuid	fk → outbound_webhooks
event_id	uuid	fk → events
event_type	text
status	text	default PENDING
idempotency_key	text	unique w/ webhook

Append-only (prevent_mutation); event-scoped read via is_event_member.

event_templates0032

Self-managed (per user)

Phase-3 reusable event templates with provisional art_form/style/mood taxonomy; published_at non-null ⇒ public.

Column	Type	Note
id	uuid	pk
name	text
author_id	uuid	fk → auth.users
art_form / style / mood	text	provisional taxonomy
spec	jsonb	provisional shape
published_at	timestamptz	non-null ⇒ public

RLS: authors manage their own; published templates are publicly readable. Taxonomy columns are provisional (Phase 3).

template_uses0032

Append-only (immutable)

Append-only usage log — who instantiated which template, when.

Column	Type	Note
id	uuid	pk
template_id	uuid	fk → event_templates
used_by	uuid	fk → auth.users
used_at	timestamptz

Append-only (prevent_mutation).

ai_suggestions0033

Append-only (immutable)

Phase-3 AI suggestions captured against an event, with a 0..1 confidence check. Append-only.

Column	Type	Note
id	uuid	pk
event_id	uuid	fk → events
kind	text
payload	jsonb
confidence	numeric(5,4)	check 0..1

Append-only (prevent_mutation); event-scoped. Phase-3 stub — capture only.

nugget_taxonomy0033

Append-only (immutable)

Phase-3 global controlled vocabulary for nugget tagging (mood / camera / sound / emotion). Append-only.

Column	Type	Note
id	uuid	pk
tag	text	unique
mood / camera / sound / emotion	text

Append-only (prevent_mutation); global (not event-scoped). Phase-3 stub.

events_archive0034

Append-only (immutable)

Cold copy of a completed event written by fn_archive_completed_event; mirrors live columns + archival bookkeeping, no FKs back.

Column	Type	Note
id	uuid	pk (live id)
code	text
operational_state	text	flattened from enum
archived_at	timestamptz
archived_by	uuid

Archive snapshot; relationships preserved by id value only (no FKs to live tables).

event_nodes_archive0034

Append-only (immutable)

Cold copy of a completed event’s nodes (sibling of events_archive).

Column	Type	Note
id	uuid	pk (live id)
event_id	uuid	by value
node_state	text	flattened from enum
readiness_score	numeric(5,2)
archived_at	timestamptz

Archive snapshot; no FKs to live tables.

event_roles_archive0034

Append-only (immutable)

Cold copy of a completed event’s role slots.

Column	Type	Note
id	uuid	pk (live id)
event_id	uuid	by value
app_role / role_category	text	flattened from enums
fulfillment_status	text	flattened from enum
archived_at	timestamptz

Archive snapshot; no FKs to live tables.

invitations_archive0034

Append-only (immutable)

Cold copy of a completed event’s invitations.

Column	Type	Note
id	uuid	pk (live id)
event_id	uuid	by value
role_id	uuid	by value
token	uuid
archived_at	timestamptz

Archive snapshot; no FKs to live tables.

Authored from the migration DDL itself (illustrative project-tracking state), not a query against live data. Columns are the notable ones per table, not always exhaustive for wide tables; types are abbreviated for display.