Lokstra Schema Design

Lokstra Schema Design

Purpose: Generate production-ready PostgreSQL database schemas for Lokstra modules with multi-tenant architecture, enforcing data integrity, security, and performance best practices.

Table of Contents

1. When to Use This Skill
2. Prerequisites
3. Step-by-Step Process
4. Multi-Tenant Requirements
5. Table Design Standards
6. Indexing Strategy
7. Security & Access Control
8. Migration Workflow
9. Quality Checklist
10. Examples
11. Reference Files

When to Use This Skill

Trigger Conditions

Use this skill when:

• ✅ Module requirements are approved - Business logic is finalized
• ✅ API specifications are complete - Endpoints and data models defined
• ✅ Ready to design data layer - Need PostgreSQL table definitions
• ✅ Creating migrations - Need UP/DOWN SQL files
• ✅ Defining relationships - Need foreign keys and constraints

Prerequisites

Before starting schema design, ensure you have:

1. Approved Module Requirements (docs/modules/{module-name}/REQUIREMENTS.md)

   • Functional requirements with acceptance criteria
   • Use cases and business rules
   • Data retention policies

2. Approved API Specifications (docs/modules/{module-name}/API_SPEC.md)

   • Request/response DTOs
   • Validation rules
   • Endpoint definitions

3. Multi-Tenant Architecture Decision

   • Shared schema (recommended for 100-10K tenants)
   • Separate schemas (for 10-1K tenants)
   • Separate databases (for 1-100 large tenants)

4. Database Environment

   • PostgreSQL 14+ installed
   • Migration tool configured (golang-migrate, Lokstra migration runner)

Step-by-Step Process

Step 1: Analyze Requirements (30 minutes)

Goal: Extract data entities and relationships from requirements

Actions:

1. Read module requirements document
2. Identify core entities (nouns in use cases)
3. Map entity relationships (one-to-many, many-to-many)
4. List required queries and access patterns
5. Identify data volume estimates

Output: Entity-Relationship diagram (text or tool)

Example:

Module: Patient Management
Entities:
- Patients (10K-100K records per tenant)
- Appointments (100K-1M records per tenant)
- Medical Records (500K-5M records per tenant)

Relationships:
- Patient 1:N Appointments
- Patient 1:N Medical Records
- Appointment N:1 Doctor (from User module)

Step 2: Design Table Structures (60 minutes)

Goal: Define tables with all columns, types, and constraints

Actions:

1. Create table for each entity
2. Add tenant_id TEXT NOT NULL to every table (multi-tenant)
3. Choose ID strategy: ULID (recommended) or UUID v7
4. Define business columns with appropriate types
5. Add audit columns: created_at, updated_at, deleted_at
6. Add CHECK constraints for enum-like fields
7. Add unique constraints (scoped to tenant)

Output: SQL CREATE TABLE statements

Template:

-- Table: {module}.{entity_plural}
-- Purpose: {Brief description}
-- Estimated rows: {volume estimate}
-- Multi-tenant: Yes (tenant_id required on all queries)

CREATE TABLE {module}.{entity_plural} (
    -- Primary Key
    id TEXT PRIMARY KEY,  -- ULID: Sortable, URL-safe, 26 chars
    
    -- Multi-Tenant (MANDATORY)
    tenant_id TEXT NOT NULL,
    
    -- Business Columns
    {column_name} {TYPE} {NOT NULL} {DEFAULT},
    
    -- Foreign Keys (composite with tenant_id)
    {reference_id} TEXT NOT NULL,
    
    -- Audit Columns
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,  -- Soft delete (NULL = active)
    
    -- Check Constraints
    CONSTRAINT chk_{table}_{column} CHECK ({column} IN ('value1', 'value2')),
    
    -- Unique Constraints (scoped to tenant)
    CONSTRAINT uq_{table}_tenant_{column} UNIQUE(tenant_id, {column})
);

See: AUTH_SCHEMA_EXAMPLE.md for complete 10-table example

Step 3: Define Indexes (30 minutes)

Goal: Optimize query performance for common access patterns

Actions:

1. Add index on tenant_id for every table
2. Create composite indexes with tenant_id as first column
3. Add partial indexes to exclude soft-deleted records
4. Create GIN indexes for JSONB columns
5. Add covering indexes for hot queries

Index Priority:

• Critical: tenant_id (every table)
• High: Foreign keys, status columns, date ranges
• Medium: Full-text search, JSONB paths
• Low: Rarely queried columns

Template:

-- Indexes for {table_name}

-- 1. Tenant isolation (partial index excludes deleted)
CREATE INDEX idx_{table}_tenant 
    ON {module}.{table}(tenant_id) 
    WHERE deleted_at IS NULL;

-- 2. Composite index for common query (tenant_id FIRST)
CREATE INDEX idx_{table}_tenant_{column} 
    ON {module}.{table}(tenant_id, {column} DESC) 
    WHERE deleted_at IS NULL;

-- 3. Foreign key lookup
CREATE INDEX idx_{table}_tenant_{fk} 
    ON {module}.{table}(tenant_id, {fk_column});

-- 4. GIN index for JSONB
CREATE INDEX idx_{table}_metadata_gin 
    ON {module}.{table} USING GIN(metadata_jsonb);

-- 5. Covering index (avoid table lookup)
CREATE INDEX idx_{table}_tenant_{col}_covering 
    ON {module}.{table}(tenant_id, {column}) 
    INCLUDE (id, status, created_at);

See: MULTI_TENANT_SCHEMA_PATTERNS.md - Section on Indexing Strategies

Step 4: Add Security (30 minutes)

Goal: Enforce tenant isolation at database level

Actions:

1. Enable Row-Level Security (RLS) on all tenant tables
2. Create RLS policies for SELECT, INSERT, UPDATE, DELETE
3. Define composite foreign keys with tenant_id
4. Add comments for sensitive columns (PII, passwords)

Template:

-- Row-Level Security for {table_name}

-- 1. Enable RLS
ALTER TABLE {module}.{table} ENABLE ROW LEVEL SECURITY;

-- 2. SELECT policy (filter by tenant)
CREATE POLICY tenant_isolation_select ON {module}.{table}
    FOR SELECT
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 3. INSERT policy (enforce tenant)
CREATE POLICY tenant_isolation_insert ON {module}.{table}
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 4. UPDATE policy (own tenant only)
CREATE POLICY tenant_isolation_update ON {module}.{table}
    FOR UPDATE
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 5. DELETE policy (own tenant only)
CREATE POLICY tenant_isolation_delete ON {module}.{table}
    FOR DELETE
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- Composite Foreign Key (prevents cross-tenant references)
ALTER TABLE {module}.{child_table}
ADD CONSTRAINT fk_{child}_tenant_{parent} 
    FOREIGN KEY (tenant_id, {parent_id}) 
    REFERENCES {module}.{parent_table}(tenant_id, id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

See: MULTI_TENANT_SCHEMA_PATTERNS.md - Section on Row-Level Security

Step 5: Add Triggers & Functions (15 minutes)

Goal: Automate repetitive tasks (timestamps, audit logs)

Actions:

1. Create update_updated_at_column() function (once per database)
2. Attach trigger to each table for auto-updating updated_at
3. Create audit trigger for sensitive tables (optional)

Template:

-- Function: Auto-update updated_at column (create once)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger: Attach to each table
CREATE TRIGGER trg_{table}_updated_at
    BEFORE UPDATE ON {module}.{table}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Optional: Audit trigger for sensitive tables
CREATE OR REPLACE FUNCTION audit_{table}_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO {module}.audit_logs (
            tenant_id, table_name, record_id, action, 
            old_values, new_values, changed_by
        ) VALUES (
            OLD.tenant_id, TG_TABLE_NAME, OLD.id, 'UPDATE',
            row_to_json(OLD), row_to_json(NEW),
            current_setting('app.user_id', true)::TEXT
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_{table}_audit
    AFTER UPDATE ON {module}.{table}
    FOR EACH ROW
    EXECUTE FUNCTION audit_{table}_changes();

See: AUTH_SCHEMA_EXAMPLE.md - Section on Triggers

Step 6: Create Migration Files (30 minutes)

Goal: Version-controlled schema changes with rollback capability

Actions:

1. Create migrations/{module}/ directory
2. Generate sequential UP migration files
3. Generate corresponding DOWN migration files
4. Include all tables, indexes, RLS policies, triggers
5. Test UP/DOWN on clean database

Naming Convention:

migrations/{module}/
├── 001_create_{table1}.up.sql
├── 001_create_{table1}.down.sql
├── 002_create_{table2}.up.sql
├── 002_create_{table2}.down.sql
└── ...

UP Migration Template:

-- Migration: Create {table} table
-- Module: {module-name}
-- Version: 1.0.0
-- Date: 2026-01-20
-- Author: {your-name}

BEGIN;

-- Create table
CREATE TABLE IF NOT EXISTS {module}.{table} (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    -- ... columns
);

-- Create indexes
CREATE INDEX idx_{table}_tenant ON {module}.{table}(tenant_id);
-- ... more indexes

-- Enable RLS
ALTER TABLE {module}.{table} ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY tenant_isolation ON {module}.{table}
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- Attach triggers
CREATE TRIGGER trg_{table}_updated_at
    BEFORE UPDATE ON {module}.{table}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Add comments
COMMENT ON TABLE {module}.{table} IS '{Description}';
COMMENT ON COLUMN {module}.{table}.tenant_id IS 'Multi-tenant isolation';

COMMIT;

DOWN Migration Template:

-- Migration: Rollback {table} table creation
-- Module: {module-name}

BEGIN;

-- Drop triggers
DROP TRIGGER IF EXISTS trg_{table}_updated_at ON {module}.{table};

-- Drop RLS policies
DROP POLICY IF EXISTS tenant_isolation ON {module}.{table};

-- Drop table (CASCADE removes dependent objects)
DROP TABLE IF EXISTS {module}.{table} CASCADE;

COMMIT;

See: MIGRATION_PATTERNS.md for comprehensive migration guide

Multi-Tenant Requirements

Mandatory Elements

Every schema MUST include:

1. tenant_id Column (CRITICAL)

-- ✅ CORRECT: All business tables
CREATE TABLE patients (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,  -- ← MANDATORY
    full_name VARCHAR(255) NOT NULL
);

-- ✅ Exception: Global lookup tables
CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
COMMENT ON TABLE countries IS 'Global lookup table (no tenant_id)';

-- ❌ WRONG: Missing tenant_id
CREATE TABLE patients (
    id TEXT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL  -- ← Missing tenant_id
);

2. Composite Foreign Keys (CRITICAL)

-- ✅ CORRECT: FK includes tenant_id
ALTER TABLE appointments
ADD CONSTRAINT fk_appointments_patient 
    FOREIGN KEY (tenant_id, patient_id) 
    REFERENCES patients(tenant_id, id);

-- ❌ WRONG: FK without tenant_id
ALTER TABLE appointments
ADD CONSTRAINT fk_appointments_patient 
    FOREIGN KEY (patient_id)  -- ← Missing tenant_id
    REFERENCES patients(id);

3. Scoped Unique Constraints (CRITICAL)

-- ✅ CORRECT: Unique per tenant
CREATE UNIQUE INDEX uq_patients_tenant_email 
    ON patients(tenant_id, email) 
    WHERE deleted_at IS NULL;

-- ❌ WRONG: Unique globally
CREATE UNIQUE INDEX uq_patients_email 
    ON patients(email);  -- ← Prevents email reuse across tenants

4. Row-Level Security (CRITICAL)

-- ✅ CORRECT: RLS enforces tenant isolation
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON patients
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- ❌ WRONG: No RLS (relies on app filtering only)
-- Missing: ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

See: MULTI_TENANT_SCHEMA_PATTERNS.md for complete patterns

Table Design Standards

Data Type Guidelines

Column Naming

• Use snake_case (lowercase with underscores)
• Avoid abbreviations: full_name not fname
• Boolean prefix: is_active, has_profile
• Timestamp suffix: created_at, deleted_at
• Foreign key suffix: user_id, patient_id

Required Columns

Every table MUST have:

CREATE TABLE {table} (
    -- Primary Key
    id TEXT PRIMARY KEY,
    
    -- Multi-Tenant
    tenant_id TEXT NOT NULL,
    
    -- Business columns...
    
    -- Audit Columns (REQUIRED)
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ  -- Soft delete
);

Indexing Strategy

Index Types

Index Priorities

Always Create:

1. Primary key (automatic)
2. tenant_id (every table)
3. Foreign keys
4. Unique constraints

High Priority:

• Status columns used in WHERE
• Date ranges (created_at, updated_at)
• Frequently joined columns

Medium Priority:

• JSONB columns (GIN)
• Full-text search (GIN + tsvector)
• Covering indexes for hot queries

Index Best Practices

-- ✅ CORRECT: tenant_id first, partial index
CREATE INDEX idx_appointments_tenant_date 
    ON appointments(tenant_id, appointment_date DESC) 
    WHERE deleted_at IS NULL;

-- ✅ CORRECT: Covering index (avoids table lookup)
CREATE INDEX idx_patients_tenant_email_covering 
    ON patients(tenant_id, LOWER(email)) 
    INCLUDE (id, full_name, status);

-- ✅ CORRECT: GIN for JSONB
CREATE INDEX idx_patients_metadata_gin 
    ON patients USING GIN(metadata);

-- ❌ WRONG: tenant_id not first
CREATE INDEX idx_appointments_date_tenant 
    ON appointments(appointment_date, tenant_id);  -- ← Wrong order

-- ❌ WRONG: No partial index (includes deleted)
CREATE INDEX idx_patients_tenant 
    ON patients(tenant_id);  -- ← Should add WHERE deleted_at IS NULL

See: MULTI_TENANT_SCHEMA_PATTERNS.md - Indexing Strategies section

Security & Access Control

Row-Level Security (RLS)

Purpose: Database-level enforcement of tenant isolation (defense-in-depth)

When to use:

• All tables with tenant_id column
• Prevents accidental cross-tenant data access
• Complements application-level filtering

Setup:

-- 1. Enable RLS on table
ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

-- 2. Create policy for SELECT
CREATE POLICY tenant_isolation_select ON patients
    FOR SELECT
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 3. Create policy for INSERT
CREATE POLICY tenant_isolation_insert ON patients
    FOR INSERT
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 4. Create policy for UPDATE
CREATE POLICY tenant_isolation_update ON patients
    FOR UPDATE
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

-- 5. Create policy for DELETE (or disable if soft delete only)
CREATE POLICY tenant_isolation_delete ON patients
    FOR DELETE
    USING (tenant_id = current_setting('app.tenant_id', true)::TEXT);

Application Code (Go):

func (r *PatientRepository) List(ctx *request.Context) ([]*Patient, error) {
    // Set tenant_id in session variable
    _, err := r.db.Exec("SET LOCAL app.tenant_id = $1", ctx.TenantID)
    if err != nil {
        return nil, err
    }
    
    // Query automatically filtered by RLS policy
    rows, err := r.db.Query(`
        SELECT id, tenant_id, full_name, email, created_at 
        FROM patients 
        WHERE deleted_at IS NULL
        ORDER BY created_at DESC
    `)
    // RLS ensures: AND tenant_id = current_setting('app.tenant_id')::TEXT
    
    // ... parse results
}

Sensitive Data Handling

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    email VARCHAR(255) NOT NULL,  -- PII: Email
    password_hash TEXT NOT NULL,  -- Bcrypt hash (cost 12)
    phone VARCHAR(50),  -- PII: Phone
    ssn_encrypted TEXT,  -- PII: Encrypted with AES-256
    -- ...
);

COMMENT ON COLUMN users.password_hash IS 'Bcrypt hash with cost factor 12 (NEVER store plaintext)';
COMMENT ON COLUMN users.email IS 'PII: User email address (GDPR applicable)';
COMMENT ON COLUMN users.ssn_encrypted IS 'PII: Social Security Number (encrypted with AES-256, key in KMS)';

Migration Workflow

Development Workflow

# 1. Create new migration files
lokstra migrate create add_emergency_contact

# 2. Edit UP migration
# migrations/patient/003_add_emergency_contact.up.sql

# 3. Edit DOWN migration
# migrations/patient/003_add_emergency_contact.down.sql

# 4. Test UP migration
go run . migrate up

# 5. Test DOWN migration (rollback)
go run . migrate down

# 6. Test UP again (ensure idempotent)
go run . migrate up

# 7. Commit to version control
git add migrations/patient/003_*.sql
git commit -m "feat(patient): add emergency contact fields"

Production Deployment

# 1. Backup database
pg_dump -U postgres -d prod_db -F c -f backup_$(date +%Y%m%d).dump

# 2. Check migration status
go run . migrate status

# 3. Apply migrations
go run . migrate up

# 4. Verify data integrity
psql -U postgres -d prod_db -c "SELECT COUNT(*) FROM patients WHERE tenant_id IS NULL;"

# 5. If issues, rollback
go run . migrate down
# Then restore from backup if needed

Zero-Downtime Patterns

Adding Column:

-- Phase 1: Add nullable column
ALTER TABLE patients ADD COLUMN priority VARCHAR(20);

-- Phase 2: Backfill (in batches, off-peak hours)
-- (Use DO $$ loop with batching - see MIGRATION_PATTERNS.md)

-- Phase 3: Make NOT NULL
ALTER TABLE patients ALTER COLUMN priority SET NOT NULL;
ALTER TABLE patients ALTER COLUMN priority SET DEFAULT 'normal';

Adding Index (Non-Blocking):

-- Use CONCURRENTLY (runs outside transaction, no table lock)
CREATE INDEX CONCURRENTLY idx_patients_priority 
    ON patients(tenant_id, priority) 
    WHERE deleted_at IS NULL;

See: MIGRATION_PATTERNS.md for complete migration patterns

Quality Checklist

Use this checklist before finalizing schema. Minimum passing score: 85/100 points.

Critical Requirements (Must have all)

• [10 pts] All tables have tenant_id TEXT NOT NULL
• [10 pts] Composite foreign keys include tenant_id
• [10 pts] RLS enabled on all tenant tables
• [5 pts] Audit columns: created_at, updated_at

Multi-Tenancy (30 pts total)

• [5 pts] Unique constraints scoped to tenant
• [5 pts] Indexes have tenant_id as first column
• [5 pts] Global tables documented as exceptions

Data Types (15 pts)

• [5 pts] IDs are TEXT (ULID/UUID v7)
• [5 pts] Timestamps are TIMESTAMPTZ
• [5 pts] CHECK constraints for enum-like fields

Indexing (20 pts)

• [5 pts] Index on tenant_id for every table
• [5 pts] Composite indexes (tenant_id first)
• [5 pts] Partial indexes exclude soft-deleted
• [5 pts] GIN indexes for JSONB columns

Security (15 pts)

• [5 pts] RLS policies (SELECT, INSERT, UPDATE, DELETE)
• [3 pts] Composite FKs prevent cross-tenant refs
• [2 pts] Password hashing documented

Audit (10 pts)

• [5 pts] updated_at trigger attached
• [5 pts] Soft delete (deleted_at) implemented

Performance (10 pts)

• [5 pts] Partitioning for large tables (>10M rows)
• [5 pts] JSONB used appropriately

Total Score: ___ / 100

Passing: 85+ points (all critical requirements + 50+ other)

See: SCHEMA_VALIDATION_CHECKLIST.md for detailed validation

Examples

Complete Auth Module Schema

See AUTH_SCHEMA_EXAMPLE.md for a production-ready example with:

• 10 tables (tenants, users, roles, permissions, role_permissions, user_roles, refresh_tokens, password_reset_tokens, login_attempts, audit_logs)
• 57 indexes (tenant, composite, partial, GIN)
• RLS policies on all tables
• Triggers for auto-update timestamps and audit logging
• Composite foreign keys
• Partitioning examples
• Sample queries

Key Patterns from Example

1. Tenant Scoped Queries:

-- Always filter by tenant_id
SELECT id, full_name, email, status
FROM patients
WHERE tenant_id = $1  -- ← MANDATORY
  AND deleted_at IS NULL
ORDER BY created_at DESC;

2. Composite FK Enforcement:

-- Parent table must have unique (tenant_id, id)
ALTER TABLE patients 
ADD CONSTRAINT uq_patients_tenant_id UNIQUE(tenant_id, id);

-- Child table references both columns
ALTER TABLE appointments
ADD CONSTRAINT fk_appointments_patient 
    FOREIGN KEY (tenant_id, patient_id) 
    REFERENCES patients(tenant_id, id);

3. Soft Delete Pattern:

-- Mark as deleted (never DELETE)
UPDATE patients 
SET deleted_at = NOW() 
WHERE tenant_id = $1 AND id = $2;

-- Query active records only
SELECT * FROM patients 
WHERE tenant_id = $1 AND deleted_at IS NULL;

Reference Files

Comprehensive Guides

1. AUTH_SCHEMA_EXAMPLE.md (28 KB)

   • Complete 10-table auth module schema
   • All multi-tenant patterns demonstrated
   • RLS policies, triggers, indexes
   • Sample queries and data volume estimates

2. MULTI_TENANT_SCHEMA_PATTERNS.md (22 KB)

   • 3 multi-tenancy approaches (shared schema recommended)
   • Tenant isolation patterns
   • Composite foreign key patterns
   • 8 indexing strategies
   • RLS implementation with app code
   • Partitioning strategies
   • Anti-patterns to avoid

3. MIGRATION_PATTERNS.md (17 KB)

   • UP/DOWN migration templates
   • 6 common migration scenarios
   • Zero-downtime migration techniques
   • Batched data updates
   • Rollback strategies
   • Testing checklist

4. SCHEMA_VALIDATION_CHECKLIST.md (18 KB)

   • 100-point quality scoring system
   • 16 validation criteria with SQL queries
   • Minimum 85/100 to pass
   • Common issues and fixes
   • 2.5-hour review process

Templates

5. SCHEMA_TEMPLATE.md (748 lines)
   • Generic schema documentation template
   • Use as starting point for new modules

Output Format

Save schema design to: docs/modules/{module-name}/SCHEMA.md

Document Structure

# {Module Name} - Database Schema

**Module**: {module-name}
**Version**: 1.0.0
**Database**: PostgreSQL 14+
**Multi-Tenant**: Yes (Shared Schema)
**Date**: 2026-01-20

## Overview

{Brief description of module's data model}

## Tables

### 1. {table_name_plural}

**Purpose**: {What this table stores}
**Estimated Rows**: {volume estimate per tenant}
**Partitioning**: {Yes/No - strategy if applicable}

#### Table Definition

```sql
CREATE TABLE {module}.{table_plural} (
    -- ... full SQL definition
);

Indexes

-- List all indexes
CREATE INDEX ...;

Row-Level Security

ALTER TABLE ... ENABLE ROW LEVEL SECURITY;
CREATE POLICY ...;

Triggers

CREATE TRIGGER ...;

Sample Queries

-- Common query 1
SELECT ...;

-- Common query 2
SELECT ...;

Relationships

{ER diagram or text description}

Data Volume Estimates

Migration Files

• migrations/{module}/001_create_{table1}.up.sql
• migrations/{module}/001_create_{table1}.down.sql
• ...

Validation Checklist

• All tables have tenant_id
• Composite foreign keys
• RLS enabled
• ...

Score: 92/100 (A - Very Good)

---

## Best Practices Summary

### DO

✅ Include `tenant_id` in ALL business tables  
✅ Use composite foreign keys with `tenant_id`  
✅ Scope unique constraints to tenant  
✅ Enable RLS on all tenant tables  
✅ Use `TIMESTAMPTZ` (not TIMESTAMP)  
✅ Use `TEXT` for IDs (ULID/UUID v7)  
✅ Add partial indexes: `WHERE deleted_at IS NULL`  
✅ Put `tenant_id` first in composite indexes  
✅ Attach `updated_at` trigger to all tables  
✅ Soft delete with `deleted_at` column  
✅ Add CHECK constraints for enum-like fields  
✅ Test migrations on staging first  
✅ Write DOWN migrations for rollback  
✅ Comment sensitive columns (PII, passwords)  
✅ Partition tables with >10M rows

### DON'T

❌ Skip `tenant_id` in any business table  
❌ Create foreign keys without `tenant_id`  
❌ Use global unique constraints  
❌ Rely only on app-level tenant filtering  
❌ Use `TIMESTAMP` without timezone  
❌ Use auto-increment integers for IDs  
❌ Create indexes without tenant_id first  
❌ Hard delete business data  
❌ Store passwords in plaintext  
❌ Mix structured data with JSONB  
❌ Create blocking indexes in production  
❌ Update millions of rows in one transaction  
❌ Deploy migrations without backup  
❌ Forget to test rollback procedure  
❌ Reuse migration sequence numbers

---

## Common Pitfalls

### Pitfall 1: Missing tenant_id in Queries

```sql
-- ❌ WRONG: Missing tenant filter (returns ALL tenants' data)
SELECT * FROM patients WHERE email = 'john@example.com';

-- ✅ CORRECT: Always filter by tenant
SELECT * FROM patients 
WHERE tenant_id = $1 AND email = 'john@example.com';

Pitfall 2: Non-Composite Foreign Keys

-- ❌ WRONG: Allows cross-tenant references
CREATE TABLE appointments (
    patient_id TEXT REFERENCES patients(id)  -- ← No tenant_id
);

-- ✅ CORRECT: Composite FK prevents cross-tenant
CREATE TABLE appointments (
    tenant_id TEXT NOT NULL,
    patient_id TEXT NOT NULL,
    FOREIGN KEY (tenant_id, patient_id) 
        REFERENCES patients(tenant_id, id)
);

Pitfall 3: Long-Running Migrations

-- ❌ WRONG: Updates all rows in one transaction (locks table)
UPDATE patients SET status = 'active' WHERE status IS NULL;

-- ✅ CORRECT: Batch updates with delays
DO $$
DECLARE batch_size INTEGER := 1000;
BEGIN
    LOOP
        UPDATE patients SET status = 'active' 
        WHERE status IS NULL 
        AND id IN (SELECT id FROM patients WHERE status IS NULL LIMIT batch_size);
        EXIT WHEN NOT FOUND;
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

PostgreSQL-Specific Features

Extensions

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Full-text search
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- JSONB operators
CREATE EXTENSION IF NOT EXISTS "btree_gin";

Advanced Types

-- Enum type (use CHECK constraint instead for flexibility)
CREATE TYPE appointment_status AS ENUM ('scheduled', 'confirmed', 'completed');

-- Range type
CREATE TABLE bookings (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    time_range TSTZRANGE NOT NULL,
    EXCLUDE USING GIST (tenant_id WITH =, time_range WITH &&)
);

Partitioning

-- Range partitioning by date
CREATE TABLE audit_logs (
    id TEXT NOT NULL,
    tenant_id TEXT NOT NULL,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_logs_2026_01 PARTITION OF audit_logs
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- List partitioning by tenant (for very large tenants)
CREATE TABLE transactions (
    id TEXT NOT NULL,
    tenant_id TEXT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY LIST (tenant_id);

CREATE TABLE transactions_tenant_abc123 PARTITION OF transactions
    FOR VALUES IN ('abc123');

Troubleshooting

Issue: Migration Fails on Foreign Key

Error: ERROR: insert or update on table violates foreign key constraint

Cause: Child table has records referencing non-existent parent records

Fix:

-- Find orphaned records
SELECT DISTINCT a.patient_id
FROM appointments a
LEFT JOIN patients p ON a.tenant_id = p.tenant_id AND a.patient_id = p.id
WHERE p.id IS NULL;

-- Remove or fix orphaned records before adding FK
DELETE FROM appointments WHERE patient_id IN (SELECT ...);
-- OR
UPDATE appointments SET patient_id = NULL WHERE patient_id IN (SELECT ...);

-- Then add FK
ALTER TABLE appointments ADD CONSTRAINT fk_appointments_patient ...;

Issue: RLS Policy Not Working

Error: Queries return no rows even though data exists

Cause: app.tenant_id session variable not set

Fix:

// Set session variable before querying
_, err := db.Exec("SET LOCAL app.tenant_id = $1", ctx.TenantID)
if err != nil {
    return nil, err
}

// Now RLS policy can access current_setting('app.tenant_id')
rows, err := db.Query("SELECT * FROM patients WHERE deleted_at IS NULL")

Issue: Slow Queries on Large Tables

Error: Queries take >1 second on tables with >1M rows

Cause: Missing indexes or wrong index column order

Fix:

-- Check query plan
EXPLAIN ANALYZE 
SELECT * FROM appointments 
WHERE tenant_id = 'abc123' 
  AND appointment_date >= '2026-01-01';

-- If "Seq Scan" appears, add index
CREATE INDEX idx_appointments_tenant_date 
    ON appointments(tenant_id, appointment_date DESC) 
    WHERE deleted_at IS NULL;

-- Verify index is used
EXPLAIN ANALYZE SELECT ...;
-- Should show "Index Scan using idx_appointments_tenant_date"

Total Length: ~650 lines
Estimated Reading Time: 30 minutes
Last Updated: 2024-01-20
Skill Version: 2.0.0

Converted and distributed by TomeVault — claim your Tome and manage your conversions.