FleetOS Database Migration Guide

Quick Start

1-Step Migration (Recommended)

1. Open: https://supabase.com/dashboard → Your Project → SQL Editor
2. Copy: database/APPLY_ALL_MIGRATIONS.sql (entire file)
3. Paste into SQL Editor
4. Click "Run"
5. Verify success message

That's it! All tables, columns, indexes, and views will be created.

---

What Gets Applied

Migration 001: Progressive Authentication Tiers

Tables Modified:

• users table: Adds auth_tier, fleetos_data, upgraded_at, tier_changed_at columns

New Tables:

• user_tier_history - Audit trail for tier changes

New Functions:

• user_can_create_requests() - Permission helper

• user_can_view_requests() - Permission helper

• upgrade_user_tier() - Tier upgrade with audit trail

New Views:

• authenticated_users - Users with accounts

• magic_link_users - Users with only magic links

• user_tier_summary - Tier distribution summary

Data Migration:

• Existing FleetOS users → AUTHENTICATED tier

• Existing Robo-Hub users → ROBO_HUB tier

• Existing Robo-Dapp users → ROBO_DAPP tier

---

Migration 002: Magic Link Tracking

Tables Modified:

• service_requests table: Adds tracking_token column with UNIQUE constraint

Indexes Created:

• idx_service_requests_tracking_token for fast token lookups

---

Migration 003: Viral Analytics

New Tables:

1. viral_events - Tracks viral moment impressions, clicks, conversions

2. viral_conversions - Tracks invite actions and relationship creation

3. viral_preferences - User opt-outs and cooldown periods

4. viral_feedback - User feedback on viral moments

New Views:

1. viral_moment_summary - Performance metrics per moment type

2. viral_daily_activity - Daily breakdown of viral activity

3. user_viral_journey - Per-user viral engagement metrics

---

Verification After Migration

Option 1: Run Verification Script

node scripts/verify-migration.js

Expected Output:

✅ PASSED: auth_tier column exists
✅ PASSED: Tier distribution calculated
✅ PASSED: Helper functions created
✅ PASSED: All new columns exist
✅ MIGRATION VERIFICATION COMPLETE!

Option 2: Manual SQL Verification

Run these queries in Supabase SQL Editor:

-- Check auth tier setup
SELECT * FROM user_tier_summary;

-- Check tracking token column
SELECT column_name FROM information_schema.columns
WHERE table_name = 'service_requests' AND column_name = 'tracking_token';

-- Check viral tables exist
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name LIKE 'viral_%'
ORDER BY table_name;

-- Check viral views exist
SELECT table_name FROM information_schema.views
WHERE table_schema = 'public' AND table_name LIKE 'viral_%'
ORDER BY table_name;

---

Expected Results

User Tier Distribution

auth_tier      | user_count | fleetos_users | robo_hub_users | robo_dapp_users
---------------|------------|---------------|----------------|-----------------
MAGIC_LINK     | 6          | 6             | 0              | 0
AUTHENTICATED  | 0          | 0             | 0              | 0
ROBO_HUB       | 0          | 0             | 0              | 0
ROBO_DAPP      | 0          | 0             | 0              | 0

This is expected - all existing users start at MAGIC_LINK tier and upgrade when they perform their first gated action.

---

Rollback (If Needed)

If you need to undo the migrations:

-- WARNING: This will delete data!

BEGIN;

-- Drop views
DROP VIEW IF EXISTS viral_moment_summary CASCADE;
DROP VIEW IF EXISTS viral_daily_activity CASCADE;
DROP VIEW IF EXISTS user_viral_journey CASCADE;
DROP VIEW IF EXISTS authenticated_users CASCADE;
DROP VIEW IF EXISTS magic_link_users CASCADE;
DROP VIEW IF EXISTS user_tier_summary CASCADE;

-- Drop tables
DROP TABLE IF EXISTS viral_feedback CASCADE;
DROP TABLE IF EXISTS viral_preferences CASCADE;
DROP TABLE IF EXISTS viral_conversions CASCADE;
DROP TABLE IF EXISTS viral_events CASCADE;
DROP TABLE IF EXISTS user_tier_history CASCADE;

-- Drop functions
DROP FUNCTION IF EXISTS upgrade_user_tier CASCADE;
DROP FUNCTION IF EXISTS user_can_view_requests CASCADE;
DROP FUNCTION IF EXISTS user_can_create_requests CASCADE;

-- Remove columns from users
ALTER TABLE users DROP COLUMN IF EXISTS tier_changed_at;
ALTER TABLE users DROP COLUMN IF EXISTS upgraded_at;
ALTER TABLE users DROP COLUMN IF EXISTS fleetos_data;
ALTER TABLE users DROP COLUMN IF EXISTS auth_tier;

-- Remove column from service_requests
ALTER TABLE service_requests DROP COLUMN IF EXISTS tracking_token;

COMMIT;

SELECT '⚠️  All migrations rolled back' as status;

⚠️ Use rollback ONLY in development! Never rollback in production with real user data.

---

Troubleshooting

Error: "column already exists"

Cause: Migration has been partially applied before

Solution: This is safe! The migration uses IF NOT EXISTS clauses, so it will skip existing items and only create missing ones.

Error: "permission denied"

Cause: Not using service role key or insufficient permissions

Solution:

1. Verify you're in Supabase SQL Editor (not a frontend query)

2. Check that you're project owner/admin

3. Try running migrations one at a time

Error: "relation does not exist"

Cause: Missing core tables (users, service_requests, etc.)

Solution:

1. Apply core FleetOS schema first: database/fleetos/schema.sql

2. Then run migrations

Migration takes too long

Cause: Large dataset

Solution:

• Migrations should complete in 10-30 seconds for <10,000 users

• If it takes >60 seconds, check for table locks

• Try running during low-traffic period

---

Migration History

Version	Date	Description
001	2025-12-30	Progressive authentication tiers
002	2025-12-27	Magic link tracking tokens
003	2025-12-27	Viral analytics infrastructure

---

Next Steps After Migration

1. ✅ Apply RLS policies: database/fleetos/rls-policies.sql

2. ✅ Verify migration: node scripts/verify-migration.js

3. ⏭️ Update frontend to use auth tiers

4. ⏭️ Test permission gates

5. ⏭️ Deploy to production

---

Support

If migrations fail or produce unexpected results:

1. Check Supabase logs: Dashboard → Logs → Postgres Logs

2. Run verification script: node scripts/verify-migration.js

3. Check table schemas: \d+ table_name in psql

4. Review this guide's troubleshooting section

---

Migration Status: 📋 READY TO APPLY

Estimated Time: 10-30 seconds

Risk Level: 🟢 LOW (uses IF NOT EXISTS, safe to re-run)