database-modeling

Database Modeling

Design PostgreSQL schemas with proper conventions, Alembic migrations, performant indexes, pgvector for semantic search, and full-text search capabilities.

When to Use

• Creating new database tables or modifying existing schemas
• Writing Alembic migrations
• Adding indexes to optimize slow queries
• Setting up pgvector for embedding storage
• Configuring full-text search
• Reviewing schema design for anti-patterns

Available Operations

1. Design tables following naming conventions
2. Create Alembic migrations (upgrade and downgrade)
3. Add indexes for query optimization
4. Set up pgvector for semantic search
5. Configure full-text search with tsvector
6. Analyze and optimize slow queries with EXPLAIN

Multi-Step Workflow

Step 1: Design the Table Schema

Follow these naming conventions strictly:

• Table names: snake_case, plural (demands, tasks, agents)
• Primary keys: id UUID DEFAULT gen_random_uuid()
• Foreign keys: <singular_table>_id (e.g., demand_id)
• Timestamps: created_at, updated_at with default NOW()
• Soft delete: deleted_at TIMESTAMP NULL

Step 2: Create the Alembic Migration

Generate and write the migration file.

# Generate a new migration
alembic revision --autogenerate -m "create_demands_table"

# Or create manually
alembic revision -m "create_demands_table"

Write the migration:

# alembic/versions/001_create_demands.py
def upgrade():
    op.create_table(
        'demands',
        sa.Column('id', sa.UUID(), primary_key=True, server_default=sa.text('gen_random_uuid()')),
        sa.Column('description', sa.Text(), nullable=False),
        sa.Column('status', sa.VARCHAR(20), nullable=False, server_default='created'),
        sa.Column('requester', sa.VARCHAR(100), nullable=False),
        sa.Column('created_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.TIMESTAMP(timezone=True), server_default=sa.text('NOW()')),
    )

def downgrade():
    op.drop_table('demands')