sprint_planer_web/migrations/001_init.sql

-- Phase 2/3 initial schema.
--
-- Users = people who sign in via Entra ID.
-- Workers = entities that get work assigned (may or may not also be users).
-- Never conflate the two.

CREATE TABLE users (
    id              INTEGER PRIMARY KEY,
    entra_oid       TEXT NOT NULL UNIQUE,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    is_admin        INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL,
    last_login_at   TEXT
);

CREATE TABLE workers (
    id           INTEGER PRIMARY KEY,
    name         TEXT NOT NULL UNIQUE,
    is_active    INTEGER NOT NULL DEFAULT 1,
    default_rtb  REAL NOT NULL DEFAULT 0.0,
    created_at   TEXT NOT NULL,
    updated_at   TEXT NOT NULL
);

CREATE TABLE sprints (
    id                INTEGER PRIMARY KEY,
    name              TEXT NOT NULL,
    start_date        TEXT NOT NULL,
    end_date          TEXT NOT NULL,
    reserve_fraction  REAL NOT NULL DEFAULT 0.2,
    is_archived       INTEGER NOT NULL DEFAULT 0,
    created_at        TEXT NOT NULL,
    updated_at        TEXT NOT NULL
);

CREATE TABLE sprint_weeks (
    id                    INTEGER PRIMARY KEY,
    sprint_id             INTEGER NOT NULL REFERENCES sprints(id) ON DELETE CASCADE,
    sort_order            INTEGER NOT NULL,
    iso_week              INTEGER NOT NULL,
    start_date            TEXT NOT NULL,
    max_working_days      REAL NOT NULL,
    UNIQUE (sprint_id, sort_order)
);

CREATE TABLE sprint_workers (
    id           INTEGER PRIMARY KEY,
    sprint_id    INTEGER NOT NULL REFERENCES sprints(id)  ON DELETE CASCADE,
    worker_id    INTEGER NOT NULL REFERENCES workers(id)  ON DELETE RESTRICT,
    rtb          REAL NOT NULL DEFAULT 0.0,
    sort_order   INTEGER NOT NULL,
    UNIQUE (sprint_id, worker_id)
);

CREATE TABLE sprint_worker_days (
    id                INTEGER PRIMARY KEY,
    sprint_worker_id  INTEGER NOT NULL REFERENCES sprint_workers(id) ON DELETE CASCADE,
    sprint_week_id    INTEGER NOT NULL REFERENCES sprint_weeks(id)   ON DELETE CASCADE,
    days              REAL NOT NULL DEFAULT 0,
    UNIQUE (sprint_worker_id, sprint_week_id)
);

CREATE TABLE tasks (
    id               INTEGER PRIMARY KEY,
    sprint_id        INTEGER NOT NULL REFERENCES sprints(id) ON DELETE CASCADE,
    title            TEXT NOT NULL,
    owner_worker_id  INTEGER REFERENCES workers(id) ON DELETE SET NULL,
    priority         INTEGER NOT NULL CHECK (priority IN (1, 2)),
    sort_order       INTEGER NOT NULL,
    created_at       TEXT NOT NULL,
    updated_at       TEXT NOT NULL
);

CREATE TABLE task_assignments (
    id                INTEGER PRIMARY KEY,
    task_id           INTEGER NOT NULL REFERENCES tasks(id)           ON DELETE CASCADE,
    sprint_worker_id  INTEGER NOT NULL REFERENCES sprint_workers(id)  ON DELETE CASCADE,
    days              REAL NOT NULL DEFAULT 0,
    UNIQUE (task_id, sprint_worker_id)
);

CREATE TABLE audit_log (
    id            INTEGER PRIMARY KEY,
    occurred_at   TEXT NOT NULL,
    user_id       INTEGER,
    user_email    TEXT,
    action        TEXT NOT NULL,
    entity_type   TEXT NOT NULL,
    entity_id     INTEGER,
    before_json   TEXT,
    after_json    TEXT,
    ip_address    TEXT,
    user_agent    TEXT
);

CREATE INDEX idx_audit_occurred_at ON audit_log(occurred_at DESC);
CREATE INDEX idx_audit_entity      ON audit_log(entity_type, entity_id);
CREATE INDEX idx_tasks_sprint      ON tasks(sprint_id, sort_order);
CREATE INDEX idx_sw_sprint         ON sprint_workers(sprint_id, sort_order);