Domain-Driven Schema Boundaries

CREATE SCHEMA identity;
CREATE SCHEMA billing;
CREATE SCHEMA catalog;
 
REVOKE ALL ON SCHEMA billing FROM PUBLIC;
GRANT USAGE ON SCHEMA billing TO billing_app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA billing TO billing_app_role;
 
CREATE TABLE identity.users (
  user_id    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      text NOT NULL UNIQUE
);
 
CREATE TABLE billing.subscriptions (
  subscription_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id          uuid NOT NULL,  -- logical reference; FK optional across contexts
  plan_code        text NOT NULL
);

BEGIN;
 
CREATE SCHEMA IF NOT EXISTS identity;
CREATE SCHEMA IF NOT EXISTS catalog;
CREATE SCHEMA IF NOT EXISTS fulfillment;
 
CREATE ROLE storefront_app LOGIN PASSWORD 'replace-me';
CREATE ROLE fulfillment_app LOGIN PASSWORD 'replace-me';
 
CREATE TABLE identity.customers (
  customer_id  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email        text NOT NULL UNIQUE,
  created_at   timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE catalog.products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL,
  price       numeric(12, 2) NOT NULL CHECK (price >= 0)
);
 
CREATE TABLE fulfillment.orders (
  order_id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id   uuid NOT NULL,
  product_id    bigint NOT NULL REFERENCES catalog.products (product_id),
  quantity      integer NOT NULL CHECK (quantity > 0),
  status        text NOT NULL DEFAULT 'pending',
  created_at    timestamptz NOT NULL DEFAULT now()
);
 
-- Cross-context FK only where coupling is intentional
-- identity.customers is referenced logically by customer_id (no FK to avoid tight coupling)
 
REVOKE ALL ON SCHEMA identity, catalog, fulfillment FROM PUBLIC;
 
GRANT USAGE ON SCHEMA identity, catalog TO storefront_app;
GRANT SELECT, INSERT, UPDATE ON identity.customers TO storefront_app;
GRANT SELECT ON catalog.products TO storefront_app;
GRANT INSERT ON fulfillment.orders TO storefront_app;
 
GRANT USAGE ON SCHEMA catalog, fulfillment TO fulfillment_app;
GRANT SELECT ON catalog.products TO fulfillment_app;
GRANT SELECT, UPDATE ON fulfillment.orders TO fulfillment_app;
 
ALTER ROLE storefront_app SET search_path = identity, catalog, fulfillment, public;
ALTER ROLE fulfillment_app SET search_path = fulfillment, catalog, public;
 
COMMIT;

-- List tables by schema
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2;
 
-- Move a misplaced table into the right context
ALTER TABLE public.invoices SET SCHEMA billing;