settled-reach/.pql/changelog/ticket_idmap/0000-schema.sql

-- Auto-generated by migrate-ids (D-26). CREATE TABLE statements
-- for the planning schema; per-table dir keeps the changelog
-- self-describing per D-15.
-- pql:created_by: migrate-ids
-- pql:canonical_version: 2


CREATE TABLE IF NOT EXISTS decisions (
    id                TEXT PRIMARY KEY,
    type              TEXT NOT NULL CHECK(type IN ('confirmed','question','rejected')),
    domain            TEXT NOT NULL,
    title             TEXT NOT NULL,
    status            TEXT NOT NULL DEFAULT 'active'
                          CHECK(status IN ('active','superseded','resolved','open')),
    date              TEXT,
    file_path         TEXT NOT NULL,
    synced_at         TEXT NOT NULL DEFAULT (datetime('now')),
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER
);

CREATE TABLE IF NOT EXISTS decision_refs (
    source_id         TEXT NOT NULL REFERENCES decisions(id) ON DELETE CASCADE,
    target_id         TEXT NOT NULL REFERENCES decisions(id) ON DELETE CASCADE,
    ref_type          TEXT NOT NULL
                          CHECK(ref_type IN ('supersedes','references','resolves','depends_on','amends')),
    note              TEXT,
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER,
    PRIMARY KEY (source_id, target_id, ref_type)
);

-- Identity split (D-26): a ticket's stable, collision-proof identity is its
-- record_id (a locally-generated ULID, planning.NewRecordID); the friendly
-- T-NNN label lives in ticket_idmap and may be reconciled. Every structural
-- reference (parent, deps, history, labels) targets record_id, so a label
-- clash never corrupts the graph — only ticket_idmap needs a relabel.
CREATE TABLE IF NOT EXISTS tickets (
    record_id         TEXT PRIMARY KEY,
    type              TEXT NOT NULL CHECK(type IN ('initiative','epic','story','task','bug')),
    parent_record_id  TEXT REFERENCES tickets(record_id),
    title             TEXT NOT NULL,
    description       TEXT,
    -- No CHECK enumeration: the ticket status vocabulary is per-vault
    -- configurable (ticket_statuses in .pql/config.yaml). Validation lives
    -- in Go (planning.StatusSet), so adding/renaming statuses needs no
    -- schema change. The DEFAULT is a harmless fallback — CreateTicket
    -- always inserts the configured default explicitly.
    status            TEXT NOT NULL DEFAULT 'backlog',
    priority          TEXT DEFAULT 'medium'
                          CHECK(priority IN ('critical','high','medium','low')),
    assigned_to       TEXT,
    team              TEXT,
    decision_ref      TEXT REFERENCES decisions(id),
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER
);

-- ticket_idmap maps a record_id to its current friendly label (T-NNN).
-- ticket_id is intentionally NOT globally unique: two uncoordinated clones
-- can mint the same label, which surfaces as a duplicate-label collision
-- (detected at replay) and is fixed with "pql ticket relabel".
CREATE TABLE IF NOT EXISTS ticket_idmap (
    record_id         TEXT PRIMARY KEY REFERENCES tickets(record_id),
    ticket_id         TEXT NOT NULL,
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER
);

CREATE TABLE IF NOT EXISTS ticket_deps (
    blocker_record_id TEXT NOT NULL REFERENCES tickets(record_id),
    blocked_record_id TEXT NOT NULL REFERENCES tickets(record_id),
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER,
    PRIMARY KEY (blocker_record_id, blocked_record_id)
);

CREATE TABLE IF NOT EXISTS ticket_history (
    ticket_record_id  TEXT NOT NULL REFERENCES tickets(record_id),
    field             TEXT NOT NULL,
    old_value         TEXT,
    new_value         TEXT,
    changed_by        TEXT,
    changed_at        TEXT NOT NULL DEFAULT (datetime('now')),
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT UNIQUE,
    canonical_version INTEGER
);

CREATE TABLE IF NOT EXISTS ticket_labels (
    ticket_record_id  TEXT NOT NULL REFERENCES tickets(record_id),
    label             TEXT NOT NULL,
    created_at        TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at        TEXT NOT NULL DEFAULT (datetime('now')),
    deleted_at        TEXT,
    hash              TEXT,
    canonical_version INTEGER,
    PRIMARY KEY (ticket_record_id, label)
);

CREATE TABLE IF NOT EXISTS meta (
    key        TEXT PRIMARY KEY,
    value      TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_tickets_status        ON tickets(status);
CREATE INDEX IF NOT EXISTS idx_tickets_team          ON tickets(team);
CREATE INDEX IF NOT EXISTS idx_tickets_decision_ref  ON tickets(decision_ref);
CREATE INDEX IF NOT EXISTS idx_tickets_assigned      ON tickets(assigned_to);
CREATE INDEX IF NOT EXISTS idx_tickets_parent        ON tickets(parent_record_id);
CREATE INDEX IF NOT EXISTS idx_ticket_idmap_label    ON ticket_idmap(ticket_id);
CREATE INDEX IF NOT EXISTS idx_decisions_domain      ON decisions(domain);
CREATE INDEX IF NOT EXISTS idx_decisions_type        ON decisions(type);
CREATE INDEX IF NOT EXISTS idx_decision_refs_target  ON decision_refs(target_id);