Skip to main content

MVP.0 Migrations

Version: MVP.0 (Foundation + Hostaway One-Way Distribution) Timeline: Weeks 1-10 Status: Ready for Implementation

Overview​

These migrations establish the foundational database schema for TVL platform MVP.0, including:

  • Multi-tenant organization and account structure
  • User identity and authentication
  • Role-based access control
  • Space and Unit supply management
  • Channel distribution (Hostaway one-way sync)
  • Basic audit logging

Total Tables: 14

Migration Scripts​

Execute in this order:

001_initial_schema.sql​

Purpose: Create all core tables for MVP.0 Tables Created:

  • organizations (5 columns)
  • accounts (8 columns)
  • users (12 columns)
  • memberships (10 columns)
  • roles (5 columns)
  • permissions (5 columns)
  • sessions (10 columns)
  • spaces (16 columns)
  • units (14 columns)
  • unit_snapshots (7 columns)
  • channel_targets (12 columns)
  • channel_listings (13 columns)
  • outbound_audit (13 columns)
  • audit_events (10 columns)

Dependencies: None (initial schema) Rollback: DROP SCHEMA public CASCADE; CREATE SCHEMA public;

002_rls_policies.sql​

Purpose: Enable Row-Level Security for multi-tenancy Policies Created:

  • Org isolation policies (all tables)
  • Account-level access policies
  • User session policies

Dependencies: 001_initial_schema.sql Rollback: DROP POLICY ... ON ... for each policy

003_seed_roles.sql​

Purpose: Seed system roles and permissions Data Seeded:

  • Roles: admin, ops, owner_admin, manager, viewer
  • Permissions: 30+ core permissions
  • RolePermission mappings

Dependencies: 001_initial_schema.sql Rollback: DELETE FROM role_permissions; DELETE FROM permissions; DELETE FROM roles WHERE is_system=true;

Prerequisites​

Database Requirements​

  • PostgreSQL 15+
  • Extensions: citext, uuid-ossp, pgcrypto
  • Superuser access (for RLS policies)

Environment Variables​

DATABASE_URL=postgresql://user:pass@host:5432/tvl_mvp
DB_POOL_MIN=2
DB_POOL_MAX=10

Execution Instructions​

Development Environment​

cd /migrations/mvp.0

# Run all migrations
psql $DATABASE_URL -f 001_initial_schema.sql
psql $DATABASE_URL -f 002_rls_policies.sql
psql $DATABASE_URL -f 003_seed_roles.sql

# Verify
psql $DATABASE_URL -c "\dt"  # List all tables
psql $DATABASE_URL -c "SELECT COUNT(*) FROM roles;"  # Should return 5

Production Environment​

# Use migration tool (e.g., Flyway, Liquibase, or custom Node script)
npm run migrate:up

# Or manually with transaction wrapper
psql $DATABASE_URL << EOF
BEGIN;
\i 001_initial_schema.sql
\i 002_rls_policies.sql
\i 003_seed_roles.sql
COMMIT;
EOF

Validation Tests​

After migration, run these validation queries:

Test 1: Table Count​

SELECT COUNT(*) AS table_count
FROM information_schema.tables
WHERE table_schema = 'public';
-- Expected: 14

Test 2: Role Seeding​

SELECT name FROM roles ORDER BY name;
-- Expected: admin, manager, ops, owner_admin, viewer

Test 3: RLS Enabled​

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = true;
-- Expected: All 14 tables

Test 4: Foreign Keys​

SELECT COUNT(*)
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY';
-- Expected: 25+ foreign keys

Rollback Procedure​

WARNING: This will delete all data!

BEGIN;

-- Drop policies first
DROP POLICY IF EXISTS org_isolation ON organizations;
DROP POLICY IF EXISTS org_isolation ON accounts;
-- ... (all policies)

-- Drop tables in reverse dependency order
DROP TABLE IF EXISTS audit_events CASCADE;
DROP TABLE IF EXISTS outbound_audit CASCADE;
DROP TABLE IF EXISTS channel_listings CASCADE;
DROP TABLE IF EXISTS channel_targets CASCADE;
DROP TABLE IF EXISTS unit_snapshots CASCADE;
DROP TABLE IF EXISTS units CASCADE;
DROP TABLE IF EXISTS spaces CASCADE;
DROP TABLE IF EXISTS sessions CASCADE;
DROP TABLE IF EXISTS role_permissions CASCADE;
DROP TABLE IF EXISTS permissions CASCADE;
DROP TABLE IF EXISTS memberships CASCADE;
DROP TABLE IF EXISTS roles CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS accounts CASCADE;
DROP TABLE IF EXISTS organizations CASCADE;

COMMIT;

Known Issues​

Issue 1: RLS Policy Performance​

Symptom: Slow queries on large tables with RLS enabled Solution: Create partial indexes on org_id + status columns Status: Monitored

Issue 2: account_id Nullable​

Symptom: Some tables missing account_id enforcement Solution: Add NOT NULL constraint in MVP.1 after data migration Status: Deferred to MVP.1

Next Steps​

After successful MVP.0 migration:

  1. Verify data model: Create test Organization, Account, User
  2. Test RLS policies: Ensure users can only see their org's data
  3. Seed test data: Run development seed scripts
  4. Performance baseline: Measure query performance
  5. Move to MVP.1: When ready, execute migrations in /migrations/mvp.1/