Skip to main content

Database Reference

Single Source of Truth: SQL migration files in migrations/ folder

Overview​

TVL Platform database schema is maintained in SQL migration files following industry standards (Rails, Django, Prisma pattern).

Why migrations as source of truth?

  • ✅ Chronological (shows evolution over time)
  • ✅ Executable (can build database from scratch)
  • ✅ Version controlled (Git tracks all changes)
  • ✅ No duplication (schema defined once)
  • ✅ Industry standard (how databases are actually built)

Structure​

migrations/
├── mvp.0/              MVP.0 - Foundation + Hostaway one-way sync
│   ├── 001_initial_schema.sql      All 14 tables (identity, supply, channels, etc.)
│   ├── 002_rls_policies.sql        Row-level security policies
│   └── 003_seed_data.sql           Roles, permissions, lookups
│
├── mvp.1/              MVP.1 - Two-way sync + Booking awareness
│   └── 004_add_availability.sql    Availability and booking tables
│
├── mvp.2/              MVP.2 - Multi-channel distribution
│   └── 005_multi_channel.sql       Airbnb, VRBO connectors
│
├── v1/                 V1.0 - Full booking engine + payments
│   ├── 006_bookings.sql
│   └── 007_payments.sql
│
├── v2/                 V2.0 - Events & experiences
│
└── v3/                 V3.0 - Multi-vendor marketplace

Viewing Schema​

See MVP.0 Schema (Foundation)​

cat docs/reference/database/migrations/mvp.0/*.sql

See Complete MVP Schema (MVP.0 + MVP.1 + MVP.2)​

cat docs/reference/database/migrations/mvp.0/*.sql \
    docs/reference/database/migrations/mvp.1/*.sql \
    docs/reference/database/migrations/mvp.2/*.sql

See Complete V1 Schema​

cat docs/reference/database/migrations/mvp.0/*.sql \
    docs/reference/database/migrations/mvp.1/*.sql \
    docs/reference/database/migrations/mvp.2/*.sql \
    docs/reference/database/migrations/v1/*.sql

Understanding the Business Model​

For business context and conceptual models: → See Domain Specifications

Each domain specification explains:

  • Business requirements - Why these tables exist
  • Conceptual ERD - Entity relationships (boxes and arrows)
  • Business rules - Validation, workflows, constraints
  • Version applicability - Which MVP/V versions use this domain

Separation:

  • Specifications: Business model (WHAT and WHY)
  • Migrations: SQL implementation (HOW)

Running Migrations​

Local Development (Supabase)​

# Start local Supabase
supabase start

# Run all migrations
supabase db push

# Or manually:
psql -h localhost -U postgres -d postgres < migrations/mvp.0/001_initial_schema.sql
psql -h localhost -U postgres -d postgres < migrations/mvp.0/002_rls_policies.sql
psql -h localhost -U postgres -d postgres < migrations/mvp.0/003_seed_data.sql

Production (Supabase Dashboard)​

  1. Navigate to Supabase Dashboard → SQL Editor
  2. Copy migration file contents
  3. Execute SQL
  4. Verify with: SELECT * FROM migrations_log;

Adding New Tables​

Step 1: Update Domain Specification​

docs/specifications/domains/{domain}/specification.md

Document the business requirement and conceptual model.

Step 2: Create Migration File​

# Create new migration
touch docs/reference/database/migrations/mvp.1/005_add_availability.sql

Step 3: Write SQL​

-- migrations/mvp.1/005_add_availability.sql
-- Purpose: Add availability calendar tables for two-way sync
-- References: specifications/domains/04-availability-calendar/

CREATE TABLE availability_blocks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status availability_status NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Prevent overlapping blocks
  EXCLUDE USING GIST (
    unit_id WITH =,
    daterange(start_date, end_date, '[]') WITH &&
  )
);

-- Indexes
CREATE INDEX idx_availability_org_id ON availability_blocks(org_id);
CREATE INDEX idx_availability_unit_id ON availability_blocks(unit_id);
CREATE INDEX idx_availability_dates ON availability_blocks(start_date, end_date);

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

CREATE POLICY tenant_isolation ON availability_blocks
  USING (org_id = current_setting('app.current_org_id')::uuid);

Step 4: Test Migration​

# Test on local database
psql -h localhost -U postgres < migrations/mvp.1/005_add_availability.sql

# Verify
psql -h localhost -U postgres -c "\d availability_blocks"

Schema Standards​

All tables must follow these conventions:

Required Columns​

id UUID PRIMARY KEY DEFAULT gen_random_uuid()
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
account_id UUID NOT NULL  -- for actor-owned entities
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Required Elements​

  • ✅ Primary keys (UUID)
  • ✅ Foreign keys with ON DELETE behavior
  • ✅ Indexes on all FKs, org_id, account_id
  • ✅ JSONB columns with GIN indexes
  • ✅ Row-Level Security (RLS) policies
  • ✅ Check constraints for data integrity
  • ✅ Comments explaining purpose

Multi-Tenancy​

  • ALL actor-owned tables MUST have both org_id AND account_id
  • System/lookup tables may have org_id only
  • RLS policies enforce org-level isolation

See: Multi-Tenancy Implementation

FAQ​

Q: Where can I see all tables at once? A: Concatenate all migration files up to your target version (see "Viewing Schema" above).

Q: Why aren't schemas organized by domain? A: Databases are built chronologically via migrations, not by domain. Domain context is in specifications.

Q: Where are data dictionary and ERD diagrams? A: Business ERDs are in domain specifications. Technical ERD can be generated from database using tools like SchemaSpy or dbdocs.io.

Q: What about seed data? A: Seed data is in migrations (e.g., 003_seed_data.sql). Roles, permissions, and lookup values.

Q: How do I see what changed between versions? A: Look at migration files or see Version Deltas for high-level changes.

Q: Can I query the database schema directly? A: Yes! Once migrations are run:

-- List all tables
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- Describe a table
\d organizations

-- See all foreign keys
SELECT * FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';

Last Updated: 2025-10-25 Migrations Location: migrations/ Latest Version: MVP.0 (initial schema)