Database Optimization

Schema Design Principles

| Form | Use When | |------|----------| | 1NF | Always (atomic values) | | 2NF | Most tables | | 3NF | Transactional data | | Denormalized | Read-heavy, reporting |

Index Strategy

| Type | Use Case | |------|----------| | B-Tree | Default, range queries | | Hash | Exact match only | | GIN (Postgres) | Full-text, JSONB, arrays | | Partial | Subset of rows | | Composite | Multi-column queries |

> Index type names vary by engine (e.g. GIN/GiST/BRIN are Postgres-specific; > MySQL/SQLite expose a different set). Treat engine-specific rows as examples.

When to Index

• Primary keys (automatic)
• Foreign keys
• WHERE clause columns
• ORDER BY columns
• JOIN columns

When NOT to Index

• Low cardinality columns
• Frequently updated columns
• Small tables ( Illustrative — this is one team's SQLAlchemy/Postgres setup shown as a concrete

> example. Substitute your ORM, driver, and schema conventions. The schema-design, > indexing, and query-analysis guidance above is the engine-agnostic, reusable part.

Async SQLAlchemy Setup (.py)

from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

engine = create_async_engine(
    DATABASE_URL,           # postgresql+asyncpg://...
    echo=False, future=True,
    pool_size=5, max_overflow=10,
    pool_recycle=3600,      # Recycle connections after 1 hour
    pool_pre_ping=True,     # Detect stale connections
)
async_session_maker = async_sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)

Session dependency (commit-on-success, rollback-on-error):

async def get_db() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise

Model Conventions

All models inherit from the shared declarative Base and follow these patterns:

| Convention | Pattern | Example | |-----------|---------|---------| | Primary key | String(36), UUID as string | id: Mapped[str] = mapped_column(String(36), primary_key=True) | | Timestamps | DateTime(timezone=True) + utc_now | created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=utc_now) | | Foreign keys | Explicit ondelete policy | ForeignKey("projects.id", ondelete="CASCADE") | | Nullable FK | ondelete="SET NULL" | ForeignKey("users.id", ondelete="SET NULL") | | Indexes | On FKs and query columns | index=True on projectid, createdat, github_id | | Type hints | Mapped[] with mapped_column | SQLAlchemy 2.0 declarative style | | Relationships | TYPE_CHECKING guard for imports | Avoids circular imports between modules |

Models Overview

| Table | Model | Key Fields | |-------|-------|-----------| | projects | Project | id, path (unique), name, createdat | | task_refs | TaskRef | id, projectid (FK), conversationid, sessionid, createdby (FK) | | users | User | id (uuid4), githubid (unique), githublogin, avatarurl | | user_projects | UserProject | userid (FK), projectid (FK), role, UniqueConstraint |

Relationship Patterns

# Parent side - cascade delete orphans
tasks: Mapped[list["TaskRef"]] = relationship(
    "TaskRef", back_populates="project", cascade="all, delete-orphan"
)
# Child side
project: Mapped["Project"] = relationship("Project", back_populates="tasks")

Alembic Migration Conventions

• Sync driver: Alembic uses psycopg2 (strips +asyncpg from URL)
• Advisory locks: pg_advisory_lock(1573678) prevents concurrent migrations
• Schema validation: main.py validates ORM vs DB schema on startup, logs warnings
• Migration file naming: {hash}_{description}.py with upgrade() and downgrade()
• All models imported in env.py: Required for autogenerate support
• Safe pattern: Always include both upgrade() and downgrade() functions

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: komluk
• Source: komluk/scaffolding
• License: MIT

Install and usage instructions live in the source repository linked above.