`sql/001_init.sql`

48 lines
-- The AI Factory — schema for the standalone `ai_factory` database.
--
-- This file MUST be applied against the standalone `ai_factory` DB only.
-- DO NOT run inside dw_unified. The orchestrator's safety guard will refuse
-- to boot if PG_DATABASE=dw_unified, but pgsql files don't have that guard,
-- so verify the connection target before running:
--
--   createdb ai_factory                                   # one-time
--   psql -d ai_factory -f sql/001_init.sql                # apply schema
--
-- Tables live in the default `public` schema since this DB is single-purpose.

CREATE TABLE IF NOT EXISTS runs (
    id              BIGSERIAL PRIMARY KEY,
    prompt          TEXT NOT NULL,
    artifact_type   TEXT,
    artifact_name   TEXT,
    status          TEXT NOT NULL DEFAULT 'pending',
    current_stage   INTEGER NOT NULL DEFAULT 0,
    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,
    artifact_type   TEXT NOT NULL,
    artifact_name   TEXT NOT NULL,
    location        TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'draft',
    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 (artifact_type, artifact_name);