Database Schema

AEGIS uses PostgreSQL 15 with three extensions: pgvector for vector similarity search, Apache AGE for the knowledge graph, and uuid-ossp for UUID generation. The schema is initialized from SQL files mounted into the PostgreSQL container at startup.

Schema Initialization

The database is initialized by three SQL files executed in order:

File	Purpose
`00-create-extension-age.sql`	Create the AGE extension with `IF NOT EXISTS` guard
`infrastructure/docker/postgres/init.sql`	Core tables: audit logs, episodic memories, agents, skills, approvals, conversations, budget
`infrastructure/docker/postgres/002_checklist_compliance_tables.sql`	Checklist templates, filing checklists, compliance status, rule versions, filing rule snapshots
`infrastructure/docker/postgres/007_entity_type_definitions.sql`	Admin-managed entity type system: type definitions, field definitions, relationship rules, RRC mappings
`infrastructure/docker/postgres/029_entity_type_vertex_label.sql`	Stored `vertex_label` column on `entity_type_definitions` (type_key → AGE label bridge, entity-explorer hardening Root A)
`services/agent-config-service/.../001_initial_schema.sql`	Prompt management: namespaces, templates, versions, budget tiers, audit log, access control

Extensions


CREATE EXTENSION IF NOT EXISTS vector;       -- pgvector for episodic memory
CREATE EXTENSION IF NOT EXISTS age;          -- Apache AGE for knowledge graph
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- UUID generation

Graph Setup

After enabling extensions, the init script creates the AGE graph. Graphs are per-tenant (tenant_<hex>_oilgas, where <hex> is the dash-free hex of the tenant UUID) — there is no single global oilgas graph. init.sql seeds the dev tenant’s graph:


LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('tenant_00000000000000000000000000000001_oilgas');
SET search_path = public, ag_catalog, "$user";

The oilgas suffix is the logical schema name (the first installed vertical); entity/vertex labels are runtime DB-backed via entity_type_definitions (see Graph Schema), not hardcoded.

Every PostgreSQL connection that runs Cypher queries must execute LOAD 'age' and SET search_path = ag_catalog, "$user", public before any graph operation. The AgePool class handles this automatically.

Core Tables (init.sql)

audit_logs

Append-only audit trail. Database triggers prevent UPDATE and DELETE operations.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Unique log entry ID
`tenant_id`	`VARCHAR(50)`	`NOT NULL`	Tenant isolation key
`event_type`	`VARCHAR(100)`	`NOT NULL`	Type of audit event
`event_data`	`JSONB`	`NOT NULL`	Full event payload
`actor_id`	`VARCHAR(100)`		Who performed the action. Stays TEXT permanently (R42e): the table is append-only and hash-chained, so rows are never rewritten — historical actor strings resolve at READ time through `identity_map`
`actor_type`	`VARCHAR(20)`	default `'system'`	Actor category (system, user, agent)
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Event timestamp
`signature`	`VARCHAR(256)`		HMAC signature for tamper detection

Protection Triggers:


CREATE OR REPLACE FUNCTION prevent_audit_modification()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION 'Audit logs are append-only. Updates and deletes are prohibited.';
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER no_update_audit BEFORE UPDATE ON audit_logs
    FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();
 
CREATE TRIGGER no_delete_audit BEFORE DELETE ON audit_logs
    FOR EACH ROW EXECUTE FUNCTION prevent_audit_modification();

The audit trail is immutable by design. Any attempt to UPDATE or DELETE a row will raise a PostgreSQL exception. This is enforced at the database level, not the application level, so it cannot be bypassed by service code.

users

Authentication accounts for the auth service. Accounts are admin-provisioned (no self-serve signup); passwords are stored as bcrypt hashes.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Unique user ID
`email`	`VARCHAR(255)`	`NOT NULL`	Login email (case-insensitively unique)
`password_hash`	`TEXT`	`NOT NULL`	bcrypt password hash. Empty string = invited or system user — login fails closed
`display_name`	`VARCHAR(200)`		Optional display name
`is_active`	`BOOLEAN`	`NOT NULL`, default `TRUE`	Inactive users cannot log in; deactivation is the removal path (no hard delete)
`token_version`	`INT`	`NOT NULL`, default `0`	R42a revocation seam — bumped by logout-all / deactivation / password reset; compared against the JWT `tv` claim
`password_set_at`	`TIMESTAMPTZ`		R42a — `NULL` = invited (empty `password_hash`, login fails closed)
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last update timestamp

Indexes:

Index	Type	Columns	Notes
`idx_users_email_lower`	UNIQUE	`LOWER(email)`	Enforces case-insensitive email uniqueness (no `citext` extension)

This table is created in init.sql for fresh databases; the auth-service boot-time migration runner (R42a) also re-ships it idempotently, so a fresh database converges on service start without manual psql. The legacy roles TEXT[] shadow column is dropped in R42e — grants live solely in user_roles; the boot runner drops the column only after its one-time backfill has run.

System users (R42e, migration 031): four non-loginable accounts seeded for FK integrity and attribution — password_hash = '' fails closed at login, is_active = TRUE so they satisfy FKs and appear in attribution joins:

Email	UUID	Purpose
`system@aegis.local`	`00000000-0000-0000-0000-000000000000`	Deliberately the nil UUID — the `SYSTEM_USER` literal seeds have always stamped into `created_by`, so historical rows satisfy the new FKs without a rewrite
`legacy@aegis.local`	`…0000001e9ac1`	Backfill target for unmappable historical identity strings
`eval-runner@aegis.local`	`…0000000000e5`	Eval replay identity — inserted only if the email is absent (deployed boxes keep their real row)
`demo-seed@aegis.local`	`…0000deed5eed`	Demo-data ownership tag — the demo seeder deletes-then-recreates only rows carrying this id

RBAC tables: roles, role_permissions, user_roles, user_invites (R42a)

Owned by auth-service’s boot-time migration runner (services/auth-service/src/auth_service/migrations/003_rbac_substrate.sql). Boot-order rule: non-auth services must never FK to roles/user_roles — only users is guaranteed to exist everywhere.

Table	Purpose
`roles`	`(id, tenant_id NULL=platform, key, display_name, description, is_system)` — the 4 seeded system roles (`admin`, `power_user`, `reviewer`, `operator`); unique on `(scope, key)`
`role_permissions`	`(role_id FK CASCADE, permission)` — `resource.action` strings; seeded from the signed-off R42 Appendix C matrix in `auth_service/rbac.py`
`user_roles`	`(user_id FK CASCADE, role_id FK CASCADE, granted_by, granted_at)` — sole source of truth for a user’s roles; backfilled once from the legacy `users.roles` array (column dropped in R42e)
`user_invites`	`(user_id FK CASCADE, token_hash UNIQUE sha256, purpose invite/password_reset, expires_at, used_at, created_by)` — one-time tokens, single-use via `used_at IS NULL` + row lock; invite 7d, reset 1h

episodic_memories

Conversation summaries with vector embeddings for semantic search.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Memory ID
`agent_id`	`VARCHAR(100)`	`NOT NULL`	Which agent generated this memory
`user_id`	`UUID`	`REFERENCES users(id) ON DELETE RESTRICT`	User who participated in the conversation (retyped from `VARCHAR` in R42e, migration 031)
`conversation_id`	`VARCHAR(100)`	`NOT NULL`	Source conversation
`summary`	`TEXT`	`NOT NULL`	Human-readable summary
`key_decisions`	`JSONB`		Decisions made during the conversation
`entities_mentioned`	`JSONB`		Entity IDs referenced
`tools_called`	`JSONB`		Tools invoked during execution
`embedding`	`vector(1536)`		OpenAI text-embedding-3-small vector
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp

Indexes:

Index	Type	Columns	Notes
`idx_episodic_embedding`	IVFFlat	`embedding vector_cosine_ops`	`WITH (lists = 100)` for cosine similarity
`idx_episodic_agent_user`	B-tree	`(agent_id, user_id)`	Filter by agent and user

agents

Agent definitions with configuration, system prompts, and skill assignments.

Column	Type	Constraints	Description
`id`	`VARCHAR(100)`	`PRIMARY KEY`	Agent identifier (e.g., `rule37-agent`)
`name`	`VARCHAR(200)`	`NOT NULL`	Display name
`config`	`JSONB`	`NOT NULL`	System prompt, model prefs, skills, budgets, HITL policies
`status`	`VARCHAR(20)`	default `'active'`	Agent status
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last update timestamp

skills

Skill registry with the three-tier injection architecture.

Column	Type	Constraints	Description
`id`	`VARCHAR(100)`	`PRIMARY KEY`	Skill identifier (e.g., `spacing-calculation`)
`name`	`VARCHAR(200)`	`NOT NULL`	Display name
`tier1_manifest`	`JSONB`	`NOT NULL`	Tier 1: name, description, triggers (~50 tokens)
`tier2_definition`	`JSONB`	`NOT NULL`	Tier 2: full specification (~200-800 tokens)
`tier3_artifact_refs`	`JSONB`		Tier 3: references to artifact content
`domain_tags`	`VARCHAR(100)[]`		Array of domain labels for skill matching
`status`	`VARCHAR(20)`	default `'active'`	Skill status
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last update timestamp

skill_artifacts

Tier 3 artifact content for skills (reference tables, form guides, regulatory text).

Column	Type	Constraints	Description
`id`	`VARCHAR(100)`	`PRIMARY KEY`	Artifact identifier
`skill_id`	`VARCHAR(100)`	`REFERENCES skills(id)`	Parent skill
`name`	`VARCHAR(200)`	`NOT NULL`	Artifact name
`content`	`TEXT`	`NOT NULL`	Full artifact content
`content_hash`	`VARCHAR(64)`	`NOT NULL`	SHA-256 hash for change detection
`token_estimate`	`INT`		Approximate token count
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp

approval_requests

HITL approval requests for agent execution checkpoints.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Request ID
`execution_id`	`VARCHAR(100)`	`NOT NULL`	Agent execution ID
`agent_id`	`VARCHAR(100)`	`NOT NULL`	Agent that created the request
`checkpoint_type`	`VARCHAR(100)`	`NOT NULL`	HITL checkpoint type (e.g., `pre_filing`)
`state_snapshot`	`JSONB`	`NOT NULL`	Full agent state at checkpoint
`reviewer_id`	`UUID`	`REFERENCES users(id) ON DELETE RESTRICT`	Assigned reviewer (retyped from `VARCHAR` in R42e, migration 031)
`reviewer_strategy`	`VARCHAR(50)`	`NOT NULL`	Assignment strategy: `named_individual` or `role_based`
`status`	`VARCHAR(20)`	default `'pending'`	Request status
`decision`	`VARCHAR(20)`		Reviewer decision: `approved`, `rejected`, `modified`
`reviewer_comments`	`TEXT`		Reviewer feedback
`origin`	`TEXT`	`NOT NULL` default `'conversation'`, CHECK ∈ (`conversation`,`replay`)	R40b (`022_approval_origin.sql`): `replay` for eval-replay approvals so the admin queue hides them (list defaults to `origin='conversation'`; replay approvals never escalate to humans)
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`decided_at`	`TIMESTAMPTZ`		Decision timestamp

Index:

Index	Columns	Purpose
`idx_approval_status`	`(status, reviewer_id)`	Filter pending approvals by reviewer

conversations

Conversation sessions linking users to agents.

Column	Type	Constraints	Description
`id`	`VARCHAR(100)`	`PRIMARY KEY`	Conversation ID
`agent_id`	`VARCHAR(100)`	`NOT NULL`	Assigned agent
`user_id`	`UUID`	`REFERENCES users(id) ON DELETE RESTRICT`	Owning user (backfilled from first attributed message by migration 023; retyped to `uuid` + FK in R42e, migration 031)
`status`	`VARCHAR(20)`	default `'active'`	Conversation status
`title`	`VARCHAR(200)`		Display title (falls back to first user message)
`conversation_type`	`VARCHAR(50)`		`filing_prep`, `field_event`, `replay` (eval), `test` (golden/smoke) — `replay`/`test` are hidden from the user list
`last_message_at`	`TIMESTAMPTZ`		Recency for list ordering
`deleted_at`	`TIMESTAMPTZ`		Soft-delete marker
`metadata`	`JSONB`		Additional metadata
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last activity

budget_usage

Token and cost tracking per agent execution.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Record ID
`agent_id`	`VARCHAR(100)`	`NOT NULL`	Agent identifier
`execution_id`	`VARCHAR(100)`	`NOT NULL`	Execution identifier
`tokens_used`	`INT`	`NOT NULL`	Total tokens consumed
`cost_usd`	`DECIMAL(10,6)`		Dollar cost of the execution
`model`	`VARCHAR(100)`		LLM model used
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Record timestamp

Checklist & Compliance Tables (002_checklist_compliance_tables.sql)

checklist_templates

Templates defining the checklist structure for each compliance domain (one template per domain per version).

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Template ID
`tenant_id`	`VARCHAR(50)`	`NOT NULL`	Tenant key
`compliance_domain`	`VARCHAR(50)`	`NOT NULL`	Domain: `rule_37`, `rule_32`, `form_pr`, `flaring_monitor`
`version`	`INT`	default `1`	Template version
`items`	`JSONB`	`NOT NULL`	Ordered array of checklist item definitions
`min_required_items`	`JSONB`		Item indices required for HITL submission
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last update

Unique constraint: (tenant_id, compliance_domain, version)

filing_checklists

Active checklist instances — one per entity per compliance domain work session.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Checklist instance ID
`tenant_id`	`VARCHAR(50)`	`NOT NULL`	Tenant key
`entity_id`	`VARCHAR(255)`	`NOT NULL`	KG entity ID (well API#, facility ID)
`entity_type`	`VARCHAR(50)`	`NOT NULL`	Entity type: `well`, `lease`, `facility`, etc.
`entity_name`	`VARCHAR(500)`		Display name
`compliance_domain`	`VARCHAR(50)`	`NOT NULL`	Compliance domain
`template_id`	`UUID`	`REFERENCES checklist_templates(id)`	Source template
`status`	`VARCHAR(20)`	default `'draft'`, CHECK constraint	Filing status
`deadline`	`TIMESTAMPTZ`		Filing deadline
`items`	`JSONB`	`NOT NULL`	Current state of each checklist item
`metadata`	`JSONB`	default `'{}'`	Conversation IDs, reviewer notes, alerts
`created_by`	`UUID`	`REFERENCES users(id) ON DELETE RESTRICT`	Who initiated the checklist (retyped from `VARCHAR` in R42e, migration 031)
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	default `NOW()`	Last update

Status values: draft, in_progress, ready_for_review, in_review, approved, rejected, filed, exception

Indexes:

Index	Columns
`idx_filing_checklists_entity`	`(tenant_id, entity_id, compliance_domain)`
`idx_filing_checklists_status`	`(tenant_id, status)`

checklist_artifacts

Generated documents, PDFs, and data exports attached to checklist items.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Artifact ID
`checklist_id`	`UUID`	`REFERENCES filing_checklists(id) ON DELETE CASCADE`	Parent checklist
`item_index`	`INT`	`NOT NULL`	Checklist item this artifact belongs to
`artifact_type`	`VARCHAR(50)`	`NOT NULL`	Type: `document`, `pdf`, `plat_draft`, `data_export`, `waiver`, `form_draft`
`name`	`VARCHAR(500)`	`NOT NULL`	Artifact name
`content_type`	`VARCHAR(100)`		MIME type
`content`	`TEXT`		Text/markdown/JSON content
`file_path`	`VARCHAR(1000)`		Path for binary artifacts
`metadata`	`JSONB`	default `'{}'`	Source attribution, version, confidence
`generated_by`	`VARCHAR(50)`	CHECK `IN ('agent', 'user', 'system')`	Who created the artifact
`created_at`	`TIMESTAMPTZ`	default `NOW()`	Creation timestamp

Index: idx_checklist_artifacts_checklist on (checklist_id, item_index)

compliance_status

Materialized cache of the entity-by-domain compliance matrix.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Row ID
`tenant_id`	`VARCHAR(50)`	`NOT NULL`	Tenant key
`entity_id`	`VARCHAR(255)`	`NOT NULL`	KG entity ID
`entity_type`	`VARCHAR(50)`	`NOT NULL`	Entity type
`entity_name`	`VARCHAR(500)`		Display name
`entity_field`	`VARCHAR(255)`		Field name from KG
`entity_district`	`VARCHAR(10)`		RRC district
`compliance_domain`	`VARCHAR(50)`	`NOT NULL`	Compliance domain
`status`	`VARCHAR(20)`	`NOT NULL`, CHECK constraint	Compliance status
`deadline`	`TIMESTAMPTZ`		Next deadline for this domain
`checklist_id`	`UUID`	`REFERENCES filing_checklists(id)`	Active checklist if exists
`details`	`JSONB`	default `'{}'`	Domain-specific status details
`last_assessed_at`	`TIMESTAMPTZ`	default `NOW()`	Last assessment timestamp

Status values: compliant, action_needed, overdue, not_applicable, in_review

Unique constraint: (tenant_id, entity_id, compliance_domain)

Indexes:

Index	Columns
`idx_compliance_status_tenant`	`(tenant_id, status)`
`idx_compliance_status_domain`	`(tenant_id, compliance_domain)`
`idx_compliance_status_deadline`	`(tenant_id, deadline)`

rule_versions

Immutable, versioned regulatory rules. Never UPDATE existing rules — always create a new version.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Rule version ID
`tenant_id`	`VARCHAR(50)`	`NOT NULL`	Tenant key
`rule_type`	`VARCHAR(50)`	`NOT NULL`	`statewide`, `field_specific`, `notice`
`rule_domain`	`VARCHAR(50)`	`NOT NULL`	`spacing`, `density`, `flaring`, `reporting`
`rule_identifier`	`VARCHAR(255)`	`NOT NULL`	Unique rule key (e.g., `SWR_37`)
`version`	`INT`	`NOT NULL`	Version number (monotonically increasing)
`effective_date`	`DATE`	`NOT NULL`	When the rule took effect
`superseded_date`	`DATE`		When superseded (NULL if current)
`source`	`VARCHAR(50)`	`NOT NULL`	`rrc_ingestion`, `rule_monitor_agent`, `manual`, `docket_hearing`
`source_reference`	`VARCHAR(500)`		Citation, docket number, URL
`rule_data`	`JSONB`	`NOT NULL`	Actual rule parameters
`graph_node_id`	`VARCHAR(255)`		Reference to KG node
`change_summary`	`TEXT`		What changed in this version
`detected_at`	`TIMESTAMPTZ`	default `NOW()`	When the change was detected
`detected_by`	`VARCHAR(100)`		Who/what detected the change
`status`	`VARCHAR(20)`	default `'active'`, CHECK constraint	Rule status

Status values: active, superseded, pending_review, draft

Unique constraint: (tenant_id, rule_identifier, version)

Indexes:

Index	Columns	Notes
`idx_rule_versions_active`	`(tenant_id, rule_identifier, status)`	Partial: `WHERE status = 'active'`
`idx_rule_versions_domain`	`(tenant_id, rule_domain)`

filing_rule_snapshots

Frozen copies of rules at the time a filing was initiated. Used for stale detection.

Column	Type	Constraints	Description
`id`	`UUID`	`PRIMARY KEY`, default `uuid_generate_v4()`	Snapshot ID
`checklist_id`	`UUID`	`REFERENCES filing_checklists(id) ON DELETE CASCADE`	Parent checklist
`rule_version_id`	`UUID`	`REFERENCES rule_versions(id)`	Original rule version
`snapshotted_at`	`TIMESTAMPTZ`	default `NOW()`	Snapshot timestamp
`rule_data_at_snapshot`	`JSONB`	`NOT NULL`	Frozen copy of rule_data
`is_current`	`BOOLEAN`	default `true`	False if rule has been superseded
`superseded_by`	`UUID`	`REFERENCES rule_versions(id)`	Newer version if superseded
`acknowledged`	`BOOLEAN`	default `false`	User acknowledged the stale rule
`acknowledged_by`	`VARCHAR(255)`		Who acknowledged
`acknowledged_at`	`TIMESTAMPTZ`		When acknowledged

Indexes:

Index	Columns	Notes
`idx_filing_rule_snapshots_checklist`	`(checklist_id)`
`idx_filing_rule_snapshots_stale`	`(is_current)`	Partial: `WHERE is_current = false`

Entity Type System Tables (007_entity_type_definitions.sql)

entity_type_definitions

Admin-managed entity type schemas (e.g., Well, Facility, Wellpad).

Column	Type	Constraints	Description
`type_id`	`UUID`	`PRIMARY KEY`, default `gen_random_uuid()`	Type ID
`tenant_id`	`UUID`	`NOT NULL`	Tenant key
`type_key`	`VARCHAR(64)`	`NOT NULL`	Machine key (e.g., `well`, `facility`)
`vertex_label`	`TEXT`	`NOT NULL`, `CHECK` identifier shape (migration 029)	Stored AGE graph vertex label (e.g., `Well`, `FlaringAuthorization`). Immutable; backfilled as PascalCase of `type_key`. Unique per `(tenant_id, vertex_label)`
`display_name`	`VARCHAR(128)`	`NOT NULL`	UI display name
`icon`	`VARCHAR(64)`		Icon identifier
`color`	`VARCHAR(7)`		Hex color code
`compliance_footprint`	`BOOLEAN`	`NOT NULL`, default `false`	Whether this type appears in compliance matrix
`is_system_type`	`BOOLEAN`	`NOT NULL`, default `false`	System types cannot be deleted
`created_at`	`TIMESTAMPTZ`	`NOT NULL`, default `NOW()`	Creation timestamp
`updated_at`	`TIMESTAMPTZ`	`NOT NULL`, default `NOW()`	Auto-updated via trigger

Unique constraint: (tenant_id, type_key)

Index: idx_etd_tenant on (tenant_id)

Auto-update trigger: trg_etd_updated fires BEFORE UPDATE to set updated_at = NOW().

entity_field_definitions

Field definitions for each entity type (dynamic schema).

Column	Type	Constraints	Description
`field_id`	`UUID`	`PRIMARY KEY`, default `gen_random_uuid()`	Field ID
`type_id`	`UUID`	`NOT NULL`, `REFERENCES entity_type_definitions(type_id) ON DELETE CASCADE`	Parent entity type
`tenant_id`	`UUID`	`NOT NULL`	Tenant key
`field_key`	`VARCHAR(64)`	`NOT NULL`	Machine key (e.g., `api_number`)
`label`	`VARCHAR(128)`	`NOT NULL`	Display label
`input_type`	`VARCHAR(32)`	`NOT NULL`	Input type (text, number, date, select, etc.)
`required`	`BOOLEAN`	`NOT NULL`, default `false`	Whether field is required
`validation_preset`	`VARCHAR(64)`		Preset validation rule
`options`	`JSONB`		Options for select/enum fields
`display_group`	`VARCHAR(64)`		UI grouping
`sort_order`	`INTEGER`	`NOT NULL`, default `0`	Display order
`created_at`	`TIMESTAMPTZ`	`NOT NULL`, default `NOW()`	Creation timestamp

Unique constraint: (type_id, field_key)

Index: idx_efd_type_order on (type_id, sort_order)

entity_relationship_rules

Defines allowed relationships between entity types.

Column	Type	Constraints	Description
`rule_id`	`UUID`	`PRIMARY KEY`, default `gen_random_uuid()`	Rule ID
`tenant_id`	`UUID`	`NOT NULL`	Tenant key
`parent_type_id`	`UUID`	`NOT NULL`, `REFERENCES entity_type_definitions(type_id) ON DELETE CASCADE`	Parent entity type
`child_type_id`	`UUID`	`NOT NULL`, `REFERENCES entity_type_definitions(type_id) ON DELETE CASCADE`	Child entity type
`cardinality`	`VARCHAR(16)`	`NOT NULL`, default `'one_to_many'`	Relationship cardinality
`required`	`BOOLEAN`	`NOT NULL`, default `false`	Whether relationship is required
`display_label`	`VARCHAR(128)`		UI label for the relationship
`created_at`	`TIMESTAMPTZ`	`NOT NULL`, default `NOW()`	Creation timestamp

Unique constraint: (tenant_id, parent_type_id, child_type_id)

Indexes: idx_err_tenant, idx_err_parent, idx_err_child

rrc_field_mappings

Maps RRC data fields to entity field definitions for automated data import.

Column	Type	Constraints	Description
`mapping_id`	`UUID`	`PRIMARY KEY`, default `gen_random_uuid()`	Mapping ID
`tenant_id`	`UUID`	`NOT NULL`	Tenant key
`type_id`	`UUID`	`NOT NULL`, `REFERENCES entity_type_definitions(type_id) ON DELETE CASCADE`	Entity type
`rrc_field_key`	`VARCHAR(128)`	`NOT NULL`	RRC field name
`entity_field_id`	`UUID`	`NOT NULL`, `REFERENCES entity_field_definitions(field_id) ON DELETE CASCADE`	Target entity field
`created_at`	`TIMESTAMPTZ`	`NOT NULL`, default `NOW()`	Creation timestamp

Index: idx_rfm_type on (type_id)

Connecting to the Database


# Via psql
psql -h localhost -U aegis -d aegis
 
# Password: aegis_local (from docker-compose.yml)

Prompt Management Tables (008_prompt_management.sql)

These tables support the agent-config-service’s prompt template management system.

prompt_namespaces

Organizes prompt templates into named groups with approval and access settings.

Column	Type	Description
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	Tenant isolation
`namespace_key`	`VARCHAR(64)`	Unique key within tenant (e.g., `agents`)
`display_name`	`VARCHAR(255)`	Human-readable name
`auto_approve`	`BOOLEAN`	Skip approval on promotion to active
`approvers`	`TEXT[]`	List of approver user IDs
`max_render_timeout`	`INTEGER`	Render timeout in milliseconds
`data_classification`	`VARCHAR(20)`	`public`, `internal`, `confidential`, `restricted`

prompt_budget_tiers

Token budget limits per namespace.

Column	Type	Description
`id`	`UUID`	Primary key
`namespace_id`	`UUID`	FK to prompt_namespaces
`tier_key`	`VARCHAR(64)`	Unique key within namespace
`max_tokens`	`INTEGER`	Maximum token count for prompts in this tier

prompt_templates

Prompt template metadata with a pointer to the active version.

Column	Type	Description
`id`	`UUID`	Primary key
`namespace_id`	`UUID`	FK to prompt_namespaces
`slug`	`VARCHAR(128)`	Unique identifier within namespace
`budget_tier_id`	`UUID`	FK to prompt_budget_tiers (optional)
`expected_variables`	`JSONB`	Variable definitions for the template
`active_version_id`	`UUID`	FK to the currently active prompt_version

prompt_versions

Versioned prompt bodies with lifecycle status and validation results.

Column	Type	Description
`id`	`UUID`	Primary key
`template_id`	`UUID`	FK to prompt_templates
`version_number`	`INTEGER`	Auto-incrementing per template
`status`	`VARCHAR(20)`	`draft`, `pre_production`, `active`, `archived`
`body`	`TEXT`	Jinja2 template content
`draft_owner_id`	`UUID`	Owner (only for drafts; retyped to `uuid` by agent-config migration 020, R42e)
`target_users`	`TEXT[]`	Pre-prod per-user pinning list
`target_roles`	`TEXT[]`	Pre-prod role targeting (migration 021, R42e) — a pre_production version is served when the caller is pinned in `target_users` OR holds a role overlapping `target_roles`
`validation_result`	`JSONB`	Full validation pipeline result
`author_id`	`UUID`	Who created this version (retyped to `uuid` by migration 020, R42e)
`approved_by`	`UUID`	Who approved activation (retyped to `uuid` by migration 020, R42e)

Partial unique indexes enforce: one draft per user per template, one pre-prod per template, one active per template.

prompt_audit_log

Append-only, HMAC-signed audit trail for all prompt lifecycle events. Triggers prevent UPDATE and DELETE.

Column	Type	Description
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	Tenant isolation
`template_id`	`UUID`	FK to prompt_templates
`version_id`	`UUID`	FK to prompt_versions
`action`	`VARCHAR(30)`	Lifecycle event type
`actor_id`	`VARCHAR(255)`	Who performed the action. Stays TEXT permanently (R42e) — append-only, HMAC-verified rows are never rewritten; audit-log list responses resolve the actor’s email at read time (`actor_resolved`, via `identity_map`)
`hmac_signature`	`VARCHAR(128)`	HMAC-SHA256 tamper detection

namespace_access_control

Role-based access control per namespace per user.

Column	Type	Description
`namespace_id`	`UUID`	FK to prompt_namespaces
`user_id`	`UUID`	User identifier (retyped to `uuid` by agent-config migration 020, R42e)
`role`	`VARCHAR(20)`	`viewer`, `author`, `approver`, `admin`
`granted_by`	`VARCHAR(255)`	Who granted this access

Skills-Based Agent Tables (agent-config-service, R35)

These tables back the R35 skills-based agent architecture. As of R35 Phase 3a the skill-loading source reads them — system_prompt_node resolves the agent_definitions row by agent_key (= agent_type) via fetch_agent_definition (a direct Postgres read over the orchestration pool) and loads its root_skill_key skill (rule_37 → rrc_rule37, rule_32 → rrc_rule32, general → NULL = no task skill). AGENT_DEFAULT_SKILLS is demoted to a fallback that fires only for an unseeded tenant (no row → fetch returns None); a seeded row is authoritative, so there is no double-load. As of P3b prompts are assembled from skill personas + context templates (no longer FALLBACK_PROMPTS), and as of P4 the visible tool list derives from core capabilities + the loaded skills’ llm_visible code blocks (AGENT_TOOLS / get_tools_for_agent deleted; the lone R35-FENCE keeps the hardcoded set for the deferred flaring_monitor / compliance_monitor agents).

skill_personas (`008_skill_persona_and_flags.sql`)

A persona is the voice/identity for a skill. Either an inline prompt_text or an R29 prompt-template slug (prompt_template_ref) — exactly one of the two (enforced at the app layer).

Column	Type	Description
`id`	`UUID`	Primary key
`skill_id`	`UUID`	FK to `skill_definitions(id)` (the UUID, `ON DELETE CASCADE`)
`persona_key`	`VARCHAR(128)`	Unique within skill (`UNIQUE (skill_id, persona_key)`)
`display_name`	`VARCHAR(255)`	Human-readable name
`prompt_text`	`TEXT`	Inline persona prompt (voice/identity only)
`prompt_template_ref`	`VARCHAR(255)`	OR an R29 prompt-template slug
`is_default`	`BOOLEAN`	At most one default per skill (partial unique index `WHERE is_default`)
`sort_order`	`INTEGER`	Display ordering

agent_definitions (`009_agent_definitions.sql`)

Platform/agent records. Seeded dormant in P1 (general, rule_37, rule_32).

Column	Type	Description
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	Tenant isolation (`UNIQUE (tenant_id, agent_key)`)
`agent_key`	`VARCHAR(128)`	Agent identifier (e.g. `rule_37`)
`root_skill_key`	`VARCHAR(128)`	SLUG → `skill_definitions.skill_key` (NULL = general mode). No FK — it references the slug, not the UUID PK; the slug→UUID lookup happens once at load (`orchestration/integrity.py:resolve_skill_uuid`). Do not “fix” into a UUID FK.
`persona_key`	`VARCHAR(128)`	NULL = root skill default, else platform default
`model_config`	`JSONB`	Model preference. Seeded rows no longer pin `model_preference` (now `null`), so the tenant Platform Settings `default_model` governs; an explicitly set per-agent value still overrides
`loaded_skills`	`TEXT[]`	Carried to honor the `004_skill_schema.sql` DO-block contract
`is_active` / `is_system`	`BOOLEAN`	Flags

New columns on existing skill tables (`008`)

Table	Column	Type	Description
`skill_code_blocks`	`llm_visible`	`BOOLEAN NOT NULL DEFAULT true`	Lightweight tool-visibility flag (R35 §11.3)
`skill_definitions`	`context_mode`	`VARCHAR(20) NOT NULL DEFAULT 'reason_alongside'`	`reason_alongside` \| `compute_and_return` (R35 §11.4; isolating runtime deferred to the Sub-Agent release)
`skill_definitions`	`response_mode` (migration `019`)	`VARCHAR(20) NOT NULL DEFAULT 'llm_synthesis'`	`llm_synthesis` \| `verbatim` — the verbatim output contract: the platform renders the skill’s code-block tool results to the user as the turn’s primary response (promoted artifact card); the LLM still sees the result in context but is instructed not to restate it. Success envelopes only — errors stay on the normal synthesis path.

A startup integrity check (orchestration-engine) verifies that every active agent_definition’s root_skill_key / persona_key resolves. It is warn-not-fail in R35 P1 (the rrc_rule37 / rrc_rule32 skills land in P2) and flips to fail-loud via the R35_INTEGRITY_FAIL_LOUD env var.

Skill Router Tables (agent-config-service, `010_skill_routers.sql`, R36)

These tables back R36 message-driven skill selection (pull_routers / select_skill). The router layer is a new curated recruitment taxonomy — deliberately not domain_tags (routing-inert display metadata). Multi-homing = multiple skill_router_map rows. Selection payloads are derived reads over these tables — no materialized/compiled router block exists anywhere.

skill_routers

Admin-curated recruitment taxonomy. recruitment_blurb is the only routing-authoritative prose in the system (admin-owned, topic-scoped, never absorbs child skill text). One router level, no recursion. Rows are soft-deactivated (is_active = false), never hard-DELETEd — CRUD enforces this so the MAX(updated_at) manifest-delta watermark cannot miss a change.

Column	Type	Description
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	Tenant isolation (`UNIQUE (tenant_id, router_key)`)
`router_key`	`VARCHAR(128)`	Router identifier (e.g. `spacing`, `flaring`)
`display_name`	`VARCHAR(255)`	Human-readable name
`recruitment_blurb`	`TEXT NOT NULL`	The manifest text the LLM recruits against
`is_active`	`BOOLEAN`	Soft-deactivate flag (no hard delete)
`created_at` / `updated_at`	`TIMESTAMPTZ`	`MAX(updated_at)` is the manifest-delta watermark

skill_router_map

Router → skill mappings with a promotion state machine. Selection reads join only status = 'approved' rows; admin-authored submissions auto-approve. UNIQUE (router_id, skill_id) keeps one row per pair (multi-homing = multiple pairs); a mapping change re-promotes the row to pending_review in place.

Column	Type	Description
`id`	`UUID`	Primary key
`router_id`	`UUID`	FK to `skill_routers(id)` `ON DELETE CASCADE`
`skill_id`	`UUID`	FK to `skill_definitions(id)` `ON DELETE CASCADE`
`status`	`VARCHAR(20)`	`draft` \| `pending_review` \| `approved` \| `rejected`
`submitted_by` / `reviewed_by`	`UUID`	Promotion actors
`review_comment`	`TEXT`	Reviewer feedback

skill_description_versions

Author-submitted disambiguation descriptions, append-only — the level-2 payload text the LLM reads to pick a skill. The payload read takes the latest approved version per skill (ORDER BY created_at DESC); approving a new version supersedes the old by recency (no status flip on prior rows), and a pending revision never blocks the currently-approved one.

Column	Type	Description
`id`	`UUID`	Primary key
`skill_id`	`UUID`	FK to `skill_definitions(id)` `ON DELETE CASCADE`
`description`	`TEXT NOT NULL`	Disambiguation description
`status`	`VARCHAR(20)`	`draft` \| `pending_review` \| `approved` \| `rejected`
`submitted_by` / `reviewed_by`	`UUID`	Promotion actors

New column on `skill_definitions` (`010`)

Table	Column	Type	Description
`skill_definitions`	`version_check_on_turn`	`BOOLEAN NOT NULL DEFAULT false`	`true` = compliance-grade: the loaded-skill version check runs every turn; `false` = check only at skill-touch (router re-pull). Governance economy, not perf.

Platform Settings Tables (agent-config-service, `016_platform_settings.sql` + `017_llm_call_settings.sql`)

Tenant-level LLM runtime config plus per-user daily budget overrides — the admin Platform Settings page (/configuration/platform-settings) replaces box env-var edits. All value columns are nullable: NULL = inherit the next tier down (user override → tenant setting → service env default). Like the other agent-config-service migrations, 016/017 self-apply at boot. 017 adds the per-LLM-call knobs (max_output_tokens_per_call, thinking_mode) — the fix for Claude Sonnet 5’s adaptive-thinking-by-default eating a small max_tokens ceiling.

platform_settings

One row per tenant.

Column	Type	Description
`tenant_id`	`UUID`	Primary key
`default_model`	`TEXT`	Primary LLM model (curated LiteLLM id); `NULL` = env `DEFAULT_LLM_MODEL`
`fallback_model`	`TEXT`	One retry on primary-model errors; `NULL` = no fallback
`max_tokens_per_execution`	`INTEGER`	Per-execution token budget; `NULL` = env default
`max_cost_usd_per_execution`	`NUMERIC(10,4)`	Per-execution cost budget; `NULL` = env default
`max_output_tokens_per_call`	`INTEGER`	Per-LLM-call `max_tokens` ceiling (thinking + visible output); `NULL` = env default (migration `017`)
`thinking_mode`	`TEXT`	`adaptive` \| `disabled`, sent explicitly to Anthropic models; `NULL` = env default (migration `017`)
`max_tokens_per_day`	`BIGINT`	Tenant-aggregate daily token cap; `NULL` = uncapped
`max_cost_usd_per_day`	`NUMERIC(12,4)`	Tenant-aggregate daily cost cap; `NULL` = uncapped
`updated_by`	`TEXT`	Last admin to write
`created_at` / `updated_at`	`TIMESTAMPTZ`	Timestamps

user_budget_overrides

Per-user daily caps under the tenant aggregate.

Column	Type	Description
`tenant_id` / `user_id`	`UUID`	Composite primary key
`max_tokens_per_day`	`BIGINT`	Daily token cap for this user
`max_cost_usd_per_day`	`NUMERIC(12,4)`	Daily cost cap for this user
`updated_by`	`TEXT`	Last admin to write
`created_at` / `updated_at`	`TIMESTAMPTZ`	Timestamps

Index: idx_user_budget_overrides_tenant on (tenant_id)

prompt_audit_log changes

Platform-settings mutations are audit-logged to prompt_audit_log, so 016 widens action to VARCHAR(64) and re-creates prompt_audit_log_action_check with the three new actions appended (platform_settings.update, platform_settings.user_override.upsert, platform_settings.user_override.delete). 016 is now the authoritative owner of that CHECK (the 012/013 single-owner lesson); the append-only triggers are untouched.

Conversation Runtime State Tables (`016_conversation_runtime_state.sql`, R36)

Orchestration-side tables (in infrastructure/docker/postgres/) — the durable home for the R36 sealed prefix + tail-append rules. This is deliberately not a LangGraph checkpointer: messages stay in conversation_messages; graph state stays in-memory per turn. Additive and idempotent; apply manually on existing volumes (numbered migrations are not auto-applied).

conversation_runtime_state

One row per conversation, created at conversation birth. No FK to conversations(id) — that row is upserted at end of turn while this one is born at the start of the first turn.

Column	Type	Description
`conversation_id`	`VARCHAR(100)`	Primary key
`tenant_id`	`UUID NOT NULL`	Tenant isolation
`sealed_prefix`	`TEXT NOT NULL`	The index-0 system block exactly as rendered at birth (persona + identity + router manifest). Trigger-enforced immutable — reused byte-for-byte on reopen
`router_watermark`	`TIMESTAMPTZ`	Manifest-delta watermark (`MAX(skill_routers.updated_at)` observed). `NULL` for agent-mode conversations (no manifest rendered)
`rendered_router_keys`	`TEXT[]`	Router keys the conversation has been shown (frozen manifest + deltas)
`loaded_skills`	`JSONB`	slug → version map of loaded skills (version pinning + reload-on-cold-resume source)

The trg_sealed_prefix_immutable trigger raises on any UPDATE that changes sealed_prefix — one prefix per conversation, for the life of the conversation.

conversation_context_appends

Ordered, timestamped append-only log (UPDATE/DELETE rejected by trigger, matching the audit_logs house pattern). Every post-birth context change is a tail append; together with the sealed prefix this is the R36 audit property.

Column	Type	Description
`id`	`UUID`	Primary key
`conversation_id`	`VARCHAR(100)`	Conversation (`UNIQUE (conversation_id, seq)`)
`seq`	`INT NOT NULL`	Append order
`kind`	`VARCHAR(30)`	`router_delta` \| `router_retirement` \| `tombstone` \| `supersession`
`content`	`TEXT NOT NULL`	The appended notice

Tool-Call Dedup Table (`018_tool_call_dedup.sql`, R39 P2)

Durable idempotency for MUTATING_TOOLS: tool_node claims (conversation_id, tool_call_id) with INSERT ... ON CONFLICT DO NOTHING before dispatching a mutating tool. Losing the claim means the call already ran (the stored result string is returned byte-identically) or is still running (bounded poll, then a structured duplicate_in_flight error — never a second execution). The claim happens downstream of the HITL gate, so a gate pause writes no row and the approved replay claims as the first execution. Flag: TOOL_DEDUP_ENABLED (default on).

tool_call_dedup

Column	Type	Description
`conversation_id`	`TEXT`	PK (with `tool_call_id`)
`tool_call_id`	`TEXT`	The LLM-assigned tool call id
`tool_name`	`TEXT NOT NULL`	The mutating tool executed
`status`	`TEXT`	`in_flight` \| `done` (CHECK-enforced)
`result`	`TEXT`	The tool_output string, stored verbatim for replay
`created_at` / `completed_at`	`TIMESTAMPTZ`	Claim / completion times; 30-day retention sweep at boot

Eval Capture Tables (`019_eval_capture.sql`, R40a)

Expert verdict capture: turn_snapshots freezes the full assembled context of a completed turn (published fire-and-forget to the snapshots:pending Redis stream at turn end; a background writer drains it — the turn path never waits on persistence). eval_cases is the system of record for tester verdicts; Langfuse gets a best-effort score mirror whose failure never fails capture. Snapshots older than 30 days are swept at boot unless referenced by an eval_cases row (promoted snapshots are permanent). Flag: TURN_SNAPSHOTS_ENABLED (default on).

turn_snapshots

Column	Type	Description
`execution_id`	`TEXT`	PK — the turn’s id (also the seed of the deterministic Langfuse trace id)
`conversation_id`	`TEXT`	Conversation the turn belongs to
`tenant_id`	`UUID NOT NULL`	Tenant scope
`persona_key` / `model_id`	`TEXT`	Persona and model used on the turn
`agent_type` / `agent_id`	`TEXT`	Driving agent (added by `020_turn_snapshots_agent.sql`, R40a hotfix) — lets the R40b replay runner select the agent deterministically. Pre-hotfix rows are `NULL`; replay skips agent-less cases
`loaded_skills`	`JSONB`	skill_key → version at turn end
`system_prompt`	`TEXT`	Assembled prompt as sent (sealed prefix + tail)
`messages`	`JSONB NOT NULL`	Full final message list incl. tool-role messages (truncated tail-first with an in-list marker above 1MB)
`tool_calls`	`JSONB`	Per-call `{name, input, output}` convenience view
`final_output`	`TEXT NOT NULL`	Last assistant message
`payload_bytes`	`INTEGER`	Original (pre-truncation) payload size

eval_cases

Column	Type	Description
`id`	`UUID`	Primary key
`tenant_id`	`UUID NOT NULL`	Tenant scope
`conversation_id` / `execution_id`	`TEXT`	Capture anchors; `UNIQUE (execution_id, captured_by)` — a repeat gesture upserts, never duplicates. No hard FK to `turn_snapshots`
`score`	`SMALLINT`	`-1` \| `1` (CHECK-enforced)
`expected_output` / `note` / `category_tag`	`TEXT`	Optional enrichment (sticky across upserts)
`captured_by`	`UUID NOT NULL`	Gateway-injected `X-User-Id`
`snapshot_status`	`TEXT`	`linked` \| `missing` — explicit linkage truth, upgraded by the boot re-link pass
`mirror_status`	`TEXT`	`mirrored` \| `failed` \| `pending` — Langfuse mirror outcome, never silent
`replay_mode`	`TEXT` (generated)	`frozen` when linked, else `live` — consumed by the R40b runner

Note: turn_snapshots also carries agent_type / agent_id (added by 020_turn_snapshots_agent.sql, R40a hotfix) so the R40b runner can select the driving agent for an expert case.

Eval Runs Tables (`021_eval_runs.sql`, R40b)

Baseline-able eval runs. A baseline is a tagged, completed run flagged is_baseline — at most one per (tenant, model), enforced by a partial unique index (uq_eval_runs_one_baseline_per_model … WHERE is_baseline), so a model bump gets its own baseline and the regression diff structurally compares like-for-like. The dashboard reads these tables exclusively (never Langfuse).

eval_runs

Column	Type	Description
`id`	`UUID`	Primary key
`tag`	`TEXT`	Human label; a baseline is a tagged run
`tenant_id`	`UUID NOT NULL`	Tenant scope
`model_id`	`TEXT NOT NULL`	Exact resolved model string this run drove
`git_sha`	`TEXT NOT NULL`	SERVICE build SHA (from `/health`), not the CLI checkout
`skill_revisions`	`JSONB NOT NULL`	`{skill_key: version}` registry snapshot
`judge_threshold`	`NUMERIC`	score ≥ threshold → `pass`
`run_as_user_id` / `run_as_tenant_id`	`UUID NOT NULL`	Identity the replay drove as (§8b; R41 re-scopes)
`status`	`TEXT NOT NULL`	`running` \| `completed` \| `aborted`
`is_baseline`	`BOOLEAN`	Baseline flag; CHECK `NOT is_baseline OR status='completed'`
`n_cases` / `n_pass` / `n_fail` / `n_degraded`	`INTEGER`	Rolled up from `eval_case_results`
`tokens_used` / `cost_usd`	`BIGINT` / `NUMERIC`	Summed per run (from `ExecuteResponse`)
`created_at` / `completed_at`	`TIMESTAMPTZ`

eval_case_results

Column	Type	Description
`id`	`UUID`	Primary key
`eval_run_id`	`UUID NOT NULL`	FK → `eval_runs` (`ON DELETE CASCADE`)
`case_source`	`TEXT`	`pin` \| `expert`
`case_ref`	`TEXT`	Pin id (`P-A1`) or `eval_cases.id`; `UNIQUE (eval_run_id, case_source, case_ref)` — the regression join key
`replay_conv_id` / `replay_exec_id`	`TEXT`	Fresh replay ids (forensics; exec id → trace)
`verdict`	`TEXT`	`pass` \| `fail` \| `degraded`
`grader`	`TEXT`	`pin_check` \| `llm_judge`
`judge_score`	`NUMERIC`	null for `pin_check`
`violations`	`JSONB`	Pin violation list, or judge rationale
`tokens_used` / `cost_usd`	`INTEGER` / `NUMERIC`	Per-case spend

Flaring Authorizations Table (`028_flaring_authorizations.sql`, F1)

SQL system of record for R-32 flaring authorizations (owned by flaring-monitor). The KG FlaringAuthorization vertex is a write-through projection synced by flaring/authorizations.py on every SQL write; the R41 assessors keep reading the vertex through F1–F3. Migration 028 also normalizes flare_events.well_id/facility_id/authorization_id to TEXT (they hold entity slugs, not UUIDs) and backfills flare_events.authorization_id by the attribution rule: the single approved authorization whose effective→expiration window covers the event start and whose scope covers the event’s well — 0 or >1 candidates leave it NULL (un-attributed volume = unauthorized flaring).

`flaring_authorizations`

Column	Type	Notes
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	`NOT NULL`
`entity_id`	`TEXT`	`UNIQUE` — the `fa-…` slug (KG vertex identity)
`authorization_number`	`TEXT`	e.g. `FL-2024-08821`; `UNIQUE (tenant_id, authorization_number)`
`authorization_type`	`TEXT`	`initial` \| `renewal`
`status`	`TEXT`	`draft` \| `filed` \| `approved` \| `expired` \| `denied`
`well_id` / `lease_id`	`TEXT`	Scope — well- or lease-level (slugs)
`operator_id`	`TEXT`	`NOT NULL`
`filed_date` / `effective_date` / `expiration_date`	`DATE`	`expiration_date NOT NULL`
`max_volume_mcf_per_day`	`NUMERIC`	Daily cap
`authorized_volume_mcf`	`NUMERIC`	Total period cap (nullable)
`authorized_days`	`INTEGER`	180-day style cap
`period_start` / `period_end`	`DATE`	Volume-cap window (feeds `calculate_burn_rate`)
`reason` / `infrastructure_timeline`	`TEXT`	Narrative fields mirrored to the vertex

Event Number Sequences (`030_event_number_sequences_reconcile.sql`)

The event-numbering split, reconciled after a long-standing migration collision: migration 004 created event_number_sequences keyed by the platform event_type_enum, 006 created custom_event_number_sequences keyed by an event_type_id UUID, and 010 tried to RENAME the custom table over the platform one — which errors on every ordered application (and failed silently in tolerant psql mode). The two consumers in flaring/event_number.py need mutually exclusive shapes, so the contract from 030 on is two tables:

Table	Keyed by	Purpose
`event_number_sequences`	`(tenant_id, event_type, year)` — the platform `event_type_enum`	Per-year numbering for platform events
`custom_event_number_sequences`	`(tenant_id, event_type_id, year)` — UUID FK → `event_type_definitions(id)`	Per-year numbering for custom events

010’s rename statement is tombstoned; 030 converges every historical shape (004-first survivor, rename-succeeded, zombie re-created 006 tables, missing tables) and is a no-op where flaring’s schema is absent entirely.

Identity Normalization (`031_identity_normalization.sql`, R42e)

R42e makes user identity uuid end-to-end with DB-level foreign keys. Migration 031 (applied manually on existing volumes, safe to re-apply) does three things:

Seeds the system users — see the users table above for the four non-loginable accounts (system@ = nil UUID, legacy@, eval-runner@, demo-seed@).
Retypes VARCHAR/TEXT identity columns to uuid via a convergent UPDATE (castable-and-exists → kept; identity_map hit → mapped; else → legacy@aegis.local) followed by a guarded ALTER TYPE.
Adds FKs → users(id) ON DELETE RESTRICT — NOT VALID first, then VALIDATE — on ~17 core columns: conversations.user_id, conversation_messages.user_id, conversation_attachments.user_id, episodic_memories.user_id, approval_requests.reviewer_id, filing_checklists.created_by, ontology_versions.created_by, projects.owner_user_id, eval_cases.captured_by, eval_runs.run_as_user_id, and the operational-event created_by / reviewed_by columns (operational_events, event_type_definitions, guard/duplication/detection-rule tables).

audit_logs.actor_id and prompt_audit_log.actor_id are deliberately not touched — append-only, hash-chained history is never rewritten; those actors resolve at read time through identity_map. This is the permanent design, not a transition state. FKs reference users(id) only — never roles/user_roles (the boot-order rule). The agent-config-owned tables are normalized by that service’s own self-applying migration 020; users.roles is dropped by auth-service’s boot runner after its backfill.

`identity_map`

Column	Type	Notes
`legacy_id`	`TEXT`	Primary key — a historical identity string (`system`, `system-seed`, `demo-seed`, old tenant-UUID strings, …)
`user_id`	`UUID`	`NOT NULL REFERENCES users(id) ON DELETE RESTRICT`
`source`	`TEXT`	Where the mapping came from (e.g. `seed`)

Used both by the retype backfills above and by read-time resolution of append-only actor columns. auth-service re-converges the seeded rows on every boot.

Connector Tables (connector-service, R44a)

Owned by connector-service’s boot-time migration runner (services/connector-service/src/connector_service/migrations/001_connector_catalog.sql, self-applied on every boot — auth-service pattern, no applied-tracking; guarded by ci/connector_double_boot.sh).

integration_connectors

The external data source catalog. One row per registered connector; secret material never lands here — credential_ref is a secret-store reference only.

Column	Type	Notes
`id`	`UUID`	Primary key
`tenant_id`	`UUID`	`UNIQUE (tenant_id, connector_key)`
`connector_key`	`VARCHAR(128)`	Stable lowercase slug — becomes part of the secret name and later (R44c) tool names
`connector_type`	`TEXT`	`postgres` \| `snowflake` (CHECK)
`config`	`JSONB`	Non-secret, typed per engine (postgres: host/port/database/sslmode; snowflake: account/warehouse/database/schema/role)
`credential_ref`	`TEXT`	Secret-store ref ONLY (`gcp://...` / `dev://...`); never material
`status`	`TEXT`	`unconfigured` \| `probing` \| `active` \| `probe_failed` \| `disabled` (CHECK)
`read_only_verified_at`	`TIMESTAMPTZ`	Set by a passing probe; cleared on credential/destination change
`last_probe`	`JSONB`	`{at, ok, checks, error, remediation}` — no secret material
`data_classification`	`TEXT`	Connector-level classification floor for exposures (R44b), default `internal`
`created_by`	`UUID`	Registering admin

connector_schema_snapshots

Discovery snapshots (is_current flag; superseded rows retained). snapshot is {schemas: [{name, tables: [{name, columns, approx_rows}]}], truncated, truncation_reason}, size-guarded (schema cap 50 / 2 MB byte cap — degrades loudly, never silently).

connector_execution_log

Governance trail for R44b previews and R44c executions, created now so the first execution ever run has its audit row. params_hash is a SHA-256 over canonical params JSON — parameter values are never logged. status is preview | ok | truncated | error | denied; error_class is typed (no message bodies). connector_tag_mappings is reserved by name only.

connector_exposures

First-class, connector-scoped, parameterized SELECT queries (R44b, migration 002). Approve once, bind to many skills (R44c). UNIQUE (connector_id, exposure_key); ON DELETE CASCADE from integration_connectors.

Column	Type	Notes
`id`	`UUID`	Primary key
`connector_id`	`UUID`	FK → `integration_connectors` (CASCADE)
`exposure_key`	`VARCHAR(128)`	Stable slug; the R44c tool name derives from it
`description`	`TEXT`	Becomes the LLM tool description (R44c) — written for the model
`sql_text`	`TEXT`	The parameterized read; validated structurally at every write
`sql_hash`	`TEXT`	sha256 of the raw exact `sql_text` (no canonicalization)
`dialect`	`TEXT`	`postgres` \| `snowflake` — derived from the connector engine
`params`	`JSONB`	`[{name, type, required, default, description}]`
`row_cap` / `byte_cap` / `timeout_s`	`INT`	Execution caps (1–1000 / 1 KB–4 MB / 1–30 s)
`data_classification`	`TEXT`	NULL inherits the connector floor; never resolves below it
`review_status`	`TEXT`	`draft` \| `pending_review` \| `approved` \| `rejected` \| `retired` (CHECK)
`approved_sql_hash`	`TEXT`	The pinned hash — R44c execute verifies `sql_hash == approved_sql_hash`; any content edit reverts to `draft` and clears this
`proposed_by` / `approved_by`	`UUID`	Author / approver

Key Source Files

File	Purpose
`infrastructure/docker/postgres/init.sql`	Core table definitions
`infrastructure/docker/postgres/002_checklist_compliance_tables.sql`	Checklist, compliance, and rule tables
`infrastructure/docker/postgres/007_entity_type_definitions.sql`	Entity type system tables
`infrastructure/docker/postgres/00-create-extension-age.sql`	AGE extension setup
`services/agent-config-service/src/agent_config/migrations/001_initial_schema.sql`	Prompt management tables
`services/agent-config-service/src/agent_config/migrations/008_skill_persona_and_flags.sql`	R35: skill_personas, llm_visible, context_mode
`services/agent-config-service/src/agent_config/migrations/009_agent_definitions.sql`	R35: agent_definitions table
`services/agent-config-service/src/agent_config/migrations/010_skill_routers.sql`	R36: skill_routers, skill_router_map, skill_description_versions, version_check_on_turn
`services/agent-config-service/src/agent_config/migrations/016_platform_settings.sql`	Platform Settings: platform_settings, user_budget_overrides, prompt_audit_log.action widening
`infrastructure/docker/postgres/016_conversation_runtime_state.sql`	R36: conversation_runtime_state (sealed prefix), conversation_context_appends
`infrastructure/docker/postgres/018_tool_call_dedup.sql`	R39 P2: tool_call_dedup (idempotent authoring writes)
`infrastructure/docker/postgres/019_eval_capture.sql`	R40a: turn_snapshots + eval_cases (expert verdict capture)
`infrastructure/docker/postgres/030_event_number_sequences_reconcile.sql`	Event-numbering split: platform vs custom sequence tables, 010 rename tombstoned
`infrastructure/docker/postgres/031_identity_normalization.sql`	R42e: system users, identity_map, uuid retypes + FKs → users(id)
`services/agent-config-service/src/agent_config/migrations/020_identity_normalization.sql`	R42e: uuid retypes on agent-config-owned identity columns
`services/agent-config-service/src/agent_config/migrations/021_prompt_target_roles.sql`	R42e: `prompt_versions.target_roles` (role-based pre-prod targeting)
`services/connector-service/src/connector_service/migrations/001_connector_catalog.sql`	R44a: integration_connectors, connector_schema_snapshots, connector_execution_log
`shared/src/aegis_shared/db/postgres.py`	AsyncPG connection pool helpers

Database Schema

Schema Initialization

Extensions

Graph Setup

Core Tables (init.sql)

audit_logs

users

RBAC tables: roles, role_permissions, user_roles, user_invites (R42a)

episodic_memories

agents

skills

skill_artifacts

approval_requests

conversations

budget_usage

Checklist & Compliance Tables (002_checklist_compliance_tables.sql)

checklist_templates

filing_checklists

checklist_artifacts

compliance_status

rule_versions

filing_rule_snapshots

Entity Type System Tables (007_entity_type_definitions.sql)

entity_type_definitions

entity_field_definitions

entity_relationship_rules

rrc_field_mappings

Connecting to the Database

Prompt Management Tables (008_prompt_management.sql)

prompt_namespaces

prompt_budget_tiers

prompt_templates

prompt_versions

prompt_audit_log

namespace_access_control

Skills-Based Agent Tables (agent-config-service, R35)

skill_personas (008_skill_persona_and_flags.sql)

agent_definitions (009_agent_definitions.sql)

New columns on existing skill tables (008)

Skill Router Tables (agent-config-service, 010_skill_routers.sql, R36)

skill_routers

skill_router_map

skill_description_versions

New column on skill_definitions (010)

Platform Settings Tables (agent-config-service, 016_platform_settings.sql + 017_llm_call_settings.sql)

platform_settings

user_budget_overrides

prompt_audit_log changes

Conversation Runtime State Tables (016_conversation_runtime_state.sql, R36)

conversation_runtime_state

conversation_context_appends

Tool-Call Dedup Table (018_tool_call_dedup.sql, R39 P2)

tool_call_dedup

Eval Capture Tables (019_eval_capture.sql, R40a)

turn_snapshots

eval_cases

Eval Runs Tables (021_eval_runs.sql, R40b)

eval_runs

eval_case_results

Flaring Authorizations Table (028_flaring_authorizations.sql, F1)

flaring_authorizations

Event Number Sequences (030_event_number_sequences_reconcile.sql)

Identity Normalization (031_identity_normalization.sql, R42e)

identity_map

Connector Tables (connector-service, R44a)

integration_connectors

connector_schema_snapshots

connector_execution_log

connector_exposures

Key Source Files

skill_personas (`008_skill_persona_and_flags.sql`)

agent_definitions (`009_agent_definitions.sql`)

New columns on existing skill tables (`008`)

Skill Router Tables (agent-config-service, `010_skill_routers.sql`, R36)

New column on `skill_definitions` (`010`)

Platform Settings Tables (agent-config-service, `016_platform_settings.sql` + `017_llm_call_settings.sql`)

Conversation Runtime State Tables (`016_conversation_runtime_state.sql`, R36)

Tool-Call Dedup Table (`018_tool_call_dedup.sql`, R39 P2)

Eval Capture Tables (`019_eval_capture.sql`, R40a)

Eval Runs Tables (`021_eval_runs.sql`, R40b)

Flaring Authorizations Table (`028_flaring_authorizations.sql`, F1)

`flaring_authorizations`

Event Number Sequences (`030_event_number_sequences_reconcile.sql`)

Identity Normalization (`031_identity_normalization.sql`, R42e)

`identity_map`