PacketStation/server/packetstation/migrations/0003_domain.sql

-- 0003_domain: the core domain. Projects and units, packets and their child tables, change
-- orders, documents and ITPs, plus the views v_units and v_open_cos. Runs as ps_migrate; 0001's
-- default privileges give ps_app SELECT, INSERT, UPDATE, DELETE on every table here.
-- Append-only tables revoke below. Text ID columns (work order base, lot, split, sub, part ids)
-- stay text: leading zeros are kept and lot 7A occurs.

CREATE TYPE packet_status AS ENUM (
    'draft', 'warehouse', 'ready', 'assigned', 'in_progress', 'review', 'qc', 'complete', 'filed'
);
CREATE TYPE wh_stage AS ENUM ('incoming', 'pulling', 'hold', 'pulled');
CREATE TYPE co_review AS ENUM ('pending', 'approved', 'rejected');
CREATE TYPE co_origin AS ENUM ('visual', 'drawing', 'manual');
CREATE TYPE source_kind AS ENUM ('visual', 'hand');
CREATE TYPE project_status AS ENUM ('open', 'hold', 'closed');
CREATE TYPE route_kind AS ENUM ('databook', 'shipping');
CREATE TYPE drawing_state AS ENUM (
    'none', 'needed', 'attached', 'rev_mismatch', 'not_released', 'not_found', 'lookup_failed', 'hand'
);
CREATE TYPE itp_status AS ENUM ('open', 'signed', 'reopened');

-- ---------------------------------------------------------------- organisation

-- Weld departments get the welding checklist and need welds and filler before submit.
ALTER TABLE departments ADD COLUMN weld bool NOT NULL DEFAULT false;
UPDATE departments SET weld = true WHERE code IN ('03', '04', '05', '06', '07', '10');

-- A worker with rows here may only work on packets at these stations. No rows = any station.
CREATE TABLE user_station_limits (
    user_id    uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    station_id bigint NOT NULL REFERENCES stations (id),
    PRIMARY KEY (user_id, station_id)
);

-- ---------------------------------------------------------------- projects and units

-- *_src says where a value came from: visual, hand, or null when unset. A hand value is never
-- overwritten by the feed; *_visual keeps what VISUAL said beside it.
CREATE TABLE projects (
    id              bigserial PRIMARY KEY,
    number          text NOT NULL,
    name            text,
    customer        text,
    po              text,
    due             date,
    status          project_status NOT NULL DEFAULT 'open',
    notes           text,
    source          text NOT NULL DEFAULT 'hand' CHECK (source IN ('hand', 'visual')),
    needs_details   bool NOT NULL DEFAULT false,
    customer_src    text CHECK (customer_src IN ('visual', 'hand')),
    customer_visual text,
    po_src          text CHECK (po_src IN ('visual', 'hand')),
    po_visual       text,
    order_date      date,
    order_date_src  text CHECK (order_date_src IN ('visual', 'hand')),
    ship_date       date,
    ship_date_src   text CHECK (ship_date_src IN ('visual', 'hand')),
    version         int NOT NULL DEFAULT 1,
    created_at      timestamptz NOT NULL DEFAULT now(),
    created_by      uuid REFERENCES users (id),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    removed_at      timestamptz,
    removed_by      uuid REFERENCES users (id),
    removed_reason  text
);

CREATE UNIQUE INDEX projects_number_live ON projects (upper(number)) WHERE removed_at IS NULL;

-- Every current and old number of every project and unit, upper-cased, so a lookup by any
-- number resolves. unit_id is null for a project number. The unit_id FK is added after units.
CREATE TABLE project_aliases (
    alias      text PRIMARY KEY CHECK (alias = upper(alias)),
    project_id bigint NOT NULL REFERENCES projects (id),
    unit_id    bigint,
    kind       text NOT NULL CHECK (kind IN ('renumber', 'unit_renumber', 'import')),
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX project_aliases_project ON project_aliases (project_id);

-- lot is text: 1 to 3 characters, upper-cased, zero-padded to 3 when numeric (001, 7A).
CREATE TABLE units (
    id             bigserial PRIMARY KEY,
    project_id     bigint NOT NULL REFERENCES projects (id),
    lot            text NOT NULL CHECK (lot ~ '^[A-Z0-9]{1,3}$' AND (lot !~ '^[0-9]+$' OR length(lot) = 3)),
    name           text,
    due            date,
    status         project_status NOT NULL DEFAULT 'open',
    notes          text,
    customer_src   text CHECK (customer_src IN ('visual', 'hand')),
    po_src         text CHECK (po_src IN ('visual', 'hand')),
    order_date     date,
    order_date_src text CHECK (order_date_src IN ('visual', 'hand')),
    ship_date      date,
    ship_date_src  text CHECK (ship_date_src IN ('visual', 'hand')),
    version        int NOT NULL DEFAULT 1,
    created_at     timestamptz NOT NULL DEFAULT now(),
    created_by     uuid REFERENCES users (id),
    updated_at     timestamptz NOT NULL DEFAULT now(),
    removed_at     timestamptz,
    removed_by     uuid REFERENCES users (id),
    removed_reason text
);

CREATE UNIQUE INDEX units_project_lot_live ON units (project_id, lot) WHERE removed_at IS NULL;

ALTER TABLE project_aliases
    ADD CONSTRAINT project_aliases_unit_id_fkey FOREIGN KEY (unit_id) REFERENCES units (id);

CREATE TABLE project_renumbers (
    id         bigserial PRIMARY KEY,
    at         timestamptz NOT NULL DEFAULT now(),
    by         uuid REFERENCES users (id),
    project_id bigint NOT NULL REFERENCES projects (id),
    unit_id    bigint REFERENCES units (id),
    old_number text NOT NULL,
    new_number text NOT NULL,
    reason     text
);

-- The unit number is the project number plus the lot. A unit's own due date wins over the project's.
CREATE VIEW v_units AS
SELECT u.*,
       p.number || u.lot AS number,
       p.number AS project_number,
       p.name AS project_name,
       p.status AS project_status,
       coalesce(u.due, p.due) AS due_effective
FROM units u
JOIN projects p ON p.id = u.project_id;

-- ---------------------------------------------------------------- assets

-- One row per stored file. storage_key is the path under ASSETS_DIR. packet_id is the packet the
-- file was uploaded for, if any; the attach rows (packet_pages, packet_docs) are the real links.
CREATE TABLE assets (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    sha256        bytea NOT NULL,
    size          bigint NOT NULL,
    mime          text NOT NULL,
    original_name text,
    storage_key   text NOT NULL UNIQUE,
    source        text NOT NULL DEFAULT 'upload',
    source_ref    jsonb,
    pages         int,
    uploaded_by   uuid REFERENCES users (id),
    uploaded_at   timestamptz NOT NULL DEFAULT now(),
    packet_id     bigint
);

CREATE INDEX assets_sha256 ON assets (sha256);

-- ---------------------------------------------------------------- packets

-- version goes up in every write transaction (the pipeline does it, never a trigger).
-- next_*_no are the counters for server-assigned child numbers, taken under the FOR UPDATE lock.
CREATE TABLE packets (
    id                bigserial PRIMARY KEY,
    unit_id           bigint NOT NULL REFERENCES units (id),
    dept_code         text NOT NULL REFERENCES departments (code),
    op                text,
    title             text,
    customer          text,
    drawing           text,
    rev               text,
    wps               text,
    status            packet_status NOT NULL DEFAULT 'draft',
    status_at         timestamptz NOT NULL DEFAULT now(),
    station_id        bigint REFERENCES stations (id),
    welder_id         uuid REFERENCES users (id),
    welder_stamp      text,
    for_sup_id        uuid REFERENCES users (id),
    heat_map          bool,
    wh_skipped        bool NOT NULL DEFAULT false,
    wh_sent_at        timestamptz,
    routed_to         route_kind,
    return_note       text,
    vclosed_at        timestamptz,
    vclosed_by        uuid REFERENCES users (id),
    databook_at       timestamptz,
    source_kind       source_kind NOT NULL DEFAULT 'hand',
    source            jsonb NOT NULL DEFAULT '{}',
    visual_key        text,
    visual_rel        date,
    visual_want       date,
    drawing_state     drawing_state NOT NULL DEFAULT 'none',
    flags             text[] NOT NULL DEFAULT '{}',
    visual_fp_applied text,
    visual_closed_at  timestamptz,
    visual            jsonb NOT NULL DEFAULT '{}',
    next_weld_no      int NOT NULL DEFAULT 1,
    next_heat_no      int NOT NULL DEFAULT 1,
    next_page_no      int NOT NULL DEFAULT 1,
    next_co_no        int NOT NULL DEFAULT 1,
    version           int NOT NULL DEFAULT 1,
    created_at        timestamptz NOT NULL DEFAULT now(),
    created_by        uuid REFERENCES users (id),
    updated_at        timestamptz NOT NULL DEFAULT now(),
    removed_at        timestamptz,
    removed_by        uuid REFERENCES users (id),
    removed_reason    text
);

-- One live packet per VISUAL key. A removed packet keeps its key so the feed never recreates it.
CREATE UNIQUE INDEX packets_visual_key ON packets (visual_key)
    WHERE visual_key IS NOT NULL AND removed_at IS NULL;
CREATE INDEX packets_status_live ON packets (status) WHERE removed_at IS NULL;
CREATE INDEX packets_unit ON packets (unit_id);
CREATE INDEX packets_dept_status ON packets (dept_code, status);

-- Sets updated_at only. A read never bumps version; the write pipeline bumps it explicitly.
CREATE FUNCTION ps_packet_touch() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER packets_touch
    BEFORE UPDATE ON packets
    FOR EACH ROW EXECUTE FUNCTION ps_packet_touch();

-- ---------------------------------------------------------------- change orders

-- One pending VISUAL change order per packet at a time; the feed re-aims it rather than adding one.
CREATE TABLE change_orders (
    id                    bigserial PRIMARY KEY,
    packet_id             bigint NOT NULL REFERENCES packets (id),
    co_no                 int NOT NULL,
    origin                co_origin NOT NULL,
    review                co_review NOT NULL DEFAULT 'pending',
    minor                 bool NOT NULL DEFAULT false,
    hold                  bool NOT NULL DEFAULT false,
    held_by               uuid REFERENCES users (id),
    held_at               timestamptz,
    ecn                   text,
    reason                text,
    issued_at             timestamptz DEFAULT now(),
    issued_by             uuid REFERENCES users (id),
    issued_signature_id   bigint REFERENCES signatures (id),
    approved_at           timestamptz,
    approved_by           uuid REFERENCES users (id),
    approved_signature_id bigint REFERENCES signatures (id),
    rejected_at           timestamptz,
    rejected_by           uuid REFERENCES users (id),
    rejected_reason       text,
    rejected_signature_id bigint REFERENCES signatures (id),
    vis                   jsonb,
    waiting               jsonb,
    staged                jsonb,
    target_fp             text,
    row_version           int NOT NULL DEFAULT 1,
    UNIQUE (packet_id, co_no)
);

CREATE UNIQUE INDEX change_orders_visual_pending ON change_orders (packet_id)
    WHERE origin = 'visual' AND review = 'pending';

CREATE TABLE co_items (
    id       bigserial PRIMARY KEY,
    co_id    bigint NOT NULL REFERENCES change_orders (id),
    old_page int,
    old_dwg  text,
    old_rev  text,
    new_page int,
    new_dwg  text,
    new_rev  text,
    added    bool NOT NULL DEFAULT false
);

CREATE INDEX co_items_co ON co_items (co_id);

CREATE TABLE co_required_depts (
    co_id     bigint NOT NULL REFERENCES change_orders (id),
    dept_code text NOT NULL REFERENCES departments (code),
    PRIMARY KEY (co_id, dept_code)
);

-- Insert-only. A primary key violation means the department already verified.
CREATE TABLE co_verifications (
    co_id        bigint NOT NULL REFERENCES change_orders (id),
    dept_code    text NOT NULL REFERENCES departments (code),
    by           uuid NOT NULL REFERENCES users (id),
    at           timestamptz NOT NULL DEFAULT now(),
    note         text,
    signature_id bigint NOT NULL REFERENCES signatures (id),
    it_reason    text,
    PRIMARY KEY (co_id, dept_code)
);

-- The old openCOs(): an approved change order that some required department has not verified,
-- or a pending one Clerical put on hold. pending_depts lists the unverified departments, sorted.
CREATE VIEW v_open_cos AS
SELECT x.packet_id, x.co_id, x.co_no, x.review, x.hold, x.pending_depts
FROM (
    SELECT co.packet_id,
           co.id AS co_id,
           co.co_no,
           co.review,
           co.hold,
           ARRAY(
               SELECT r.dept_code
               FROM co_required_depts r
               WHERE r.co_id = co.id
                 AND NOT EXISTS (
                     SELECT 1 FROM co_verifications v
                     WHERE v.co_id = r.co_id AND v.dept_code = r.dept_code
                 )
               ORDER BY r.dept_code
           ) AS pending_depts
    FROM change_orders co
    WHERE co.review IN ('approved', 'pending')
) x
WHERE (x.review = 'approved' AND cardinality(x.pending_depts) > 0)
   OR (x.review = 'pending' AND x.hold);

-- ---------------------------------------------------------------- packet children

-- All key parts are text. lot, split and sub default to '' so the unique key works without nulls.
CREATE TABLE packet_work_orders (
    id            bigserial PRIMARY KEY,
    packet_id     bigint NOT NULL REFERENCES packets (id),
    wo_base       text NOT NULL,
    lot           text NOT NULL DEFAULT '',
    split         text NOT NULL DEFAULT '',
    sub           text NOT NULL DEFAULT '',
    seq           int,
    qty           numeric,
    target_hrs    numeric,
    op            text,
    description   text,
    visual_status text,
    missing_runs  int NOT NULL DEFAULT 0,
    gone_at       timestamptz,
    sort          int NOT NULL DEFAULT 0,
    row_version   int NOT NULL DEFAULT 1,
    UNIQUE (packet_id, wo_base, lot, split, sub)
);

-- n comes from packets.next_page_no. A page replaced by a change order is voided, never deleted.
CREATE TABLE packet_pages (
    id          bigserial PRIMARY KEY,
    packet_id   bigint NOT NULL REFERENCES packets (id),
    n           int NOT NULL,
    type        text NOT NULL CHECK (type IN ('drawing', 'info', 'overlay')),
    asset_id    uuid REFERENCES assets (id),
    pn          int NOT NULL DEFAULT 1,
    dwg         text,
    rev         text,
    co_id       bigint REFERENCES change_orders (id),
    void_co_id  bigint REFERENCES change_orders (id),
    void_at     timestamptz,
    void_by     uuid REFERENCES users (id),
    void_new_n  int,
    row_version int NOT NULL DEFAULT 1,
    UNIQUE (packet_id, n)
);

CREATE TABLE bom_lines (
    id           bigserial PRIMARY KEY,
    packet_id    bigint NOT NULL REFERENCES packets (id),
    item         int NOT NULL,
    qty          numeric,
    part_id      text,
    description  text,
    dwg_ref      text,
    cl_item      text,
    length       text,
    material     text,
    heat         text,
    na           bool NOT NULL DEFAULT false,
    issued       numeric,
    loc          text,
    pulled       bool NOT NULL DEFAULT false,
    pulled_at    timestamptz,
    pulled_by    uuid REFERENCES users (id),
    mtr_asset_id uuid REFERENCES assets (id),
    src          text CHECK (src IN ('visual', 'hand')),
    vkey         text,
    whs          text,
    comm         text,
    cons         text,
    sub          text,
    grp          text,
    sort         int NOT NULL DEFAULT 0,
    row_version  int NOT NULL DEFAULT 1,
    UNIQUE (packet_id, item)
);

-- At most one open short per BOM line.
CREATE TABLE bom_shorts (
    id          bigserial PRIMARY KEY,
    packet_id   bigint NOT NULL REFERENCES packets (id),
    bom_line_id bigint NOT NULL REFERENCES bom_lines (id) ON DELETE CASCADE,
    qty         numeric NOT NULL,
    qty_open    numeric NOT NULL,
    reason      text,
    ref         text,
    eta         date,
    note        text,
    opened_at   timestamptz NOT NULL DEFAULT now(),
    opened_by   uuid REFERENCES users (id),
    closed_at   timestamptz,
    row_version int NOT NULL DEFAULT 1
);

CREATE UNIQUE INDEX bom_shorts_open ON bom_shorts (bom_line_id) WHERE closed_at IS NULL;
CREATE INDEX bom_shorts_packet_open ON bom_shorts (packet_id) WHERE closed_at IS NULL;

CREATE TABLE short_receipts (
    id        bigserial PRIMARY KEY,
    short_id  bigint NOT NULL REFERENCES bom_shorts (id),
    packet_id bigint NOT NULL REFERENCES packets (id),
    qty       numeric NOT NULL,
    heat      text,
    note      text,
    at        timestamptz NOT NULL DEFAULT now(),
    by        uuid REFERENCES users (id)
);

CREATE INDEX short_receipts_short ON short_receipts (short_id);

-- One stage per visit to a shop department. The open stage has closed_at null.
-- checklist maps key -> {done, at, by, stamp}.
CREATE TABLE packet_stages (
    id                  bigserial PRIMARY KEY,
    packet_id           bigint NOT NULL REFERENCES packets (id),
    dept_code           text NOT NULL REFERENCES departments (code),
    station_id          bigint REFERENCES stations (id),
    welder_id           uuid REFERENCES users (id),
    welder_stamp        text,
    checklist           jsonb NOT NULL DEFAULT '{}',
    filler              text,
    filler_lot          text,
    preheat             text,
    notes               text,
    clock_elapsed_ms    bigint NOT NULL DEFAULT 0,
    clock_running_since timestamptz,
    clock_started_at    timestamptz,
    qc_snapshot         jsonb,
    opened_at           timestamptz NOT NULL DEFAULT now(),
    closed_at           timestamptz,
    row_version         int NOT NULL DEFAULT 1
);

CREATE UNIQUE INDEX packet_stages_open ON packet_stages (packet_id) WHERE closed_at IS NULL;
CREATE INDEX packet_stages_packet ON packet_stages (packet_id, id);

-- weld_no comes from packets.next_weld_no. fx and fy are fractions of the page.
CREATE TABLE welds (
    id             bigserial PRIMARY KEY,
    packet_id      bigint NOT NULL REFERENCES packets (id),
    weld_no        int NOT NULL,
    page           int,
    fx             double precision,
    fy             double precision,
    wo_base        text,
    piece          int,
    items          int[] NOT NULL DEFAULT '{}',
    type           text,
    size           text,
    process        text,
    stamp          text,
    welder_id      uuid REFERENCES users (id),
    dept_code      text REFERENCES departments (code),
    stage_id       bigint REFERENCES packet_stages (id),
    carried_from   int,
    at             timestamptz NOT NULL DEFAULT now(),
    by             uuid REFERENCES users (id),
    row_version    int NOT NULL DEFAULT 1,
    removed_at     timestamptz,
    removed_by     uuid REFERENCES users (id),
    removed_reason text
);

CREATE UNIQUE INDEX welds_packet_no_live ON welds (packet_id, weld_no) WHERE removed_at IS NULL;

-- tag_no comes from packets.next_heat_no.
CREATE TABLE heat_tags (
    id             bigserial PRIMARY KEY,
    packet_id      bigint NOT NULL REFERENCES packets (id),
    tag_no         int NOT NULL,
    page           int,
    item           int,
    fx             double precision,
    fy             double precision,
    carried_from   int,
    at             timestamptz NOT NULL DEFAULT now(),
    by             uuid REFERENCES users (id),
    row_version    int NOT NULL DEFAULT 1,
    removed_at     timestamptz,
    removed_by     uuid REFERENCES users (id),
    removed_reason text
);

CREATE UNIQUE INDEX heat_tags_packet_no_live ON heat_tags (packet_id, tag_no) WHERE removed_at IS NULL;

-- One heat table position per page; written as an idempotent upsert.
CREATE TABLE heat_table_placements (
    id        bigserial PRIMARY KEY,
    packet_id bigint NOT NULL REFERENCES packets (id),
    page      int NOT NULL,
    x         double precision,
    y         double precision,
    s         double precision,
    by        uuid REFERENCES users (id),
    at        timestamptz NOT NULL DEFAULT now(),
    UNIQUE (packet_id, page)
);

-- The warehouse stage history. The current row has closed_at null; a change closes it and inserts.
CREATE TABLE packet_wh (
    id        bigserial PRIMARY KEY,
    packet_id bigint NOT NULL REFERENCES packets (id),
    stage     wh_stage NOT NULL,
    by        uuid REFERENCES users (id),
    at        timestamptz NOT NULL DEFAULT now(),
    reason    text,
    note      text,
    auto      bool NOT NULL DEFAULT false,
    closed_at timestamptz
);

CREATE UNIQUE INDEX packet_wh_current ON packet_wh (packet_id) WHERE closed_at IS NULL;

-- Every status change. Replaces the old hist and sentBack lists.
CREATE TABLE packet_transitions (
    id           bigserial PRIMARY KEY,
    packet_id    bigint NOT NULL REFERENCES packets (id),
    at           timestamptz NOT NULL DEFAULT now(),
    by           uuid REFERENCES users (id),
    action       text NOT NULL,
    from_status  packet_status,
    to_status    packet_status,
    from_dept    text,
    to_dept      text,
    reason       text,
    signature_id bigint REFERENCES signatures (id),
    it_reason    text
);

CREATE INDEX packet_transitions_packet ON packet_transitions (packet_id, id);

-- One open inspection per packet. weld_exam maps weld_no -> {result, note}.
CREATE TABLE qc_inspections (
    id           bigserial PRIMARY KEY,
    packet_id    bigint NOT NULL REFERENCES packets (id),
    stage_id     bigint REFERENCES packet_stages (id),
    sent_at      timestamptz NOT NULL DEFAULT now(),
    sent_by      uuid REFERENCES users (id),
    book         bool,
    spool        bool,
    location     text,
    sent_note    text,
    checks       jsonb NOT NULL DEFAULT '{}',
    weld_exam    jsonb NOT NULL DEFAULT '{}',
    nde_method   text,
    nde_result   text,
    nde_report   text,
    hardness     text,
    nace         bool,
    inspector    text,
    stamp        text,
    notes        text,
    result       text CHECK (result IN ('accepted', 'rejected')),
    result_at    timestamptz,
    result_by    uuid REFERENCES users (id),
    result_note  text,
    signature_id bigint REFERENCES signatures (id),
    row_version  int NOT NULL DEFAULT 1,
    closed_at    timestamptz
);

CREATE UNIQUE INDEX qc_inspections_open ON qc_inspections (packet_id) WHERE closed_at IS NULL;

CREATE TABLE qc_history (
    id        bigserial PRIMARY KEY,
    packet_id bigint NOT NULL REFERENCES packets (id),
    at        timestamptz NOT NULL DEFAULT now(),
    result    text,
    inspector text,
    note      text,
    dept_code text,
    snapshot  jsonb
);

CREATE INDEX qc_history_packet ON qc_history (packet_id, id);

-- HMAC seal written when a packet is filed. At most one unbroken seal per packet.
CREATE TABLE packet_seals (
    id            bigserial PRIMARY KEY,
    packet_id     bigint NOT NULL REFERENCES packets (id),
    hash          bytea NOT NULL,
    key_id        text NOT NULL,
    sealed_at     timestamptz NOT NULL DEFAULT now(),
    sealed_by     uuid REFERENCES users (id),
    signature_id  bigint REFERENCES signatures (id),
    broken_at     timestamptz,
    broken_by     uuid REFERENCES users (id),
    broken_reason text
);

CREATE UNIQUE INDEX packet_seals_unbroken ON packet_seals (packet_id) WHERE broken_at IS NULL;

-- The user-visible history. by_name and role are snapshots at the time of writing.
CREATE TABLE packet_log (
    id            bigserial PRIMARY KEY,
    packet_id     bigint NOT NULL REFERENCES packets (id),
    at            timestamptz NOT NULL DEFAULT now(),
    by            uuid REFERENCES users (id),
    by_name       text,
    role          text,
    action        text NOT NULL,
    note          text,
    it_reason     text,
    signature_id  bigint REFERENCES signatures (id),
    transition_id bigint REFERENCES packet_transitions (id)
);

CREATE INDEX packet_log_packet ON packet_log (packet_id, id);

-- What the feed last observed per leg. Written by the worker only.
CREATE TABLE packet_visual_state (
    packet_id    bigint PRIMARY KEY REFERENCES packets (id),
    legs         jsonb NOT NULL DEFAULT '[]',
    ticket_hours numeric,
    order_ref    jsonb,
    observed_at  timestamptz
);

-- ---------------------------------------------------------------- documents and ITPs

CREATE TABLE packet_docs (
    id             bigserial PRIMARY KEY,
    packet_id      bigint NOT NULL REFERENCES packets (id),
    asset_id       uuid NOT NULL REFERENCES assets (id),
    kind           text NOT NULL,
    title          text,
    added_at       timestamptz NOT NULL DEFAULT now(),
    added_by       uuid REFERENCES users (id),
    removed_at     timestamptz,
    removed_by     uuid REFERENCES users (id),
    removed_reason text
);

CREATE INDEX packet_docs_packet ON packet_docs (packet_id) WHERE removed_at IS NULL;

-- Q.C. documents for a project or a unit. A new revision points the old row at the new one.
CREATE TABLE qc_docs (
    id            bigserial PRIMARY KEY,
    project_id    bigint REFERENCES projects (id),
    unit_id       bigint REFERENCES units (id),
    type          text NOT NULL,
    rev           text,
    asset_id      uuid NOT NULL REFERENCES assets (id),
    auto          bool NOT NULL DEFAULT false,
    superseded_by bigint REFERENCES qc_docs (id),
    added_at      timestamptz NOT NULL DEFAULT now(),
    added_by      uuid REFERENCES users (id)
);

-- One ITP per unit. template is frozen when the ITP is made.
CREATE TABLE itps (
    id                   bigserial PRIMARY KEY,
    unit_id              bigint UNIQUE NOT NULL REFERENCES units (id),
    template             jsonb NOT NULL,
    status               itp_status NOT NULL DEFAULT 'open',
    signoff_at           timestamptz,
    signoff_by           uuid REFERENCES users (id),
    signoff_signature_id bigint REFERENCES signatures (id),
    signoff_asset_id     uuid REFERENCES assets (id),
    reopen_at            timestamptz,
    reopen_by            uuid REFERENCES users (id),
    reopen_reason        text,
    version              int NOT NULL DEFAULT 1
);

CREATE TABLE itp_lines (
    id           bigserial PRIMARY KEY,
    itp_id       bigint NOT NULL REFERENCES itps (id),
    key          text NOT NULL,
    result       text CHECK (result IN ('approve', 'reject', 'na')),
    by           uuid REFERENCES users (id),
    at           timestamptz,
    ncr          text,
    note         text,
    signature_id bigint REFERENCES signatures (id),
    row_version  int NOT NULL DEFAULT 1,
    UNIQUE (itp_id, key)
);

-- Every reject keeps its NCR id, even after the line is approved later.
CREATE TABLE itp_rejects (
    id       bigserial PRIMARY KEY,
    itp_id   bigint NOT NULL REFERENCES itps (id),
    line_key text NOT NULL,
    ncr      text NOT NULL,
    note     text,
    at       timestamptz NOT NULL DEFAULT now(),
    by       uuid REFERENCES users (id)
);

CREATE TABLE itp_blocks (
    id           bigserial PRIMARY KEY,
    itp_id       bigint NOT NULL REFERENCES itps (id),
    block_key    text NOT NULL,
    signed_at    timestamptz NOT NULL DEFAULT now(),
    signed_by    uuid REFERENCES users (id),
    signature_id bigint REFERENCES signatures (id),
    UNIQUE (itp_id, block_key)
);

-- ---------------------------------------------------------------- append-only records

CREATE TRIGGER project_renumbers_append_only
    BEFORE UPDATE OR DELETE ON project_renumbers
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER project_renumbers_no_truncate
    BEFORE TRUNCATE ON project_renumbers
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER short_receipts_append_only
    BEFORE UPDATE OR DELETE ON short_receipts
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER short_receipts_no_truncate
    BEFORE TRUNCATE ON short_receipts
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER packet_transitions_append_only
    BEFORE UPDATE OR DELETE ON packet_transitions
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER packet_transitions_no_truncate
    BEFORE TRUNCATE ON packet_transitions
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER qc_history_append_only
    BEFORE UPDATE OR DELETE ON qc_history
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER qc_history_no_truncate
    BEFORE TRUNCATE ON qc_history
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER packet_log_append_only
    BEFORE UPDATE OR DELETE ON packet_log
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER packet_log_no_truncate
    BEFORE TRUNCATE ON packet_log
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER itp_rejects_append_only
    BEFORE UPDATE OR DELETE ON itp_rejects
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER itp_rejects_no_truncate
    BEFORE TRUNCATE ON itp_rejects
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

CREATE TRIGGER co_verifications_append_only
    BEFORE UPDATE OR DELETE ON co_verifications
    FOR EACH ROW EXECUTE FUNCTION ps_append_only();
CREATE TRIGGER co_verifications_no_truncate
    BEFORE TRUNCATE ON co_verifications
    FOR EACH STATEMENT EXECUTE FUNCTION ps_append_only();

-- Explicit, after the default privileges: the app may only read and append these.
REVOKE UPDATE, DELETE, TRUNCATE ON project_renumbers, short_receipts, packet_transitions, qc_history,
    packet_log, itp_rejects, co_verifications FROM ps_app;
GRANT SELECT, INSERT ON project_renumbers, short_receipts, packet_transitions, qc_history,
    packet_log, itp_rejects, co_verifications TO ps_app;
-- Views are read-only to the app.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON v_units, v_open_cos FROM ps_app;
GRANT SELECT ON v_units, v_open_cos TO ps_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ps_app;