PostgreSQL Database Setup and Schema Design

[starbops]

PostgreSQL Database Setup and Schema Design

User Story

As a developer, I want a robust PostgreSQL database with optimized schema so that we can efficiently store and query task execution data.

Technical Requirements

• Design database schema for tasks, users, executions
• Implement database migrations with golang-migrate
• Set up pgx connection pooling with optimal settings
• Create repository pattern with prepared statements
• Add database indexes for common query patterns
• Implement JSONB support for flexible task metadata

Acceptance Criteria

• All tables created with proper constraints and indexes
• Connection pooling configured with optimal settings
• Migrations run successfully up and down
• Repository pattern implemented with proper error handling
• Database queries under 50ms for typical operations
• JSONB fields support complex metadata queries

Definition of Done

• Database schema deployed and tested
• All repository methods have unit tests
• Connection pooling configured and verified
• Database performance benchmarks documented
• Migration scripts tested in both directions

Database Schema

Users Table

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

Tasks Table

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    script_content TEXT NOT NULL,
    script_type VARCHAR(50) NOT NULL DEFAULT 'python',
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    priority INTEGER NOT NULL DEFAULT 0,
    timeout_seconds INTEGER NOT NULL DEFAULT 10,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_tasks_user_status ON tasks(user_id, status);
CREATE INDEX idx_tasks_created_at ON tasks(created_at);
CREATE INDEX idx_tasks_priority_status ON tasks(priority DESC, status);
CREATE INDEX idx_tasks_metadata_gin ON tasks USING GIN(metadata);

Task Executions Table

CREATE TABLE task_executions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    return_code INTEGER,
    stdout TEXT,
    stderr TEXT,
    execution_time_ms INTEGER,
    memory_usage_bytes BIGINT,
    started_at TIMESTAMP WITH TIME ZONE,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_executions_task_created ON task_executions(task_id, created_at DESC);
CREATE INDEX idx_executions_status_created ON task_executions(status, created_at DESC);

Implementation Commands

# Install migration tool
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration
migrate create -ext sql -dir migrations -seq initial_schema

# Run migrations
migrate -path migrations -database "postgres://user:pass@localhost/voidrunner?sslmode=disable" up

# Install pgx driver
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool

Related Epic

Contributes to Epic #1: Core API Infrastructure