Sri-dharshini/multi-tenant-organization-system

1-- Create enum types2DO $$ BEGIN3  CREATE TYPE role AS ENUM ('organization', 'admin', 'member');4EXCEPTION WHEN duplicate_object THEN null;5END $$;6 7DO $$ BEGIN8  CREATE TYPE task_status AS ENUM ('todo', 'in_progress', 'done', 'cancelled');9EXCEPTION WHEN duplicate_object THEN null;10END $$;11 12DO $$ BEGIN13  CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'urgent');14EXCEPTION WHEN duplicate_object THEN null;15END $$;16 17DO $$ BEGIN18  CREATE TYPE event_status AS ENUM ('upcoming', 'ongoing', 'completed', 'cancelled');19EXCEPTION WHEN duplicate_object THEN null;20END $$;21 22DO $$ BEGIN23  CREATE TYPE complaint_status AS ENUM ('open', 'in_review', 'resolved', 'dismissed');24EXCEPTION WHEN duplicate_object THEN null;25END $$;26 27DO $$ BEGIN28  CREATE TYPE audit_action AS ENUM ('created', 'updated', 'deleted', 'assigned', 'status_changed', 'completed');29EXCEPTION WHEN duplicate_object THEN null;30END $$;31 32-- Create extension for UUID generation33CREATE EXTENSION IF NOT EXISTS "pgcrypto";34 35-- Organizations table36CREATE TABLE IF NOT EXISTS organizations (37  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),38  name TEXT NOT NULL UNIQUE,39  description TEXT,40  logo_url TEXT,41  created_at TIMESTAMP DEFAULT NOW() NOT NULL,42  updated_at TIMESTAMP DEFAULT NOW() NOT NULL43);44 45-- Users table46CREATE TABLE IF NOT EXISTS users (47  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),48  email TEXT NOT NULL UNIQUE,49  password_hash TEXT,50  name TEXT NOT NULL,51  role role NOT NULL DEFAULT 'member',52  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,53  phone TEXT,54  address TEXT,55  department TEXT,56  position TEXT,57  bio TEXT,58  avatar_url TEXT,59  oauth_provider TEXT,60  oauth_id TEXT,61  is_active BOOLEAN DEFAULT true NOT NULL,62  created_at TIMESTAMP DEFAULT NOW() NOT NULL,63  updated_at TIMESTAMP DEFAULT NOW() NOT NULL64);65 66-- Tasks table67CREATE TABLE IF NOT EXISTS tasks (68  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),69  title TEXT NOT NULL,70  description TEXT,71  status task_status NOT NULL DEFAULT 'todo',72  priority task_priority NOT NULL DEFAULT 'medium',73  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,74  created_by_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,75  assigned_to_id UUID REFERENCES users(id) ON DELETE SET NULL,76  due_date TIMESTAMP,77  completed_at TIMESTAMP,78  created_at TIMESTAMP DEFAULT NOW() NOT NULL,79  updated_at TIMESTAMP DEFAULT NOW() NOT NULL80);81 82-- Events table83CREATE TABLE IF NOT EXISTS events (84  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),85  title TEXT NOT NULL,86  description TEXT,87  location TEXT,88  status event_status NOT NULL DEFAULT 'upcoming',89  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,90  created_by_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,91  start_date TIMESTAMP NOT NULL,92  end_date TIMESTAMP,93  max_attendees INTEGER,94  created_at TIMESTAMP DEFAULT NOW() NOT NULL,95  updated_at TIMESTAMP DEFAULT NOW() NOT NULL96);97 98-- Event Attendees table99CREATE TABLE IF NOT EXISTS event_attendees (100  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),101  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,102  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,103  joined_at TIMESTAMP DEFAULT NOW() NOT NULL104);105 106-- Complaints table107CREATE TABLE IF NOT EXISTS complaints (108  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),109  title TEXT NOT NULL,110  description TEXT NOT NULL,111  status complaint_status NOT NULL DEFAULT 'open',112  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,113  submitted_by_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,114  response TEXT,115  responded_by_id UUID REFERENCES users(id) ON DELETE SET NULL,116  responded_at TIMESTAMP,117  target_role TEXT DEFAULT 'organization' NOT NULL,118  is_anonymous BOOLEAN DEFAULT false NOT NULL,119  created_at TIMESTAMP DEFAULT NOW() NOT NULL,120  updated_at TIMESTAMP DEFAULT NOW() NOT NULL121);122 123-- Task Audit Logs table124CREATE TABLE IF NOT EXISTS task_audit_logs (125  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),126  task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,127  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,128  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,129  action audit_action NOT NULL,130  old_value TEXT,131  new_value TEXT,132  field_changed TEXT,133  created_at TIMESTAMP DEFAULT NOW() NOT NULL134);135 136-- Create indexes for performance137CREATE INDEX IF NOT EXISTS idx_users_org ON users(organization_id);138CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);139CREATE INDEX IF NOT EXISTS idx_tasks_org ON tasks(organization_id);140CREATE INDEX IF NOT EXISTS idx_tasks_created_by ON tasks(created_by_id);141CREATE INDEX IF NOT EXISTS idx_tasks_assigned_to ON tasks(assigned_to_id);142CREATE INDEX IF NOT EXISTS idx_events_org ON events(organization_id);143CREATE INDEX IF NOT EXISTS idx_complaints_org ON complaints(organization_id);144CREATE INDEX IF NOT EXISTS idx_audit_logs_task ON task_audit_logs(task_id);145CREATE INDEX IF NOT EXISTS idx_audit_logs_org ON task_audit_logs(organization_id);146 147-- ============================================148-- Seeding Demo Data (Idempotent)149-- ============================================150 151DO $$ 152DECLARE153  org_id UUID := '4aae5727-0e65-4c95-91cd-d069f718c245';154  admin1_id UUID := '3caa0037-ef67-4a9d-8221-64050f501e0a';155  admin2_id UUID := 'fed87ae6-d7ab-436f-83a0-c65585aad255';156  member1_id UUID := '9ed3c61a-7281-416c-9388-b6c9cfa9e849';157  member2_id UUID := '77f0ccbf-c0a7-4b74-8c3c-61bdf115a8da';158  member3_id UUID := '206c9a91-2434-4077-823f-d1c3682536b2';159  member4_id UUID := 'b6461cf5-6e1a-4ff5-b92c-a9141d44489b';160  member5_id UUID := '579dbaa8-804b-4f60-af87-b6c0922785ed';161  member6_id UUID := '39fb12ee-807f-472c-9cf6-c544e03036be';162  member7_id UUID := '53f1328b-7eb0-4258-a81d-1a86d901f332';163  164  task1_id UUID := '5130553f-7bb2-423a-b51d-de32c0432559';165  task2_id UUID := '3e9e51a2-d636-4810-934e-6729afab0edb';166  task3_id UUID := '7ba932fe-a9c0-42ff-9d45-3460200ce891';167  task4_id UUID := 'c60ca530-5380-4275-aad7-ef0744385e05';168  task5_id UUID := '5b396c3a-0506-4acb-a9a1-1a04ec98722c';169  170  event1_id UUID := '1f4b2d1d-2b50-4fd0-b0c7-cf3fc4c8c0bf';171  event2_id UUID := '4a53aed6-0d90-47cc-adae-f8733d1321d2';172BEGIN173 174-- Insert Organization (1)175IF NOT EXISTS (SELECT 1 FROM organizations WHERE name = 'Acme Corp') THEN176  INSERT INTO organizations (id, name, description) 177  VALUES (org_id, 'Acme Corp', 'A highly dynamic and fast-paced startup.');178ELSE179  SELECT id INTO org_id FROM organizations WHERE name = 'Acme Corp';180END IF;181 182-- Insert Admins (2)183INSERT INTO users (id, name, email, password_hash, role, organization_id, is_active)184VALUES 185(admin1_id, 'Alice Admin', 'admin1@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'admin', org_id, true),186(admin2_id, 'Bob Boss', 'admin2@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'admin', org_id, true)187ON CONFLICT (email) DO NOTHING;188 189-- Insert Members (7)190INSERT INTO users (id, name, email, password_hash, role, organization_id, is_active)191VALUES 192(member1_id, 'Charlie Carter', 'member1@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),193(member2_id, 'Diana Davidson', 'member2@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),194(member3_id, 'Ethan Edwards', 'member3@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),195(member4_id, 'Fiona Foster', 'member4@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),196(member5_id, 'George Grant', 'member5@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),197(member6_id, 'Hannah Hughes', 'member6@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true),198(member7_id, 'Ian Irvine', 'member7@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'member', org_id, true)199ON CONFLICT (email) DO NOTHING;200 201-- Insert Organization Owner Account (1) mapped to same Org ID202INSERT INTO users (id, name, email, password_hash, role, organization_id, is_active)203VALUES 204('2d1e78d0-2625-4281-a0e9-df55f4e4a542', 'Acme Corp', 'org@acme.com', '$2b$10$CmN6Amu3MufY1YPoQ1DzWeEWry/He6.9j1C4hZsUA2mUTBoQKfWaq', 'organization', org_id, true)205ON CONFLICT (email) DO NOTHING;206 207-- Insert Demo Tasks208IF NOT EXISTS (SELECT 1 FROM tasks WHERE title = 'Deploy v2.0 to Production') THEN209  INSERT INTO tasks (id, title, description, status, priority, organization_id, created_by_id, assigned_to_id, due_date)210  VALUES 211  (task1_id, 'Deploy v2.0 to Production', 'Ensure all staging tests pass before pushing.', 'in_progress', 'high', org_id, admin1_id, member1_id, NOW() + INTERVAL '2 days'),212  (task2_id, 'Design new Landing Page', 'Update the UI to a modern light theme.', 'done', 'medium', org_id, admin2_id, member2_id, NOW() - INTERVAL '1 day'),213  (task3_id, 'Fix Login Middleware Bug', 'Users are being redirected wrongly. Fix auth token cookies.', 'todo', 'urgent', org_id, admin1_id, member3_id, NOW() + INTERVAL '1 day'),214  (task4_id, 'Prepare Q3 Financial Report', 'Gather all analytics data across the multitenant system.', 'todo', 'high', org_id, admin2_id, member4_id, NOW() + INTERVAL '5 days'),215  (task5_id, 'Onboard New Employees', 'Setup equipment and accounts for new hires.', 'in_progress', 'low', org_id, admin1_id, member5_id, NOW() + INTERVAL '7 days');216END IF;217 218-- Insert Demo Events219IF NOT EXISTS (SELECT 1 FROM events WHERE title = 'Q3 All Hands Meeting') THEN220  INSERT INTO events (id, title, description, location, status, organization_id, created_by_id, start_date, end_date)221  VALUES 222  (event1_id, 'Q3 All Hands Meeting', 'Company wide sync to discuss roadmap and accomplishments.', 'Virtual - Zoom', 'upcoming', org_id, admin1_id, NOW() + INTERVAL '3 days', NOW() + INTERVAL '3 days 2 hours'),223  (event2_id, 'Team Building Retreat', 'Annual getaway to boost team morale.', 'Lake Tahoe Cabin', 'upcoming', org_id, admin2_id, NOW() + INTERVAL '14 days', NOW() + INTERVAL '16 days');224 225  -- Add Attendees226  INSERT INTO event_attendees (event_id, user_id)227  VALUES 228  (event1_id, admin1_id), (event1_id, admin2_id), (event1_id, member1_id), (event1_id, member2_id), (event1_id, member3_id), (event1_id, member4_id),229  (event2_id, admin2_id), (event2_id, member5_id), (event2_id, member6_id), (event2_id, member7_id);230END IF;231 232END $$;233