Database schema

The brain's schema is defined by the SQLAlchemy models under z4j_brain/persistence/models and applied by the Alembic migrations that z4j migrate runs. The table section below is rendered from those models by scripts/gen_database_schema_doc.py, and the release suite fails when the page and the models disagree, so what you read here is what the current build creates.

How to read it:

• Column types are the PostgreSQL types. The self-contained SQLite backend runs the same models with SQLite's type mapping.
• enum name(...) names the PostgreSQL enum type and lists every value it accepts, in the order the model declares them. Adding a value is a migration, not just a code change, and a value added by a later migration is appended to the type's own label order in a real database: project_role reads viewer, operator, admin, auditor there, so do not rely on the printed order for ORDER BY or comparisons.
• FK -> table.column (CASCADE) names the referenced column and the ON DELETE rule.
• default shows the database-side default. Columns without one are set by the application.
• Two tables are range-partitioned on PostgreSQL by the migrations rather than by the models, so the clause does not appear below: events by occurred_at and schedule_fires by scheduled_for. Each has a worker that creates the daily partitions ahead of time and drops the ones past retention (see Backups and Schedule fire history). On PostgreSQL the partition key is part of those tables' primary keys and unique constraints; SQLite is not partitioned and keeps the plain constraints shown here.

Related pages: Database migrations, Backup and restore, Audit log, HMAC audit chain.

58 tables, in alphabetical order.

agent_offline_alerts

One offline episode already alerted for an agent.

Column	Type	Null	Notes
agent_id	uuid	no	FK -> agents.id (CASCADE)
anchor_at	timestamptz	no
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_agent_offline_alerts_agent_anchor: (agent_id, anchor_at).

Indexes:

• ix_agent_offline_alerts_created on (created_at)

agent_status_history

A single agent_status frame snapshot.

Column	Type	Null	Notes
id	bigint	no	PK
project_id	uuid	no	FK -> projects.id (CASCADE)
agent_id	uuid	no	FK -> agents.id (CASCADE)
captured_at	timestamptz	no
payload	jsonb	no	default {}

Indexes:

• agent_status_history_agent_time_idx on (agent_id, captured_at)
• agent_status_history_project_time_idx on (project_id, captured_at)

agent_workers

One discrete worker process under an agent_id.

Column	Type	Null	Notes
agent_id	uuid	no	FK -> agents.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
worker_id	varchar(128)	yes
role	varchar(32)	yes
framework	varchar(40)	yes
pid	integer	yes
started_at	timestamptz	yes
state	varchar(20)	no	default online
last_seen_at	timestamptz	yes
last_connect_at	timestamptz	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_agent_workers_agent_worker: (agent_id, worker_id).

Indexes:

• ix_agent_workers_agent_state on (agent_id, state)
• ix_agent_workers_project_state on (project_id, state)
• ux_agent_workers_legacy_agent on (agent_id) (unique, where worker_id IS NULL)

agents

A connected (or registered-but-offline) agent.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
name	varchar(200)	no
token_hash	varchar	no	unique
protocol_version	varchar(20)	no
framework_adapter	varchar(40)	no
engine_adapters	text[]	no	default {}
scheduler_adapters	text[]	no	default {}
capabilities	jsonb	no	default {}
state	enum agent_state(online, offline, unknown)	no	default unknown
last_seen_at	timestamptz	yes
last_connect_at	timestamptz	yes
metadata	jsonb	no	default {}
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()
revoked_at	timestamptz	yes

Unique uq_agents_project_name: (project_id, name).

Unique uq_agents_token_hash: (token_hash).

Indexes:

• ix_agents_last_seen_at on (last_seen_at)
• ix_agents_project_state on (project_id, state)

api_keys

A personal API key for programmatic access to the brain.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
name	varchar(200)	no
token_hash	varchar	no	unique
prefix	varchar(8)	no
last_used_at	timestamptz	yes
last_used_ip	varchar	yes
expires_at	timestamptz	yes
revoked_at	timestamptz	yes
revoked_reason	varchar(200)	yes
scopes	text[]	no	default {}
project_id	uuid	yes	FK -> projects.id (CASCADE)
allowed_cidrs	jsonb	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_api_keys_token_hash: (token_hash).

Indexes:

• ix_api_keys_project_id on (project_id)
• ix_api_keys_token_hash on (token_hash)
• ix_api_keys_user_id on (user_id)

audit_chain_preparation

Authenticated bridge between the preparation and activation revisions.

Column	Type	Null	Notes
singleton_id	varchar(32)	no	PK, default 'audit-chain'
format_version	integer	no
preparation_id	uuid	no	unique
audit_key_id	varchar(64)	no
preparation_revision	varchar(80)	no
target_activation_revision	varchar(80)	no
preparation_mac	varchar(64)	no

Unique uq_audit_chain_preparation_preparation_id: (preparation_id).

Check ck_audit_chain_preparation_format_version: format_version = 1.

Check ck_audit_chain_preparation_singleton_id: singleton_id = 'audit-chain'.

audit_chain_state

The one authenticated state machine for the active audit generation.

Column	Type	Null	Notes
singleton_id	varchar(32)	no	PK, default 'audit-chain'
format_version	integer	no
generation	uuid	no
installation_id	uuid	no
state_key_id	varchar(64)	no
head_row_hmac	varchar(64)	yes
head_hmac_key_id	varchar(64)	yes
head_occurred_at	timestamptz	yes
head_id	uuid	yes
prune_row_hmac	varchar(64)	yes
prune_hmac_key_id	varchar(64)	yes
prune_occurred_at	timestamptz	yes
prune_id	uuid	yes
active_row_count	bigint	no
observed_active_row_count	bigint	no	default 0
active_key_counts	jsonb	no
frozen_row_count	bigint	no
frozen_snapshot_digest	varchar(64)	yes
retired_recovery_binding	jsonb	yes
state_mac	text	no

Check ck_audit_chain_state_active_row_count_nonnegative: active_row_count >= 0.

Check ck_audit_chain_state_format_version: format_version = 1.

Check ck_audit_chain_state_frozen_row_count_nonnegative: frozen_row_count >= 0.

Check ck_audit_chain_state_observed_active_row_count_nonnegative: observed_active_row_count >= 0.

Check ck_audit_chain_state_singleton_id: singleton_id = 'audit-chain'.

audit_forward_state

Durable per-sink delivery cursor for the audit forwarder.

Column	Type	Null	Notes
sink_id	varchar(64)	no	PK
last_forwarded_occurred_at	timestamptz	yes
last_forwarded_id	uuid	yes
last_attempt_at	timestamptz	yes
last_success_at	timestamptz	yes
consecutive_failures	integer	no	default 0
updated_at	timestamptz	no	default now()

audit_log

A single audit-log entry.

Column	Type	Null	Notes
project_id	uuid	yes
user_id	uuid	yes
api_key_id	uuid	yes
action	varchar(80)	no
target_type	varchar(40)	no
target_id	varchar(200)	yes
result	varchar(20)	no
metadata	jsonb	no	default {}
source_ip	inet	yes
user_agent	text	yes
occurred_at	timestamptz	no	default now()
outcome	varchar(20)	yes
event_id	uuid	yes
row_hmac	varchar(64)	yes
prev_row_hmac	varchar(64)	yes
legacy_frozen	boolean	yes
hmac_version	integer	yes
hmac_key_id	varchar(64)	yes
legacy_integrity_class	varchar(64)	yes
legacy_origin	varchar(120)	yes
chain_generation	uuid	yes
id	uuid	no	PK

Indexes:

• ix_audit_log_action_occurred on (action, occurred_at)
• ix_audit_log_action_pattern on (action, occurred_at DESC, source_ip)
• ix_audit_log_api_key_id on (api_key_id) (where api_key_id IS NOT NULL)
• ix_audit_log_occurred_at on (occurred_at)
• ix_audit_log_project_occurred on (project_id, occurred_at)
• ix_audit_log_user_occurred on (user_id, occurred_at)
• ux_audit_log_prev_row_hmac on (prev_row_hmac) (unique, where prev_row_hmac IS NOT NULL AND legacy_frozen = false)

automation_firing_outbox

One deferred automation firing awaiting replay.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
trigger	varchar(64)	no
fields	jsonb	no	default {}
attempts	integer	no	default 0
next_attempt_at	timestamptz	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Indexes:

• ix_automation_firing_outbox_created on (created_at)
• ix_automation_firing_outbox_project on (project_id)

automation_rule_admissions

One breaker admission in the current rule-configuration epoch.

Column	Type	Null	Notes
rule_id	uuid	no	FK -> automation_rules.id (CASCADE)
admitted_at	timestamptz	no
weight	integer	no	default 1
id	uuid	no	PK

Check ck_automation_rule_admissions_ck_automation_rule_admissions_positive_weight: weight > 0.

Indexes:

• ix_automation_rule_admissions_rule_time on (rule_id, admitted_at)

automation_rules

One automation rule, scoped to a project.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
name	varchar(200)	no
is_enabled	boolean	no	default true
dry_run	boolean	no	default false
trigger	varchar(64)	no
conditions	jsonb	no	default {}
actions	jsonb	no	default []
max_executions_per_window	integer	no	default 100
window_seconds	integer	no	default 3600
cb_tripped	boolean	no	default false
cb_window_start	timestamptz	yes
cb_execution_count	integer	no	default 0
last_notify_at	timestamptz	yes
created_by	uuid	yes	FK -> users.id (SET NULL)
source_hash	varchar(128)	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()
config_revision	integer	no	default 1
cb_config_digest	varchar(64)	yes

Unique uq_automation_rules_project_name: (project_id, name).

Indexes:

• ix_automation_rules_project_trigger on (project_id, trigger, is_enabled)

bulk_retry_request_children

One child payload in a durable outbox, written once by convention.

Column	Type	Null	Notes
parent_id	uuid	no	FK -> bulk_retry_requests.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
ordinal	integer	no
engine	varchar(40)	no
target_agent_id	uuid	yes
payload	jsonb	no
payload_digest	varchar(64)	no
payload_size	integer	no
required_contract_version	integer	no	default 1
delivery_state	varchar(24)	no	default pending
outcome	varchar(16)	no	default unobserved
claimed_agent_id	uuid	yes
claimed_generation	uuid	yes
claimed_at	timestamptz	yes
claim_deadline_at	timestamptz	yes
completed_at	timestamptz	yes
error	text	yes
created_at	timestamptz	no	default now()
id	uuid	no	PK

Unique uq_bulk_retry_request_children_parent_ordinal: (parent_id, ordinal).

Indexes:

• ix_bulk_retry_children_claim_deadline on (claim_deadline_at)
• ix_bulk_retry_children_parent_delivery on (parent_id, delivery_state, ordinal)
• ix_bulk_retry_children_project_delivery on (project_id, delivery_state, engine)

bulk_retry_requests

One idempotent request and its sealed-plan identity.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
issued_by	uuid	yes	FK -> users.id (SET NULL)
idempotency_key	varchar(200)	no
canonicalizer_version	integer	no
canonical_request	bytea	no
canonical_digest	varchar(64)	no
effective_request	jsonb	no
plan_digest	varchar(64)	no
control_state	varchar(16)	no	default running
target_agent_id	uuid	yes
child_count	integer	no
max_in_flight	integer	no
deadline_at	timestamptz	no
sealed_at	timestamptz	no	default now()
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()
last_progress_at	timestamptz	yes
id	uuid	no	PK

Unique uq_bulk_retry_requests_project_key: (project_id, idempotency_key).

Indexes:

• ix_bulk_retry_requests_control_progress on (control_state, last_progress_at, created_at)
• ix_bulk_retry_requests_deadline on (deadline_at)

commands

A command issued by an operator (or worker) against an agent.

Column	Type	Null	Notes
project_id	uuid	no
issued_by	uuid	yes	FK -> users.id (SET NULL)
agent_id	uuid	yes
action	varchar(80)	no
target_type	varchar(40)	no
target_id	varchar(200)	yes
payload	jsonb	no	default {}
idempotency_key	varchar(200)	yes
status	enum command_status(pending, dispatched, completed, failed, timeout, cancelled)	no	default pending
result	jsonb	yes
error	text	yes
issued_at	timestamptz	no	default now()
dispatched_at	timestamptz	yes
completed_at	timestamptz	yes
timeout_at	timestamptz	no
source_ip	inet	yes
bulk_retry_child_id	uuid	yes	FK -> bulk_retry_request_children.id (SET NULL)
schedule_protocol_marker	integer	yes
schedule_state_nonce	uuid	yes
schedule_id	uuid	yes
schedule_fire_id	uuid	yes
schedule_scheduled_for	timestamptz	yes
schedule_observed_control_token	uuid	yes
schedule_receipt_control_token	uuid	yes
schedule_execution_fire_id	uuid	yes
schedule_acceptance_revision	bigint	yes
schedule_definition_digest	varchar(64)	yes
schedule_expected_revision	bigint	yes
schedule_expected_last_run_at	timestamptz	yes
schedule_expected_next_run_at	timestamptz	yes
schedule_next_run_at	timestamptz	yes
cadence_initial_claim_deadline	timestamptz	yes
first_delivery_claimed_at	timestamptz	yes
cadence_redelivery_deadline	timestamptz	yes
delivery_transport_kind	varchar(32)	yes
delivery_registry_owner_id	uuid	yes
delivery_session_generation	varchar(128)	yes
delivery_claim_token	uuid	yes
agent_acknowledged_at	timestamptz	yes
id	uuid	no	PK

Unique uq_commands_project_idempotency_key: (project_id, idempotency_key).

Indexes:

• ix_commands_issued_by_at on (issued_by, issued_at)
• ix_commands_project_status_issued on (project_id, status, issued_at)
• ix_commands_schedule_fire_receipt on (schedule_id, schedule_fire_id, schedule_receipt_control_token)
• ix_commands_timeout_at on (timeout_at)
• ux_commands_bulk_retry_child on (bulk_retry_child_id) (unique)

events

A raw lifecycle event reported by an agent.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no	PK, FK -> projects.id (RESTRICT)
agent_id	uuid	no	FK -> agents.id (RESTRICT)
engine	varchar(40)	no
task_id	varchar(200)	no	default ``
kind	varchar(80)	no
occurred_at	timestamptz	no	PK
payload	jsonb	no	default {}

Primary key: (project_id, occurred_at, id).

Indexes:

• ix_events_project_kind on (project_id, kind, occurred_at)
• ix_events_project_task on (project_id, task_id, occurred_at)

export_jobs

A background export request (CSV, JSON, XLSX) to a configured sink.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
export_type	varchar(20)	no
format	varchar(10)	no
filters	jsonb	no	default {}
status	varchar(20)	no	default pending
row_count	integer	yes
file_path	varchar	yes
error	varchar	yes
completed_at	timestamptz	yes
sink	varchar(20)	yes
size_bytes	bigint	yes
started_at	timestamptz	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

extension_store

Global schemaless storage for plugins and extensions.

Column	Type	Null	Notes
key	varchar(200)	no	unique
value	jsonb	no	default {}
autoload	boolean	no	default false
description	varchar	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_extension_store_key: (key).

Indexes:

• ix_extension_store_autoload on (autoload)

feature_flags

A runtime feature flag (key-value with enabled toggle).

Column	Type	Null	Notes
key	varchar(100)	no	unique
description	text	no	default ''
enabled	boolean	no	default false
value	text	no	default ''
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_feature_flags_key: (key).

first_boot_tokens

A one-time setup token.

Column	Type	Null	Notes
token_hash	varchar	no
expires_at	timestamptz	no
created_at	timestamptz	no	default now()
id	uuid	no	PK

invitations

A pending invitation to join a project.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
email	varchar(255)	no
role	varchar(20)	no	default 'viewer'
invited_by	uuid	yes	FK -> users.id (SET NULL)
token_hash	varchar	no
expires_at	timestamptz	no
accepted_at	timestamptz	yes
accepted_by_user_id	uuid	yes	FK -> users.id (SET NULL)
revoked_at	timestamptz	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Indexes:

• ix_invitations_project_pending on (project_id, expires_at)
• ix_invitations_token_hash on (token_hash) (unique)

memberships

A user's role within a single project.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
role	enum project_role(viewer, auditor, operator, admin)	no	default viewer
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_memberships_user_project: (user_id, project_id).

Indexes:

• ix_memberships_project_id on (project_id)
• ix_memberships_user_id on (user_id)

mfa_recovery_codes

One hashed, single-use TOTP recovery code.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
code_hash	varchar	no
created_at	timestamptz	no	default now()
consumed_at	timestamptz	yes
id	uuid	no	PK

Indexes:

• ix_mfa_recovery_codes_user_id on (user_id)

misfire_alerts

One misfire episode already alerted for a schedule.

Column	Type	Null	Notes
schedule_id	uuid	no	FK -> schedules.id (CASCADE)
anchor_at	timestamptz	no
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_misfire_alerts_schedule_anchor: (schedule_id, anchor_at).

Indexes:

• ix_misfire_alerts_created on (created_at)

notification_channels

A project-shared notification delivery target.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
name	varchar(200)	no
type	varchar(20)	no
config	text	no
is_active	boolean	no	default True
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

notification_deliveries

Record of one EXTERNAL delivery attempt, never updated after write.

Column	Type	Null	Notes
subscription_id	uuid	yes	FK -> user_subscriptions.id (SET NULL)
channel_id	uuid	yes	FK -> notification_channels.id (SET NULL)
user_channel_id	uuid	yes	FK -> user_channels.id (SET NULL)
project_id	uuid	no	FK -> projects.id (CASCADE)
trigger	varchar(40)	no
task_id	varchar(200)	yes
task_name	varchar(500)	yes
status	varchar(20)	no
response_code	integer	yes
response_body	text	yes
error	text	yes
channel_name	varchar(200)	yes
channel_type	varchar(20)	yes
sent_at	timestamptz	no	default now()
triggered_by_user_id	uuid	yes	FK -> users.id (SET NULL)
id	uuid	no	PK
recipient_user_id	uuid	yes	FK -> users.id (SET NULL)

Indexes:

• ix_notification_deliveries_project_sent on (project_id, sent_at DESC)
• ix_notification_deliveries_recipient_sent on (recipient_user_id, sent_at DESC)
• ix_notification_deliveries_triggered_by_user on (triggered_by_user_id)

password_reset_tokens

A one-time password-reset token.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
token_hash	varchar	no
expires_at	timestamptz	no
consumed_at	timestamptz	yes
created_at	timestamptz	no	default now()
id	uuid	no	PK

pending_fires

One schedule fire awaiting replay when an agent comes online.

Column	Type	Null	Notes
id	uuid	no	PK
fire_id	uuid	no
schedule_id	uuid	no	FK -> schedules.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
engine	varchar(40)	no
payload	jsonb	no
scheduled_for	timestamptz	no
enqueued_at	timestamptz	no
expires_at	timestamptz	no
protocol_marker	integer	yes
state_write_nonce	uuid	yes
observed_control_token	uuid	yes
receipt_control_token	uuid	yes
definition_digest	varchar(64)	yes
expected_schedule_revision	bigint	yes
expected_last_run_at	timestamptz	yes
expected_next_run_at	timestamptz	yes
prepared_next_run_at	timestamptz	yes
acceptance_revision	bigint	yes
execution_fire_id	uuid	yes

Unique uq_pending_fires_fire_receipt: (fire_id, receipt_control_token).

Indexes:

• ix_pending_fires_expires on (expires_at)
• ix_pending_fires_replay on (project_id, engine, scheduled_for)
• uq_pending_fires_legacy_fire on (fire_id) (unique, where receipt_control_token IS NULL)

project_config

Per-project configuration overrides.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
key	varchar(200)	no
value	jsonb	no	default {}
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_project_config_project_key: (project_id, key).

Indexes:

• ix_project_config_project on (project_id)

project_default_subscriptions

An admin-defined subscription template for new project members.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
trigger	varchar(40)	no
filters	jsonb	no
in_app	boolean	no	default True
project_channel_ids	uuid[]	no
cooldown_seconds	integer	no	default 0
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_project_default_subscription_trigger: (project_id, trigger).

projects

A z4j project.

Column	Type	Null	Notes
slug	varchar(63)	no	unique
name	varchar(200)	no
description	varchar	yes
environment	varchar(40)	no	default production
timezone	varchar(64)	no	default UTC
is_active	boolean	no	default true
automation_enabled	boolean	no	default true
default_scheduler_owner	varchar(64)	no	default z4j-scheduler
allowed_schedulers	jsonb	yes
settings	jsonb	yes
organization_id	uuid	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()
automation_revision	integer	no	default 1

Unique uq_projects_slug: (slug).

Indexes:

• ix_projects_active on (is_active)

queues

A task queue observed by an agent.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
name	varchar(200)	no
engine	varchar(40)	no
broker_type	varchar(40)	yes
broker_url_hint	varchar	yes
last_seen_at	timestamptz	yes
pending_count	integer	no	default 0
consumer_count	integer	no	default 0
metadata	jsonb	no	default {}
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_queues_project_engine_name: (project_id, engine, name).

Indexes:

• ix_queues_project_id on (project_id)

saved_views

A saved filter/view preset for a dashboard page.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
name	varchar(200)	no
page	varchar(50)	no
filters	jsonb	no	default {}
is_default	boolean	no	default false
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

schedule_change_log

One upsert, delete tombstone, or filtered global gap.

Column	Type	Null	Notes
revision	bigint	no	PK
project_id	uuid	no
schedule_id	uuid	no
schedule_owner	varchar(40)	no
change_kind	varchar(16)	no
protocol_version	integer	no
snapshot	jsonb	yes
occurred_at	timestamptz	no	default now()

Check ck_schedule_change_log_ck_schedule_change_log_kind: change_kind IN ('upsert', 'delete', 'gap').

Check ck_schedule_change_log_ck_schedule_change_log_payload: (change_kind = 'upsert' AND snapshot IS NOT NULL) OR (change_kind IN ('delete', 'gap') AND snapshot IS NULL).

Check ck_schedule_change_log_ck_schedule_change_log_protocol: protocol_version = 1.

Check ck_schedule_change_log_ck_schedule_change_log_revision_positive: revision > 0.

Indexes:

• ix_schedule_change_log_project_id on (project_id)
• ix_schedule_change_log_schedule_id on (schedule_id)
• ix_schedule_change_log_schedule_owner on (schedule_owner)

schedule_external_control_operations

Durable external set-to-state operation awaiting source projection.

Column	Type	Null	Notes
id	uuid	no	PK
request_idempotency_key	varchar(200)	no
schedule_id	uuid	no
command_id	uuid	no	unique
agent_id	uuid	no
stream_id	uuid	no
epoch_uuid	uuid	no
epoch_number	bigint	no
source_key	varchar(500)	no
expected_accepted_sequence	bigint	no
expected_schedule_revision	bigint	no
expected_control_token	uuid	no
prior_projection	jsonb	no
prior_projection_digest	varchar(64)	no
desired_projection	jsonb	no
desired_projection_digest	varchar(64)	no
status	varchar(20)	no	default PENDING
adapter_instance_id	varchar(200)	no
session_generation	varchar(200)	no
registry_owner_id	uuid	no
dispatch_lease	uuid	yes
reserved_sequence	bigint	yes
result_projection_id	uuid	yes
state_nonce	uuid	no
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_schedule_external_control_operations_command_id: (command_id).

Unique uq_schedule_external_control_request: (stream_id, request_idempotency_key).

Check ck_schedule_external_control_operations_ck_schedule_external_control_epoch_sequence: epoch_number > 0 AND expected_accepted_sequence >= 0.

Check ck_schedule_external_control_operations_ck_schedule_external_control_reserved_sequence: reserved_sequence IS NULL OR reserved_sequence > 0.

Check ck_schedule_external_control_operations_ck_schedule_external_control_revision: expected_schedule_revision > 0.

Check ck_schedule_external_control_operations_ck_schedule_external_control_status: status IN ('PENDING', 'CLAIMED', 'APPLIED', 'FAILED', 'AMBIGUOUS', 'CANCELLED').

Indexes:

• ix_schedule_external_control_operations_schedule_id on (schedule_id)
• ix_schedule_external_control_operations_stream_id on (stream_id)

schedule_external_epoch_allocator

Installation-wide, never-reset external epoch allocator.

Column	Type	Null	Notes
singleton_id	varchar(40)	no	PK
current_epoch_number	bigint	no
guard_version	integer	no
activation_id	uuid	no
activation_manifest_digest	varchar(64)	no
activation_audit_id	uuid	no

Check ck_schedule_external_epoch_allocator_ck_schedule_external_epoch_allocator_guard: guard_version = 1.

Check ck_schedule_external_epoch_allocator_ck_schedule_external_epoch_allocator_nonnegative: current_epoch_number >= 0.

Check ck_schedule_external_epoch_allocator_ck_schedule_external_epoch_allocator_singleton: singleton_id = 'schedule-external-epoch'.

schedule_external_projections

Exact-replay ledger for accepted external projections.

Column	Type	Null	Notes
id	uuid	no	PK
stream_id	uuid	no
epoch_uuid	uuid	no
epoch_number	bigint	no
sequence	bigint	no
kind	varchar(40)	no
payload_digest	varchar(64)	no
source_keys	jsonb	no
mutation_digest	varchar(64)	no
operation_id	uuid	yes
accepted_at	timestamptz	no	default now()

Unique uq_schedule_external_projection_sequence: (stream_id, epoch_uuid, sequence).

Check ck_schedule_external_projections_ck_schedule_external_projection_positive: epoch_number > 0 AND sequence > 0.

Indexes:

• ix_schedule_external_projections_stream_id on (stream_id)

schedule_external_snapshot_frames

Durable staging for one framed stable snapshot.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no
stream_id	uuid	no
epoch_uuid	uuid	no
epoch_number	bigint	no
sequence	bigint	no
snapshot_id	uuid	no
frame_kind	varchar(20)	no
frame_index	integer	no
frame_count	integer	no
row_count	integer	no
owner	varchar(40)	no
source_scope	varchar(500)	no
adapter_instance_id	varchar(200)	no
snapshot_digest	varchar(64)	no
frame_digest	varchar(64)	no
stable_source	boolean	no
schedules	jsonb	no
received_at	timestamptz	no	default now()

Unique uq_schedule_external_snapshot_frame_index: (stream_id, epoch_uuid, sequence, frame_index).

Check ck_schedule_external_snapshot_frames_ck_schedule_external_snapshot_frame_bounds: frame_count >= 0 AND row_count >= 0 AND frame_index >= 0 AND frame_index <= frame_count.

Check ck_schedule_external_snapshot_frames_ck_schedule_external_snapshot_frame_kind: frame_kind IN ('rows', 'terminal').

Check ck_schedule_external_snapshot_frames_ck_schedule_external_snapshot_frame_positive: epoch_number > 0 AND sequence > 0.

Indexes:

• ix_schedule_external_snapshot_assembly on (stream_id, epoch_uuid, sequence, snapshot_id)
• ix_schedule_external_snapshot_frames_project_id on (project_id)
• ix_schedule_external_snapshot_frames_stream_id on (stream_id)

schedule_external_stream_epochs

Retained history for one Brain-issued external stream epoch.

Column	Type	Null	Notes
epoch_uuid	uuid	no	PK
epoch_number	bigint	no	unique
stream_id	uuid	no
phase	varchar(40)	no
authorized_adapter_instance_id	varchar(200)	yes
executor_agent_id	uuid	yes
executor_registry_owner_id	uuid	yes
executor_session_generation	varchar(200)	yes
executor_worker_id	varchar(200)	yes
accepted_sequence	bigint	no	default 0
sealed_sequence	bigint	yes
last_snapshot_digest	varchar(64)	yes
last_projection_digest	varchar(64)	yes
activation_requirement	varchar(100)	yes
created_at	timestamptz	no	default now()
activated_at	timestamptz	yes
sealed_at	timestamptz	yes
retired_at	timestamptz	yes

Unique uq_schedule_external_stream_epochs_epoch_number: (epoch_number).

Unique uq_schedule_external_stream_epoch_number: (stream_id, epoch_number).

Check ck_schedule_external_stream_epochs_ck_schedule_external_epoch_executor_authority: (authorized_adapter_instance_id IS NULL AND executor_agent_id IS NULL AND executor_registry_owner_id IS NULL AND executor_session_generation IS NULL) OR (authorized_adapter_instance_id IS NOT NULL AND executor_agent_id IS NOT NULL AND executor_registry_owner_id IS NOT NULL AND executor_session_generation IS NOT NULL).

Check ck_schedule_external_stream_epochs_ck_schedule_external_epoch_phase: phase IN ('ACTIVATING', 'ACTIVE', 'DRAINING', 'SEALED', 'RETIRED', 'AMBIGUOUS', 'RESTORE_REACTIVATION_REQUIRED').

Check ck_schedule_external_stream_epochs_ck_schedule_external_epoch_positive: epoch_number > 0.

Check ck_schedule_external_stream_epochs_ck_schedule_external_epoch_sealed_sequence: sealed_sequence IS NULL OR sealed_sequence >= accepted_sequence.

Check ck_schedule_external_stream_epochs_ck_schedule_external_epoch_sequence_nonnegative: accepted_sequence >= 0.

Indexes:

• ix_schedule_external_stream_epochs_stream_id on (stream_id)

schedule_external_streams

Non-cascading identity for one external owner/source scope.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no
owner	varchar(40)	no
source_scope	varchar(500)	no
source_scope_digest	varchar(64)	no
current_epoch_uuid	uuid	no
current_epoch_number	bigint	no
phase	varchar(40)	no
authorized_adapter_instance_id	varchar(200)	yes
executor_agent_id	uuid	yes
executor_registry_owner_id	uuid	yes
executor_session_generation	varchar(200)	yes
executor_worker_id	varchar(200)	yes
accepted_sequence	bigint	no	default 0
sealed_sequence	bigint	yes
last_snapshot_digest	varchar(64)	yes
last_projection_digest	varchar(64)	yes
activation_requirement	varchar(100)	yes
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_schedule_external_stream_current_epoch: (id, current_epoch_uuid).

Unique uq_schedule_external_stream_scope: (project_id, owner, source_scope).

Check ck_schedule_external_streams_ck_schedule_external_stream_epoch_positive: current_epoch_number > 0.

Check ck_schedule_external_streams_ck_schedule_external_stream_executor_authority: (authorized_adapter_instance_id IS NULL AND executor_agent_id IS NULL AND executor_registry_owner_id IS NULL AND executor_session_generation IS NULL) OR (authorized_adapter_instance_id IS NOT NULL AND executor_agent_id IS NOT NULL AND executor_registry_owner_id IS NOT NULL AND executor_session_generation IS NOT NULL).

Check ck_schedule_external_streams_ck_schedule_external_stream_owner: owner <> 'z4j-scheduler'.

Check ck_schedule_external_streams_ck_schedule_external_stream_phase: phase IN ('ACTIVATING', 'ACTIVE', 'DRAINING', 'SEALED', 'RETIRED', 'AMBIGUOUS', 'RESTORE_REACTIVATION_REQUIRED').

Check ck_schedule_external_streams_ck_schedule_external_stream_sealed_sequence: sealed_sequence IS NULL OR sealed_sequence >= accepted_sequence.

Check ck_schedule_external_streams_ck_schedule_external_stream_sequence_nonnegative: accepted_sequence >= 0.

Indexes:

• ix_schedule_external_stream_owner_scope on (project_id, owner, source_scope_digest)
• ix_schedule_external_streams_project_id on (project_id)

schedule_fires

One historical fire of a schedule.

Column	Type	Null	Notes
id	uuid	no	PK
fire_id	uuid	no
schedule_id	uuid	no
project_id	uuid	no
command_id	uuid	yes
triggered_by_user_id	uuid	yes	FK -> users.id (SET NULL)
status	varchar(32)	no
scheduled_for	timestamptz	no
fired_at	timestamptz	no
acked_at	timestamptz	yes
latency_ms	integer	yes
error_code	varchar(64)	yes
error_message	varchar(2000)	yes
scheduler_ack_status	varchar(32)	yes
scheduler_acknowledged_at	timestamptz	yes
scheduler_ack_task_id	varchar(200)	yes
scheduler_ack_error_code	varchar(64)	yes
scheduler_ack_error_message	varchar(2000)	yes
protocol_marker	integer	yes
state_write_nonce	uuid	yes
observed_control_token	uuid	yes
receipt_control_token	uuid	yes
acceptance_revision	bigint	yes
definition_digest	varchar(64)	yes
expected_schedule_revision	bigint	yes
expected_last_run_at	timestamptz	yes
expected_next_run_at	timestamptz	yes
prepared_next_run_at	timestamptz	yes

Unique uq_schedule_fires_fire_receipt: (fire_id, receipt_control_token).

Indexes:

• ix_schedule_fires_circuit_breaker on (schedule_id, status, fired_at)
• ix_schedule_fires_schedule_recent on (schedule_id, fired_at)
• uq_schedule_fires_legacy_fire on (fire_id) (unique, where receipt_control_token IS NULL)

schedule_occurrence_resolutions

One operator/deletion exit for cadence evidence without a hold.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no
schedule_id	uuid	no
fire_id	uuid	no
scheduled_for	timestamptz	no
command_id	uuid	yes
source_evidence_kind	varchar(32)	no
source_evidence_id	uuid	no
authority_kind	varchar(16)	no
observed_control_token	uuid	yes
receipt_control_token	uuid	yes
command_status	varchar(32)	no
work_may_have_executed	boolean	no
resolution_disposition	varchar(32)	no
resolved_at	timestamptz	no
resolved_by	uuid	yes
resolution_source	varchar(32)	no
resolution_control_token	uuid	yes
deletion_tombstone_revision	bigint	yes
state_write_nonce	uuid	no

Unique uq_schedule_occurrence_resolution_identity: (schedule_id, fire_id, scheduled_for, authority_kind, command_id).

Unique uq_schedule_occurrence_resolution_source: (source_evidence_kind, source_evidence_id).

Check ck_schedule_occurrence_resolutions_ck_schedule_occurrence_resolution_authority: (authority_kind = 'LEGACY_NULL' AND receipt_control_token IS NULL) OR (authority_kind = 'TOKEN' AND receipt_control_token IS NOT NULL).

Check ck_schedule_occurrence_resolutions_ck_schedule_occurrence_resolution_authority_kind: authority_kind IN ('LEGACY_NULL', 'TOKEN').

Check ck_schedule_occurrence_resolutions_ck_schedule_occurrence_resolution_disposition: resolution_disposition IN ('OPERATOR_SKIPPED', 'SCHEDULE_DELETED').

Check ck_schedule_occurrence_resolutions_ck_schedule_occurrence_resolution_exit: (resolution_disposition = 'OPERATOR_SKIPPED' AND resolved_by IS NOT NULL AND resolution_source = 'OPERATOR' AND resolution_control_token IS NOT NULL AND deletion_tombstone_revision IS NULL) OR (resolution_disposition = 'SCHEDULE_DELETED' AND resolution_source = 'SCHEDULE_DELETE' AND resolution_control_token IS NULL AND deletion_tombstone_revision IS NOT NULL).

Check ck_schedule_occurrence_resolutions_ck_schedule_occurrence_resolution_source_kind: source_evidence_kind IN ('COMMAND', 'PENDING_FIRE', 'SCHEDULE_FIRE').

Indexes:

• ix_schedule_occurrence_resolution_fire on (fire_id, scheduled_for)
• ix_schedule_occurrence_resolution_schedule on (schedule_id, resolved_at)
• uq_schedule_occurrence_resolution_legacy_commandless on (schedule_id, fire_id, scheduled_for, authority_kind) (unique, where command_id IS NULL AND authority_kind = 'LEGACY_NULL')
• uq_schedule_occurrence_resolution_token_commandless on (schedule_id, fire_id, scheduled_for, receipt_control_token) (unique, where command_id IS NULL AND authority_kind = 'TOKEN')

schedule_owner_cutovers

Retained idempotency and attestation authority for one owner cutover.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no
from_owner	varchar(40)	no
to_owner	varchar(40)	no
source_scope	varchar(500)	no
preview_manifest_digest	varchar(64)	no
preview_manifest	jsonb	no
cursor_policy	varchar(40)	no
quiescence_attestation	jsonb	no
quiescence_attestation_digest	varchar(64)	no
source_stream_manifest	jsonb	no
target_stream_id	uuid	yes
result_manifest	jsonb	no
result_manifest_digest	varchar(64)	no
completed_at	timestamptz	no
created_at	timestamptz	no	default now()

Check ck_schedule_owner_cutovers_ck_schedule_owner_cutover_cursor_policy: cursor_policy IN ('PRESERVE', 'PRESERVE_FUTURE', 'RESET_CURSOR').

Check ck_schedule_owner_cutovers_ck_schedule_owner_cutover_distinct_owners: from_owner <> to_owner.

Indexes:

• ix_schedule_owner_cutovers_project_id on (project_id)

schedule_revision_state

One transactional global schedule-revision allocator.

Column	Type	Null	Notes
singleton_id	varchar(32)	no	PK
current_revision	bigint	no
change_log_pruned_through	bigint	no
guard_version	integer	yes
activation_id	uuid	yes
activation_manifest_digest	varchar(64)	yes
activation_audit_id	uuid	yes

Check ck_schedule_revision_state_ck_schedule_revision_state_activation: (guard_version IS NULL AND activation_id IS NULL AND activation_manifest_digest IS NULL AND activation_audit_id IS NULL) OR (guard_version = 1 AND activation_id IS NOT NULL AND activation_manifest_digest IS NOT NULL AND activation_audit_id IS NOT NULL).

Check ck_schedule_revision_state_ck_schedule_revision_state_current_nonnegative: current_revision >= 0.

Check ck_schedule_revision_state_ck_schedule_revision_state_pruned_boundary: change_log_pruned_through >= 0 AND change_log_pruned_through <= current_revision.

Check ck_schedule_revision_state_ck_schedule_revision_state_singleton: singleton_id = 'schedule-revision'.

schedule_terminal_holds

One unresolved or resolved generation-scoped cadence stop.

Column	Type	Null	Notes
id	uuid	no	PK
project_id	uuid	no
schedule_id	uuid	no
fire_id	uuid	no
scheduled_for	timestamptz	no
command_id	uuid	no
observed_control_token	uuid	no
receipt_control_token	uuid	no
acceptance_revision	bigint	no
terminal_status	varchar(32)	no
terminal_detail	varchar(1024)	yes
state_write_nonce	uuid	no
created_at	timestamptz	no
resolved_at	timestamptz	yes
resolved_by	uuid	yes
resolution_disposition	varchar(32)	yes
resolution_source	varchar(32)	yes
work_may_have_executed	boolean	yes
resolution_control_token	uuid	yes
deletion_tombstone_revision	bigint	yes

Unique uq_schedule_terminal_hold_identity: (fire_id, scheduled_for, receipt_control_token).

Check ck_schedule_terminal_holds_ck_schedule_terminal_hold_resolution: (resolved_at IS NULL AND resolved_by IS NULL AND resolution_disposition IS NULL AND resolution_source IS NULL AND work_may_have_executed IS NULL AND resolution_control_token IS NULL AND deletion_tombstone_revision IS NULL) OR (resolved_at IS NOT NULL AND resolution_disposition = 'OPERATOR_SKIPPED' AND resolved_by IS NOT NULL AND resolution_source = 'OPERATOR' AND work_may_have_executed = TRUE AND resolution_control_token IS NOT NULL AND deletion_tombstone_revision IS NULL) OR (resolved_at IS NOT NULL AND resolution_disposition = 'SCHEDULE_DELETED' AND resolution_source = 'SCHEDULE_DELETE' AND resolution_control_token IS NULL AND deletion_tombstone_revision IS NOT NULL).

Indexes:

• ix_schedule_terminal_holds_project on (project_id, created_at)
• uq_schedule_terminal_hold_unresolved_schedule on (schedule_id) (unique, where resolved_at IS NULL)

scheduler_rate_buckets

One token bucket per scheduler cert CN.

Column	Type	Null	Notes
cert_cn	varchar(255)	no	PK
tokens	float	no
last_refill	timestamptz	no	default now()
capacity	float	no
refill_per_second	float	no
updated_at	timestamptz	no	default now()

schedules

A periodic schedule managed by the brain.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
engine	varchar(40)	no
scheduler	varchar(40)	no
name	varchar(200)	no
task_name	varchar(500)	no
kind	enum schedule_kind(cron, interval, solar, clocked)	no
expression	varchar	no
timezone	varchar(64)	no	default UTC
queue	varchar(200)	yes
priority	enum task_priority(critical, high, normal, low)	no	default normal
args	jsonb	no	default []
kwargs	jsonb	no	default {}
is_enabled	boolean	no	default true
last_run_at	timestamptz	yes
next_run_at	timestamptz	yes
total_runs	bigint	no	default 0
external_id	varchar(200)	yes
catch_up	varchar(32)	no	default skip
overlap_policy	varchar(32)	no	default allow
paused_at	timestamptz	yes
source	varchar(64)	no	default dashboard
source_hash	varchar(128)	yes
last_fire_id	uuid	yes
control_token	uuid	yes
legacy_fire_control_token	uuid	yes
schedule_revision	bigint	yes
definition_digest	varchar(64)	yes
cadence_semantics_version	integer	yes
cadence_runtime_fingerprint	varchar(64)	yes
quarantine_control_token	uuid	yes
quarantine_code	varchar(64)	yes
quarantine_detail	varchar(500)	yes
quarantined_at	timestamptz	yes
last_cadence_acceptance_control_token	uuid	yes
last_cadence_acceptance_fire_id	uuid	yes
last_cadence_acceptance_scheduled_for	timestamptz	yes
last_cadence_acceptance_revision	bigint	yes
external_stream_id	uuid	yes
external_epoch_uuid	uuid	yes
external_epoch_number	bigint	yes
external_source_key	varchar(500)	yes
external_source_sequence	bigint	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_schedules_project_scheduler_name: (project_id, scheduler, name).

Indexes:

• ix_schedules_project_id on (project_id)

sessions

A live (or revoked) dashboard session.

Column	Type	Null	Notes
id	uuid	no	PK
user_id	uuid	no	FK -> users.id (CASCADE)
csrf_token	varchar(64)	no
issued_at	timestamptz	no	default now()
expires_at	timestamptz	no
last_seen_at	timestamptz	no	default now()
revoked_at	timestamptz	yes
revocation_reason	varchar(40)	yes
ip_at_issue	inet	no
user_agent_at_issue	varchar(256)	yes
mfa_verified_at	timestamptz	yes

Indexes:

• ix_sessions_expires_at on (expires_at)
• ix_sessions_user_id on (user_id)

task_annotations

An operator-authored note attached to a task.

Column	Type	Null	Notes
task_id	uuid	no	FK -> tasks.id (CASCADE)
user_id	uuid	no	FK -> users.id (CASCADE)
content	text	no
annotation_type	varchar(20)	no	default note
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

tasks

Latest-known state of a single task instance.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
engine	varchar(40)	no
task_id	varchar(200)	no
name	varchar(500)	no
queue	varchar(200)	yes
state	enum task_state(pending, received, started, success, failure, retry, revoked, rejected, unknown)	no	default pending
priority	enum task_priority(critical, high, normal, low)	no	default normal
args	jsonb	yes
kwargs	jsonb	yes
result	jsonb	yes
exception	text	yes
traceback	text	yes
fingerprint	varchar(32)	yes
last_failed_at	timestamptz	yes
retry_count	integer	no	default 0
eta	timestamptz	yes
received_at	timestamptz	yes
started_at	timestamptz	yes
finished_at	timestamptz	yes
runtime_ms	bigint	yes
worker_name	varchar(200)	yes
parent_task_id	varchar(200)	yes
root_task_id	varchar(200)	yes
tags	text[]	no	default {}
metadata	jsonb	no	default {}
search_vector	tsvector	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_tasks_project_engine_task_id: (project_id, engine, task_id).

Indexes:

• ix_tasks_project_fingerprint on (project_id, fingerprint)
• ix_tasks_project_finished on (project_id, finished_at)
• ix_tasks_project_name on (project_id, name)
• ix_tasks_project_queue on (project_id, queue)
• ix_tasks_project_state_started on (project_id, state, started_at)

trusted_devices

A "remember this device" row.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
cookie_id_hash	varchar(64)	no	unique
label	varchar(200)	no
created_at	timestamptz	no	default now()
last_seen_at	timestamptz	no	default now()
expires_at	timestamptz	no
revoked_at	timestamptz	yes
id	uuid	no	PK

Unique uq_trusted_devices_cookie_id_hash: (cookie_id_hash).

Indexes:

• ix_trusted_devices_expires_at on (expires_at)
• ix_trusted_devices_user_id on (user_id)

user_channels

A user-scoped notification delivery target.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
name	varchar(200)	no
type	varchar(20)	no
config	text	no
is_verified	boolean	no	default False
is_active	boolean	no	default True
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_user_channel_name: (user_id, name).

user_notifications

One in-app notification delivered to a user.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
subscription_id	uuid	yes	FK -> user_subscriptions.id (SET NULL)
trigger	varchar(40)	no
reason	varchar(20)	no	default 'subscribed'
title	varchar(500)	no
body	text	yes
data	jsonb	no
read_at	timestamptz	yes
created_at	timestamptz	no	default now()
id	uuid	no	PK

user_preferences

Per-user key-value preferences.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
key	varchar(100)	no
value	jsonb	no	default {}
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_user_preferences_user_key: (user_id, key).

Indexes:

• ix_user_preferences_user on (user_id)

user_subscriptions

A user's choice to receive a given trigger on a given project.

Column	Type	Null	Notes
user_id	uuid	no	FK -> users.id (CASCADE)
project_id	uuid	no	FK -> projects.id (CASCADE)
trigger	varchar(40)	no
filters	jsonb	no
in_app	boolean	no	default True
project_channel_ids	uuid[]	no
user_channel_ids	uuid[]	no
muted_until	timestamptz	yes
cooldown_seconds	integer	no	default 0
last_fired_at	timestamptz	yes
is_active	boolean	no	default True
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_user_subscription_trigger: (user_id, project_id, trigger).

users

A dashboard user account.

Column	Type	Null	Notes
email	citext	no	unique
password_hash	varchar	no
display_name	varchar	yes
first_name	varchar(100)	yes
last_name	varchar(100)	yes
is_admin	boolean	no	default false
is_active	boolean	no	default true
last_login_at	timestamptz	yes
force_password_change	boolean	no	default false
timezone	varchar(64)	no	default UTC
failed_login_count	integer	no	default 0
locked_until	timestamptz	yes
last_failed_login_at	timestamptz	yes
last_failed_login_ip	inet	yes
password_changed_at	timestamptz	yes
mfa_secret_encrypted	bytea	yes
mfa_enrolled_at	timestamptz	yes
mfa_enforcement_started_at	timestamptz	yes
failed_mfa_count	integer	no	default 0
mfa_locked_until	timestamptz	yes
last_totp_counter	bigint	yes
avatar_url	varchar	yes
external_id	varchar(255)	yes
sso_provider	varchar(50)	yes
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_users_email: (email).

Indexes:

• ix_users_active on (is_active)

workers

A worker process observed by an agent.

Column	Type	Null	Notes
project_id	uuid	no	FK -> projects.id (CASCADE)
engine	varchar(40)	no
name	varchar(200)	no
hostname	varchar(255)	yes
pid	integer	yes
concurrency	integer	yes
queues	text[]	no	default {}
state	enum worker_state(online, offline, draining, unknown)	no	default unknown
last_heartbeat	timestamptz	yes
load_average	jsonb	yes
memory_bytes	bigint	yes
active_tasks	integer	no	default 0
metadata	jsonb	no	default {}
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_workers_project_engine_name: (project_id, engine, name).

Indexes:

• ix_workers_project_state on (project_id, state)

z4j_meta

Application metadata key-value store.

Column	Type	Null	Notes
key	varchar(100)	no	unique
value	text	no	default ''
id	uuid	no	PK
created_at	timestamptz	no	default now()
updated_at	timestamptz	no	default now()

Unique uq_z4j_meta_key: (key).