Database Schema

Database Schema

Maestro uses three stores with distinct responsibilities: PostgreSQL (auth, billing, durable task runs — the source of truth), MongoDB (logs, sessions, marketplace, traces — analytics + event source), and Qdrant (RAG vectors). See Architecture-Overview for how they fit together.

PostgreSQL (SQLAlchemy 2.0 async)

models/base.py defines Base(DeclarativeBase), a portable GUID type (native PG UUID, CHAR(36) elsewhere so tests can run on SQLite), and TimestampMixin(created_at, updated_at).

users (user.py)

id, email (unique), hashed_password (Argon2), display_name, bio, avatar_color, avatar_emoji, subscription_tier (default starter, denormalized cache), default_provider, model_preferences (JSONB, per-role model routing), totp_secret (AES-encrypted), totp_enabled, email_verified, timezone, default_reviewer_enabled, deletion_requested_at (indexed). Relationship → api_keys (cascade). Property two_factor_enabled.

api_keys (api_key.py)

id, user_id (FK cascade), provider, encrypted_key (Text, AES-256-GCM), label, key_hint (****abcd), is_active, base_url, model (last two only for custom provider).

subscriptions (subscription.py)

One row per user. id, user_id (unique FK), plan, status, provider, provider_subscription_id, provider_customer_id, current_period_start / current_period_end (quota-window anchors), trial_end (legacy, nullable, unused), cancel_at_period_end.

payment_methods (payment_method.py)

id, user_id, provider, provider_payment_method_id, brand, last4, exp_month, exp_year, is_default. Full PAN is never stored — only brand + last4 + expiry.

usage_records (usage_record.py)

Append-only quota ledger — the only source quota trusts. id, user_id, task_id (unique — idempotency key), tokens, provider, status, billable, period_start (= subscriptions.current_period_start). Index ix_usage_records_user_period.

refresh_tokens (refresh_token.py)

id = jti, user_id, family_id (session lineage for rotation/reuse-detection), revoked_at, expires_at, user_agent, ip_address, last_used_at.

recovery_codes (recovery_code.py)

id, user_id, code_hash (Argon2), used_at.

email_tokens (email_token.py)

id, user_id, purpose, token_hash (SHA-256, unique — only the hash is stored), expires_at, used_at. Rows CASCADE with the user.

Durable engine tables (task_run.py)

• task_runs — task_id PK (str UUID), user_id, status, current_step, payload (frozen TaskCreate, no keys), provider, cancel_requested, worker_id, lease_expires_at, attempt, deadline_at. Index ix_task_runs_reclaim(status, lease_expires_at).
• task_checkpoints — id (BigSerial), task_id (FK cascade), step_key, payload, tokens_used, created_at. Unique (task_id, step_key).
• task_questions — question_id PK, task_id (FK), question, answer, status (pending | answered | expired), asked_at, answered_at, expires_at.

Alembic migration chain

Revision	What it adds
0001_initial	users, api_keys
0002_user_default_provider	users.default_provider
0003_billing_and_quota	subscriptions, payment_methods, usage_records; removes the free tier
0004_account_deletion	users.deletion_requested_at + index (30-day grace)
0005_api_key_endpoint	api_keys.base_url, api_keys.model (custom endpoints)
0006_refresh_tokens	refresh_tokens (rotation + reuse-detection)
0007_profile_personalization_2fa_sessions	profile columns, TOTP columns, recovery_codes, session context
0008_email_verification	users.email_verified + email_tokens
0009_remove_trial_and_discount	removes trial + first-month discount (full-price only)
0010_task_runs	durable engine: task_runs, task_checkpoints, task_questions
0011_model_prefs	users.model_preferences (JSONB)

Migrations are managed exclusively through Alembic (backend/alembic.ini, backend/alembic/). Never run manual SQL.

MongoDB (Motor)

ensure_indexes (called on startup) creates indexes and TTLs, retuning via collMod. Collections (MongoCollection enum):

Collection	Purpose
agent_logs	Step-by-step agent execution log; the seq-ordered event source for WS replay. user_id written on every doc (needed for GDPR purge).
task_sessions	Task sessions + intermediate state + result (analytics; capped event mirror).
marketplace_items	Published agent teams + metadata.
agent_configurations	Custom agent system prompts + tool definitions + provenance.
documents	Uploaded RAG document metadata.
marketplace_installs	Install history (for install counts / trends).
marketplace_reviews	Ratings + reviews.
trace_spans	OTel-shaped spans for the cost/trace views; TTL-expired.

Qdrant (vectors)

Collection	Purpose
conversation_memories	Per-user conversation embeddings for RAG recall.
document_chunks	Uploaded document chunks + embeddings.

EMBEDDING_DIM = 768 (nomic-embed-text). All queries are filtered by user_id so memory is strictly per-user isolated. See RAG-and-Memory.

Two schema rules that bite

1. Account purge order is Mongo → Qdrant → PostgreSQL last. The deletion_requested_at flag on the PG row is how the sweep re-finds the account; if the PG row were deleted before Mongo/Qdrant are cleaned, data would be orphaned irreversibly. purge_user_data raises (does not swallow) so the sweep retries. See Security.
2. Quota is enforced only via Postgres usage_records. MongoDB task_sessions is analytics only. Tokens are counted by TokenMeter and written to the ledger in the task's finally block on every terminal path. See Billing-and-Quota.