schema.sql

-- One global revision counter. Every write to threads/overlay stamps a new rev
-- from this sequence, so "/changes?since=<rev>" can return only what changed.
CREATE SEQUENCE IF NOT EXISTS global_rev;

-- The harvested index (one row per thread, shared across all four platforms).
CREATE TABLE IF NOT EXISTS threads (
  key         TEXT PRIMARY KEY,          -- "platform:id"
  platform    TEXT NOT NULL,
  id          TEXT NOT NULL,
  title       TEXT,
  url         TEXT,
  created     TIMESTAMPTZ,
  updated     TIMESTAMPTZ,
  first_msg   TEXT,
  gone        BOOLEAN NOT NULL DEFAULT FALSE,
  first_seen  TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen   TIMESTAMPTZ NOT NULL DEFAULT now(),
  rev         BIGINT NOT NULL DEFAULT nextval('global_rev')
);

-- The curation layer (your tags). Separate from threads so a re-harvest never
-- touches it. Keyed by the same thread key.
CREATE TABLE IF NOT EXISTS overlay (
  key         TEXT PRIMARY KEY REFERENCES threads(key) ON DELETE CASCADE,
  source      TEXT,                      -- Me / Collaborator / ... (extensible)
  prio        TEXT,                      -- 1 / 2 / 3 / YTP
  status      TEXT,                      -- WIP / Blocked / Done / Dropped
  pct         INTEGER,                   -- 0..100 (progress when WIP)
  category    TEXT,
  subcat      TEXT,
  tagged_by   TEXT,                      -- which user last edited (attribution)
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  rev         BIGINT NOT NULL DEFAULT nextval('global_rev')
);

-- Editable vocabularies.
CREATE TABLE IF NOT EXISTS sources    (name TEXT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS categories (name TEXT PRIMARY KEY);

-- Known users (for attribution; created on first login).
CREATE TABLE IF NOT EXISTS users (name TEXT PRIMARY KEY, last_seen TIMESTAMPTZ DEFAULT now());

CREATE INDEX IF NOT EXISTS threads_rev_idx ON threads(rev);
CREATE INDEX IF NOT EXISTS overlay_rev_idx ON overlay(rev);

-- Seed the focal "Owner" source values + your category taxonomy.
INSERT INTO sources(name) VALUES ('Me'),('Collaborator') ON CONFLICT DO NOTHING;
INSERT INTO categories(name) VALUES
  ('Development'),('Research'),('Writing'),
  ('Operations'),('Learning'),('Personal'),('Other')
ON CONFLICT DO NOTHING;