Intentional Denormalization

-- Normalized source of truth
CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (customer_id),
  status      text NOT NULL
);
 
CREATE TABLE customers (
  customer_id  bigint PRIMARY KEY,
  email        text NOT NULL
);
 
-- Denormalized read copy on orders (document the invariant)
ALTER TABLE orders ADD COLUMN customer_email text;
 
CREATE OR REPLACE FUNCTION sync_order_customer_email()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  SELECT email INTO NEW.customer_email FROM customers WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER orders_customer_email_trg
  BEFORE INSERT OR UPDATE OF customer_id ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_order_customer_email();

BEGIN;
 
CREATE TABLE products (
  product_id  bigint PRIMARY KEY,
  sku         text NOT NULL,
  name        text NOT NULL,
  price       numeric(12, 2) NOT NULL
);
 
CREATE TABLE orders (
  order_id  bigint PRIMARY KEY,
  placed_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
  order_id      bigint NOT NULL REFERENCES orders (order_id),
  product_id    bigint NOT NULL REFERENCES products (product_id),
  quantity      integer NOT NULL CHECK (quantity > 0),
  -- Snapshot denorm: catalog price may change later
  unit_price    numeric(12, 2) NOT NULL,
  product_name  text NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
 
-- Backfill denorm columns on insert via trigger
CREATE OR REPLACE FUNCTION order_items_snapshot_product()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  p products%ROWTYPE;
BEGIN
  SELECT * INTO p FROM products WHERE product_id = NEW.product_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'product % not found', NEW.product_id;
  END IF;
  NEW.unit_price := COALESCE(NEW.unit_price, p.price);
  NEW.product_name := COALESCE(NEW.product_name, p.name);
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER order_items_snapshot_trg
  BEFORE INSERT ON order_items
  FOR EACH ROW EXECUTE FUNCTION order_items_snapshot_product();
 
INSERT INTO products (product_id, sku, name, price)
VALUES (1, 'A1', 'Alpha', 19.99);
 
INSERT INTO orders (order_id) VALUES (100);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, product_name)
VALUES (100, 1, 2, 19.99, 'Alpha');
 
-- Catalog rename does not rewrite historical line items
UPDATE products SET name = 'Alpha Pro' WHERE product_id = 1;
 
SELECT product_name, unit_price FROM order_items WHERE order_id = 100;
 
COMMIT;

-- Drift detection: denorm email should match customer unless snapshot rules say otherwise
SELECT o.order_id, o.customer_email, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_email IS DISTINCT FROM c.email;