`db/migrations/001_page_overrides.sql`

54 lines
-- @dw/inline-editor — Postgres schema (dw_unified)
-- Append-only audit + current-state override layer.

CREATE TABLE IF NOT EXISTS page_overrides (
  site         TEXT NOT NULL,
  path         TEXT NOT NULL,
  selector     TEXT NOT NULL,
  kind         TEXT NOT NULL CHECK (kind IN ('text','html','image','image_alt','href')),
  value        TEXT NOT NULL,
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by   TEXT NOT NULL,
  PRIMARY KEY (site, path, selector)
);
CREATE INDEX IF NOT EXISTS idx_page_overrides_site_path ON page_overrides(site, path);

-- Append-only — no UPDATE, no DELETE in normal use. Reverts are new rows.
CREATE TABLE IF NOT EXISTS page_edit_log (
  id            BIGSERIAL PRIMARY KEY,
  site          TEXT NOT NULL,
  path          TEXT NOT NULL,
  selector      TEXT NOT NULL,
  kind          TEXT NOT NULL,
  old_value     TEXT,
  new_value     TEXT NOT NULL,
  editor_email  TEXT NOT NULL,
  editor_ip     INET,
  editor_ua     TEXT,
  is_revert     BOOLEAN NOT NULL DEFAULT FALSE,
  ts            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_page_edit_log_site_path ON page_edit_log(site, path, ts DESC);
CREATE INDEX IF NOT EXISTS idx_page_edit_log_editor    ON page_edit_log(editor_email, ts DESC);

-- Editor file uploads — metadata; the actual bytes land under site's public/uploads
CREATE TABLE IF NOT EXISTS page_uploads (
  id            BIGSERIAL PRIMARY KEY,
  site          TEXT NOT NULL,
  url           TEXT NOT NULL,            -- the public URL (/uploads/abc.jpg)
  abs_path      TEXT NOT NULL,            -- filesystem path on origin
  mime_type     TEXT NOT NULL,
  bytes         BIGINT NOT NULL,
  uploaded_by   TEXT NOT NULL,
  uploader_ip   INET,
  ts            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_page_uploads_site ON page_uploads(site, ts DESC);

-- Rate limit table (sliding window — cleaned periodically)
CREATE TABLE IF NOT EXISTS page_edit_ratelimit (
  editor_email  TEXT NOT NULL,
  ts            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_page_edit_ratelimit ON page_edit_ratelimit(editor_email, ts DESC);