Database Schema

Extensions Required

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

The migrator enables these from SQL_FRAGMENTS.enableExtensions on every boot (packages/db/src/migrate.ts).

Core Tables

tenants

Organisation units. One Azure AD tenant = one OIAB tenant.

Column	Type	Notes
id	uuid PK
name	text	Display name
slug	text UNIQUE	URL-safe: "contoso"
entra_tenant_id	text UNIQUE	Azure AD tenant ID
settings	jsonb	{ groupRoleMapping: { "<group-id>": "admin" } }
status	text	active | suspended | deleted

users

One row per person. Links to their tenant and (optionally) Azure AD identity.

Column	Type	Notes
id	uuid PK
handle	text UNIQUE	Short identifier
tenant_id	uuid → tenants	NULL = single-operator
email	text
display_name	text
entra_object_id	text	Azure AD object ID
status	text	active | suspended | deactivated

roles / user_roles

RBAC roles and their assignments.

• roles.permissions is a typed JSONB column (RbacPermissions)
• user_roles links users to roles within a tenant
• System roles (owner/admin/member/viewer) are seeded on tenant creation

entra_sessions

Active Azure AD SSO sessions with encrypted tokens.

Memory Tables

memories

Long-term agent memories with pgvector embeddings.

Column	Type	Notes
id	uuid PK
user_id	uuid → users
tenant_id	uuid → tenants
kind	text	fact | preference | event | skill-note
content	text
visibility	text	private | team | org
embedding	vector(1536)	Added via raw SQL migration
source_session_id	text	Session that created this memory

personas

Named personas (e.g. "work", "personal") that contextualise memories.

Job Queue

jobs

The central job queue. Workers use SELECT FOR UPDATE SKIP LOCKED.

Column	Type	Notes
id	uuid PK
kind	text	agent-turn | orchestrator-* | embed-memories | …
status	text	pending | running | completed | failed
payload	jsonb	Job-specific data
run_at	timestamp	Earliest execution time
attempts	int	Current attempt count
max_attempts	int	Default 3
dedupe_key	text UNIQUE (partial)	Prevents duplicate jobs
locked_by	text	Worker ID holding the lock

Orchestrator Tables

orchestrator_plans

One row per complex task decomposition.

Column	Notes
status	planning → executing → synthesizing → completed | failed
plan	JSONB subtask definitions
result	Synthesised final response

orchestrator_subtasks

One row per sub-task in a plan.

Column	Notes
index	0-based position
dependencies	int[] of dependency indices
status	pending → running → completed | failed
result	Sub-task output text

Learning Loop Tables

trajectories

Tool usage trajectory per session with embedding.

Column	Notes
tools	JSONB: [{ tool, input_hash, output_hash }]
embedding	vector(1536)
outcome	success | failure | partial
reflection	LLM-generated reflection text

skills

Versioned reusable skills.

Column	Notes
status	proposed → promoted → deprecated
version	Auto-incremented on promote
source_trajectories	UUID[] of contributing trajectories

Analytics

usage_events

Per-request token/cost data. Idempotent on request_id (prevents double-counting on retries).

audit_log

Append-only compliance log. Partitioned by (tenant_id, ts) index for fast range queries.

Templates & Providers

sessions_index

Session metadata plus the frozen template behavior used by that session.

Column	Notes
template_slug	Provenance only: which template the session started from
template_system_prompt	Frozen system prompt for stable re-prompts
template_model_override	Frozen provider/model override for stable re-prompts
template_snapshot_at	When the template snapshot was captured

agent_templates

Pre-built agent configurations. NULL tenant_id = global template.

providers

Encrypted LLM provider credentials. One row per provider per user.

Migrations

The source of truth is the idempotent migrator in packages/db/src/migrate.ts — a single inline DDL block (SCHEMA + PLATFORM_SCHEMA + GATEWAY_SCHEMA) where every statement uses CREATE TABLE IF NOT EXISTS / ADD COLUMN IF NOT EXISTS. There are no numbered migration files in play today. A legacy packages/db/migrations/0002_multi_tenancy.sql file is kept only as a raw-SQL reference for operators who want to diff the multi-tenancy slice.

Migrations run automatically on worker/API boot via runMigrations(postgresUrl).