PostgreSQL Database Schema (ORM Models)

ELI5

The PostgreSQL schema is like the blueprint for a permanent library building. While the current system uses temporary folding tables (in-memory registries), the blueprint shows exactly how the real library will be organised: where the books go, how they’re indexed, and how to find them quickly. The building is ready — it just hasn’t been moved into yet.

Technical Deep Dive

Database Tables

erDiagram
    SPRITES {
        uuid id PK
        string name UK
        string version
        string role
        json capabilities
        text system_prompt
        boolean protected
        boolean gate_authority
        json sprite_metadata
        string fingerprint_algorithm
        string fingerprint_hash
        timestamptz created
    }
    COUNCILS {
        uuid id PK
        string domain UK
        json sprites
        json gate_agents
        json rules
        json council_metadata
        timestamptz created_at
    }
    CHAINS {
        uuid id PK
        uuid council_id FK
        string name
        json steps
        json gates
        string timeout
        timestamptz created_at
    }
    EXECUTION_HISTORY {
        uuid id PK
        uuid council_id
        uuid chain_id
        string status
        timestamptz started_at
        timestamptz completed_at
        integer duration_ms
        json steps
        json gates
        json input_data
        json output_data
        text error_message
        timestamptz created_at
    }
    COUNCILS ||--o{ CHAINS : "has many"

Table: sprites

Table: councils

Table: chains

Table: execution_history

Indexes

-- sprites
CREATE INDEX idx_sprites_name ON sprites(name);
CREATE INDEX idx_sprites_fingerprint ON sprites(fingerprint_hash);

-- councils
CREATE INDEX idx_councils_domain ON councils(domain);

-- chains
CREATE INDEX idx_chains_council_id ON chains(council_id);

-- execution_history
CREATE INDEX idx_exec_history_council_id ON execution_history(council_id);
CREATE INDEX idx_exec_history_chain_id ON execution_history(chain_id);
CREATE INDEX idx_exec_history_status ON execution_history(status);

SQLAlchemy ORM Models

from sqlalchemy import Column, String, Text, Boolean, JSON, Integer, DateTime, func
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class SpriteORM(Base):
    __tablename__ = "sprites"
    id = Column(UUID(as_uuid=True), primary_key=True)
    name = Column(String, unique=True, index=True, nullable=False)
    version = Column(String, nullable=False)
    role = Column(String, nullable=False)
    capabilities = Column(JSON, nullable=False)
    system_prompt = Column(Text, nullable=False)
    protected = Column(Boolean, default=False)
    gate_authority = Column(Boolean, default=False)
    sprite_metadata = Column(JSON, nullable=False)
    fingerprint_algorithm = Column(String, nullable=False)
    fingerprint_hash = Column(String, index=True, nullable=False)
    created = Column(DateTime(timezone=True), server_default=func.now())

Async Database Setup

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://iris:iris@localhost:5432/iris_db",
    pool_size=20,
    pool_recycle=3600,
    pool_pre_ping=True
)
session_factory = async_sessionmaker(
    engine,
    expire_on_commit=False,
    autoflush=False
)

async def get_db():
    async with session_factory() as session:
        yield session

Note: While the async infrastructure is fully configured, init_db() and close_db() are currently no-ops. The in-memory registries are used for all CRUD operations.

Migration (Alembic)

# Initialize migrations
alembic init db

# Generate a migration
alembic revision --autogenerate -m "Add execution_history table"

# Apply migrations
alembic upgrade head

Key Terms

• SQLAlchemy ORM → Object-Relational Mapper mapping Python classes to PostgreSQL tables
• asyncpg → Async PostgreSQL driver for Python; used with create_async_engine(...)
• JSON column → PostgreSQL native JSON storage for complex nested structures (capabilities, steps, gates)
• TIMESTAMPTZ → Timestamp with timezone; prevents ambiguity across time zones
• Server default → Database-computed default value (func.now() for timestamps)
• Pool pre-ping → Validates connections before use to handle database restarts gracefully
• Alembic → Database migration tool for SQLAlchemy; manages schema versioning

Q&A

Q: Why JSON columns instead of normalized tables? A: IRIS data is deeply nested and schema-flexible. JSON columns allow storing complex structures (capabilities, steps, gates) without dozens of join tables. PostgreSQL’s JSONB indexing supports efficient queries on nested fields.

Q: Can I query sprites by capability name? A: Yes, using PostgreSQL JSON operators: SELECT * FROM sprites WHERE capabilities @> '[{"name": "generate_code"}]'.

Q: What happens when the database connection drops? A: pool_pre_ping=True validates connections before use. If a connection is dead, SQLAlchemy transparently creates a new one.

Q: How do I run migrations in the Docker stack? A: Add an Alembic migration service to docker-compose.yaml that runs alembic upgrade head before starting iris-service.

Q: Will switching to PostgreSQL break the API? A: No. The Pydantic models and API responses remain identical. Only the internal storage layer changes. The registries will be replaced with database queries.

Examples

The PostgreSQL schema is like a permanent city archive:

• sprites table = Resident records (name, ID, skills, fingerprint)
• councils table = Neighbourhood associations (domain, member list, rules)
• chains table = Event permits (steps to set up a street fair, associated with a neighbourhood)
• execution_history = Event logs (what happened, when, who was involved, how long it took)
• JSON columns = Filing folders with variable contents (one resident might have 3 skills, another might have 15 — no need for a separate “skills” building)
• Indexes = The card catalog that lets you find residents by name or fingerprint without reading every file
• Async engine = A modern archive where multiple researchers can request documents simultaneously without blocking each other