Skip to content

Database Foundation & PostgreSQL Setup

Objective

Set up PostgreSQL database with Docker, create initial schema structure, and establish database patterns for the MVP template.

Background

Following the patterns from docs/database-patterns.md, we need a solid database foundation with proper security schemas and authentication tables.

Tasks

  • Create docker-compose.yml with PostgreSQL 15+
  • Set up database initialization scripts
  • Create security schemas (app_public, app_private)
  • Implement base authentication tables
  • Add RLS (Row Level Security) policies
  • Create JWT authentication functions
  • Add common utility functions
  • Set up database roles (anonymous, app_user, app_admin)

Database Structure

-- Core schemas
CREATE SCHEMA app_public;  -- GraphQL exposed
CREATE SCHEMA app_private; -- Internal only

-- Authentication tables
app_public.users
app_private.user_secrets
app_public.sessions

-- Example domain tables (task management)
app_public.teams
app_public.projects
app_public.tasks

Docker Configuration

# docker-compose.yml
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: melange_mvp
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    volumes:
      - ./db/init:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"

Acceptance Criteria

  • Database starts with docker-compose up
  • Initial schemas created automatically
  • Authentication tables functional
  • RLS policies enforced
  • JWT generation works
  • Can connect with psql for debugging
  • Seeds script for demo data

Testing

  • Connection test from Node.js
  • Authentication flow test
  • RLS policy verification
  • JWT token generation/validation

Dependencies

  • Docker & Docker Compose
  • PostgreSQL 15+
  • No Melange dependencies (pure SQL)

Priority: 🔴 Critical

Foundation for entire stack.

Estimated Effort: 1-2 days

Files to Create

db/
├── init/
│   ├── 00-schemas.sql
│   ├── 01-roles.sql
│   ├── 02-auth-tables.sql
│   ├── 03-auth-functions.sql
│   ├── 04-domain-tables.sql
│   └── 05-rls-policies.sql
├── seeds/
│   └── demo-data.sql
└── docker-compose.yml

CI Validation

  • Database starts in CI
  • Migrations run successfully
  • Tests can connect