Paul Trowbridge
d1197df7d5
setup_sql/01_schema.sql was an idempotent bootstrap script — CREATE TABLE IF NOT EXISTS plus a tail of ALTER ... ADD COLUMN IF NOT EXISTS. It had no record of what any given database had applied, which is how a branch could declare col_meta.in_grain while the running database lacked it, with nothing able to detect the mismatch. The symptom would have been a confusing 'column "in_grain" does not exist' inside an unrelated request. Migrations-only, no hand-maintained current-state file to drift: - setup_sql/migrations/*.sql applied in filename order, recorded in pf.schema_version with a checksum. Split along the schema's actual evolution, so each column is declared exactly once — 01_schema.sql had grown to declare dim_group, dim_period_col and in_grain twice each. - lib/migrations.js holds the bookkeeping, shared by the CLI and the boot check. scripts/migrate.js provides up | status | baseline. - server.js refuses to start when the database is behind, listing what is pending. This converts silent drift into a clear boot message, which was the whole point. PF_SKIP_MIGRATION_CHECK=1 bypasses. - Four integrity guards, each verified to fire: a migration modified after being applied, one recorded as applied but missing from disk, one that would apply out of order, and a re-run when already current. - No IF NOT EXISTS on new migrations. The bookkeeping already guarantees one run each, and the guards hide ordering mistakes — that is exactly why 01_schema.sql had ALTERs sitting above the CREATE TABLE they depended on, broken for anyone installing from scratch. 0004 keeps the guard only because it was applied by hand before migrations existed. Verified: replaying all four migrations into a throwaway schema reproduces the live pf schema exactly, 41 columns, column for column. schema.generated.sql is a pg_dump snapshot for reading, refreshed by npm run schema:dump. It excludes the runtime fc_* tables and strips pg_dump's random \restrict token and version banner, so regenerating an unchanged schema yields an identical file rather than a spurious diff. pf.dim_period stays out of migrations — it is a parameterised data load (fiscal year start month), not a schema change. The dev database (ubm) has been baselined at all four migrations. Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
385 lines
8.6 KiB
SQL
385 lines
8.6 KiB
SQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET transaction_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
--
|
|
-- Name: pf; Type: SCHEMA; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE SCHEMA pf;
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: col_meta; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.col_meta (
|
|
id integer NOT NULL,
|
|
source_id integer NOT NULL,
|
|
cname text NOT NULL,
|
|
label text,
|
|
role text DEFAULT 'ignore'::text NOT NULL,
|
|
is_key boolean DEFAULT false NOT NULL,
|
|
opos integer,
|
|
dim_group text,
|
|
dim_period_col text,
|
|
in_grain boolean DEFAULT false NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Name: col_meta_id_seq; Type: SEQUENCE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE pf.col_meta_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: col_meta_id_seq; Type: SEQUENCE OWNED BY; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE pf.col_meta_id_seq OWNED BY pf.col_meta.id;
|
|
|
|
--
|
|
-- Name: dim_period; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.dim_period (
|
|
sdat date NOT NULL,
|
|
edat date NOT NULL,
|
|
drange daterange NOT NULL,
|
|
ndays integer NOT NULL,
|
|
cal_year integer NOT NULL,
|
|
cal_quarter integer NOT NULL,
|
|
cal_month integer NOT NULL,
|
|
cal_month_abbr text NOT NULL,
|
|
cal_month_name text NOT NULL,
|
|
cal_label text NOT NULL,
|
|
fisc_year integer NOT NULL,
|
|
fisc_quarter integer NOT NULL,
|
|
fisc_quarter_label text NOT NULL,
|
|
fisc_month integer NOT NULL,
|
|
fisc_month_abbr text NOT NULL,
|
|
fisc_month_name text NOT NULL,
|
|
fisc_label text NOT NULL,
|
|
period_key text NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Name: log; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.log (
|
|
id bigint NOT NULL,
|
|
version_id integer NOT NULL,
|
|
pf_user text NOT NULL,
|
|
stamp timestamp with time zone DEFAULT now() NOT NULL,
|
|
operation text NOT NULL,
|
|
slice jsonb,
|
|
params jsonb,
|
|
note text
|
|
);
|
|
|
|
--
|
|
-- Name: log_id_seq; Type: SEQUENCE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE pf.log_id_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: log_id_seq; Type: SEQUENCE OWNED BY; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE pf.log_id_seq OWNED BY pf.log.id;
|
|
|
|
--
|
|
-- Name: schema_version; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.schema_version (
|
|
filename text NOT NULL,
|
|
checksum text NOT NULL,
|
|
applied_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
applied_by text
|
|
);
|
|
|
|
--
|
|
-- Name: source; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.source (
|
|
id integer NOT NULL,
|
|
schema text NOT NULL,
|
|
tname text NOT NULL,
|
|
label text,
|
|
status text DEFAULT 'active'::text NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
created_by text,
|
|
default_layout jsonb
|
|
);
|
|
|
|
--
|
|
-- Name: source_id_seq; Type: SEQUENCE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE pf.source_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: source_id_seq; Type: SEQUENCE OWNED BY; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE pf.source_id_seq OWNED BY pf.source.id;
|
|
|
|
--
|
|
-- Name: sql; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.sql (
|
|
id integer NOT NULL,
|
|
source_id integer NOT NULL,
|
|
operation text NOT NULL,
|
|
sql text NOT NULL,
|
|
generated_at timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
--
|
|
-- Name: sql_id_seq; Type: SEQUENCE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE pf.sql_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: sql_id_seq; Type: SEQUENCE OWNED BY; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE pf.sql_id_seq OWNED BY pf.sql.id;
|
|
|
|
--
|
|
-- Name: version; Type: TABLE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE TABLE pf.version (
|
|
id integer NOT NULL,
|
|
source_id integer NOT NULL,
|
|
name text NOT NULL,
|
|
description text,
|
|
status text DEFAULT 'open'::text NOT NULL,
|
|
exclude_iters jsonb DEFAULT '["reference"]'::jsonb NOT NULL,
|
|
created_at timestamp with time zone DEFAULT now() NOT NULL,
|
|
created_by text,
|
|
closed_at timestamp with time zone,
|
|
closed_by text
|
|
);
|
|
|
|
--
|
|
-- Name: version_id_seq; Type: SEQUENCE; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE SEQUENCE pf.version_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
--
|
|
-- Name: version_id_seq; Type: SEQUENCE OWNED BY; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER SEQUENCE pf.version_id_seq OWNED BY pf.version.id;
|
|
|
|
--
|
|
-- Name: col_meta id; Type: DEFAULT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.col_meta ALTER COLUMN id SET DEFAULT nextval('pf.col_meta_id_seq'::regclass);
|
|
|
|
--
|
|
-- Name: log id; Type: DEFAULT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.log ALTER COLUMN id SET DEFAULT nextval('pf.log_id_seq'::regclass);
|
|
|
|
--
|
|
-- Name: source id; Type: DEFAULT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.source ALTER COLUMN id SET DEFAULT nextval('pf.source_id_seq'::regclass);
|
|
|
|
--
|
|
-- Name: sql id; Type: DEFAULT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.sql ALTER COLUMN id SET DEFAULT nextval('pf.sql_id_seq'::regclass);
|
|
|
|
--
|
|
-- Name: version id; Type: DEFAULT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.version ALTER COLUMN id SET DEFAULT nextval('pf.version_id_seq'::regclass);
|
|
|
|
--
|
|
-- Name: col_meta col_meta_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.col_meta
|
|
ADD CONSTRAINT col_meta_pkey PRIMARY KEY (id);
|
|
|
|
--
|
|
-- Name: col_meta col_meta_source_id_cname_key; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.col_meta
|
|
ADD CONSTRAINT col_meta_source_id_cname_key UNIQUE (source_id, cname);
|
|
|
|
--
|
|
-- Name: dim_period dim_period_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.dim_period
|
|
ADD CONSTRAINT dim_period_pkey PRIMARY KEY (sdat);
|
|
|
|
--
|
|
-- Name: log log_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.log
|
|
ADD CONSTRAINT log_pkey PRIMARY KEY (id);
|
|
|
|
--
|
|
-- Name: schema_version schema_version_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.schema_version
|
|
ADD CONSTRAINT schema_version_pkey PRIMARY KEY (filename);
|
|
|
|
--
|
|
-- Name: source source_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.source
|
|
ADD CONSTRAINT source_pkey PRIMARY KEY (id);
|
|
|
|
--
|
|
-- Name: source source_schema_tname_key; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.source
|
|
ADD CONSTRAINT source_schema_tname_key UNIQUE (schema, tname);
|
|
|
|
--
|
|
-- Name: sql sql_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.sql
|
|
ADD CONSTRAINT sql_pkey PRIMARY KEY (id);
|
|
|
|
--
|
|
-- Name: sql sql_source_id_operation_key; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.sql
|
|
ADD CONSTRAINT sql_source_id_operation_key UNIQUE (source_id, operation);
|
|
|
|
--
|
|
-- Name: version version_pkey; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.version
|
|
ADD CONSTRAINT version_pkey PRIMARY KEY (id);
|
|
|
|
--
|
|
-- Name: version version_source_id_name_key; Type: CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.version
|
|
ADD CONSTRAINT version_source_id_name_key UNIQUE (source_id, name);
|
|
|
|
--
|
|
-- Name: dim_period_cal_idx; Type: INDEX; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE INDEX dim_period_cal_idx ON pf.dim_period USING btree (cal_year, cal_month);
|
|
|
|
--
|
|
-- Name: dim_period_drange_idx; Type: INDEX; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE INDEX dim_period_drange_idx ON pf.dim_period USING gist (drange);
|
|
|
|
--
|
|
-- Name: dim_period_fisc_idx; Type: INDEX; Schema: pf; Owner: -
|
|
--
|
|
|
|
CREATE INDEX dim_period_fisc_idx ON pf.dim_period USING btree (fisc_year, fisc_month);
|
|
|
|
--
|
|
-- Name: col_meta col_meta_source_id_fkey; Type: FK CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.col_meta
|
|
ADD CONSTRAINT col_meta_source_id_fkey FOREIGN KEY (source_id) REFERENCES pf.source(id) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Name: log log_version_id_fkey; Type: FK CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.log
|
|
ADD CONSTRAINT log_version_id_fkey FOREIGN KEY (version_id) REFERENCES pf.version(id) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Name: sql sql_source_id_fkey; Type: FK CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.sql
|
|
ADD CONSTRAINT sql_source_id_fkey FOREIGN KEY (source_id) REFERENCES pf.source(id) ON DELETE CASCADE;
|
|
|
|
--
|
|
-- Name: version version_source_id_fkey; Type: FK CONSTRAINT; Schema: pf; Owner: -
|
|
--
|
|
|
|
ALTER TABLE ONLY pf.version
|
|
ADD CONSTRAINT version_source_id_fkey FOREIGN KEY (source_id) REFERENCES pf.source(id) ON DELETE RESTRICT;
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|
|
|