Guide for designing and managing a butler's PostgreSQL database schema. Use when creating tables, writing migrations, adding indexes, or evolving a butler's data model.
Installer avec Codex ou Claude Copiez ce prompt, collez-le dans Codex, Claude ou un autre assistant, puis laissez-le vérifier la page du skill et l'installer pour vous.
Une commande directe contourne le prompt de vérification. Examinez la source avant de l'exécuter.
Guide for designing and managing a butler's PostgreSQL database schema. Use when creating tables, writing migrations, adding indexes, or evolving a butler's data model.
Butler Database Schema Design
Use this skill when creating or modifying a butler's database schema — adding tables, writing Alembic migrations, designing indexes, or evolving the data model for a specific butler's needs.
Hard Constraints
Shared database, per-butler schemas. All butlers share a single PostgreSQL database named butlers. Each butler gets its own schema (general, health, messenger, etc.) plus read access to the public schema. Inter-butler data exchange happens only via MCP tools through the Switchboard.
Five core tables in every butler schema. See the Core Tables section below. All five are created by core_001_target_state_baseline.py and replicated into each butler's schema via search_path.
Migrations via Alembic only. No raw DDL in application code. No "just run this SQL."
Raw SQL via op.execute(). Migrations use raw SQL strings, not SQLAlchemy ORM operations. There are no SQLAlchemy models (target_metadata=None).
Backward compatibility in all migrations. Every migration must be safe to run while the previous version of the code is still active.
Database Topology
PostgreSQL database: "butlers"
├── public # Extensions + cross-butler tables (identity, model catalog, etc.)
├── general # General butler's domain tables
├── health # Health butler's domain tables
├── messenger # Messenger butler's domain tables
├── relationship # Relationship butler's domain tables
├── switchboard # Switchboard butler's domain tables
└── public # Extensions (pgcrypto, vector, uuid-ossp)
Each butler's runtime connection sets:
SET search_path TO<own_schema>, public
This means a butler's tools can query state, sessions, etc. without schema-qualifying — those tables exist in the butler's own schema. Tables in public (like calendar_sources) are also visible without qualification.
Runtime Roles & ACL
Each butler gets a runtime role (butler_<name>_rw) with:
Own schema: SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES on tables; USAGE, SELECT, UPDATE on sequences
Shared schema: SELECT only on tables; USAGE, SELECT on sequences
Other butler schemas: All access REVOKED
These roles and privileges are managed by core_001_target_state_baseline.py. New butlers must be added to the _BUTLER_SCHEMAS tuple in that migration (or a subsequent one).
Core Tables (Every Butler Schema Gets These)
Every butler schema contains five core tables created by the core_001 migration. They are created once in the migration but land in whichever schema search_path is pointing to at migration time.
Table
Purpose
Primary access pattern
state
Key-value JSONB store
Point lookups by key, prefix scans
sessions
Runtime invocation history & trace metadata
Recent-first, lookup by request_id
scheduled_tasks
Cron-driven recurring prompts + job dispatch
Query enabled + due tasks
route_inbox
Accept-then-process inbox for route requests
Filter by lifecycle_state
butler_secrets
Encrypted secrets store (tokens, API keys)
Lookup by secret_key, filter by category
1. state — Key-Value Store
General-purpose persistent storage for structured data. Used by core components and modules to store configuration state, counters, flags, cached results, module-specific KV data.
CREATE TABLE state (
key TEXT PRIMARY KEY,
value JSONB NOT NULLDEFAULT'{}'::jsonb,
updated_at TIMESTAMPTZ NOT NULLDEFAULT now(),
version INTEGERNOT NULLDEFAULT1
);
-- Prefix scans for namespaced keys (e.g., "module:email:%")CREATE INDEX idx_state_key_prefix ON state (key text_pattern_ops);
Keys should be namespaced with colons: module:email:last_check, scheduler:last_tick, config:override:timezone. The version column tracks mutation count for optimistic concurrency.
2. sessions — Runtime Invocation History
Every LLM CLI invocation spawned by this butler is recorded here. Includes trace metadata, token usage, and cost tracking.
CREATE TABLE sessions (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
prompt TEXT NOT NULL,
trigger_source TEXT NOT NULL, -- 'schedule:<task-name>', 'tick', 'external', 'trigger'
model TEXT,
success BOOLEAN,
error TEXT,
result TEXT,
tool_calls JSONB NOT NULLDEFAULT'[]'::jsonb,
duration_ms INTEGER,
trace_id TEXT,
request_id TEXT,
cost JSONB,
input_tokens INTEGER,
output_tokens INTEGER,
parent_session_id UUID,
started_at TIMESTAMPTZ NOT NULLDEFAULT now(),
completed_at TIMESTAMPTZ
);
CREATE INDEX idx_sessions_request_id ON sessions (request_id);
3. scheduled_tasks — Cron-Driven Scheduler
Stores both TOML-defined (bootstrap) and runtime-created scheduled tasks. Supports two dispatch modes: prompt (spawns an LLM session) and job (calls a Python function directly).
CREATE TABLE scheduled_tasks (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
name TEXT NOT NULLUNIQUE,
cron TEXT NOT NULL,
prompt TEXT, -- Required for prompt mode, NULL for job mode
dispatch_mode TEXT NOT NULLDEFAULT'prompt',
job_name TEXT, -- Required for job mode
job_args JSONB,
timezone TEXT NOT NULLDEFAULT'UTC',
start_at TIMESTAMPTZ, -- Window start (optional)
end_at TIMESTAMPTZ, -- Window end (optional)
until_at TIMESTAMPTZ, -- Expiry date (optional)
display_title TEXT,
calendar_event_id UUID, -- FK to calendar_events for linked events
source TEXT NOT NULLDEFAULT'db', -- 'toml' or 'db'
enabled BOOLEANNOT NULLDEFAULTtrue,
next_run_at TIMESTAMPTZ,
last_run_at TIMESTAMPTZ,
last_result JSONB,
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT now(),
CONSTRAINT scheduled_tasks_dispatch_mode_check
CHECK (dispatch_mode IN ('prompt', 'job')),
CONSTRAINT scheduled_tasks_dispatch_payload_check
CHECK (
(dispatch_mode ='prompt'AND prompt ISNOT NULLAND job_name ISNULL)
OR (dispatch_mode ='job'AND job_name ISNOT NULL)
),
CONSTRAINT scheduled_tasks_window_bounds_check
CHECK (start_at ISNULLOR end_at ISNULLOR end_at > start_at),
CONSTRAINT scheduled_tasks_until_bounds_check
CHECK (until_at ISNULLOR start_at ISNULLOR until_at >= start_at)
);
CREATEUNIQUE INDEX ix_scheduled_tasks_calendar_event_id
ON scheduled_tasks (calendar_event_id)
WHERE calendar_event_id ISNOT NULL;
4. route_inbox — Accept-Then-Process Inbox
Incoming route requests are accepted immediately (returning an ID) then processed asynchronously.
CREATE TABLE route_inbox (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
received_at TIMESTAMPTZ NOT NULLDEFAULT now(),
route_envelope JSONB NOT NULL,
lifecycle_state TEXT NOT NULLDEFAULT'accepted',
processed_at TIMESTAMPTZ,
session_id UUID,
error TEXT
);
CREATE INDEX idx_route_inbox_lifecycle_state
ON route_inbox (lifecycle_state, received_at);
5. butler_secrets — Secrets Store
Generic secrets store for tokens, API keys, and sensitive configuration. Secrets are stored per-butler in the butler's own schema.
CREATE TABLE butler_secrets (
secret_key TEXT PRIMARY KEY,
secret_value TEXT NOT NULL,
category TEXT NOT NULLDEFAULT'general',
description TEXT,
is_sensitive BOOLEANNOT NULLDEFAULTtrue,
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
updated_at TIMESTAMPTZ NOT NULLDEFAULT now(),
expires_at TIMESTAMPTZ
);
CREATE INDEX ix_butler_secrets_category ON butler_secrets (category);
Cross-Butler Tables (in public)
Tables in the public schema are readable by all butlers but writable only by core migrations. These are created by core_005 and later core migrations.
Calendar Projection Tables
The calendar module projects Google Calendar data into these shared tables:
Table
Purpose
calendar_sources
Calendar provider sources with lane (user/butler)
calendar_events
Base events with recurrence rules
calendar_event_instances
Expanded recurring event occurrences
calendar_sync_cursors
Incremental sync state per source
calendar_action_log
Idempotent mutation audit trail
Key design patterns in calendar tables:
GiST indexes on time ranges:USING GIST (tstzrange(starts_at, ends_at, '[)')) for efficient overlap queries
Idempotency keys:idempotency_key TEXT NOT NULL UNIQUE on action log
Lane-based partitioning:lane IN ('user', 'butler') separates read-only user calendars from writable butler calendars
Module Tables
Modules create tables in the butler's own schema via module-specific migration chains.
Memory Module (mem_001)
The memory module uses pgvector for semantic search. Four tables:
-- Example: facts table (key columns)CREATE TABLE facts (
id UUID PRIMARY KEYDEFAULT gen_random_uuid(),
subject TEXT NOT NULL,
predicate TEXT NOT NULL,
content TEXT NOT NULL,
embedding vector(384),
search_vector tsvector,
importance FLOATNOT NULLDEFAULT5.0,
confidence FLOATNOT NULLDEFAULT1.0,
decay_rate FLOATNOT NULLDEFAULT0.008,
permanence TEXT NOT NULLDEFAULT'standard',
validity TEXT NOT NULLDEFAULT'active',
scope TEXT NOT NULLDEFAULT'global',
reference_count INTEGERNOT NULLDEFAULT0,
tags JSONB DEFAULT'[]'::jsonb,
metadata JSONB DEFAULT'{}'::jsonb,
created_at TIMESTAMPTZ NOT NULLDEFAULT now(),
last_referenced_at TIMESTAMPTZ
);
CREATE INDEX idx_facts_subject_predicate ON facts (subject, predicate);
CREATE INDEX idx_facts_scope_validity ON facts (scope, validity) WHERE validity ='active';
CREATE INDEX idx_facts_search ON facts USING gin(search_vector);
CREATE INDEX idx_facts_tags ON facts USING gin(tags);
CREATE INDEX idx_facts_embedding ON facts USING ivfflat (embedding vector_cosine_ops) WITH (lists =20);
Approvals Module
Two tables for tool-call approval gating:
Table
Purpose
approval_rules
Pre-approval rules (tool + arg constraints)
pending_actions
Actions awaiting approval/execution
Contacts Module
Three tables for external contact sync:
Table
Purpose
contacts_source_accounts
Registered sync provider accounts
contacts_sync_state
Per-account incremental sync cursor
contacts_source_links
External-to-local contact provenance
Butler-Specific Tables
Each butler defines its own domain tables via migrations in roster/<name>/migrations/. Examples:
Health butler — measurements, medications, medication_doses, conditions, meals, symptoms, research
Switchboard butler — butler_registry, routing_log, extraction_queue, extraction_log, message_inbox, and more
Schema Design Principles
JSONB for flexible/evolving fields. Use typed columns for things you query on (foreign keys, timestamps, amounts). Use JSONB for metadata, details, and fields that vary across records.
Always include created_at. Every table gets created_at TIMESTAMPTZ NOT NULL DEFAULT now().
Include updated_at on mutable tables. If rows get updated, track when.
Use UUID primary keys for domain tables. Use BIGINT GENERATED ALWAYS AS IDENTITY only for high-volume append-only tables.
Use TEXT over VARCHAR. PostgreSQL treats them identically. TEXT is simpler.
Prefer JSONB arrays for tags (JSONB DEFAULT '[]'::jsonb) over TEXT[] — this is the established pattern across the codebase.
Cascade deletes where ownership is clear.ON DELETE CASCADE for child records that have no meaning without their parent.
Use CHECK constraints for enums.CHECK (status IN ('pending', 'active', 'done')) instead of a separate lookup table.
Every timestamp column used in WHERE or ORDER BY gets a descending index. Butlers almost always want "most recent first."
CREATE INDEX idx_<table>_<col>ON<table> (<col>DESC);
Compound indexes for filtered recency queries. If you filter by a category and sort by time:
CREATE INDEX idx_<table>_<filter>_recent ON<table> (<filter_col>, <time_col>DESC);
GIN indexes for JSONB columns you search inside. Use jsonb_path_ops for containment queries (@>), plain GIN if you also need key-existence checks (?, ?|):
CREATE INDEX idx_<table>_<col>_gin ON<table>USING GIN (<col> jsonb_path_ops);
-- or plain GIN (established pattern in codebase):CREATE INDEX idx_<table>_<col>_gin ON<table>USING GIN (<col>);
GIN indexes for JSONB array columns:
CREATE INDEX idx_<table>_tags_gin ON<table>USING GIN (tags);
Partial indexes for hot subsets. If you frequently query only active items or pending tasks:
CREATE INDEX idx_<table>_active ON<table> (<col>) WHERE status ='active';
CREATE INDEX idx_tasks_due ON scheduled_tasks (next_run_at) WHERE enabled =true;
GiST indexes for time-range overlap queries (used by calendar):
CREATE INDEX idx_<table>_time_window_gist
ON<table>USING GIST (tstzrange(starts_at, ends_at, '[)'));
IVFFLAT indexes for vector embeddings (used by memory):
CREATE INDEX idx_<table>_embedding
ON<table>USING ivfflat (embedding vector_cosine_ops) WITH (lists =20);
Don't index columns you never filter or sort on. No index on detail unless you actually run JSONB containment queries against it.
Naming Convention
The codebase uses both idx_ and ix_ prefixes (both are acceptable). Be consistent within a single migration file. Pattern: idx_<table>_<column(s)>.
Alembic Migration System
All schema changes go through Alembic. No exceptions.
Multi-Chain Architecture
Migrations are organized into independent chains that are auto-discovered by alembic/env.py:
"""<Short description of what this migration does>.
Revision ID: <prefix>_<number>
Revises:
Create Date: YYYY-MM-DD HH:MM:SS.000000
"""from __future__ import annotations
from alembic import op
# revision identifiers, used by Alembic.
revision = "<prefix>_001"# e.g., "health_001", "mem_001", "core_005"
down_revision = None# None for first migration in chain, else previous revision
branch_labels = ("<chain>",) # Only on first migration in a chain (e.g., ("health",))
depends_on = None# Cross-chain dependency (e.g., "core_001")defupgrade() -> None:
# Raw SQL via op.execute() — NO SQLAlchemy ORM operations
op.execute("""
CREATE TABLE IF NOT EXISTS example (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
data JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
""")
op.execute("""
CREATE INDEX IF NOT EXISTS idx_example_name
ON example (name)
""")
defdowngrade() -> None:
op.execute("DROP INDEX IF EXISTS idx_example_name")
op.execute("DROP TABLE IF EXISTS example")
Key Conventions
Raw SQL only. Use op.execute("CREATE TABLE ..."), not op.create_table(...) with SQLAlchemy Column objects. The project has target_metadata=None.
IF NOT EXISTS / IF EXISTS. All DDL uses idempotent forms because multiple schema-scoped runs execute the same migration file.
Branch labels on first migration only. The first migration in a chain sets branch_labels = ("<chain_name>",). Subsequent migrations in the chain set branch_labels = None.
Revision ID prefixes. Use a chain prefix for readability:
Create the migration file: roster/<butler-name>/migrations/001_<butler>_tables.py
Set branch_labels = ("<butler-name>",) on the first migration
Use the revision ID pattern: <butler-name>_001
The migration will be auto-discovered by alembic/env.py
Example first migration for a new butler:
"""create_finance_tables
Revision ID: finance_001
Revises:
Create Date: 2026-02-23 00:00:00.000000
"""from __future__ import annotations
from alembic import op
revision = "finance_001"
down_revision = None
branch_labels = ("finance",)
depends_on = Nonedefupgrade() -> None:
op.execute("""
CREATE TABLE IF NOT EXISTS accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE,
account_type TEXT NOT NULL,
currency TEXT NOT NULL DEFAULT 'USD',
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
""")
op.execute("""
CREATE TABLE IF NOT EXISTS transactions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
amount NUMERIC(12,2) NOT NULL,
description TEXT,
category TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
occurred_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
)
""")
op.execute("""
CREATE INDEX IF NOT EXISTS idx_transactions_account_occurred
ON transactions (account_id, occurred_at DESC)
""")
op.execute("""
CREATE INDEX IF NOT EXISTS idx_transactions_category_occurred
ON transactions (category, occurred_at DESC)
""")
defdowngrade() -> None:
op.execute("DROP TABLE IF EXISTS transactions")
op.execute("DROP TABLE IF EXISTS accounts")
Backward Compatibility Rules
Every migration must be backward-compatible. Assume the old code is still running when the migration executes.
Operation
Safe?
How to do it safely
Add a table
Yes
CREATE TABLE IF NOT EXISTS. Old code ignores it.
Add a nullable column
Yes
ALTER TABLE ADD COLUMN ... DEFAULT NULL. Old code ignores it.
Add a column with a default
Yes
ALTER TABLE ADD COLUMN ... DEFAULT <value>. Old code ignores it.
Add an index
Yes
Use CREATE INDEX CONCURRENTLY for large tables. See note below.
Drop a column
Two-phase.
Phase 1: Stop reading/writing the column in code. Deploy. Phase 2: Drop column.
Rename a column
Two-phase.
Phase 1: Add new column, backfill, update code. Phase 2: Drop old column.
Drop a table
Two-phase.
Phase 1: Remove all code references. Deploy. Phase 2: Drop table.
Change a column type
Careful.
Add new column, backfill, migrate code, drop old.
Add NOT NULL
Two-phase.
Phase 1: Backfill NULLs, set default in code. Phase 2: SET NOT NULL.
CONCURRENTLY note:CREATE INDEX CONCURRENTLY cannot run inside a transaction. If needed, the migration must disable the transaction wrapper:
defupgrade() -> None:
op.execute("COMMIT") # Exit Alembic's transaction
op.execute("CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_name ON table (col)")
Schema-Scoped Migration Execution
When the daemon runs migrations for a butler, alembic/env.py sets the target schema:
# In env.py run_migrations_online():if target_schema isnotNone:
connection.exec_driver_sql(f"CREATE SCHEMA IF NOT EXISTS {own_schema}")
connection.exec_driver_sql(f"SET search_path TO {own_schema}, public")
This means:
Core tables (state, sessions, etc.) are created in the butler's own schema, not in public
Butler-specific tables are also created in the butler's own schema
Each butler has its own copy of core tables (no cross-butler contamination)
The public schema contains only tables explicitly created there by core migrations (calendar projections, etc.)
Adding a New Butler to Core ACL
When adding a new butler, core_001 (or a subsequent core migration) must list the butler in _BUTLER_SCHEMAS to create its runtime role and grant privileges. If the butler is added after the initial deployment, write a new core migration that:
Creates the schema
Creates the runtime role
Grants appropriate privileges
What NOT to Do
Don't use SQLAlchemy ORM in migrations — use op.execute() with raw SQL
Don't create separate databases — all butlers share butlers DB with schema isolation
Don't access other butler schemas directly — use MCP/Switchboard for inter-butler communication
Don't skip IF NOT EXISTS — migrations run per-schema, idempotency is required
Don't use datetime.now() in SQL — use now() for consistency
Don't put migrations in src/butlers/db/ — they go in alembic/versions/core/, src/butlers/modules/*/migrations/, or roster/*/migrations/
Don't import sqlalchemy in migrations — only import from alembic import op
