-- ============================================================================= -- MIGRATION: Platform RBAC, Merchant Model, Staff Permissions -- Convention: Prisma maps createdAt→createdOn, updatedAt→updatedOn in DB -- Run: idempotent — safe to re-execute -- ============================================================================= BEGIN; CREATE EXTENSION IF NOT EXISTS "pgcrypto"; -- =========================================================================== -- SECTION 1 — ENUM: Add new UserRole values -- =========================================================================== ALTER TYPE admin."UserRole" ADD VALUE IF NOT EXISTS 'platform_admin'; ALTER TYPE admin."UserRole" ADD VALUE IF NOT EXISTS 'account_manager'; ALTER TYPE admin."UserRole" ADD VALUE IF NOT EXISTS 'operator'; ALTER TYPE admin."UserRole" ADD VALUE IF NOT EXISTS 'merchant'; -- =========================================================================== -- SECTION 2 — PLATFORM SCHEMA: Enhance existing tables -- =========================================================================== -- 2a. platform.roles ALTER TABLE platform.roles ADD COLUMN IF NOT EXISTS scope VARCHAR(50) NOT NULL DEFAULT 'platform' CHECK (scope IN ('platform','merchant')), ADD COLUMN IF NOT EXISTS description TEXT, ADD COLUMN IF NOT EXISTS "isSystem" BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); COMMENT ON COLUMN platform.roles.scope IS 'platform = your team | merchant = store owner team'; COMMENT ON COLUMN platform.roles."isSystem" IS 'System roles cannot be deleted via UI'; -- 2b. platform.permissions ALTER TABLE platform.permissions ADD COLUMN IF NOT EXISTS scope VARCHAR(50) NOT NULL DEFAULT 'platform' CHECK (scope IN ('platform','merchant')), ADD COLUMN IF NOT EXISTS category VARCHAR(100), ADD COLUMN IF NOT EXISTS description TEXT, ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); COMMENT ON COLUMN platform.permissions.scope IS 'platform = controls admin UI | merchant = controls store ops'; COMMENT ON COLUMN platform.permissions.category IS 'UI group: Overview, Merchants, Catalog, Sales, Finance, Team'; -- 2c. platform.role_permissions ALTER TABLE platform.role_permissions ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); -- 2d. platform.organizations (= Merchant account) ALTER TABLE platform.organizations ADD COLUMN IF NOT EXISTS email VARCHAR(255), ADD COLUMN IF NOT EXISTS phone VARCHAR(50), ADD COLUMN IF NOT EXISTS plan VARCHAR(50) NOT NULL DEFAULT 'starter' CHECK (plan IN ('starter','growth','enterprise')), ADD COLUMN IF NOT EXISTS "planExpiresAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "suspendedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "suspendedBy" VARCHAR(255), ADD COLUMN IF NOT EXISTS "suspensionReason" TEXT, ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); COMMENT ON TABLE platform.organizations IS 'Merchant accounts — each org is one business on the platform'; COMMENT ON COLUMN platform.organizations."suspendedAt" IS 'NULL = active. Set by Platform Admin / Account Manager'; -- =========================================================================== -- SECTION 3 — STORE SCHEMA: Enhance existing tables -- =========================================================================== -- 3a. store.stores ALTER TABLE store.stores ADD COLUMN IF NOT EXISTS location VARCHAR(500), ADD COLUMN IF NOT EXISTS city VARCHAR(200), ADD COLUMN IF NOT EXISTS state VARCHAR(200), ADD COLUMN IF NOT EXISTS country VARCHAR(10) DEFAULT 'IN', ADD COLUMN IF NOT EXISTS "deactivatedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deactivatedBy" VARCHAR(255), ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); -- 3b. store.store_staff ALTER TABLE store.store_staff ADD COLUMN IF NOT EXISTS "organizationId" VARCHAR(255), ADD COLUMN IF NOT EXISTS "roleId" VARCHAR(255), ADD COLUMN IF NOT EXISTS "inviteEmail" VARCHAR(255), ADD COLUMN IF NOT EXISTS "inviteToken" VARCHAR(255), ADD COLUMN IF NOT EXISTS "inviteExpiresAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "joinedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); COMMENT ON COLUMN store.store_staff."organizationId" IS 'Links staff to their merchant org'; COMMENT ON COLUMN store.store_staff."roleId" IS 'FK to platform.roles — replaces free-text role column'; -- 3c. NEW — store.store_staff_permissions CREATE TABLE IF NOT EXISTS store.store_staff_permissions ( id VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text, "storeStaffId" VARCHAR(255) NOT NULL, "permissionId" VARCHAR(255) NOT NULL, granted BOOLEAN NOT NULL DEFAULT TRUE, "isActive" BOOLEAN NOT NULL DEFAULT TRUE, "sortOrder" INT NOT NULL DEFAULT 0, "createdBy" VARCHAR(255) NOT NULL DEFAULT 'system', "createdOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "updatedBy" VARCHAR(255) NOT NULL DEFAULT 'system', "updatedOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "deletedAt" TIMESTAMPTZ, "deletedBy" VARCHAR(255), CONSTRAINT pk_store_staff_permissions PRIMARY KEY (id), CONSTRAINT uq_staff_permission UNIQUE ("storeStaffId","permissionId"), CONSTRAINT fk_ssp_staff FOREIGN KEY ("storeStaffId") REFERENCES store.store_staff(id) ON DELETE CASCADE, CONSTRAINT fk_ssp_permission FOREIGN KEY ("permissionId") REFERENCES platform.permissions(id) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS idx_ssp_staff_id ON store.store_staff_permissions ("storeStaffId"); CREATE INDEX IF NOT EXISTS idx_ssp_perm_id ON store.store_staff_permissions ("permissionId"); COMMENT ON TABLE store.store_staff_permissions IS 'Per-staff permission overrides — merchant owner manages via Team page'; COMMENT ON COLUMN store.store_staff_permissions.granted IS 'TRUE=extra grant beyond role default. FALSE=explicit revoke'; -- =========================================================================== -- SECTION 4 — ADMIN SCHEMA -- =========================================================================== ALTER TABLE admin.users ADD COLUMN IF NOT EXISTS "organizationId" VARCHAR(255), ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); CREATE INDEX IF NOT EXISTS idx_users_org ON admin.users ("organizationId"); -- =========================================================================== -- SECTION 5 — AUDIT SCHEMA -- =========================================================================== ALTER TABLE audit.change_log ADD COLUMN IF NOT EXISTS "actorRole" VARCHAR(100), ADD COLUMN IF NOT EXISTS "actorName" VARCHAR(300), ADD COLUMN IF NOT EXISTS "ipAddress" VARCHAR(45), ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS "deletedBy" VARCHAR(255); -- =========================================================================== -- SECTION 6 — NEW TABLES -- =========================================================================== -- 6a. platform.platform_settings CREATE TABLE IF NOT EXISTS platform.platform_settings ( id VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text, key VARCHAR(200) NOT NULL, value JSONB, description TEXT, "isActive" BOOLEAN NOT NULL DEFAULT TRUE, "sortOrder" INT NOT NULL DEFAULT 0, "createdBy" VARCHAR(255) NOT NULL DEFAULT 'system', "createdOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "updatedBy" VARCHAR(255) NOT NULL DEFAULT 'system', "updatedOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "deletedAt" TIMESTAMPTZ, "deletedBy" VARCHAR(255), CONSTRAINT pk_platform_settings PRIMARY KEY (id), CONSTRAINT uq_platform_settings_key UNIQUE (key) ); COMMENT ON TABLE platform.platform_settings IS 'Global platform config: feature flags, limits, maintenance mode'; -- 6b. platform.invite_tokens CREATE TABLE IF NOT EXISTS platform.invite_tokens ( id VARCHAR(255) NOT NULL DEFAULT gen_random_uuid()::text, token VARCHAR(255) NOT NULL, "inviteType" VARCHAR(50) NOT NULL CHECK ("inviteType" IN ('staff','merchant')), email VARCHAR(255) NOT NULL, "organizationId" VARCHAR(255), "roleId" VARCHAR(255), "storeId" VARCHAR(255), "invitedBy" VARCHAR(255) NOT NULL, "expiresAt" TIMESTAMPTZ NOT NULL, "acceptedAt" TIMESTAMPTZ, "isActive" BOOLEAN NOT NULL DEFAULT TRUE, "sortOrder" INT NOT NULL DEFAULT 0, "createdBy" VARCHAR(255) NOT NULL DEFAULT 'system', "createdOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "updatedBy" VARCHAR(255) NOT NULL DEFAULT 'system', "updatedOn" TIMESTAMPTZ NOT NULL DEFAULT NOW(), "deletedAt" TIMESTAMPTZ, "deletedBy" VARCHAR(255), CONSTRAINT pk_invite_tokens PRIMARY KEY (id), CONSTRAINT uq_invite_token UNIQUE (token) ); CREATE INDEX IF NOT EXISTS idx_invite_email ON platform.invite_tokens (email); CREATE INDEX IF NOT EXISTS idx_invite_org ON platform.invite_tokens ("organizationId"); COMMENT ON TABLE platform.invite_tokens IS 'Email invite flow for staff onboarding and new merchant registration'; -- =========================================================================== -- SECTION 7 — INDEXES -- =========================================================================== CREATE INDEX IF NOT EXISTS idx_org_active ON platform.organizations ("isActive"); CREATE INDEX IF NOT EXISTS idx_org_plan ON platform.organizations (plan); CREATE INDEX IF NOT EXISTS idx_org_suspended ON platform.organizations ("suspendedAt"); CREATE INDEX IF NOT EXISTS idx_stores_active ON store.stores ("organizationId","isActive"); CREATE INDEX IF NOT EXISTS idx_staff_org ON store.store_staff ("organizationId"); CREATE INDEX IF NOT EXISTS idx_staff_role ON store.store_staff ("roleId"); CREATE INDEX IF NOT EXISTS idx_roles_scope ON platform.roles (scope,"isActive"); CREATE INDEX IF NOT EXISTS idx_perms_scope ON platform.permissions (scope,category); CREATE INDEX IF NOT EXISTS idx_rp_role ON platform.role_permissions ("roleId","isActive"); -- =========================================================================== -- SECTION 8 — SEED: Permissions -- =========================================================================== INSERT INTO platform.permissions (id, code, name, module, scope, category, description, "isActive", "sortOrder", "createdBy", "updatedBy") VALUES (gen_random_uuid()::text,'platform.dashboard', 'Platform Dashboard', 'Overview', 'platform','Overview', 'View platform-wide stats and KPIs', TRUE, 10,'system','system'), (gen_random_uuid()::text,'platform.monitor', 'Live Monitor', 'Overview', 'platform','Overview', 'View real-time status of all stores', TRUE, 20,'system','system'), (gen_random_uuid()::text,'platform.merchants.view', 'View Merchants', 'Merchants','platform','Merchants','View list of merchants and their details', TRUE, 30,'system','system'), (gen_random_uuid()::text,'platform.merchants.edit', 'Edit Merchants', 'Merchants','platform','Merchants','Edit merchant profile and plan', TRUE, 40,'system','system'), (gen_random_uuid()::text,'platform.merchants.suspend', 'Suspend/Activate Merchants', 'Merchants','platform','Merchants','Activate or suspend a merchant and all their stores', TRUE, 50,'system','system'), (gen_random_uuid()::text,'platform.stores.view', 'View All Stores', 'Merchants','platform','Merchants','View all stores across the platform', TRUE, 60,'system','system'), (gen_random_uuid()::text,'platform.stores.activate', 'Toggle Store Status', 'Merchants','platform','Merchants','Activate or deactivate individual stores', TRUE, 70,'system','system'), (gen_random_uuid()::text,'platform.audit', 'View Audit Logs', 'Platform', 'platform','Platform', 'View platform-wide change and interface logs', TRUE, 80,'system','system'), (gen_random_uuid()::text,'platform.settings', 'Platform Settings', 'Platform', 'platform','Platform', 'Manage global platform configuration', TRUE, 90,'system','system'), (gen_random_uuid()::text,'dashboard.view', 'Dashboard', 'Overview', 'merchant','Overview', 'View merchant store dashboard', TRUE,100,'system','system'), (gen_random_uuid()::text,'analytics.view', 'Analytics', 'Overview', 'merchant','Overview', 'View sales analytics and reports', TRUE,110,'system','system'), (gen_random_uuid()::text,'products.view', 'View Products', 'Catalog', 'merchant','Catalog', 'Browse product catalog', TRUE,120,'system','system'), (gen_random_uuid()::text,'products.create', 'Add Products', 'Catalog', 'merchant','Catalog', 'Create new products', TRUE,130,'system','system'), (gen_random_uuid()::text,'products.edit', 'Edit Products', 'Catalog', 'merchant','Catalog', 'Update existing products', TRUE,140,'system','system'), (gen_random_uuid()::text,'products.delete', 'Delete Products', 'Catalog', 'merchant','Catalog', 'Remove products from catalog', TRUE,150,'system','system'), (gen_random_uuid()::text,'categories.view', 'View Categories', 'Catalog', 'merchant','Catalog', 'Browse category tree', TRUE,160,'system','system'), (gen_random_uuid()::text,'categories.edit', 'Manage Categories', 'Catalog', 'merchant','Catalog', 'Create, edit, reorder categories', TRUE,170,'system','system'), (gen_random_uuid()::text,'orders.view', 'View Orders', 'Sales', 'merchant','Sales', 'View order list and details', TRUE,180,'system','system'), (gen_random_uuid()::text,'orders.edit', 'Process Orders', 'Sales', 'merchant','Sales', 'Update order status and fulfillment', TRUE,190,'system','system'), (gen_random_uuid()::text,'orders.refund', 'Issue Refunds', 'Sales', 'merchant','Sales', 'Create refunds on orders', TRUE,200,'system','system'), (gen_random_uuid()::text,'stock.view', 'View Inventory', 'Sales', 'merchant','Sales', 'View stock levels', TRUE,210,'system','system'), (gen_random_uuid()::text,'stock.edit', 'Update Stock', 'Sales', 'merchant','Sales', 'Adjust inventory quantities', TRUE,220,'system','system'), (gen_random_uuid()::text,'customers.view', 'View Customers', 'Sales', 'merchant','Sales', 'Browse customer list and profiles', TRUE,230,'system','system'), (gen_random_uuid()::text,'discounts.view', 'View Discounts', 'Finance', 'merchant','Finance', 'View discount codes and promotions', TRUE,240,'system','system'), (gen_random_uuid()::text,'discounts.edit', 'Manage Discounts', 'Finance', 'merchant','Finance', 'Create and edit discount codes', TRUE,250,'system','system'), (gen_random_uuid()::text,'tax.view', 'View Tax Settings', 'Finance', 'merchant','Finance', 'View tax configuration', TRUE,260,'system','system'), (gen_random_uuid()::text,'tax.edit', 'Manage Tax', 'Finance', 'merchant','Finance', 'Update tax rules and rates', TRUE,270,'system','system'), (gen_random_uuid()::text,'shipping.view', 'View Shipping', 'Finance', 'merchant','Finance', 'View shipping zones and rates', TRUE,280,'system','system'), (gen_random_uuid()::text,'shipping.edit', 'Manage Shipping', 'Finance', 'merchant','Finance', 'Configure shipping zones and rates', TRUE,290,'system','system'), (gen_random_uuid()::text,'team.view', 'View Team', 'Team', 'merchant','Team', 'View staff members and their roles', TRUE,300,'system','system'), (gen_random_uuid()::text,'team.edit', 'Manage Team', 'Team', 'merchant','Team', 'Invite staff, assign roles and permissions', TRUE,310,'system','system') ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, module = EXCLUDED.module, scope = EXCLUDED.scope, category = EXCLUDED.category, description = EXCLUDED.description, "updatedOn" = NOW(); -- =========================================================================== -- SECTION 9 — SEED: Roles -- =========================================================================== INSERT INTO platform.roles (id, code, name, scope, description, "isSystem", "isActive", "sortOrder", "createdBy", "updatedBy") VALUES (gen_random_uuid()::text,'platform_admin', 'Platform Admin', 'platform','Full control over Shopeee platform and all merchants', TRUE,TRUE,10,'system','system'), (gen_random_uuid()::text,'account_manager','Account Manager', 'platform','Manage merchants: view, edit, activate and suspend', TRUE,TRUE,20,'system','system'), (gen_random_uuid()::text,'operator', 'Operator', 'platform','Read-only monitoring: live status, dashboards, audit logs', TRUE,TRUE,30,'system','system'), (gen_random_uuid()::text,'merchant', 'Merchant', 'merchant','Store owner — full control over their stores and team', TRUE,TRUE,40,'system','system'), (gen_random_uuid()::text,'store_manager', 'Store Manager', 'merchant','Manage day-to-day operations: products, orders, stock', TRUE,TRUE,50,'system','system'), (gen_random_uuid()::text,'accountant', 'Accountant', 'merchant','Finance view: orders (read-only), discounts, tax', TRUE,TRUE,60,'system','system') ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, scope = EXCLUDED.scope, description = EXCLUDED.description, "isSystem" = EXCLUDED."isSystem", "updatedOn" = NOW(); -- =========================================================================== -- SECTION 10 — SEED: Role-Permission mapping -- =========================================================================== INSERT INTO platform.role_permissions (id, "roleId", "permissionId", "isActive", "sortOrder", "createdBy", "updatedBy") SELECT gen_random_uuid()::text, r.id, p.id, TRUE, 0, 'system', 'system' FROM (VALUES -- platform_admin gets everything ('platform_admin','platform.dashboard'),('platform_admin','platform.monitor'), ('platform_admin','platform.merchants.view'),('platform_admin','platform.merchants.edit'), ('platform_admin','platform.merchants.suspend'),('platform_admin','platform.stores.view'), ('platform_admin','platform.stores.activate'),('platform_admin','platform.audit'), ('platform_admin','platform.settings'), ('platform_admin','dashboard.view'),('platform_admin','analytics.view'), ('platform_admin','products.view'),('platform_admin','products.create'), ('platform_admin','products.edit'),('platform_admin','products.delete'), ('platform_admin','categories.view'),('platform_admin','categories.edit'), ('platform_admin','orders.view'),('platform_admin','orders.edit'),('platform_admin','orders.refund'), ('platform_admin','stock.view'),('platform_admin','stock.edit'),('platform_admin','customers.view'), ('platform_admin','discounts.view'),('platform_admin','discounts.edit'), ('platform_admin','tax.view'),('platform_admin','tax.edit'), ('platform_admin','shipping.view'),('platform_admin','shipping.edit'), ('platform_admin','team.view'),('platform_admin','team.edit'), -- account_manager ('account_manager','platform.dashboard'),('account_manager','platform.merchants.view'), ('account_manager','platform.merchants.edit'),('account_manager','platform.merchants.suspend'), ('account_manager','platform.stores.view'),('account_manager','platform.stores.activate'), -- operator ('operator','platform.dashboard'),('operator','platform.monitor'),('operator','platform.audit'), -- merchant ('merchant','dashboard.view'),('merchant','analytics.view'), ('merchant','products.view'),('merchant','products.create'), ('merchant','products.edit'),('merchant','products.delete'), ('merchant','categories.view'),('merchant','categories.edit'), ('merchant','orders.view'),('merchant','orders.edit'),('merchant','orders.refund'), ('merchant','stock.view'),('merchant','stock.edit'),('merchant','customers.view'), ('merchant','discounts.view'),('merchant','discounts.edit'), ('merchant','tax.view'),('merchant','tax.edit'), ('merchant','shipping.view'),('merchant','shipping.edit'), ('merchant','team.view'),('merchant','team.edit'), -- store_manager ('store_manager','dashboard.view'),('store_manager','products.view'), ('store_manager','categories.view'),('store_manager','orders.view'), ('store_manager','orders.edit'),('store_manager','stock.view'), ('store_manager','stock.edit'),('store_manager','customers.view'), -- accountant ('accountant','dashboard.view'),('accountant','analytics.view'), ('accountant','orders.view'),('accountant','discounts.view'), ('accountant','tax.view'),('accountant','customers.view') ) AS mapping(role_code, perm_code) JOIN platform.roles r ON r.code = mapping.role_code JOIN platform.permissions p ON p.code = mapping.perm_code ON CONFLICT ("roleId","permissionId") DO UPDATE SET "isActive" = TRUE, "updatedOn" = NOW(); COMMIT; -- =========================================================================== -- VERIFICATION (run separately after migration) -- =========================================================================== -- SELECT r.code, r.scope, COUNT(rp.id) AS permission_count -- FROM platform.roles r -- LEFT JOIN platform.role_permissions rp ON rp."roleId" = r.id AND rp."isActive" -- GROUP BY r.code, r.scope ORDER BY r."sortOrder";