Spaces:
Running
Running
File size: 6,633 Bytes
57a1132 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 | -- =====================================================================
-- ACADFLOW CRM SAAS MULTI-TENANT & SECURITY MIGRATION (V2)
-- =====================================================================
-- Run this script in your Supabase SQL Editor to transition your database
-- to a production-grade multi-tenant SaaS schema.
BEGIN;
-- 1. Create Tenant Organization Table
CREATE TABLE IF NOT EXISTS organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
plan_type VARCHAR(50) DEFAULT 'Starter', -- e.g., 'Starter', 'Growth', 'Enterprise'
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- 2. Create Branch Table
CREATE TABLE IF NOT EXISTS branches (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL,
location VARCHAR(255),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- 3. Insert Default Tenant & Branch to prevent breaking existing data
INSERT INTO organizations (id, name, plan_type)
VALUES ('00000000-0000-0000-0000-000000000001', 'Default Academy', 'Starter')
ON CONFLICT (id) DO NOTHING;
INSERT INTO branches (id, organization_id, name, location)
VALUES ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', 'Primary Branch', 'Main Office')
ON CONFLICT (id) DO NOTHING;
-- 4. Alter Counselors Table
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;
-- Update existing counselors to default tenant
UPDATE counselors SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE counselors SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;
-- 5. Alter Leads Table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;
-- Update existing leads to default tenant
UPDATE leads SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE leads SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;
-- 6. Alter FollowUps Table
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;
-- Update existing follow-ups to default tenant
UPDATE follow_ups SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE follow_ups SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;
-- 7. Alter Admissions Table
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;
-- Update existing admissions to default tenant
UPDATE admissions SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE admissions SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;
-- 8. Alter AI Insights Table
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;
-- Update existing insights to default tenant
UPDATE ai_insights SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE ai_insights SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;
-- 9. Apply NOT NULL constraints after data updates to keep db strict
ALTER TABLE counselors ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE leads ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE follow_ups ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE admissions ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE ai_insights ALTER COLUMN organization_id SET NOT NULL;
-- 10. Create Active Views to auto-filter soft-deleted rows in application reads
CREATE OR REPLACE VIEW active_counselors AS
SELECT * FROM counselors WHERE deleted_at IS NULL;
CREATE OR REPLACE VIEW active_leads AS
SELECT * FROM leads WHERE deleted_at IS NULL;
CREATE OR REPLACE VIEW active_follow_ups AS
SELECT * FROM follow_ups WHERE deleted_at IS NULL;
CREATE OR REPLACE VIEW active_admissions AS
SELECT * FROM admissions WHERE deleted_at IS NULL;
CREATE OR REPLACE VIEW active_ai_insights AS
SELECT * FROM ai_insights WHERE deleted_at IS NULL;
-- 11. Row-Level Security (RLS) Policies
-- Turn on RLS on tables
ALTER TABLE counselors ENABLE ROW LEVEL SECURITY;
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE follow_ups ENABLE ROW LEVEL SECURITY;
ALTER TABLE admissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_insights ENABLE ROW LEVEL SECURITY;
-- Dynamic Tenant Organization Access Policies (Isolate data strictly by tenant UUID in user metadata JWT claim)
CREATE POLICY org_counselor_isolation ON counselors
FOR ALL TO authenticated
USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);
CREATE POLICY org_lead_isolation ON leads
FOR ALL TO authenticated
USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);
CREATE POLICY org_followup_isolation ON follow_ups
FOR ALL TO authenticated
USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);
CREATE POLICY org_admission_isolation ON admissions
FOR ALL TO authenticated
USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);
CREATE POLICY org_insight_isolation ON ai_insights
FOR ALL TO authenticated
USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);
COMMIT;
|