Database Schema

Every table in the AUA Framework's SQLite state store. The core v0.7+ tables handle promotions, corrections, sessions, and audit. The v1.1-veritas tables were backported from AUA-Veritas production.

File: .aua/state/aua.db Engine: SQLite 3 (WAL mode, NORMAL sync) Configured via: state.path

The full schema lives in aua/state.py → _SCHEMA. The SQLiteStateStore class initialises all tables on first run via conn.executescript(_SCHEMA). Migration from v0.7 flat files: aua config migrate --from 0.7 --to 0.8.

Core tables — v0.7+

core

promotions

Specialist promotion and rollback events. Replaces the v0.7 .aua/state/promotions.jsonl flat file. Append-only — never deleted.

Column	Type	Notes
idPK	TEXT	UUID v4. Auto-generated if not supplied.
created_at	REAL	Unix timestamp.
specialist	TEXT	Specialist name (e.g. `'swe'`).
event	TEXT	`'promote'` \| `'rollback'`.
from_model / to_model	TEXT	Model identifiers for the transition.
reverted	INTEGER	`1` if this promotion was later rolled back.
metadata	TEXT	JSON catch-all (eval scores, operator notes).

idx_promotions_specialist

core

corrections

Verified facts and DPO pairs from the arbiter. Replaces dpo_pairs/*.jsonl. Used by the correction retrieval step to inject context into specialist prompts.

Column	Type	Notes
idPK	TEXT	UUID v4.
created_at	REAL
subject	TEXT	Topic string used for retrieval matching.
domain	TEXT	Field/domain this correction applies to.
claim	TEXT	The verified correct claim (chosen in DPO pair).
rejected	TEXT	The incorrect claim (rejected in DPO pair). May be empty for preference-only corrections.
confidence	REAL	0–1. Higher = more likely to be injected into prompt.
source	TEXT	`'arbiter'` \| `'manual'`.
effective_confidence	REAL	Confidence after decay applied.
decay_class	TEXT	`'A'` (permanent) \| `'B'` (slow) \| `'C'` (fast).
metadata	TEXT	JSON: session_id, trace_id, correction_type, extra context.

idx_corrections_domain

core

sessions

Lightweight query session tracking. Updated on every query. Used to group routing traces and audit events. The Chat Session API (U-01) builds on top of this with conversations and messages below.

Column	Type	Notes
idPK	TEXT	UUID v4, auto-generated if not supplied by client.
created_at / updated_at	REAL
domain	TEXT	Dominant field for this session.
query_count	INTEGER	Incremented on every query in this session.
metadata	TEXT	JSON: token_id, trace_ids, routing summary.

core

audit_log

Append-only audit trail with a SHA-256 hash chain for tamper detection. Every event type — query, correction, promotion, auth failure, config reload — produces a row. prev_hash → curr_hash forms an unbreakable chain.

Column	Type	Notes
idPK	TEXT	UUID v4.
created_at	REAL
event_type	TEXT	`'query'` \| `'correction'` \| `'promote'` \| `'rollback'` \| `'auth_failure'` \| `'config_reload'` \| `'hook_failure'`…
session_id / trace_id / token_id	TEXT	Correlation IDs.
field	TEXT	Domain/field for this event.
specialist	TEXT	Specialist name (if applicable).
u_score / confidence / latency_ms	REAL	Performance metrics at time of event.
details	TEXT	JSON extra context.
prev_hash / curr_hash	TEXT	SHA-256 hash chain. Written by `append_audit()`. Verify with `aua doctor --check-audit`.

idx_audit_session

core

assertion_events

Per-query record of which assertions the arbiter ran, whether they passed, and what bonus or penalty was applied to the utility score. Used for blue-green debugging and assertion tuning.

Column	Type	Notes
idPK	TEXT
session_id	TEXT	FK to `sessions`.
assertion_name	TEXT	e.g. `'logical_consistency'`, `'cross_session_match'`.
level	TEXT	`'blocking'` \| `'soft'` \| `'info'`. Blocking failures abort the response.
passed	INTEGER	`1` = passed, `0` = failed.
bonus_applied	REAL	Utility bonus if assertion passed. Negative for failures.
retries_used	INTEGER	How many retries the arbiter used.
latency_ms	REAL	Time spent running this assertion.
domain / policy_name	TEXT	Context for filtering.

idx_assertion_events_session · idx_assertion_events_name · idx_assertion_events_created

v1.1-veritas backport tables

These tables were added in the Phase 13 backport from AUA-Veritas. They ship in aua/state.py and are initialised alongside the core tables. See v1.1-veritas roadmap for implementation rules.

v1.1-veritas

conversations

Named chat sessions. The root entity for the Chat Session API (U-01). Supports project scoping via project_id.

Column	Type	Notes
conversation_idPK	TEXT	UUID v4.
user_id	TEXT	Default `'local'`. Multi-user: set to token sub.
title	TEXT	Display title. PATCH /conversations/{id}/title to update.
project_id	TEXT	`NULL` = global. FK to `projects.project_id`.
created_at / updated_at	REAL	`updated_at` refreshed on every message write.

idx_conv_project · idx_conv_user

v1.1-veritas

messages

User and assistant messages. Pagination via created_at timestamp cursors. Content is AES-encrypted in production deployments (configure via aua/encryption.py).

Column	Type	Notes
message_idPK	TEXT	UUID v4.
conversation_id	TEXT	FK to `conversations`.
role	TEXT	`'user'` \| `'assistant'`.
content	TEXT	Plaintext or ciphertext. Encrypted in production.
callout_type	TEXT	`'disagreement'` \| `'correction'` \| `NULL`. Controls Chat UI card rendering.
models_used	TEXT	JSON list of specialist IDs that answered.
accuracy_level	TEXT	`'fast'` \| `'balanced'` \| `'high'` \| `'maximum'`.
confidence	TEXT	VCG confidence label: `'High'` \| `'Medium'` \| `'Uncertain'`.
created_at	REAL	Pagination cursor.

idx_msg_conv · idx_msg_created

v1.1-veritas

model_runs

One row per specialist per query. Tracks VCG welfare scores, latency, domain, and whether this specialist won. Data source for reliability analytics.

Phase 13 rule: conversation_id must be passed as an explicit parameter to every method that writes a model_run — never rely on closure capture. Without it there is no join path between a conversation and its model runs.

Column	Type	Notes
run_idPK	TEXT	UUID v4.
query_id	TEXT	Groups all specialist runs for a single query (loose ref).
conversation_id	TEXT	FK to `conversations`. Required join column.
specialist	TEXT	Specialist identifier.
round	TEXT	`'answer'` \| `'peer_review'`.
utility_score / confidence_score / vcg_welfare_score	REAL	Scores from the utility and VCG mechanisms.
vcg_winner	INTEGER	`1` if VCG selected this specialist.
corrections_applied	TEXT	JSON list of correction IDs injected into this specialist's prompt.
latency_ms	REAL	Wall-clock request-to-first-token.
domain / domain_l0 / domain_path	TEXT	Domain classification hierarchy.
created_at	REAL

idx_runs_conv · idx_runs_specialist

v1.1-veritas

token_counters

Shadow token counter per specialist per conversation. Resets to 0 after a context backup is stored (new thread begins). Three backup triggers read from this table: token threshold, message count, time gap.

Column	Type	Notes
counter_idPK	TEXT	UNIQUE(specialist, conversation_id).
specialist / conversation_id	TEXT	Composite key.
token_estimate	INTEGER	chars/4 heuristic. Resets to 0 after backup.
message_count	INTEGER	Messages since last backup. Triggers at 30.
thread_number	INTEGER	Matches `context_backups.thread_number` for current window.
updated_at	REAL

idx_counters_conv

v1.1-veritas

message_keywords

Inverted index backing full-text search. Written asynchronously by a queue-based keyword worker. A sorted in-memory list enables O(log n) prefix matching via bisect. The DB table survives restarts — rebuilt into memory at startup.

Closure-scope trap (Phase 13): the keyword worker closure inside lifespan() must import time locally — it cannot reference module-level aliases imported later in the same body. This bug silently broke search in every session for months.

Column	Type	Notes
keyword + message_idPK	TEXT	Composite primary key.
conversation_id	TEXT	Redundant with messages for direct query without join.
role	TEXT	`'user'` \| `'assistant'`.
created_at	REAL

idx_kw_conv

v1.1-veritas

context_backups

Structured handoff notes written by each specialist every ~30 messages or at 70% context pressure. A new context window receiving this backup can resume without re-briefing. Append-only.

Validity check: MAX(context_backups.created_at WHERE specialist=S, conversation_id=C) > MAX(messages.created_at WHERE conversation_id=C). backup_is_valid() encapsulates this. The 6-hour coverage job finds and fills gaps.

Column	Type	Notes
backup_idPK	TEXT	UUID v4.
conversation_id / specialist	TEXT	Composite lookup key.
trigger	TEXT	`'token_threshold'` \| `'message_count'` \| `'time_gap'` \| `'manual'` \| `'coverage_job_no_backup'`.
thread_number	INTEGER	Increments each time a backup triggers a counter reset.
backup_text	TEXT	6-section structured note: GOAL / DECISIONS / STATUS / ACTIVE FILE / PREFERENCES / RESUME.
token_estimate	INTEGER	chars/4. Used by coverage reporting.
created_at	REAL	Append-only — rows never deleted.

idx_backup_conv

v1.1-veritas

projects

Named groups for scoping conversations. When a project is active, only that project's conversations are shown and new conversations are automatically assigned to it.

Column	Type	Notes
project_idPK	TEXT	UUID v4. Referenced by `conversations.project_id`.
user_id	TEXT	Owner. Default `'local'`.
name	TEXT	Display name (e.g. `'Immigration Law'`).
created_at	REAL