Schema Patterns: Soft Delete, History, Temporal, Audit and Event Sourcing

-- soft delete with a partial unique index
ALTER TABLE app_user ADD COLUMN deleted_at timestamptz;
CREATE UNIQUE INDEX app_user_email_live ON app_user (lower(email)) WHERE deleted_at IS NULL;
CREATE VIEW live_user AS SELECT * FROM app_user WHERE deleted_at IS NULL;

-- audit trigger (generic, JSONB before/after)
CREATE TABLE audit_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text NOT NULL, row_pk text NOT NULL, action text NOT NULL,
  actor text NOT NULL DEFAULT current_setting('app.user_id', true),
  request_id text DEFAULT current_setting('app.request_id', true),
  at timestamptz NOT NULL DEFAULT now(), before jsonb, after jsonb
);
CREATE FUNCTION audit() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log (table_name, row_pk, action, before, after)
  VALUES (TG_TABLE_NAME, coalesce(NEW.id, OLD.id)::text, TG_OP,
          CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
          CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
  RETURN coalesce(NEW, OLD);
END $$;
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit();

-- temporal prices without overlaps
CREATE TABLE product_price (
  product_id bigint NOT NULL, price numeric(10,2) NOT NULL, valid tstzrange NOT NULL,
  EXCLUDE USING gist (product_id WITH =, valid WITH &&)
);
SELECT price FROM product_price WHERE product_id = 42 AND valid @> '2026-03-01'::timestamptz;