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
`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:


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

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
`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
`roles`	`TEXT[]`	`NOT NULL`, default `ARRAY['operator']`	Role strings used for RBAC
`display_name`	`VARCHAR(200)`		Optional display name
`is_active`	`BOOLEAN`	`NOT NULL`, default `TRUE`	Inactive users cannot log in
`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. For existing databases, apply the standalone idempotent migration infrastructure/deploy/migrations/001_users.sql.

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`	`VARCHAR(100)`		User who participated in the conversation
`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`	`VARCHAR(100)`		Assigned reviewer
`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
`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`	`VARCHAR(100)`		Participating user
`status`	`VARCHAR(20)`	default `'active'`	Conversation status
`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`	`VARCHAR(255)`		Who initiated the checklist
`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`)
`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`	`VARCHAR(255)`	Owner (only for drafts)
`target_users`	`TEXT[]`	Pre-prod targeting list
`validation_result`	`JSONB`	Full validation pipeline result
`author_id`	`VARCHAR(255)`	Who created this version
`approved_by`	`VARCHAR(255)`	Who approved activation

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
`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`	`VARCHAR(255)`	User identifier
`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 (mirrors the live `DEFAULT_LLM_MODEL`)
`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)

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.

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
`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

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)

Key Source Files

skill_personas (`008_skill_persona_and_flags.sql`)

agent_definitions (`009_agent_definitions.sql`)

New columns on existing skill tables (`008`)