flc/crewboard

Crewboard - team task board (boards, lists, cards, roles, history, drag and drop, MCP, AI extras). FastAPI + DataK3 + appid + Models.

• dodil-challenge
• dodil-challenge-boards

A remix template · 1 remixes · Remix flc/crewboard

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
CREATE TABLE IF NOT EXISTS boards (
  id TEXT PRIMARY KEY,            -- short key, e.g. OPS (claimed by a plain INSERT)
  title TEXT NOT NULL,
  owner_email TEXT NOT NULL,
  lists TEXT,                     -- JSON array of list names, in order; the last one is "done"
  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 cards (
  id TEXT PRIMARY KEY,            -- board key + '-' + number, e.g. OPS-12
  board_id TEXT NOT NULL,
  number BIGINT NOT NULL,
  title TEXT NOT NULL,
  list_name TEXT NOT NULL,
  position DOUBLE,                -- order inside the list (drag and drop)
  description TEXT,
  assignee TEXT,
  due_date DATE,
  labels TEXT,                    -- JSON array
  blocked_by TEXT,                -- JSON array of card ids on the same board
  archived BOOLEAN,
  version BIGINT,                 -- optimistic lock: a stale edit is refused (409)
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activity (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_id TEXT,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  changes TEXT,                   -- JSON {field: [before, after]}
  event_at TIMESTAMP
);