`sql/001_init.sql`

48 lines
-- Visual Factory schema. Standalone PG `visual_factory` DB. NEVER point at dw_unified.
-- Bootstrap: createdb visual_factory && psql -d visual_factory -f sql/001_init.sql

CREATE TABLE IF NOT EXISTS runs (
    id              BIGSERIAL PRIMARY KEY,
    brief           TEXT NOT NULL,
    domain          TEXT,
    purpose         TEXT,
    visual_type     TEXT,
    artifact_name   TEXT,
    status          TEXT NOT NULL DEFAULT 'pending',
    current_stage   INTEGER NOT NULL DEFAULT 0,
    width           INTEGER,
    height          INTEGER,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at     TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS events (
    id              BIGSERIAL PRIMARY KEY,
    run_id          BIGINT NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
    stage           INTEGER NOT NULL,
    stage_name      TEXT NOT NULL,
    level           TEXT NOT NULL DEFAULT 'info',
    message         TEXT,
    payload         JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS artifacts (
    id              BIGSERIAL PRIMARY KEY,
    run_id          BIGINT REFERENCES runs(id) ON DELETE SET NULL,
    visual_type     TEXT NOT NULL,
    artifact_name   TEXT NOT NULL,
    html_path       TEXT,
    png_path        TEXT,
    width           INTEGER,
    height          INTEGER,
    score           INTEGER,
    status          TEXT NOT NULL DEFAULT 'draft',
    published_path  TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS events_run_idx ON events (run_id, stage);
CREATE INDEX IF NOT EXISTS artifacts_name_idx ON artifacts (visual_type, artifact_name);