26 lines
745 B
PL/PgSQL
26 lines
745 B
PL/PgSQL
-- Versioned snapshots per region/permit/risk tuple.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS public.permit_risk_snapshots (
|
|
snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
region_id uuid NOT NULL,
|
|
permit_id uuid NOT NULL,
|
|
risk_id uuid NOT NULL,
|
|
permit_risk_key text NOT NULL,
|
|
version integer NOT NULL,
|
|
payload jsonb NOT NULL,
|
|
edited_by text,
|
|
change_summary text,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS permit_risk_snapshots_region_permit_risk_version_idx
|
|
ON public.permit_risk_snapshots (region_id, permit_id, risk_id, version);
|
|
|
|
CREATE INDEX IF NOT EXISTS permit_risk_snapshots_key_idx
|
|
ON public.permit_risk_snapshots (permit_risk_key);
|
|
|
|
COMMIT;
|
|
|