# Database Optimization

> Schema design, index strategy, migration safety, and query analysis. TRIGGER when: designing tables or indexes, writing a migration, or diagnosing a slow query. SKIP: writing ORM model code (use python-patterns); generic backend patterns (use python-patterns).

- **Type:** Skill
- **Install:** `agentstack add skill-komluk-scaffolding-database-optimization`
- **Verified:** Pending review
- **Seller:** [komluk](https://agentstack.voostack.com/s/komluk)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [komluk](https://github.com/komluk)
- **Source:** https://github.com/komluk/scaffolding/tree/main/skills/database-optimization

## Install

```sh
agentstack add skill-komluk-scaffolding-database-optimization
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

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

```python
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):
```python
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 project_id, created_at, 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, created_at |
| `task_refs` | `TaskRef` | id, project_id (FK), conversation_id, session_id, created_by (FK) |
| `users` | `User` | id (uuid4), github_id (unique), github_login, avatar_url |
| `user_projects` | `UserProject` | user_id (FK), project_id (FK), role, UniqueConstraint |

### Relationship Patterns
```python
# 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](https://github.com/komluk)
- **Source:** [komluk/scaffolding](https://github.com/komluk/scaffolding)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: flagged — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-komluk-scaffolding-database-optimization
- Seller: https://agentstack.voostack.com/s/komluk
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.