stubborn-otter/otter-boards

Otter Boards, a team task board you can run from the web or from Claude and ChatGPT. FastAPI + DataK3 + appid + Models + MCP.

• boards
• tasks
• fastapi
• mcp
• dodil-challenge
• dodil-challenge-boards

A remix template · 1 remixes · Remix stubborn-otter/otter-boards

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
-- The app only ever reads, and writes keyed upserts, so nothing here is deleted: rows are archived.
CREATE TABLE IF NOT EXISTS boards (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  owner_email TEXT NOT NULL,
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS memberships (
  id TEXT PRIMARY KEY,            -- board_id + ':' + email
  board_id TEXT NOT NULL,
  email TEXT NOT NULL,
  role TEXT NOT NULL              -- member | viewer | removed
);
CREATE TABLE IF NOT EXISTS lists (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  name TEXT NOT NULL,
  sort_order DOUBLE PRECISION NOT NULL,
  is_done BOOLEAN NOT NULL,       -- cards here count as finished
  archived BOOLEAN NOT NULL
);
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,            -- board_id + '-' + card_number, which is what makes numbers unique
  board_id TEXT NOT NULL,
  card_number INTEGER NOT NULL,   -- the short number people say, per board
  list_id TEXT NOT NULL,
  sort_order DOUBLE PRECISION NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  assignee TEXT,
  due_date DATE,
  labels TEXT NOT NULL,           -- JSON list of strings
  checklist TEXT NOT NULL,        -- JSON list of {text, done}
  blocked_by TEXT NOT NULL,       -- JSON list of card numbers on the same board
  archived BOOLEAN NOT NULL,
  updated_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activity (
  id TEXT PRIMARY KEY,            -- time first, so ordering by id is ordering by time
  board_id TEXT NOT NULL,
  card_id TEXT,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,           -- the sentence people read
  field_name TEXT,                -- what changed, for card changes
  old_value TEXT,
  new_value TEXT,
  event_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_embeddings (
  card_id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  text_hash TEXT NOT NULL,        -- which text this was made from, so edits refresh it
  embedding TEXT NOT NULL         -- JSON list of numbers
);