database-setup.sql

-- NOTE: SPITE runs this file automatically the first time it starts (see
-- lib/db-schema.ts, which holds a verbatim copy). Keep the two in sync -
-- pnpm check:schema fails if they drift. Running this by hand in a SQL
-- console is still fine and still safe: every statement is IF NOT EXISTS.

-- ============================================================================
-- SPITE — Database setup script (Postgres / Neon)
-- Note: the camera_bag table (from removed Camera Bag feature) is no
-- longer created; if an existing database still has it, drop it with:
--   DROP TABLE IF EXISTS camera_bag;
-- ----------------------------------------------------------------------------
-- HOW TO USE:
--   1. Open your Neon project at https://console.neon.tech
--   2. In the left sidebar, click "SQL Editor"
--   3. Paste this ENTIRE file into the editor
--   4. Click "Run"
-- It is safe to run this more than once — it only creates things that are
-- missing and will not delete or overwrite your existing data.
-- ============================================================================

-- Projects: one row per film project on your dashboard.
-- scenes / active_scene_id: per-project scene timeline state. scenes is
-- a JSONB array of {id, name}; shots[] are derived from canvas_nodes
-- at read time and not persisted here.
CREATE TABLE IF NOT EXISTS projects (
    id              uuid PRIMARY KEY,
    userid          uuid NOT NULL,
    name            text NOT NULL DEFAULT 'Untitled Project',
    description     text DEFAULT '',
    thumbnail       text,
    scenes          jsonb,
    active_scene_id text,
    createdat       timestamptz NOT NULL DEFAULT now(),
    updatedat       timestamptz NOT NULL DEFAULT now()
);
-- For projects tables that pre-date scene persistence: add the columns
-- if they aren't already there. CREATE TABLE IF NOT EXISTS above only
-- runs when the table is missing entirely.
ALTER TABLE projects ADD COLUMN IF NOT EXISTS scenes jsonb;
ALTER TABLE projects ADD COLUMN IF NOT EXISTS active_scene_id text;
-- origin: how the project is worked in — 'canvas' (node graph) or 'flow'
-- (the linear generation thread). Anything not 'canvas' is treated as Flow.
ALTER TABLE projects ADD COLUMN IF NOT EXISTS origin text NOT NULL DEFAULT 'canvas';

-- Generation history: every AI image/video you generate, plus canvas uploads.
-- This is the "asset library" the left panel reads from. id is TEXT because the
-- app generates ids like 'asset-1700000000000-ab12cd34' as well as UUIDs.
CREATE TABLE IF NOT EXISTS generation_history (
    id             text PRIMARY KEY,
    type           text,
    model          text,
    prompt         text,
    r2_url         text,
    used_in_canvas boolean DEFAULT false,
    is_upload      boolean DEFAULT false,
    created_at     timestamptz DEFAULT now(),
    expires_at     timestamptz,
    project_id     text,
    -- recovered: pulled back from fal after a stuck/vanished generation
    -- (shows the blue badge in the asset panel).
    recovered      boolean DEFAULT false,
    -- refs: reference-image proxy URLs used to produce this result, so
    -- Flow's "Reuse" can re-attach them after a reload. JSON array.
    refs           jsonb
);
ALTER TABLE generation_history ADD COLUMN IF NOT EXISTS recovered boolean DEFAULT false;
ALTER TABLE generation_history ADD COLUMN IF NOT EXISTS refs jsonb;

-- Assets: project file uploads with metadata + tags (separate from the AI
-- generation history above).
CREATE TABLE IF NOT EXISTS assets (
    id        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    projectid text NOT NULL,
    name      text,
    category  text,
    url       text,
    tags      text[],
    metadata  jsonb,
    createdat timestamptz DEFAULT now(),
    updatedat timestamptz DEFAULT now()
);

-- Canvas nodes: the boxes on the node canvas, saved per project.
-- Primary key on (projectid, nodeid) so the app's UPSERT/auto-save works.
CREATE TABLE IF NOT EXISTS canvas_nodes (
    projectid  text NOT NULL,
    nodeid     text NOT NULL,
    type       text,
    position_x double precision,
    position_y double precision,
    data       jsonb,
    createdat  timestamptz DEFAULT now(),
    PRIMARY KEY (projectid, nodeid)
);

-- Canvas edges: the connections between nodes, saved per project.
CREATE TABLE IF NOT EXISTS canvas_edges (
    projectid    text NOT NULL,
    edgeid       text NOT NULL,
    source       text,
    target       text,
    sourcehandle text,
    targethandle text,
    animated     boolean,
    data         jsonb,
    createdat    timestamptz DEFAULT now(),
    PRIMARY KEY (projectid, edgeid)
);

-- Asset folders: named groups (Characters / Props / Locations / General).
-- All columns are plain text so we never run into uuid-vs-text comparison
-- pitfalls with parameter binding (the prior schema had project_id end up
-- as uuid in some installs, which broke every WHERE filter).
CREATE TABLE IF NOT EXISTS asset_folders (
    id          text PRIMARY KEY,
    project_id  text NOT NULL,
    type        text NOT NULL,
    name        text NOT NULL,
    description text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- Asset folder items: composite key on (folder_id, asset_id) — same asset
-- can't be added twice to the same folder.
CREATE TABLE IF NOT EXISTS asset_folder_items (
    folder_id  text NOT NULL REFERENCES asset_folders(id) ON DELETE CASCADE,
    asset_id   text NOT NULL,
    added_at   timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (folder_id, asset_id)
);

-- Sessions: opaque tokens issued at login, validated on every request.
-- Replaces the previous static cookie value so a captured cookie can
-- be revoked server-side by logout / expiry.
CREATE TABLE IF NOT EXISTS sessions (
    token       text PRIMARY KEY,
    created_at  timestamptz NOT NULL DEFAULT now(),
    expires_at  timestamptz NOT NULL DEFAULT (now() + interval '30 days')
);

-- Auth attempts: per-IP failed-login log feeding the rate limiter on
-- the verify endpoint (5 attempts per IP per 60 s).
CREATE TABLE IF NOT EXISTS auth_attempts (
    ip            text NOT NULL,
    attempted_at  timestamptz NOT NULL DEFAULT now()
);

-- Spend ledger: server-side record of every accepted fal.ai submission.
-- Drives the per-hour USD ceiling enforced in /api/generate/submit.
CREATE TABLE IF NOT EXISTS spend_ledger (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id       text NOT NULL,
    estimated_usd  numeric(10,4) NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    -- request_id: fal request id tagged after submit, so a rejected job's
    -- reservation can be rolled back precisely.
    request_id     text
);
ALTER TABLE spend_ledger ADD COLUMN IF NOT EXISTS request_id text;

-- App settings: small key/value store for options editable from the
-- Settings UI at runtime (e.g. data-retention windows), overriding env
-- defaults without a redeploy.
CREATE TABLE IF NOT EXISTS app_settings (
    key         text PRIMARY KEY,
    value       text NOT NULL,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- Per-project auto-delete window for unused results. NULL = use the global
-- asset_retention_days setting; 0 = never delete anything from this project.
ALTER TABLE projects ADD COLUMN IF NOT EXISTS asset_retention_days integer;

-- Event log: things that happened with no browser open (nightly cleanup
-- deletions, version changes). Surfaced by the notifications bell.
CREATE TABLE IF NOT EXISTS app_events (
    id          bigserial PRIMARY KEY,
    kind        text NOT NULL,
    title       text NOT NULL,
    detail      jsonb,
    project_id  text,
    created_at  timestamptz NOT NULL DEFAULT now(),
    seen        boolean NOT NULL DEFAULT false
);
CREATE INDEX IF NOT EXISTS idx_app_events_created ON app_events (created_at DESC);

-- Voice ID cache: maps an audio asset's SPITE proxy URL to the
-- fal-issued voice_id created from it (Kling 2.6 voice cloning).
-- Each unique audio file costs one fal create-voice call; everything
-- after that hits the cache and is instant.
CREATE TABLE IF NOT EXISTS voice_id_cache (
    audio_url   text PRIMARY KEY,
    voice_id    text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Helpful indexes for the most common lookups.
CREATE INDEX IF NOT EXISTS idx_generation_history_project ON generation_history (project_id);
CREATE INDEX IF NOT EXISTS idx_assets_project           ON assets (projectid);
CREATE INDEX IF NOT EXISTS idx_canvas_nodes_project     ON canvas_nodes (projectid);
CREATE INDEX IF NOT EXISTS idx_canvas_edges_project     ON canvas_edges (projectid);
CREATE INDEX IF NOT EXISTS idx_asset_folders_project    ON asset_folders (project_id);
CREATE INDEX IF NOT EXISTS idx_folder_items_folder      ON asset_folder_items (folder_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires         ON sessions (expires_at);
CREATE INDEX IF NOT EXISTS idx_auth_attempts_ip_time    ON auth_attempts (ip, attempted_at);
CREATE INDEX IF NOT EXISTS idx_spend_ledger_time        ON spend_ledger (created_at);
CREATE INDEX IF NOT EXISTS idx_spend_ledger_request     ON spend_ledger (request_id);
CREATE INDEX IF NOT EXISTS idx_genhistory_project_created ON generation_history (project_id, created_at DESC);