Skip to content
z4j docs

Database schema

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

projects

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

users

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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

Section titled “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