sirenamc/stackline

Stackline, a team task board you run from the web or from Claude/ChatGPT. FastAPI + DataK3 + appid + Models + MCP.

• dodil-challenge
• dodil-challenge-boards

A remix template · 0 remixes · Remix sirenamc/stackline

schema.sql

-- Created once by the remix, in the bucket it makes. Natural keys only: DataK3 has no sequences.
-- Lists of things on a card (labels, blocked_by, checklist) are small JSON strings in TEXT
-- columns: they are always read and written with their card.
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 lists (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  name TEXT NOT NULL,
  position INTEGER NOT NULL,
  is_done BOOLEAN NOT NULL        -- cards here are finished: never overdue
);
CREATE TABLE IF NOT EXISTS cards (
  number INTEGER PRIMARY KEY,     -- the #12 people use, claimed in card_numbers first
  board_id TEXT NOT NULL,
  list_id TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  assignee TEXT,
  due_date DATE,
  labels TEXT,                    -- JSON array of strings
  blocked_by TEXT,                -- JSON array of card numbers
  checklist TEXT,                 -- JSON array of [text, done]
  position DOUBLE PRECISION,
  updated_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS card_numbers (
  number INTEGER PRIMARY KEY,     -- every number ever handed out; never reused after a delete
  board_id TEXT NOT NULL,
  claimed_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS activity (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL,
  card_number INTEGER,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  field TEXT,
  before_value TEXT,
  after_value TEXT,
  event_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS embeddings (
  card_number INTEGER PRIMARY KEY,
  board_id TEXT NOT NULL,
  text_hash TEXT NOT NULL,        -- recomputed only when the card's text changes
  vector TEXT NOT NULL            -- JSON array of floats from jina-embeddings-v4
);