contest/orbit-boards

Orbit Boards, a private, remixable team task board built with FastAPI, DataK3, AppID, Models and MCP.

• dodil-challenge
• dodil-challenge-boards

A remix template · 0 remixes · Remix contest/orbit-boards

schema.sql

-- Natural text keys are explicit; all application data lives in the remixed DataK3 bucket.
CREATE TABLE IF NOT EXISTS boards (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL CHECK (length(trim(title)) BETWEEN 1 AND 120),
  owner_email TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS memberships (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('member', 'viewer')),
  UNIQUE (board_id, email)
);

CREATE TABLE IF NOT EXISTS cards (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  title TEXT NOT NULL CHECK (length(trim(title)) BETWEEN 1 AND 240),
  list_name TEXT NOT NULL CHECK (list_name IN ('To do', 'Doing', 'Done')),
  assignee TEXT,
  due_date DATE,
  description TEXT NOT NULL DEFAULT '',
  labels TEXT NOT NULL DEFAULT '[]',
  position INTEGER NOT NULL DEFAULT 0 CHECK (position >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS cards_board_list_position_idx ON cards(board_id, list_name, position, id);
CREATE INDEX IF NOT EXISTS cards_assignee_due_idx ON cards(assignee, due_date);

CREATE TABLE IF NOT EXISTS activity (
  id TEXT PRIMARY KEY,
  board_id TEXT NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  card_id TEXT REFERENCES cards(id) ON DELETE SET NULL,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  field TEXT,
  old_value TEXT,
  new_value TEXT,
  event_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS activity_board_time_idx ON activity(board_id, event_at DESC);