theaio/theaio-boards

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

• dodil-challenge
• dodil-challenge-boards
• boards
• kanban
• mcp
• fastapi

A remix template · 0 remixes · Remix theaio/theaio-boards

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
-- Every table has a primary key. An existing starter deployment upgrades with
-- migrations/001_board_upgrade.sql (ADD COLUMN + the new tables).
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 is boards.owner_email)
);
CREATE TABLE IF NOT EXISTS lists (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  name TEXT NOT NULL,
  pos BIGINT NOT NULL             -- order on the board; the last list counts as done
);
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,            -- board_id + ':' + num: a plain INSERT claims the number
  board_id TEXT NOT NULL,
  list_name TEXT NOT NULL,        -- the starter's fixed list; written '' now, read once to upgrade
  title TEXT NOT NULL,
  assignee TEXT,
  due_date DATE,
  updated_at TIMESTAMP,
  num BIGINT,                     -- the card's number on its board ("card 12")
  list_id TEXT,                   -- lists.id
  description TEXT,
  labels TEXT,                    -- JSON array of short strings
  pos BIGINT,                     -- order inside its list
  created_by TEXT,
  created_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activity (
  id TEXT PRIMARY KEY,            -- board-level events: members, lists
  board_id TEXT NOT NULL,
  card_id TEXT,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  event_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_history (
  id TEXT PRIMARY KEY,            -- append-only: one row per field that changed
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,
  actor TEXT NOT NULL,
  changed_at TIMESTAMP NOT NULL,
  field_name TEXT NOT NULL,       -- created | title | description | assignee | due_date | labels
                                  -- | list | position | comment | blocked by
  old_value TEXT,
  new_value TEXT
);
CREATE TABLE IF NOT EXISTS card_links (
  id TEXT PRIMARY KEY,            -- card_id + '<' + blocked_by
  board_id TEXT NOT NULL,
  card_id TEXT NOT NULL,          -- this card waits on...
  blocked_by TEXT NOT NULL        -- ...this one
);
CREATE TABLE IF NOT EXISTS card_embeddings (
  card_id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  digest TEXT NOT NULL,           -- hash of the text embedded: re-embed only when it changes
  embedding VECTOR(2048)          -- jina-embeddings-v4; searched with <=> (cosine distance)
);