rgboards/rg-boards

Lanes, team task boards you run from the web or from Claude/ChatGPT (MCP). FastAPI + DataK3 + appid + Dodil Models.

• dodil-challenge
• dodil-challenge-boards

A remix template · 0 remixes · Remix rgboards/rg-boards

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
-- The app also runs app/boards/infrastructure/migrations.py at start-up (additive only), so an
-- older bucket gains new tables and columns without anyone running SQL by hand.
CREATE TABLE IF NOT EXISTS boards (
  id TEXT PRIMARY KEY,            -- 12 hex chars
  title TEXT NOT NULL,
  owner_email TEXT NOT NULL,
  created_at TIMESTAMP,
  lists TEXT                      -- JSON array of list names, in order
);
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.owner_email)
);
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,            -- board_id + '-' + number, claimed by a plain INSERT
  board_id TEXT NOT NULL,
  list_name TEXT NOT NULL,
  title TEXT NOT NULL,
  assignee TEXT,
  due_date DATE,
  updated_at TIMESTAMP,
  number BIGINT,                  -- "card 12" on its board
  description TEXT,
  labels TEXT,                    -- comma separated, lower case
  position DOUBLE,                -- order inside a list (drag and drop)
  version BIGINT,                 -- optimistic concurrency: no lost updates
  archived BOOLEAN,
  created_by TEXT,
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activity (  -- the history: who, when, what changed
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  event_at TIMESTAMP,
  field TEXT,
  old_value TEXT,
  new_value TEXT
);
CREATE TABLE IF NOT EXISTS card_links (  -- card_id is blocked by blocked_by
  id TEXT PRIMARY KEY,            -- card_id + '|' + blocked_by
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,
  blocked_by TEXT NOT NULL,
  created_by TEXT,
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS checklist_items (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,
  text TEXT NOT NULL,
  done BOOLEAN,
  position BIGINT
);
CREATE TABLE IF NOT EXISTS card_vectors (  -- embedding cache for "find cards like this one"
  card_id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  text_hash TEXT NOT NULL,
  vector TEXT NOT NULL            -- JSON array from jina-embeddings-v4
);
CREATE TABLE IF NOT EXISTS board_summaries (  -- the day's AI summary of a board
  id TEXT PRIMARY KEY,            -- board_id + ':' + YYYY-MM-DD
  board_id TEXT NOT NULL,
  day TEXT NOT NULL,
  text TEXT,
  written_at TIMESTAMP
);