Skip to content

Database Foundation & PostgreSQL Setup

John Haleyrequested to merge
database-foundation-postgresql-setup into main

Summary

  • Implements comprehensive PostgreSQL database foundation for the Melange MVP Template
  • Creates security-first database patterns with proper schema separation
  • Establishes JWT-based authentication system with Row Level Security

Changes Made

Database Infrastructure

  • Added PostgreSQL 15+ configuration in existing docker-compose.yml
  • Created initialization script directory structure in packages/migrations/init/
  • Implemented proper script execution order with numbered prefixes

Security Foundation

  • Created app_public and app_private schemas for GraphQL separation
  • Set up database roles: anonymous, app_user, app_admin with proper hierarchy
  • Enabled Row Level Security (RLS) policies for multi-tenant data isolation

Authentication System

  • Custom email and phone number validation domains
  • JWT composite type for PostGraphile integration
  • Comprehensive authentication functions:
    • User registration with password hashing
    • User login with JWT token generation
    • Password change and reset functionality
    • Current user helper functions

Example Domain Schema

  • Task management example with teams, projects, tasks, and comments
  • Proper foreign key relationships and constraints
  • Audit triggers for updated_at timestamps
  • Performance indexes on frequently queried columns

Demo Data

  • Sample users, teams, projects, and tasks for development
  • Realistic test data demonstrating all functionality

Testing

  • ✅ PostgreSQL starts successfully with Docker Compose
  • ✅ All initialization scripts execute without errors
  • ✅ Extensions (uuid-ossp, pgcrypto) install correctly
  • ✅ User registration and login functions work properly
  • ✅ Database schemas, tables, and roles created as expected

Implementation Details

Files Added

packages/migrations/
├── init/
│   ├── 00-extensions.sql      # PostgreSQL extensions
│   ├── 01-schemas.sql         # Core schemas setup
│   ├── 02-roles.sql           # Database roles
│   ├── 03-auth-tables.sql     # Authentication tables
│   ├── 04-auth-functions.sql  # JWT auth functions
│   ├── 05-domain-tables.sql   # Example domain tables
│   └── 06-rls-policies.sql    # Row Level Security
└── seeds/
    └── demo-data.sql          # Development test data

Security Features

  • Password hashing with bcrypt via pgcrypto
  • JWT token generation for PostGraphile
  • Row Level Security ensures users only access their data
  • Proper privilege separation between roles

PostGraphile Integration

  • Smart comments for GraphQL schema customization
  • Security Definer functions for safe privilege escalation
  • Custom domains for data validation
  • Proper table and function permissions

Next Steps

This completes the Database Foundation milestone. Ready for:

  1. PostGraphile API Server setup
  2. Apollo Client bindings development
  3. GraphQL-PPX compatibility validation

Closes #37

🤖 Generated with Claude Code

Merge request reports