Paul Trowbridge
39335bca75
Forecast falls back to a saved per-source layout when no version-local layout is cached, so new versions of a source open with a sensible pivot without each user reconfiguring it. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
66 lines
2.5 KiB
SQL
66 lines
2.5 KiB
SQL
-- Pivot Forecast schema install
|
|
-- Run once against target database: psql -d <db> -f setup_sql/01_schema.sql
|
|
|
|
CREATE SCHEMA IF NOT EXISTS pf;
|
|
|
|
CREATE TABLE IF NOT EXISTS pf.source (
|
|
id serial PRIMARY KEY,
|
|
schema text NOT NULL,
|
|
tname text NOT NULL,
|
|
label text,
|
|
status text NOT NULL DEFAULT 'active', -- active | archived
|
|
default_layout jsonb, -- Perspective view config used as the per-source default
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by text,
|
|
UNIQUE (schema, tname)
|
|
);
|
|
|
|
-- backfill column for existing installs
|
|
ALTER TABLE pf.source ADD COLUMN IF NOT EXISTS default_layout jsonb;
|
|
|
|
CREATE TABLE IF NOT EXISTS pf.col_meta (
|
|
id serial PRIMARY KEY,
|
|
source_id integer NOT NULL REFERENCES pf.source(id) ON DELETE CASCADE,
|
|
cname text NOT NULL,
|
|
label text,
|
|
role text NOT NULL DEFAULT 'ignore', -- dimension | value | units | date | ignore
|
|
is_key boolean NOT NULL DEFAULT false, -- true = usable in WHERE slice
|
|
opos integer,
|
|
UNIQUE (source_id, cname)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pf.version (
|
|
id serial PRIMARY KEY,
|
|
source_id integer NOT NULL REFERENCES pf.source(id) ON DELETE RESTRICT,
|
|
name text NOT NULL,
|
|
description text,
|
|
status text NOT NULL DEFAULT 'open', -- open | closed
|
|
exclude_iters jsonb NOT NULL DEFAULT '["reference"]'::jsonb,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
created_by text,
|
|
closed_at timestamptz,
|
|
closed_by text,
|
|
UNIQUE (source_id, name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS pf.log (
|
|
id bigserial PRIMARY KEY,
|
|
version_id integer NOT NULL REFERENCES pf.version(id) ON DELETE CASCADE,
|
|
pf_user text NOT NULL,
|
|
stamp timestamptz NOT NULL DEFAULT now(),
|
|
operation text NOT NULL, -- baseline | reference | scale | recode | clone
|
|
slice jsonb,
|
|
params jsonb,
|
|
note text
|
|
);
|
|
|
|
-- generated operation SQL per source, stored after col_meta is configured
|
|
CREATE TABLE IF NOT EXISTS pf.sql (
|
|
id serial PRIMARY KEY,
|
|
source_id integer NOT NULL REFERENCES pf.source(id) ON DELETE CASCADE,
|
|
operation text NOT NULL, -- get_data | baseline | reference | scale | recode | clone | undo
|
|
sql text NOT NULL,
|
|
generated_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (source_id, operation)
|
|
);
|