Ollieb89 Viflo Database Design

Database Design

PostgreSQL + SQLAlchemy 2.0 database design patterns

Quick Start

Generate Schema

python .agent/skills/database-design/scripts/generate-schema.py User \
    --fields "email:str,name:str,active:bool"

Generates:

• SQLAlchemy 2.0 model
• Alembic migration
• Pydantic schemas
• Repository class

Check Migration Safety

python .agent/skills/database-design/scripts/migration-helper.py safety \
    alembic/versions/xxx.py

Start PostgreSQL

cd .agent/skills/database-design/assets/templates/postgres-setup
docker-compose up -d

Schema Design Checklist

Before creating tables:

• Chosen appropriate data types
• Defined primary keys (UUID vs sequential)
• Planned foreign key relationships
• Considered index strategy
• Added created_at/updated_at timestamps
• Planned for soft deletes if needed

Code Generation

Schema Generator

# Basic usage
python generate-schema.py Product --fields "name:str,price:float"

# With all field types
python generate-schema.py Order \
    --fields "customer_id:int,total:float,status:str,metadata:jsonb"

Supported types: str, int, float, bool, datetime, text, uuid, jsonb

Migration Helper

# Check all migrations
python migration-helper.py check

# Check specific migration
python migration-helper.py check alembic/versions/xxx.py

# Show migration status
python migration-helper.py status

# Check production safety
python migration-helper.py safety alembic/versions/xxx.py

PostgreSQL Patterns

Data Types

Essential Indexes

# Foreign keys (always index)
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"), index=True)

# Unique constraints
email: Mapped[str] = mapped_column(unique=True)

# Composite for common queries
__table_args__ = (
    Index('ix_orders_user_date', 'user_id', 'created_at'),
)

Migration Best Practices

Safe Migrations

1. Add column: nullable=True first, backfill, then nullable=False
2. Add index: Use postgresql_concurrently=True for large tables
3. Drop column: Check no queries use it first
4. Rename: Add new column, migrate data, drop old column

Migration Safety Check

Always run before production:

python migration-helper.py safety alembic/versions/xxx.py

Flags unsafe operations:

• DROP TABLE / COLUMN
• ALTER COLUMN TYPE (rewrites table)
• ADD COLUMN NOT NULL without default

Performance

Query Optimization

# Use selectinload for relationships
from sqlalchemy.orm import selectinload

stmt = select(User).options(selectinload(User.orders))

Index Strategy

• B-Tree: Default, good for equality and range
• GIN: JSONB, arrays, full-text search
• Partial: Index subset of data (e.g., active users)
• Expression: Lowercase emails, computed values

References

Templates

Anti-Patterns

❌ Default to PostgreSQL for simple apps (SQLite may suffice)
❌ Skip indexing on foreign keys and query columns
❌ Use SELECT * - specify columns explicitly
❌ Store JSON when structured data is better
❌ Ignore N+1 queries - use eager loading
❌ Big migrations without testing on production-like data

Decision Framework

Choosing Database

When to Index

• Foreign keys: Always
• Frequently queried columns: Yes
• Low cardinality (< 100 distinct): No
• Small tables (< 1000 rows): No

Converted and distributed by TomeVault — claim your Tome and manage your conversions.