Chapter 07 / 14
Database
Domain schema
Maintained domain tables live in PostgreSQL schema app; retained compatibility tables remain temporarily in public. This makes cutover reversible without misleading _v2 names.
Administrative knowledge is jurisdictions → service_responsibilities → authorities → service_locations. Procedures and forms have stable identity rows, immutable versions, source evidence, and explicit published-version pointers. Conversations, turns, memory items, and memory consents represent tiered memory without raw audio. Tool executions, authorizations, and audit events share enforced identities.
Domain relationship guide
The diagram below groups the relational model into six public domains. It is an orientation aid rather than a substitute for SQLAlchemy constraints: its text equivalent names every displayed relationship, and the generated inventory that follows is derived from maintained model declarations. Authentication identities do not contain case data or voice audio; voice audio remains outside Postgres. The presentation demos use ephemeral data and do not create these domain records.
Migration state
The additive add_domain_schema_v2 migration is applied. Production backfill preserves 10,943 jurisdictions, 153 authorities and locations, 153 responsibilities, 291 procedures, 292 procedure versions, 294 evidence records, five form definitions/versions, the official EzB asset reference, and 1,749 audit events. Verification reports zero unexpected orphans and zero missing publication pointers.
Legacy tables remain intact for rollback. The unowned students table has three rows, no foreign keys or dependencies, and is inventoried without publishing its row values. Moving or deleting it requires separate destructive authorization.
Rules and retention
Foreign keys replace encoded polymorphic references and have supporting indexes. Stored timestamps are timezone-aware. Official AGS values are unique domain attributes, not foreign keys everywhere. Personal/operational tables name expiry, revocation, deletion, or purge state. Demos bypass persistence.
Migration commands verify the expected Alembic head and require an explicit production acknowledgement. Cleanup is deliberately separate from additive migration and observation.
- Administrative knowledge → Administrative knowledge: jurisdictions → service responsibilities
- Administrative knowledge → Procedures and forms: responsibilities → procedures
- Procedures and forms → Procedures and forms: procedures → procedure versions
- Identity → Conversations and memory: accounts → bounded conversations
- Conversations and memory → Conversations and memory: conversations → conversation turns
- Conversations and memory → Voice and backend actions: approved proposals → tool executions
- Voice and backend actions → Voice and backend actions: tool executions → audit events
- Voice and backend actions → Operations: allow-listed outcomes → product events
| Table | Schema |
|---|---|
| admin_audit_events | public |
| admin_owners | public |
| admin_sessions | public |
| app.account_tokens | app |
| app.audit_events | app |
| app.authorities | app |
| app.conversation_turns | app |
| app.conversations | app |
| app.form_assets | app |
| app.form_definitions | app |
| app.form_versions | app |
| app.jurisdictions | app |
| app.memory_consents | app |
| app.memory_items | app |
| app.procedure_versions | app |
| app.procedures | app |
| app.service_locations | app |
| app.service_responsibilities | app |
| app.source_evidence | app |
| app.tool_authorizations | app |
| app.tool_executions | app |
| app.voice_enrolments | app |
| authorities | public |
| contacts | public |
| content_revisions | public |
| conversation_memories | public |
| conversation_memory_consents | public |
| diagnostic_reports | public |
| institution_scenarios | public |
| institutions | public |
| municipalities | public |
| municipality_authorities | public |
| offices | public |
| password_reset_tokens | public |
| product_events | public |
| profiles | public |
| template_assets | public |
| tool_audit_records | public |
| tool_execution_records | public |
| tool_resource_authorizations | public |
| user_accounts | public |
| user_sessions | public |
| verification_records | public |
| voice_enrolments | public |