Implement Schema, Indexes, Partitioning, Security, RLS, and Migration Automation

CREATE ROLE servicehub_cap_owner NOLOGIN;CREATE ROLE servicehub_cap_app NOLOGIN;CREATE ROLE servicehub_cap_migrator LOGIN NOINHERIT;CREATE ROLE servicehub_cap_tenant_a LOGIN IN ROLE servicehub_cap_app;CREATE ROLE servicehub_cap_tenant_b LOGIN IN ROLE servicehub_cap_app;CREATE ROLE servicehub_cap_repl LOGIN REPLICATION;GRANT servicehub_cap_owner TO servicehub_cap_migrator;ALTER DATABASE servicehub_capstone OWNER TO servicehub_cap_owner;REVOKE CREATE ON SCHEMA public FROM PUBLIC;REVOKE ALL ON DATABASE servicehub_capstone FROM PUBLIC;GRANT CONNECT ON DATABASE servicehub_capstoneTO servicehub_cap_app, servicehub_cap_migrator;

\password servicehub_cap_migrator\password servicehub_cap_tenant_a\password servicehub_cap_tenant_b\password servicehub_cap_repl

SET ROLE servicehub_cap_owner;CREATE SCHEMA app AUTHORIZATION servicehub_cap_owner;CREATE SCHEMA auth AUTHORIZATION servicehub_cap_owner;CREATE SCHEMA ops AUTHORIZATION servicehub_cap_owner;CREATE SCHEMA extensions AUTHORIZATION servicehub_cap_owner;ALTER DATABASE servicehub_capstoneSET search_path = pg_catalog, app;RESET ROLE;GRANT USAGE ON SCHEMA app, auth TO servicehub_cap_app;ALTER ROLE servicehub_cap_migrator IN DATABASE servicehub_capstoneSET search_path = pg_catalog, app;

SET ROLE servicehub_cap_owner;CREATE TABLE app.tenants (  tenant_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  tenant_code text NOT NULL UNIQUE,  display_name text NOT NULL,  active boolean NOT NULL DEFAULT true,  created_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE TABLE app.customers (  tenant_id bigint NOT NULL REFERENCES app.tenants(tenant_id),  customer_id bigint GENERATED BY DEFAULT AS IDENTITY,  external_ref text NOT NULL,  display_name text NOT NULL,  created_at timestamptz NOT NULL DEFAULT clock_timestamp(),  PRIMARY KEY (tenant_id, customer_id),  UNIQUE (tenant_id, external_ref));CREATE TABLE app.work_orders (  tenant_id bigint NOT NULL,  work_order_id bigint GENERATED BY DEFAULT AS IDENTITY,  customer_id bigint NOT NULL,  external_ref text NOT NULL,  status text NOT NULL CHECK (status IN    ('queued','assigned','in_progress','completed','cancelled')),  scheduled_at timestamptz NOT NULL,  amount numeric(12,2) NOT NULL CHECK (amount >= 0),  note text NOT NULL DEFAULT '',  created_at timestamptz NOT NULL DEFAULT clock_timestamp(),  updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),  PRIMARY KEY (tenant_id, work_order_id),  UNIQUE (tenant_id, external_ref),  FOREIGN KEY (tenant_id, customer_id)    REFERENCES app.customers(tenant_id, customer_id));

CREATE TABLE app.work_order_events (  tenant_id bigint NOT NULL,  event_id bigint GENERATED BY DEFAULT AS IDENTITY,  work_order_id bigint NOT NULL,  event_type text NOT NULL,  occurred_at timestamptz NOT NULL,  payload jsonb NOT NULL DEFAULT '{}'::jsonb,  PRIMARY KEY (tenant_id, event_id, occurred_at),  FOREIGN KEY (tenant_id, work_order_id)    REFERENCES app.work_orders(tenant_id, work_order_id)) PARTITION BY RANGE (occurred_at);CREATE TABLE app.work_order_events_2026_08PARTITION OF app.work_order_eventsFOR VALUES FROM ('2026-08-01 00:00+00') TO ('2026-09-01 00:00+00');CREATE TABLE app.work_order_events_2026_09PARTITION OF app.work_order_eventsFOR VALUES FROM ('2026-09-01 00:00+00') TO ('2026-10-01 00:00+00');CREATE TABLE app.work_order_events_2026_10PARTITION OF app.work_order_eventsFOR VALUES FROM ('2026-10-01 00:00+00') TO ('2026-11-01 00:00+00');CREATE INDEX work_orders_queue_idxON app.work_orders (tenant_id, status, scheduled_at);CREATE INDEX work_order_events_lookup_idxON app.work_order_events (tenant_id, work_order_id, occurred_at DESC);RESET ROLE;

SELECT inhparent::regclass AS parent,       inhrelid::regclass AS partitionFROM pg_inheritsWHERE inhparent = 'app.work_order_events'::regclassORDER BY inhrelid::regclass::text;EXPLAIN (COSTS OFF)SELECT tenant_id, work_order_id, event_type, occurred_atFROM app.work_order_eventsWHERE occurred_at >= TIMESTAMPTZ '2026-09-10 00:00+00'  AND occurred_at <  TIMESTAMPTZ '2026-09-11 00:00+00';

SET ROLE servicehub_cap_owner;CREATE TABLE auth.tenant_role_map (  role_name name PRIMARY KEY,  tenant_id bigint NOT NULL UNIQUE);CREATE OR REPLACE FUNCTION auth.current_tenant_id()RETURNS bigintLANGUAGE sqlSTABLESECURITY DEFINERSET search_path = pg_catalog, authAS $$  SELECT m.tenant_id  FROM auth.tenant_role_map AS m  WHERE m.role_name = session_user$$;REVOKE ALL ON FUNCTION auth.current_tenant_id() FROM PUBLIC;GRANT EXECUTE ON FUNCTION auth.current_tenant_id() TO servicehub_cap_app;RESET ROLE;

SET ROLE servicehub_cap_owner;INSERT INTO app.tenants(tenant_code,display_name)VALUES ('tenant-a','Tenant A'),('tenant-b','Tenant B');INSERT INTO auth.tenant_role_map(role_name,tenant_id)SELECT 'servicehub_cap_tenant_a', tenant_idFROM app.tenants WHERE tenant_code='tenant-a'UNION ALLSELECT 'servicehub_cap_tenant_b', tenant_idFROM app.tenants WHERE tenant_code='tenant-b';ALTER TABLE app.tenants ENABLE ROW LEVEL SECURITY;ALTER TABLE app.customers ENABLE ROW LEVEL SECURITY;ALTER TABLE app.work_orders ENABLE ROW LEVEL SECURITY;ALTER TABLE app.work_order_events ENABLE ROW LEVEL SECURITY;CREATE POLICY tenant_rows_tenants ON app.tenantsUSING (tenant_id = auth.current_tenant_id());CREATE POLICY tenant_rows_customers ON app.customersUSING (tenant_id = auth.current_tenant_id())WITH CHECK (tenant_id = auth.current_tenant_id());CREATE POLICY tenant_rows_orders ON app.work_ordersUSING (tenant_id = auth.current_tenant_id())WITH CHECK (tenant_id = auth.current_tenant_id());CREATE POLICY tenant_rows_events ON app.work_order_eventsUSING (tenant_id = auth.current_tenant_id())WITH CHECK (tenant_id = auth.current_tenant_id());GRANT SELECT ON app.tenants TO servicehub_cap_app;GRANT SELECT,INSERT,UPDATE,DELETE ON app.customers,app.work_orders,app.work_order_eventsTO servicehub_cap_app;GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA app TO servicehub_cap_app;ALTER DEFAULT PRIVILEGES FOR ROLE servicehub_cap_owner IN SCHEMA appGRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO servicehub_cap_app;ALTER DEFAULT PRIVILEGES FOR ROLE servicehub_cap_owner IN SCHEMA appGRANT USAGE,SELECT ON SEQUENCES TO servicehub_cap_app;RESET ROLE;

-- Run as the local lab superuser.SET SESSION AUTHORIZATION servicehub_cap_tenant_a;INSERT INTO app.customers(tenant_id,external_ref,display_name)SELECT auth.current_tenant_id(),'A-001','Alice';SELECT tenant_id,external_ref,display_nameFROM app.customers;-- Deliberately try to insert Tenant B's ID.INSERT INTO app.customers(tenant_id,external_ref,display_name)SELECT tenant_id,'FORGED','Should fail'FROM app.tenantsWHERE tenant_code='tenant-b';RESET SESSION AUTHORIZATION;

SHOW ssl;SELECT pid, ssl, version, cipher, bits, client_dn, issuer_dnFROM pg_stat_sslWHERE pid = pg_backend_pid();

SELECT name,default_version,installed_version,commentFROM pg_available_extensionsORDER BY name;SELECT name,version,installed,superuser,trusted,       relocatable,schema,requiresFROM pg_available_extension_versionsORDER BY name,version;SELECT extname,extversion,extnamespace::regnamespaceFROM pg_extensionORDER BY extname;

SET ROLE servicehub_cap_owner;SET lock_timeout = '2s';ALTER TABLE app.work_ordersADD COLUMN priority smallint;ALTER TABLE app.work_ordersADD CONSTRAINT work_orders_priority_validCHECK (priority BETWEEN 1 AND 5) NOT VALID;RESET lock_timeout;-- Bounded, idempotent batches; repeat until zero rows returned.WITH batch AS (  SELECT ctid  FROM app.work_orders  WHERE priority IS NULL  ORDER BY tenant_id, work_order_id  LIMIT 5000  FOR UPDATE SKIP LOCKED)UPDATE app.work_orders AS wSET priority = 3FROM batchWHERE w.ctid = batch.ctid;ALTER TABLE app.work_ordersVALIDATE CONSTRAINT work_orders_priority_valid;RESET ROLE;

SET ROLE servicehub_cap_owner;SET lock_timeout = '2s';CREATE INDEX CONCURRENTLY work_orders_priority_schedule_idxON app.work_orders (tenant_id, priority, scheduled_at)WHERE status IN ('queued','assigned','in_progress');RESET lock_timeout;RESET ROLE;

SELECT n.nspname, r.rolname AS ownerFROM pg_namespace AS nJOIN pg_roles AS r ON r.oid=n.nspownerWHERE n.nspname IN ('app','auth','ops','extensions')ORDER BY n.nspname;SELECT relname, relrowsecurity, relforcerowsecurityFROM pg_classWHERE relnamespace='app'::regnamespace  AND relkind IN ('r','p')ORDER BY relname;SELECT schemaname, tablename, policyname, roles, cmdFROM pg_policiesWHERE schemaname='app'ORDER BY tablename,policyname;SELECT indexrelid::regclass, indisvalid, indisreadyFROM pg_indexWHERE indrelid='app.work_orders'::regclassORDER BY indexrelid::regclass::text;