PacketStation/server/packetstation/migrations/0007_qc.sql

-- 0007_qc: the Q.C. phase. The inspection's NCR, packet document notes and kinds, Q.C. folder metadata, the ITP
-- header, line and block details, the ITP log, and the Q.C. check items as a settings row. Runs as ps_migrate, in
-- one transaction like every migration file.

-- The NCR number a Q.C. reject gave (NCR-2026-0021), kept on the inspection and in its qc_history snapshot.
ALTER TABLE qc_inspections ADD COLUMN ncr text;

-- Packet documents: an optional note, and the kind is a closed set (the old PDOC_TYPES).
ALTER TABLE packet_docs ADD COLUMN note text;
ALTER TABLE packet_docs ADD CONSTRAINT packet_docs_kind
    CHECK (kind IN ('nde', 'hydro', 'dimensional', 'pmi', 'coating', 'coc', 'customer', 'ncr', 'other'));

-- Q.C. folder documents belong to exactly one project or one unit. A new revision files a new row and points the old
-- one at it (superseded_by, superseded_at, superseded_by_user). Nothing is deleted.
ALTER TABLE qc_docs
    ADD COLUMN title text,
    ADD COLUMN note text,
    ADD COLUMN superseded_at timestamptz,
    ADD COLUMN superseded_by_user uuid REFERENCES users (id);
ALTER TABLE qc_docs ADD CONSTRAINT qc_docs_scope CHECK ((project_id IS NULL) <> (unit_id IS NULL));
ALTER TABLE qc_docs ADD CONSTRAINT qc_docs_type
    CHECK (type IN ('ITP', 'WPS / PQR', 'NDE procedure', 'Hydrotest procedure', 'PMI / material',
                    'Calibration certificate', 'Customer spec', 'Other'));
CREATE INDEX qc_docs_project ON qc_docs (project_id) WHERE superseded_by IS NULL;
CREATE INDEX qc_docs_unit ON qc_docs (unit_id) WHERE superseded_by IS NULL;

-- An ITP is open or complete (signed off). 'signed' and 'reopened' stay in the enum, unused. The new value is not
-- used in this migration, so adding it inside the transaction is fine (PostgreSQL 12 and later).
ALTER TYPE itp_status ADD VALUE IF NOT EXISTS 'complete';

-- The ITP number (<unit number>-ITP) and header (client, pname, pno, po, unit) as Q.C. may edit them.
ALTER TABLE itps
    ADD COLUMN no text,
    ADD COLUMN hdr jsonb NOT NULL DEFAULT '{}',
    ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN created_by uuid REFERENCES users (id);

-- A line: Q.C.'s initials, and the customer's code, witness name and date with who recorded them.
ALTER TABLE itp_lines
    ADD COLUMN initials text,
    ADD COLUMN cust_code text,
    ADD COLUMN cust_by text,
    ADD COLUMN cust_at date,
    ADD COLUMN cust_rec uuid REFERENCES users (id),
    ADD COLUMN cust_rec_at timestamptz;

-- A reject may come before its NCR number. kind 'reject' is the rejection; kind 'ncr' records the number entered later.
ALTER TABLE itp_rejects ALTER COLUMN ncr DROP NOT NULL;
ALTER TABLE itp_rejects ADD COLUMN kind text NOT NULL DEFAULT 'reject' CHECK (kind IN ('reject', 'ncr'));

-- A block row exists before it is signed: QN #, remarks and the customer complete record live on it.
ALTER TABLE itp_blocks ALTER COLUMN signed_at DROP NOT NULL, ALTER COLUMN signed_at DROP DEFAULT;
ALTER TABLE itp_blocks
    ADD COLUMN qn text,
    ADD COLUMN remarks text,
    ADD COLUMN cust_name text,
    ADD COLUMN cust_at date,
    ADD COLUMN cust_by uuid REFERENCES users (id),
    ADD COLUMN cust_rec_at timestamptz,
    ADD COLUMN row_version int NOT NULL DEFAULT 1;

-- The ITP's own history, append-only like packet_log.
CREATE TABLE itp_log (
    id           bigserial PRIMARY KEY,
    itp_id       bigint NOT NULL REFERENCES itps (id),
    at           timestamptz NOT NULL DEFAULT now(),
    by           uuid REFERENCES users (id),
    by_name      text,
    action       text NOT NULL,
    note         text,
    signature_id bigint REFERENCES signatures (id),
    it_reason    text
);
CREATE INDEX itp_log_itp ON itp_log (itp_id, id);

CREATE TRIGGER itp_log_append_only
    BEFORE UPDATE OR DELETE ON itp_log
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER itp_log_no_truncate
    BEFORE TRUNCATE ON itp_log
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

-- Explicit, after the default privileges: the app may only read and append the ITP log.
REVOKE UPDATE, DELETE, TRUNCATE ON itp_log FROM ps_app;
GRANT SELECT, INSERT ON itp_log TO ps_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;

-- The Q.C. check items (the old qcChecksFor). {wps}, {stamp} ("[75] " or empty), {drawing} and {rev} are filled in
-- from the packet. The code keeps the same list as its fallback when this row is missing or does not parse.
INSERT INTO settings (key, value) VALUES ('qc_check_items', '{
  "groups": [
    {"group": "Data book package", "items": [
      {"key": "db_heats", "text": "Heat number recorded for every BOM item"},
      {"key": "db_mtr", "text": "MTRs on file and match the heat numbers"},
      {"key": "db_weldmap", "text": "Weld map complete: every weld identified"},
      {"key": "db_wps", "text": "Welds made to {wps}; welder {stamp}qualified"},
      {"key": "db_ck", "text": "Stall checklist and weld record complete"}
    ]},
    {"group": "Physical spool", "items": [
      {"key": "ph_dim", "text": "Dimensions verified against {drawing} Rev {rev}"},
      {"key": "ph_heat", "text": "Heat numbers on material match the heat map"},
      {"key": "ph_stamp", "text": "Welds stamped with welder ID"},
      {"key": "ph_clean", "text": "Clean: no slag, spatter or arc strikes"},
      {"key": "ph_qty", "text": "Quantity matches the work orders"}
    ]}
  ]
}'::jsonb) ON CONFLICT (key) DO NOTHING;