Database Setup Guide

Deep dive into PostgreSQL setup, migrations, and RLS policies

---

Overview

TVL Platform uses PostgreSQL 15+ with advanced features:

• ✅ Row-Level Security (RLS) - Multi-tenant isolation
• ✅ JSONB - Flexible metadata and integration data
• ✅ GIST Indexes - Prevent booking conflicts
• ✅ Partitioning - Scale audit tables to millions of rows
• ✅ Full-Text Search - Property and booking search

This guide explains how to work with the database locally.

---

Database Environments

Local PostgreSQL (Docker)

Default for 99% of development:

# .devcontainer/docker-compose.yml
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: tvl_dev
      POSTGRES_USER: tvl_user
      POSTGRES_PASSWORD: tvl_password
    ports:
      - "5432:5432"

Connection String:

postgresql://tvl_user:tvl_password@localhost:5432/tvl_dev

Benefits:

• ⚡ Fast (<1ms latency)
• 🔒 Isolated (your own database)
• 💰 Free (no API limits)
• 🧪 Safe (reset 100x/day)

---

Supabase (Remote)

Use for integration testing only:

Environment	Purpose	URL Pattern
Dev Integration	Testing Auth, Storage, Realtime	postgresql://postgres.[PROJECT_REF]:...
Staging	Team QA, pre-production	postgresql://postgres.[PROJECT_REF]:...
Production	Live customer data	postgresql://postgres.[PROJECT_REF]:...

Only switch when needed - see Switching Environments.

---

Database Schema

Required Columns (ALL Tables)

Every table must include:

CREATE TABLE example_table (
  -- Identity
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Multi-tenancy (REQUIRED for actor-owned entities)
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Domain-specific columns
  -- ...
);

See ADR-0002 for rationale: Data Modeling

---

Multi-Tenancy Rules

3-tier hierarchy:

Organization (org_id)
  └─ Account (account_id)
      └─ User (user_id)

Rules:

• ✅ Actor-owned tables MUST have org_id + account_id
• ✅ System tables may have org_id only
• ❌ Never use account_id alone without org_id

Examples:

-- Actor-owned (user creates this)
CREATE TABLE bookings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL,    -- ✅ Required
  account_id UUID NOT NULL, -- ✅ Required
  created_by UUID NOT NULL REFERENCES users(id),
  -- ...
);

-- System table (shared across accounts)
CREATE TABLE properties (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL,    -- ✅ Required
  -- account_id NOT included (shared)
  -- ...
);

---

Row-Level Security (RLS)

Why RLS?

Prevent cross-org data leaks - even if application code has bugs.

Example attack scenario:

// BUG: Forgot to filter by org_id
const bookings = await db.select().from(bookings).where(eq(bookings.id, bookingId));
// Without RLS: Returns bookings from ALL orgs (data leak!)
// With RLS: Returns nothing (RLS blocks access)

RLS is your safety net - defense in depth.

---

Enable RLS

-- Step 1: Enable RLS on table
ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;

-- Step 2: Create SELECT policy (read)
CREATE POLICY bookings_tenant_isolation ON bookings
  FOR SELECT
  USING (org_id = current_setting('app.current_org_id')::UUID);

-- Step 3: Create INSERT policy (write)
CREATE POLICY bookings_insert_policy ON bookings
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id')::UUID);

-- Step 4: Create UPDATE policy
CREATE POLICY bookings_update_policy ON bookings
  FOR UPDATE
  USING (org_id = current_setting('app.current_org_id')::UUID)
  WITH CHECK (org_id = current_setting('app.current_org_id')::UUID);

-- Step 5: Create DELETE policy
CREATE POLICY bookings_delete_policy ON bookings
  FOR DELETE
  USING (org_id = current_setting('app.current_org_id')::UUID);

---

Set Session Context

Before queries, set org_id:

// src/middleware/setOrgContext.ts
export async function setOrgContext(req: FastifyRequest) {
  const { orgId } = req.user; // From JWT

  await db.execute(sql`SET app.current_org_id = ${orgId}`);
}

// Register middleware
app.addHook('onRequest', setOrgContext);

Now all queries automatically filtered by org_id!

---

Test RLS Policies

// tests/rls/bookings.test.ts
describe('RLS: bookings table', () => {
  it('should NOT allow cross-org access', async () => {
    // Create two orgs
    const orgA = await createTestOrg('Org A');
    const orgB = await createTestOrg('Org B');

    // Create booking in org B
    const bookingB = await db.insert(bookings).values({
      orgId: orgB.id,
      accountId: orgB.accountId,
      guestName: 'Bob',
    }).returning();

    // Set session to org A
    await db.execute(sql`SET app.current_org_id = ${orgA.id}`);

    // Try to access org B booking
    const result = await db
      .select()
      .from(bookings)
      .where(eq(bookings.id, bookingB[0].id));

    // RLS should block access
    expect(result).toHaveLength(0);
  });

  it('should allow same-org access', async () => {
    const org = await createTestOrg('Org A');

    const booking = await db.insert(bookings).values({
      orgId: org.id,
      accountId: org.accountId,
      guestName: 'Alice',
    }).returning();

    await db.execute(sql`SET app.current_org_id = ${org.id}`);

    const result = await db
      .select()
      .from(bookings)
      .where(eq(bookings.id, booking[0].id));

    expect(result).toHaveLength(1);
  });
});

Run RLS tests:

pnpm test:rls

---

Migrations

Migration Structure

packages/database/migrations/
├── 001_initial_schema.sql
├── 002_rls_policies.sql
├── 003_add_bookings_table.sql
└── 004_add_availability_table.sql

File naming: {number}_{description}.sql

---

Create Migration

# Create new migration
pnpm db:migrate:create "add_payments_table"

# Creates: packages/database/migrations/005_add_payments_table.sql

Template:

-- Migration: 005_add_payments_table
-- Description: Add payments table for tracking transactions
-- Author: your-name
-- Date: 2025-01-26

-- Up Migration
CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  booking_id UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  amount_cents INT NOT NULL CHECK (amount_cents > 0),
  currency VARCHAR(3) NOT NULL DEFAULT 'USD',
  status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'completed', 'failed')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes
CREATE INDEX idx_payments_org_id ON payments(org_id);
CREATE INDEX idx_payments_account_id ON payments(account_id);
CREATE INDEX idx_payments_booking_id ON payments(booking_id);
CREATE INDEX idx_payments_status ON payments(status);

-- RLS Policies
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

CREATE POLICY payments_tenant_isolation ON payments
  FOR SELECT
  USING (org_id = current_setting('app.current_org_id')::UUID);

CREATE POLICY payments_insert_policy ON payments
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id')::UUID);

-- Down Migration
DROP TABLE IF EXISTS payments CASCADE;

---

Apply Migrations

# Run pending migrations
pnpm db:migrate

# Check status
pnpm db:migrate:status

# Rollback last migration
pnpm db:rollback

# Reset database (DESTRUCTIVE - local only!)
pnpm db:reset

---

Migration Checklist

Before committing a migration:

• Has id UUID PRIMARY KEY DEFAULT gen_random_uuid()
• Has org_id + account_id (if actor-owned)
• Has created_at + updated_at
• All FKs have ON DELETE behavior (CASCADE, SET NULL, RESTRICT)
• Indexes on all FKs, org_id, account_id
• RLS policies enabled and tested
• Check constraints for enums
• Down migration tested
• Migration is idempotent (safe to run twice)

---

Seed Data

Load Test Data

# Load seed data
pnpm db:seed

# Seeds:
# - 3 organizations
# - 10 accounts
# - 50 users
# - 100 properties
# - 500 bookings

Seed file: packages/database/seeds/001_initial_data.ts

// packages/database/seeds/001_initial_data.ts
import { db } from '../src';
import { organizations, accounts, users } from '../src/schema';

export async function seed() {
  // Create org
  const [org] = await db.insert(organizations).values({
    name: 'Test Organization',
    slug: 'test-org',
  }).returning();

  // Create account
  const [account] = await db.insert(accounts).values({
    orgId: org.id,
    name: 'Test Account',
    type: 'standard',
  }).returning();

  // Create user
  await db.insert(users).values({
    orgId: org.id,
    accountId: account.id,
    email: 'admin@test.com',
    role: 'admin',
  });

  console.log('✅ Seed data loaded');
}

---

Database Console (psql)

Open Console

# Open psql
pnpm db:console

# You'll see:
# psql (15.5)
# Type "help" for help.
# tvl_dev=#

Useful Commands

-- List all tables
\dt

-- Describe table
\d bookings

-- List all indexes
\di

-- List all policies
\dp bookings

-- Show current org_id
SELECT current_setting('app.current_org_id');

-- Set org_id
SET app.current_org_id = 'your-org-id';

-- Query bookings (respects RLS)
SELECT * FROM bookings;

-- Count records
SELECT COUNT(*) FROM bookings;

-- Exit
\q

---

pgAdmin (GUI)

Access pgAdmin

URL: http://localhost:5050

Login:

• Email: admin@thevillalife.com
• Password: admin

Connect to Database

1. Right-click Servers → Register → Server
2. General Tab:
   • Name: TVL Local
3. Connection Tab:
   • Host: postgres (Docker network name)
   • Port: 5432
   • Database: tvl_dev
   • Username: tvl_user
   • Password: tvl_password
4. Click Save

---

Advanced Features

GIST Exclusion Constraints

Prevent overlapping bookings:

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING GIST (
    property_id WITH =,
    daterange(check_in, check_out, '[]') WITH &&
  );

What this does:

• Same property_id + overlapping dates = rejected
• Prevents double bookings at database level

---

Full-Text Search

Search properties by name/description:

-- Add tsvector column
ALTER TABLE properties
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', name || ' ' || description)
  ) STORED;

-- Create GIN index
CREATE INDEX idx_properties_search ON properties USING GIN(search_vector);

-- Query
SELECT * FROM properties
WHERE search_vector @@ to_tsquery('english', 'beach & villa');

---

Partitioning (Audit Tables)

Partition audit log by month:

CREATE TABLE audit_log (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL,
  event_type VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  payload JSONB NOT NULL
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE audit_log_2025_01 PARTITION OF audit_log
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE audit_log_2025_02 PARTITION OF audit_log
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

Auto-create partitions via cron job.

---

Switching Environments

Local → Supabase Dev

# 1. Update .env.local
DATABASE_ENVIRONMENT=supabase-dev
DATABASE_URL=postgresql://postgres.[PROJECT_REF]:[PASSWORD]@...
SUPABASE_URL=https://[PROJECT_REF].supabase.co
SUPABASE_ANON_KEY=your-anon-key

# 2. Rebuild container
# F1 → Dev Containers: Rebuild Container

# 3. Run migrations
pnpm db:migrate

Supabase Dev → Local

# 1. Update .env.local
DATABASE_ENVIRONMENT=local

# 2. Rebuild container
# F1 → Dev Containers: Rebuild Container

---

Troubleshooting

Can't Connect to PostgreSQL

# Check if postgres is running
docker ps | grep postgres

# Check logs
docker logs tvl-postgres

# Restart postgres
docker restart tvl-postgres

# Test connection
psql postgresql://tvl_user:tvl_password@localhost:5432/tvl_dev

Migration Failed

# Check status
pnpm db:migrate:status

# Rollback
pnpm db:rollback

# Fix migration file
# Re-run
pnpm db:migrate

RLS Blocking Queries

-- Check if RLS is enabled
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE tablename = 'bookings';

-- Check current org_id
SELECT current_setting('app.current_org_id', true);

-- Set org_id
SET app.current_org_id = 'your-org-id';

Reset Database (Local Only!)

# DESTROYS ALL DATA - local only!
pnpm db:reset

# Rebuilds:
# 1. Drops database
# 2. Creates database
# 3. Runs migrations
# 4. Loads seed data

---

Best Practices

DO:

• ✅ Always include org_id + account_id on actor-owned tables
• ✅ Always enable RLS on all tables
• ✅ Always test RLS with cross-org access tests
• ✅ Always index org_id, account_id, foreign keys
• ✅ Always use transactions for multi-table operations
• ✅ Always add down migrations for rollback

DON'T:

• ❌ Never skip RLS policies (security critical!)
• ❌ Never use account_id without org_id
• ❌ Never hardcode org_id (use session context)
• ❌ Never run migrations directly against prod (use CI/CD)
• ❌ Never commit .env.local (use .env.example)

---

Resources

• ADR-0001: Authentication & Authorization
• ADR-0002: Data Modeling
• Guide: Multi-Tenancy Implementation
• Guide: RLS Policy Patterns
• PostgreSQL Docs: Row Security Policies

---

Last Updated: 2025-01-26 Maintained By: Tech Lead Status: Active