rgboards/rg-boards

Lanes, team task boards you run from the web or from Claude/ChatGPT (MCP). FastAPI + DataK3 + appid + Dodil Models.

• dodil-challenge
• dodil-challenge-boards

A remix template · 0 remixes · Remix rgboards/rg-boards

app/boards/infrastructure/migrations.py

"""Additive start-up migrations: create missing tables, add missing columns. Never renames or
drops. DataK3 refuses CREATE TABLE IF NOT EXISTS when the existing table has other columns,
so a table that exists only gets the columns it lacks, as explicit ALTERs.

The schema lives in schema.sql at the repo root (the remix applies it to a new bucket); this
module embeds the same statements so an existing bucket catches up on deploy.
"""
import logging
import re

log = logging.getLogger("boards.migrations")

SCHEMA = """
CREATE TABLE IF NOT EXISTS boards (id TEXT PRIMARY KEY, title TEXT NOT NULL,
  owner_email TEXT NOT NULL, created_at TIMESTAMP, lists TEXT);
CREATE TABLE IF NOT EXISTS memberships (id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  email TEXT NOT NULL, role TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS cards (id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  list_name TEXT NOT NULL, title TEXT NOT NULL, assignee TEXT, due_date DATE,
  updated_at TIMESTAMP, number BIGINT, description TEXT, labels TEXT, position DOUBLE,
  version BIGINT, archived BOOLEAN, created_by TEXT, created_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, event_at TIMESTAMP,
  field TEXT, old_value TEXT, new_value TEXT);
CREATE TABLE IF NOT EXISTS card_links (id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  card_id TEXT NOT NULL, blocked_by TEXT NOT NULL, created_by TEXT, created_at TIMESTAMP);
CREATE TABLE IF NOT EXISTS checklist_items (id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  card_id TEXT NOT NULL, text TEXT NOT NULL, done BOOLEAN, position BIGINT);
CREATE TABLE IF NOT EXISTS card_vectors (card_id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  text_hash TEXT NOT NULL, vector TEXT NOT NULL);
CREATE TABLE IF NOT EXISTS board_summaries (id TEXT PRIMARY KEY, board_id TEXT NOT NULL,
  day TEXT NOT NULL, text TEXT, written_at TIMESTAMP);
"""


def _columns(statement: str) -> tuple:
    table = re.search(r"EXISTS\s+(\w+)", statement).group(1)
    body = statement[statement.index("(") + 1:statement.rindex(")")]
    cols = []
    for part in body.split(","):
        bits = part.split()
        if len(bits) >= 2:
            cols.append((bits[0], bits[1]))
    return table, cols


def migrate(conn) -> None:
    tables = {r[0] for r in conn.execute(
        "SELECT table_name FROM information_schema.tables").fetchall()}
    for stmt in [s.strip() for s in SCHEMA.split(";") if s.strip()]:
        table, cols = _columns(stmt)
        if table not in tables:
            log.info("creating %s", table)
            conn.execute(stmt)
            continue
        have = {r[0] for r in conn.execute(
            "SELECT column_name FROM information_schema.columns WHERE table_name = %s",
            (table,)).fetchall()}
        for name, typ in cols:
            if name not in have:
                log.info("adding %s.%s", table, name)
                conn.execute(f"ALTER TABLE {table} ADD COLUMN {name} {typ}")