damien-kaiser/taskloft

Taskloft — team task boards (owner / member / viewer), card history, my plate, overdue, blockers, AI checklists and summaries, usable from Claude and ChatGPT over MCP.

• dodil-challenge
• dodil-challenge-boards

A remix template · 0 remixes · Remix damien-kaiser/taskloft

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
-- An older bucket gets the newer tables and columns from the app's additive migrations at start-up
-- (app/boards/infrastructure/repository.py, MIGRATIONS): never a rename, never a drop.
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.owner_email)
);
CREATE TABLE IF NOT EXISTS lists (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  name TEXT NOT NULL,
  position BIGINT NOT NULL,
  is_done BOOLEAN                 -- cards here count as finished (never overdue, never "on my plate")
);
CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  list_name TEXT NOT NULL,        -- the list's name, kept for readable SQL; list_id is the reference
  title TEXT NOT NULL,
  assignee TEXT,
  due_date DATE,
  updated_at TIMESTAMP,
  number BIGINT,                  -- "#12": claimed once in card_numbers
  list_id TEXT,
  description TEXT,
  labels TEXT,                    -- JSON array of short words
  position DOUBLE                 -- order inside its list (drag and drop drops between two cards)
);
CREATE TABLE IF NOT EXISTS card_numbers (
  number BIGINT PRIMARY KEY,      -- a CLAIM: plain INSERT, the loser of a race gets 23505 and retries
  card_id TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS card_blockers (
  id TEXT PRIMARY KEY,            -- card_id + '>' + blocked_by
  card_id TEXT NOT NULL,
  blocked_by TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS checklist_items (
  id TEXT PRIMARY KEY,
  card_id TEXT NOT NULL,
  text TEXT NOT NULL,
  done BOOLEAN,
  position BIGINT
);
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,
  event_at TIMESTAMP,
  changes TEXT                    -- JSON {field: [before, after]}
);
CREATE TABLE IF NOT EXISTS card_vectors (
  card_id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,         -- KNN is filtered to the boards the person can see
  text_hash TEXT NOT NULL,        -- re-embedded only when the card's text changed
  embedding VECTOR(2048)          -- jina-embeddings-v4 through Dodil Models; cosine distance (<=>)
);
CREATE TABLE IF NOT EXISTS board_summaries (
  id TEXT PRIMARY KEY,            -- board_id + ':' + hash of the facts the summary was written from
  board_id TEXT NOT NULL,
  text TEXT NOT NULL,
  made_at TIMESTAMP
);