skald/migrations/0004_authors.sql

-- Authors as personas with souls (v0.3).
--
-- Each story has a named author whose soul (SOUL.md-style markdown
-- blob) IS the LLM's system prompt. Soul replaces opus's default
-- base prompt via clawdforge's system_mode='replace' so the model
-- BECOMES the author, not "Claude playing the author."
--
-- Souls are versioned: authors.id is the immutable identity, while
-- author_revisions tracks soul edits over time. Stories pin to a
-- specific revision so "this was the version of Orson Black active
-- when chapter 8 of Coast-Down was written" is always recoverable.

CREATE TABLE authors (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            TEXT NOT NULL UNIQUE,           -- "orson-black"
    display_name    TEXT NOT NULL,                  -- "Orson Black"
    persona_tagline TEXT,                           -- "Orwell but more rebel + pissed off"
    -- Model the author writes with. Skald is opinionated: always
    -- opus max effort. Stored per-author so we can later vary
    -- (e.g. test sonnet for a faster-iterating author).
    model           TEXT NOT NULL DEFAULT 'opus',
    -- Flag for the operator: this is a synthetic literary persona,
    -- not a real human author. Display somewhere obvious.
    is_synthetic    BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- One row per soul revision. `n` is monotonic per author. Exactly
-- one revision per author is_current=true at any time (partial
-- unique index).
CREATE TABLE author_revisions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id       UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    n               INTEGER NOT NULL,
    -- The soul: SOUL.md-style markdown blob. Replaces claude's base
    -- system prompt when this revision is used.
    soul            TEXT NOT NULL,
    -- Optional override of the default scaffold prompt that wraps
    -- the soul. Default scaffold lives in skald-core::forge.
    system_template TEXT,
    -- Tools the author can call during gen. Empty default — fiction
    -- writing doesn't normally need them. Researcher-bent authors
    -- could opt in for WebSearch / Read.
    tools           TEXT[] NOT NULL DEFAULT '{}',
    -- Human note: "first cut", "tightened voice after Chapter 8 read"
    note            TEXT,
    is_current      BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (author_id, n)
);

-- At most one current revision per author.
CREATE UNIQUE INDEX idx_author_revisions_current
    ON author_revisions(author_id) WHERE is_current = true;

CREATE INDEX idx_author_revisions_author ON author_revisions(author_id);

-- Auto-touch authors.updated_at on author or revision change.
CREATE OR REPLACE FUNCTION touch_author_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE authors SET updated_at = now()
    WHERE id = COALESCE(NEW.author_id, OLD.author_id);
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER author_revisions_touch_author
    AFTER INSERT OR UPDATE OR DELETE ON author_revisions
    FOR EACH ROW
    EXECUTE FUNCTION touch_author_updated_at();

-- Stories carry author identity + the exact revision used at gen
-- time. author_id is also denormalized on the row (instead of
-- joining through revision) so common "all stories by Orson"
-- queries don't need the join.
ALTER TABLE stories
    ADD COLUMN author_id           UUID REFERENCES authors(id) ON DELETE SET NULL,
    ADD COLUMN author_revision_id  UUID REFERENCES author_revisions(id) ON DELETE SET NULL,
    -- Per-story toggle for cross-story memory. When true,
    -- ContinuationContext::assemble pulls characters / canon /
    -- summaries from EVERY story in the author's corpus, not just
    -- the parent chain.
    ADD COLUMN cross_story_memory  BOOLEAN NOT NULL DEFAULT false;

CREATE INDEX idx_stories_author ON stories(author_id);

-- Track which stories an author has access to. Auto-populated via
-- app code on every story creation (role='authored'); operator can
-- mark other stories as 'read' for cross-corpus memory.
CREATE TABLE author_corpus (
    author_id   UUID NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    story_id    UUID NOT NULL REFERENCES stories(id) ON DELETE CASCADE,
    role        TEXT NOT NULL CHECK (role IN ('authored', 'read')),
    added_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (author_id, story_id)
);

CREATE INDEX idx_author_corpus_author ON author_corpus(author_id);