eduard/cobra-boards

Cobra Boards, team task boards you can run from Claude or ChatGPT. FastAPI + DataK3 + appid + Dodil Models + MCP.

• dodil-challenge
• dodil-challenge-boards

A remix template · 1 remixes · Remix eduard/cobra-boards

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences,
-- and every id here is chosen by the app (a card is "<board_id>-<number>").
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 (the owner lives on boards)
);
CREATE TABLE IF NOT EXISTS board_lists (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  name TEXT NOT NULL,
  position DOUBLE,
  is_done BOOLEAN                 -- cards here are finished: never overdue, never blocking
);
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,            -- board_id + '-' + number, claimed by a plain INSERT
  board_id TEXT NOT NULL,
  number BIGINT NOT NULL,
  list_id TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  assignee TEXT,
  due_date DATE,
  labels TEXT,                    -- JSON array of strings
  position DOUBLE,
  version BIGINT NOT NULL,        -- bumped by every write; a stale write matches no row
  created_by TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_events (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  summary TEXT,
  changes TEXT,                   -- JSON: field -> [before, after]
  event_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_dependencies (
  id TEXT PRIMARY KEY,            -- card_id + '<' + blocker_id
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,          -- this card ...
  blocker_id TEXT NOT NULL        -- ... waits for this one
);
CREATE TABLE IF NOT EXISTS checklist_items (
  id TEXT PRIMARY KEY,
  card_id TEXT NOT NULL,
  text TEXT NOT NULL,
  done BOOLEAN,
  position DOUBLE
);
CREATE TABLE IF NOT EXISTS card_vectors (
  card_id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  text_hash TEXT NOT NULL,        -- the card text it was made from; a changed card is re-embedded
  embedding VECTOR(768)           -- arctic-embed-m-v2 (Dodil Models), cosine distance
);
CREATE TABLE IF NOT EXISTS card_comments (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,
  author TEXT NOT NULL,
  body TEXT NOT NULL,
  mentions TEXT,                  -- JSON array of the emails it @mentions
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS notifications (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL,            -- who it is for
  board_id TEXT NOT NULL,
  card_id TEXT,
  kind TEXT NOT NULL,             -- mention | assigned | comment
  actor TEXT NOT NULL,
  summary TEXT,
  created_at TIMESTAMP,
  read_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_attachments (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,
  name TEXT NOT NULL,
  content_type TEXT,
  size BIGINT,
  object_key TEXT NOT NULL,       -- where the bytes live in this bucket's object storage
  uploaded_by TEXT,
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS archived_cards (
  card_id TEXT PRIMARY KEY,       -- an archived card leaves the board, keeps its number and history
  board_id TEXT NOT NULL,
  archived_by TEXT,
  archived_at TIMESTAMP
);