Database schema

All tables live in the public schema. Managed by Alembic (SQLAlchemy 2.0 models).

projects

Column	Type	Notes
id	UUID	PK
name	varchar(200)	unique
settings	jsonb	retention, redaction overrides, tz
created_at	timestamptz
updated_at	timestamptz

users

Column	Type	Notes
id	UUID	PK
email	varchar(320)	unique, citext-normalized
display_name	varchar(200)
password_hash	text	argon2id PHC string
is_active	boolean	default true
created_at	timestamptz
updated_at	timestamptz

memberships

Column	Type	Notes
id	UUID	PK
user_id	UUID	FK → users.id CASCADE
project_id	UUID	FK → projects.id CASCADE
role	enum(owner, admin, operator, viewer)
created_at	timestamptz

Unique on (user_id, project_id).

agents

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id CASCADE
name	varchar(200)	unique with project_id
token_hash	text	unique, HMAC-SHA256
protocol_version	varchar(20)
framework_adapter	varchar(40)
engine_adapters	text[]
scheduler_adapters	text[]
capabilities	jsonb
state	enum(online, offline, unknown)
last_seen_at	timestamptz
last_connect_at	timestamptz
metadata	jsonb
created_at	timestamptz
updated_at	timestamptz

Indexes: (project_id, state), (last_seen_at).

tasks

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id CASCADE
agent_id	UUID	FK → agents.id SET NULL
engine	varchar(40)
name	varchar(500)
queue	varchar(200)
state	enum	pending/started/succeeded/failed/cancelled/retried/lost
args_redacted	jsonb
kwargs_redacted	jsonb
error	jsonb	{exc_type, exc_msg, traceback_redacted}
retried_as	UUID	FK → tasks.id
group_id	varchar(200)	Celery group_id / null
enqueued_at	timestamptz
started_at	timestamptz
finished_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

Indexes: (project_id, state), (project_id, name), (enqueued_at).

events

Column	Type	Notes
id	UUID	PK
task_id	UUID	FK → tasks.id CASCADE
agent_id	UUID	FK → agents.id SET NULL
event_type	varchar(40)	task_sent/started/succeeded/failed/retry/cancelled/expired
ts	timestamptz	from agent clock
payload	jsonb	redacted event data
created_at	timestamptz	brain receipt time

Index: (task_id, ts).

schedules

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id CASCADE
agent_id	UUID	FK → agents.id SET NULL
scheduler	varchar(40)
name	varchar(200)
task	varchar(500)
schedule_type	enum(cron, interval, date)
expression	varchar(200)	cron string or interval spec
args	jsonb
kwargs	jsonb
enabled	boolean
last_run_at	timestamptz
next_run_at	timestamptz
metadata	jsonb
created_at	timestamptz
updated_at	timestamptz

audit_log

Column	Type	Notes
id	UUID	PK, ordered (ULIDs)
ts	timestamptz
actor_user_id	UUID	nullable (system events)
project_id	UUID	nullable (global events)
action	varchar(100)	e.g. task.retry
target_type	varchar(40)
target_id	UUID
details	jsonb
row_hmac	varchar(64)	hex-encoded HMAC-SHA256
prev_row_hmac	varchar(64)	null for genesis

Index: (project_id, ts), (action).

sessions

Column	Type	Notes
id	UUID	PK
user_id	UUID	FK → users.id CASCADE
expires_at	timestamptz	sliding; 14 days
data	jsonb	session payload
created_at	timestamptz

invitations

Column	Type	Notes
id	UUID	PK
project_id	UUID	FK → projects.id CASCADE
email	varchar(320)	target
role	enum	role on accept
token_hash	text	HMAC-SHA256
expires_at	timestamptz	72h from creation
accepted_at	timestamptz	null until accepted
created_by	UUID	FK → users.id
created_at	timestamptz

password_reset_tokens

Column	Type	Notes
id	UUID	PK
user_id	UUID	FK → users.id CASCADE
token_hash	text
expires_at	timestamptz	60 min
used_at	timestamptz	null until used
created_at	timestamptz