PostgreSQL Project Template

A PostgreSQL database starter kit with schema migrations, audit logging, user management, and job queuing. Keeps data logic in the database, business policy in application code.

Architecture

Three-Schema Pattern

• project - Data tables
• project_app - Functions and views
• extensions - PostgreSQL extensions

Application code connects with access only to project_app. Direct table access is prevented. Functions in project_app perform validated operations on project tables.

Included Systems

Audit Logging - Triggers track all INSERT/UPDATE/DELETE operations. Captures user_id and ip_address from session variables, stores old/new values as JSONB with changed field tracking.

User Management - Registration, email activation, password reset, session management. Account security with failed attempt tracking and time-based locking. Functions provide atomic operations; application orchestrates workflow.

Job Queue - Database-backed queue for async operations. Critical workflows (activation emails) use transactional guarantees. Supports priorities, retries, scheduled execution, and worker claiming with row-level locking.

Quick Start

Prerequisites

• Docker and Docker Compose
• Make
• PostgreSQL client tools

Setup

# Start PostgreSQL
docker compose up -d postgres
# Deploy migrations
make db/deploy
# Check status
make db/status

Connection: postgres://project_user:1234@localhost:5432/project_db

Development

# Add migration
make db/add name=NNN_feature_name
# Edit deploy/revert/verify scripts
# database/migrations/deploy/NNN_feature_name.sql
# database/migrations/revert/NNN_feature_name.sql
# database/migrations/verify/NNN_feature_name.sql
# Deploy
make db/deploy
# Rollback
make db/revert

Design Approach

The template separates concerns between database and application:

Database functions handle atomic data operations, validation, and audit context. They return data; application decides what to do with it.

Application code makes policy decisions (attempt limits, timeout durations), orchestrates multi-step workflows, formats responses, and integrates external systems.

Critical workflows that must not be forgotten (user creation + activation email) queue jobs transactionally. Both operations succeed or both fail.

Authentication Pattern

Functions provide building blocks. Application composes them:

# Application sets policy
MAX_ATTEMPTS = 5
LOCKOUT_MINUTES = 15
user = db.user_authenticate(email, password_hash)
if not user.password_match:
 attempts = db.user_record_failed_login(user.user_id, ip)
 if attempts >= MAX_ATTEMPTS:
 db.user_lock_account(user.user_id,
 now() + timedelta(minutes=LOCKOUT_MINUTES))
 return error("Invalid credentials")
session = db.user_create_session(user.user_id,
 now() + timedelta(days=7),
 ip, user_agent)
return success(session.token)

Changing policy requires no migrations. Functions stay focused on data.

Function Categories

• User CRUD - create, get, list, update, delete (soft delete with deleted_at)
• Authentication - authenticate, create_session, verify_token, logout
• Security - record_failed_login, lock_account, unlock_account, reset_password, change_password
• Job Queue - create, claim, complete, fail, retry, cancel, cleanup, stats, find_stuck

See database/functions/*.sql for full implementations.

Common Patterns

Audit Context

Set before operations to capture who made changes:

PERFORMproject_app.set_audit_context(user_id,ip_address);-- Subsequent operations logged with this context

Soft Deletes

Tables include deleted_at TIMESTAMP. Filter with WHERE deleted_at IS NULL.

Token Expiration

Store token and expires_at. Validate with WHERE expires_at > CURRENT_TIMESTAMP.

Identifiers

User-facing: UUID. Internal relationships: BIGINT.

Project Structure

database/
 functions/ # Application layer functions
 views/ # Application layer views
 migrations/ # Sqitch migrations
 deploy/ # Forward migrations
 revert/ # Rollback scripts
 verify/ # Verification tests
 sqitch.plan # Migration order
 deploy_app.sql # Deploy functions/views
container/
 .env # Database configuration
 postgres/
 postgres-init.sh # Initialization script
compose.yml # Docker services
Makefile # Database commands

Adding Features

New Table

1. make db/add name=table_name
2. Create table in project schema with constraints
3. Add triggers: update_updated_at_column(), audit_trigger_func()
4. Write revert and verify scripts
5. make db/deploy

New Functions

1. Create database/functions/entity.sql
2. Follow conventions: p_ prefix for parameters, v_ for variables
3. Use TABLE return types for multi-column results
4. Call set_audit_context() when modifying data
5. Add to database/deploy_app.sql
6. Apply: psql $DATABASE_URL -f database/deploy_app.sql

License

Artistic License 2.0