Spark
13182f5d53
CI / test (push) Successful in 1m56s
Depuis 0.24.0, /mcp exige session ou Basic : le service d'agents s'y faisait refuser en 401 (vu en production, panneau Spark du 2026-10-21). - Table api_tokens (empreinte SHA-256 seule), CRUD dans Mon profil - Bearer honoré par le middleware sur /mcp et /api/ uniquement — jamais l'interface web ; révocation et désactivation immédiates - 235 tests verts (3 nouveaux : cycle de vie, compte désactivé, handshake MCP par jeton quand l'auth est active)
1256 lines
45 KiB
Python
1256 lines
45 KiB
Python
"""
|
|
SQLite models for Kanbante — lightweight local state.
|
|
|
|
Local state only stores what Gitea does not: the column of each issue,
|
|
its position inside the column, and per-project notes. The database path
|
|
honours the KANBANTE_DB_PATH environment variable (see app.config) —
|
|
that is the path the Dockerfile / docker-compose mount their volume on.
|
|
|
|
Migrations are additive and idempotent: init_db() creates fresh schemas
|
|
and upgrades older databases in place (position column, notes
|
|
updated_at, cleanup of rows carrying an unknown column name).
|
|
"""
|
|
|
|
import hashlib
|
|
import json
|
|
import os
|
|
import secrets
|
|
import sqlite3
|
|
from contextlib import contextmanager
|
|
from typing import Iterator
|
|
|
|
KANBAN_COLUMNS = ["backlog", "todo", "in_progress", "qa", "review", "done"]
|
|
|
|
|
|
def db_path() -> str:
|
|
"""Resolve the database path from the environment (per call)."""
|
|
return os.path.expanduser(
|
|
os.getenv("KANBANTE_DB_PATH", "~/.kanbante/kanbante.db")
|
|
)
|
|
|
|
|
|
@contextmanager
|
|
def get_db() -> Iterator[sqlite3.Connection]:
|
|
path = db_path()
|
|
directory = os.path.dirname(path)
|
|
if directory:
|
|
os.makedirs(directory, exist_ok=True)
|
|
conn = sqlite3.connect(path)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
try:
|
|
yield conn
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def init_db() -> None:
|
|
with get_db() as conn:
|
|
conn.executescript(
|
|
"""
|
|
CREATE TABLE IF NOT EXISTS board_state (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
issue_number INTEGER NOT NULL,
|
|
column_name TEXT NOT NULL DEFAULT 'backlog',
|
|
position REAL NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(workspace_id, connection_id, repo_full_name, issue_number)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_notes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
note TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(workspace_id, connection_id, repo_full_name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS board_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
issue_number INTEGER NOT NULL,
|
|
event_type TEXT NOT NULL,
|
|
from_column TEXT,
|
|
to_column TEXT,
|
|
source TEXT NOT NULL DEFAULT 'ui',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_board_repo
|
|
ON board_state(repo_full_name, column_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS today_picks (
|
|
pick_date TEXT NOT NULL,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
issue_number INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY (pick_date, workspace_id, connection_id, repo_full_name, issue_number)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS kv_store (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS snapshots (
|
|
key TEXT PRIMARY KEY,
|
|
payload TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
-- ── Multi-forges, utilisateurs, espaces (0.24.0) ──
|
|
CREATE TABLE IF NOT EXISTS forge_connections (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
kind TEXT NOT NULL DEFAULT 'gitea',
|
|
name TEXT NOT NULL,
|
|
base_url TEXT NOT NULL,
|
|
token_encrypted TEXT NOT NULL DEFAULT '',
|
|
account_login TEXT NOT NULL DEFAULT '',
|
|
webhook_secret TEXT NOT NULL DEFAULT '',
|
|
owner_user_id INTEGER,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'member',
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
draco_sub TEXT UNIQUE,
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
must_change_password INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
last_login_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS api_tokens (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL REFERENCES users(id),
|
|
name TEXT NOT NULL DEFAULT '',
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
last_used_at TEXT,
|
|
revoked_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspaces (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#7c5cfc',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspace_connections (
|
|
workspace_id INTEGER NOT NULL,
|
|
connection_id INTEGER NOT NULL,
|
|
PRIMARY KEY (workspace_id, connection_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspace_projects (
|
|
workspace_id INTEGER NOT NULL,
|
|
connection_id INTEGER NOT NULL,
|
|
repo_full_name TEXT NOT NULL,
|
|
PRIMARY KEY (workspace_id, connection_id, repo_full_name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS workspace_members (
|
|
workspace_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'member',
|
|
PRIMARY KEY (workspace_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_states (
|
|
workspace_id INTEGER NOT NULL,
|
|
connection_id INTEGER NOT NULL,
|
|
repo_full_name TEXT NOT NULL,
|
|
local_archived INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY (workspace_id, connection_id, repo_full_name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS admin_audit (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
action TEXT NOT NULL,
|
|
details TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_events_repo
|
|
ON board_events(repo_full_name, issue_number, created_at);
|
|
"""
|
|
)
|
|
_migrate(conn)
|
|
|
|
|
|
def _migrate(conn: sqlite3.Connection) -> None:
|
|
"""Upgrade schemas created by Kanbante <= 0.1.0."""
|
|
board_cols = [r[1] for r in conn.execute("PRAGMA table_info(board_state)")]
|
|
if board_cols and "position" not in board_cols:
|
|
conn.execute(
|
|
"ALTER TABLE board_state ADD COLUMN position REAL NOT NULL DEFAULT 0"
|
|
)
|
|
notes_cols = [r[1] for r in conn.execute("PRAGMA table_info(project_notes)")]
|
|
if notes_cols and "updated_at" not in notes_cols:
|
|
# SQLite rejects non-constant defaults in ADD COLUMN: add the
|
|
# column plainly, then backfill from created_at.
|
|
conn.execute("ALTER TABLE project_notes ADD COLUMN updated_at TEXT")
|
|
conn.execute(
|
|
"UPDATE project_notes SET updated_at = created_at "
|
|
"WHERE updated_at IS NULL"
|
|
)
|
|
_migrate_v24(conn)
|
|
# Rows written under a column name that no longer exists used to crash
|
|
# get_board_state() with a KeyError. Drop them once, here.
|
|
placeholders = ",".join("?" for _ in KANBAN_COLUMNS)
|
|
conn.execute(
|
|
f"DELETE FROM board_state WHERE column_name NOT IN ({placeholders})",
|
|
KANBAN_COLUMNS,
|
|
)
|
|
|
|
|
|
def _migrate_v24(conn: sqlite3.Connection) -> None:
|
|
"""0.24.0: scope columns (workspace_id, connection_id).
|
|
|
|
board_state / project_notes / today_picks carry UNIQUE
|
|
constraints on repo_full_name alone — SQLite cannot alter a
|
|
constraint, so legacy tables are rebuilt with the scoped
|
|
uniqueness; existing rows land in scope (1, 1) — the legacy
|
|
connection and the Principal workspace.
|
|
"""
|
|
|
|
def _cols(table: str) -> list[str]:
|
|
return [r[1] for r in conn.execute(f"PRAGMA table_info({table})")]
|
|
|
|
if _cols("board_state") and "connection_id" not in _cols("board_state"):
|
|
conn.executescript(
|
|
"""
|
|
CREATE TABLE board_state_v24 (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
issue_number INTEGER NOT NULL,
|
|
column_name TEXT NOT NULL DEFAULT 'backlog',
|
|
position REAL NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(workspace_id, connection_id, repo_full_name, issue_number)
|
|
);
|
|
INSERT INTO board_state_v24
|
|
(id, workspace_id, connection_id, repo_full_name,
|
|
issue_number, column_name, position, updated_at)
|
|
SELECT id, 1, 1, repo_full_name, issue_number, column_name,
|
|
position, updated_at FROM board_state;
|
|
DROP TABLE board_state;
|
|
ALTER TABLE board_state_v24 RENAME TO board_state;
|
|
CREATE INDEX IF NOT EXISTS idx_board_repo
|
|
ON board_state(repo_full_name, column_name);
|
|
"""
|
|
)
|
|
if _cols("project_notes") and "connection_id" not in _cols("project_notes"):
|
|
conn.executescript(
|
|
"""
|
|
CREATE TABLE project_notes_v24 (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
note TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(workspace_id, connection_id, repo_full_name)
|
|
);
|
|
INSERT INTO project_notes_v24
|
|
(id, workspace_id, connection_id, repo_full_name, note,
|
|
created_at, updated_at)
|
|
SELECT id, 1, 1, repo_full_name, note, created_at, updated_at
|
|
FROM project_notes;
|
|
DROP TABLE project_notes;
|
|
ALTER TABLE project_notes_v24 RENAME TO project_notes;
|
|
"""
|
|
)
|
|
if _cols("today_picks") and "connection_id" not in _cols("today_picks"):
|
|
conn.executescript(
|
|
"""
|
|
CREATE TABLE today_picks_v24 (
|
|
pick_date TEXT NOT NULL,
|
|
workspace_id INTEGER NOT NULL DEFAULT 1,
|
|
connection_id INTEGER NOT NULL DEFAULT 1,
|
|
repo_full_name TEXT NOT NULL,
|
|
issue_number INTEGER NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
PRIMARY KEY (pick_date, workspace_id, connection_id,
|
|
repo_full_name, issue_number)
|
|
);
|
|
INSERT INTO today_picks_v24
|
|
(pick_date, workspace_id, connection_id, repo_full_name,
|
|
issue_number, created_at)
|
|
SELECT pick_date, 1, 1, repo_full_name, issue_number, created_at
|
|
FROM today_picks;
|
|
DROP TABLE today_picks;
|
|
ALTER TABLE today_picks_v24 RENAME TO today_picks;
|
|
"""
|
|
)
|
|
if _cols("board_events") and "connection_id" not in _cols("board_events"):
|
|
conn.execute(
|
|
"ALTER TABLE board_events ADD COLUMN workspace_id "
|
|
"INTEGER NOT NULL DEFAULT 1"
|
|
)
|
|
conn.execute(
|
|
"ALTER TABLE board_events ADD COLUMN connection_id "
|
|
"INTEGER NOT NULL DEFAULT 1"
|
|
)
|
|
|
|
|
|
def ping_db() -> bool:
|
|
"""Cheap check used by /api/readiness."""
|
|
try:
|
|
with get_db() as conn:
|
|
conn.execute("SELECT 1").fetchone()
|
|
return True
|
|
except Exception:
|
|
return False
|
|
|
|
|
|
def get_board_state(
|
|
repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
|
|
) -> dict[str, list[dict]]:
|
|
"""Return board columns with their issues, ordered by position."""
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT issue_number, column_name, position, updated_at "
|
|
"FROM board_state WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? "
|
|
"ORDER BY position ASC, issue_number ASC",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
).fetchall()
|
|
|
|
columns: dict[str, list[dict]] = {col: [] for col in KANBAN_COLUMNS}
|
|
for row in rows:
|
|
column = row["column_name"]
|
|
if column not in columns:
|
|
# Defensive: never let one bad row break the whole board.
|
|
continue
|
|
columns[column].append(
|
|
{
|
|
"issue_number": row["issue_number"],
|
|
"position": row["position"],
|
|
"updated_at": row["updated_at"],
|
|
}
|
|
)
|
|
return columns
|
|
|
|
|
|
def move_issue(
|
|
repo_full_name: str,
|
|
issue_number: int,
|
|
to_column: str,
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
"""Move an issue to a different column (appended at the end)."""
|
|
if to_column not in KANBAN_COLUMNS:
|
|
raise ValueError(f"Invalid column: {to_column}")
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT COALESCE(MAX(position), -1) + 1 AS next_pos FROM board_state "
|
|
"WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? AND column_name = ?",
|
|
(workspace_id, connection_id, repo_full_name, to_column),
|
|
).fetchone()
|
|
conn.execute(
|
|
"""INSERT INTO board_state
|
|
(workspace_id, connection_id, repo_full_name, issue_number,
|
|
column_name, position, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
|
|
ON CONFLICT(workspace_id, connection_id, repo_full_name, issue_number)
|
|
DO UPDATE SET column_name = excluded.column_name,
|
|
position = excluded.position,
|
|
updated_at = excluded.updated_at""",
|
|
(workspace_id, connection_id, repo_full_name, issue_number,
|
|
to_column, row["next_pos"]),
|
|
)
|
|
|
|
|
|
def set_column_order(
|
|
repo_full_name: str,
|
|
column_name: str,
|
|
ordered_issue_numbers: list[int],
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
"""Persist the exact order of the cards in one column.
|
|
|
|
Each listed card keeps (or gains) this column and its position becomes
|
|
its index in the list. Used after a drag & drop so manual ordering
|
|
survives reloads.
|
|
"""
|
|
if column_name not in KANBAN_COLUMNS:
|
|
raise ValueError(f"Invalid column: {column_name}")
|
|
with get_db() as conn:
|
|
for index, issue_number in enumerate(ordered_issue_numbers):
|
|
conn.execute(
|
|
"""INSERT INTO board_state
|
|
(workspace_id, connection_id, repo_full_name,
|
|
issue_number, column_name, position, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
|
|
ON CONFLICT(workspace_id, connection_id, repo_full_name, issue_number)
|
|
DO UPDATE SET column_name = excluded.column_name,
|
|
position = excluded.position,
|
|
updated_at = excluded.updated_at""",
|
|
(workspace_id, connection_id, repo_full_name,
|
|
issue_number, column_name, float(index)),
|
|
)
|
|
|
|
|
|
def reconcile_board_state(
|
|
repo_full_name: str,
|
|
open_numbers: set[int],
|
|
closed_numbers: set[int],
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> dict[str, int]:
|
|
"""Reconcile local board state with the truth in Gitea.
|
|
|
|
- Issues closed in Gitea move to the "done" column.
|
|
- Rows for issues that no longer exist in Gitea are deleted.
|
|
Returns counters of what changed.
|
|
"""
|
|
changed: dict = {"closed_to_done": 0, "purged": 0,
|
|
"closed_numbers": [], "purged_numbers": []}
|
|
known = open_numbers | closed_numbers
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT issue_number, column_name FROM board_state "
|
|
"WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ?",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
).fetchall()
|
|
for row in rows:
|
|
number = row["issue_number"]
|
|
if number not in known:
|
|
conn.execute(
|
|
"DELETE FROM board_state "
|
|
"WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? AND issue_number = ?",
|
|
(workspace_id, connection_id, repo_full_name, number),
|
|
)
|
|
changed["purged"] += 1
|
|
changed["purged_numbers"].append(number)
|
|
elif number in closed_numbers and row["column_name"] != "done":
|
|
conn.execute(
|
|
"UPDATE board_state SET column_name = 'done', "
|
|
"updated_at = datetime('now') "
|
|
"WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? AND issue_number = ?",
|
|
(workspace_id, connection_id, repo_full_name, number),
|
|
)
|
|
changed["closed_to_done"] += 1
|
|
changed["closed_numbers"].append(number)
|
|
return changed
|
|
|
|
|
|
def get_picks(
|
|
pick_date: str, workspace_id: int = 1, connection_id: int | None = None
|
|
) -> list[dict]:
|
|
"""The day's focus picks (issue #5), in pinning order."""
|
|
with get_db() as conn:
|
|
if connection_id is None:
|
|
rows = conn.execute(
|
|
"SELECT connection_id, repo_full_name, issue_number "
|
|
"FROM today_picks WHERE pick_date = ? AND workspace_id = ? "
|
|
"ORDER BY created_at, repo_full_name, issue_number",
|
|
(pick_date, workspace_id),
|
|
).fetchall()
|
|
else:
|
|
rows = conn.execute(
|
|
"SELECT connection_id, repo_full_name, issue_number "
|
|
"FROM today_picks WHERE pick_date = ? AND workspace_id = ? "
|
|
"AND connection_id = ? "
|
|
"ORDER BY created_at, repo_full_name, issue_number",
|
|
(pick_date, workspace_id, connection_id),
|
|
).fetchall()
|
|
return [dict(row) for row in rows]
|
|
|
|
|
|
def add_pick(
|
|
pick_date: str,
|
|
repo_full_name: str,
|
|
issue_number: int,
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT OR IGNORE INTO today_picks "
|
|
"(pick_date, workspace_id, connection_id, repo_full_name, "
|
|
"issue_number) VALUES (?, ?, ?, ?, ?)",
|
|
(pick_date, workspace_id, connection_id, repo_full_name,
|
|
issue_number),
|
|
)
|
|
|
|
|
|
def remove_pick(
|
|
pick_date: str,
|
|
repo_full_name: str,
|
|
issue_number: int,
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"DELETE FROM today_picks WHERE pick_date = ? "
|
|
"AND workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? AND issue_number = ?",
|
|
(pick_date, workspace_id, connection_id, repo_full_name,
|
|
issue_number),
|
|
)
|
|
|
|
|
|
def get_kv(key: str) -> str | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT value FROM kv_store WHERE key = ?", (key,)
|
|
).fetchone()
|
|
return row["value"] if row else None
|
|
|
|
|
|
def set_kv(key: str, value: str) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT INTO kv_store (key, value) VALUES (?, ?) "
|
|
"ON CONFLICT(key) DO UPDATE SET value = excluded.value",
|
|
(key, value),
|
|
)
|
|
|
|
|
|
# ── Snapshots (chantier B, 0.22.0) ─────────────────────────
|
|
# Persisted copies of the Gitea list responses the pages read
|
|
# (repos, issues, milestones, starred). The client cache is
|
|
# hydrated from these rows at startup, so even the first page
|
|
# after a restart renders from local data while the background
|
|
# refresher re-fetches from Gitea.
|
|
|
|
def save_snapshot(key: str, payload) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT INTO snapshots (key, payload, updated_at) "
|
|
"VALUES (?, ?, datetime('now')) "
|
|
"ON CONFLICT(key) DO UPDATE SET payload = excluded.payload, "
|
|
"updated_at = excluded.updated_at",
|
|
(key, json.dumps(payload)),
|
|
)
|
|
|
|
|
|
def load_snapshots() -> dict:
|
|
"""All snapshots as {key: (payload, updated_at_utc_str)}."""
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT key, payload, updated_at FROM snapshots"
|
|
).fetchall()
|
|
out = {}
|
|
for row in rows:
|
|
try:
|
|
out[row["key"]] = (json.loads(row["payload"]), row["updated_at"])
|
|
except (ValueError, TypeError):
|
|
continue
|
|
return out
|
|
|
|
|
|
def latest_snapshot_at() -> str | None:
|
|
"""UTC timestamp (SQLite format) of the freshest snapshot."""
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT MAX(updated_at) AS latest FROM snapshots"
|
|
).fetchone()
|
|
return row["latest"] if row else None
|
|
|
|
|
|
# ── Event journal ──────────────────────────────────────────
|
|
|
|
EVENT_TYPES = ("created", "moved", "closed", "reopened", "commented", "rule")
|
|
|
|
|
|
def log_event(
|
|
repo_full_name: str,
|
|
issue_number: int,
|
|
event_type: str,
|
|
from_column: str | None = None,
|
|
to_column: str | None = None,
|
|
source: str = "ui",
|
|
created_at: str | None = None,
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
"""Append one entry to the board's event journal.
|
|
|
|
The journal is append-only: it is the memory board_state lacks
|
|
(time-in-column, activity feed, throughput / cycle-time metrics).
|
|
"""
|
|
if event_type not in EVENT_TYPES:
|
|
raise ValueError(f"Invalid event type: {event_type}")
|
|
with get_db() as conn:
|
|
if created_at:
|
|
conn.execute(
|
|
"INSERT INTO board_events (workspace_id, connection_id, "
|
|
"repo_full_name, issue_number, event_type, from_column, "
|
|
"to_column, source, created_at) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
|
|
(workspace_id, connection_id, repo_full_name, issue_number,
|
|
event_type, from_column, to_column, source, created_at),
|
|
)
|
|
else:
|
|
conn.execute(
|
|
"INSERT INTO board_events (workspace_id, connection_id, "
|
|
"repo_full_name, issue_number, event_type, from_column, "
|
|
"to_column, source) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
|
|
(workspace_id, connection_id, repo_full_name, issue_number,
|
|
event_type, from_column, to_column, source),
|
|
)
|
|
|
|
|
|
def get_events(
|
|
repo_full_name: str | None = None, limit: int = 100
|
|
) -> list[dict]:
|
|
"""Most recent journal entries, newest first (one repo or all)."""
|
|
with get_db() as conn:
|
|
if repo_full_name:
|
|
rows = conn.execute(
|
|
"SELECT * FROM board_events WHERE repo_full_name = ? "
|
|
"ORDER BY id DESC LIMIT ?",
|
|
(repo_full_name, limit),
|
|
).fetchall()
|
|
else:
|
|
rows = conn.execute(
|
|
"SELECT * FROM board_events ORDER BY id DESC LIMIT ?",
|
|
(limit,),
|
|
).fetchall()
|
|
return [dict(row) for row in rows]
|
|
|
|
|
|
def column_entry_times(
|
|
repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
|
|
) -> dict[int, str]:
|
|
"""When each issue entered its current column, per the journal.
|
|
|
|
Latest event per issue, comments excluded (a comment does not move
|
|
a card). Issues without journal entries are absent — callers fall
|
|
back to board_state.updated_at, then to the issue's creation date.
|
|
"""
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT issue_number, MAX(created_at) AS entered_at "
|
|
"FROM board_events WHERE workspace_id = ? AND connection_id = ? "
|
|
"AND repo_full_name = ? "
|
|
"AND event_type != 'commented' GROUP BY issue_number",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
).fetchall()
|
|
return {row["issue_number"]: row["entered_at"] for row in rows}
|
|
|
|
|
|
def get_project_activity(days: int = 7) -> dict:
|
|
"""Per-repo journal activity in ONE aggregated pass (issue #19).
|
|
|
|
For every repo present in the journal: closures and moves of
|
|
the last `days` days (journal stamps are UTC strings), plus
|
|
the most recent event ever seen for that repo.
|
|
"""
|
|
from datetime import datetime, timedelta, timezone
|
|
|
|
cutoff = (datetime.now(timezone.utc) - timedelta(days=days)).strftime(
|
|
"%Y-%m-%d %H:%M:%S"
|
|
)
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"""
|
|
SELECT repo_full_name,
|
|
SUM(CASE WHEN event_type = 'closed' AND created_at >= ?
|
|
THEN 1 ELSE 0 END),
|
|
SUM(CASE WHEN event_type = 'moved' AND created_at >= ?
|
|
THEN 1 ELSE 0 END),
|
|
MAX(created_at)
|
|
FROM board_events
|
|
GROUP BY repo_full_name
|
|
""",
|
|
(cutoff, cutoff),
|
|
).fetchall()
|
|
return {
|
|
r[0]: {"closed": r[1] or 0, "moved": r[2] or 0, "last_at": r[3]}
|
|
for r in rows
|
|
}
|
|
|
|
|
|
def get_metrics(repo_full_name: str | None = None, window_days: int = 30) -> dict:
|
|
"""Throughput and cycle time computed from the journal.
|
|
|
|
Cycle time = time from entering in_progress (or from creation, when
|
|
no in_progress entry exists) to the closing event, averaged over
|
|
issues closed inside the window.
|
|
"""
|
|
from datetime import datetime, timedelta, timezone
|
|
|
|
def _parse(value: str):
|
|
try:
|
|
return datetime.fromisoformat(value).replace(tzinfo=timezone.utc)
|
|
except (ValueError, TypeError):
|
|
return None
|
|
|
|
with get_db() as conn:
|
|
if repo_full_name:
|
|
rows = conn.execute(
|
|
"SELECT repo_full_name, issue_number, event_type, to_column, "
|
|
"created_at FROM board_events WHERE repo_full_name = ? "
|
|
"ORDER BY created_at, id",
|
|
(repo_full_name,),
|
|
).fetchall()
|
|
else:
|
|
rows = conn.execute(
|
|
"SELECT repo_full_name, issue_number, event_type, to_column, "
|
|
"created_at FROM board_events ORDER BY created_at, id"
|
|
).fetchall()
|
|
|
|
cutoff = datetime.now(timezone.utc) - timedelta(days=window_days)
|
|
counts: dict[str, int] = {}
|
|
starts: dict[tuple, datetime] = {}
|
|
cycles: list[float] = []
|
|
for row in rows:
|
|
at = _parse(row["created_at"])
|
|
if at is None:
|
|
continue
|
|
if at >= cutoff:
|
|
counts[row["event_type"]] = counts.get(row["event_type"], 0) + 1
|
|
key = (row["repo_full_name"], row["issue_number"])
|
|
if row["event_type"] == "created":
|
|
starts.setdefault(key, at)
|
|
elif row["event_type"] == "moved" and row["to_column"] == "in_progress":
|
|
starts[key] = at # cycle clock (re)starts on entering in_progress
|
|
elif row["event_type"] == "closed" and key in starts and at >= cutoff:
|
|
cycles.append((at - starts[key]).total_seconds() / 86400.0)
|
|
return {
|
|
"window_days": window_days,
|
|
"created": counts.get("created", 0),
|
|
"moved": counts.get("moved", 0),
|
|
"closed": counts.get("closed", 0),
|
|
"reopened": counts.get("reopened", 0),
|
|
"avg_cycle_days": round(sum(cycles) / len(cycles), 2) if cycles else None,
|
|
"cycle_samples": len(cycles),
|
|
}
|
|
|
|
|
|
def set_notes(
|
|
repo_full_name: str,
|
|
note: str,
|
|
workspace_id: int = 1,
|
|
connection_id: int = 1,
|
|
) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"""INSERT INTO project_notes
|
|
(workspace_id, connection_id, repo_full_name, note)
|
|
VALUES (?, ?, ?, ?)
|
|
ON CONFLICT(workspace_id, connection_id, repo_full_name)
|
|
DO UPDATE SET note = excluded.note,
|
|
updated_at = datetime('now')""",
|
|
(workspace_id, connection_id, repo_full_name, note),
|
|
)
|
|
|
|
|
|
def get_notes(
|
|
repo_full_name: str, workspace_id: int = 1, connection_id: int = 1
|
|
) -> str | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT note FROM project_notes WHERE workspace_id = ? "
|
|
"AND connection_id = ? AND repo_full_name = ?",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
).fetchone()
|
|
return row["note"] if row else None
|
|
|
|
|
|
def get_stats() -> dict:
|
|
"""Get overall stats across all projects."""
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT repo_full_name, column_name, COUNT(*) as cnt "
|
|
"FROM board_state GROUP BY repo_full_name, column_name"
|
|
).fetchall()
|
|
|
|
projects: dict[str, dict[str, int]] = {}
|
|
for row in rows:
|
|
if row["column_name"] not in KANBAN_COLUMNS:
|
|
continue
|
|
name = row["repo_full_name"]
|
|
if name not in projects:
|
|
projects[name] = {col: 0 for col in KANBAN_COLUMNS}
|
|
projects[name][row["column_name"]] = row["cnt"]
|
|
return projects
|
|
|
|
|
|
# ── Connexions aux forges (0.24.0, P0) ────────────────────
|
|
|
|
def list_connections(include_disabled: bool = True) -> list[dict]:
|
|
query = "SELECT * FROM forge_connections"
|
|
if not include_disabled:
|
|
query += " WHERE enabled = 1"
|
|
query += " ORDER BY id"
|
|
with get_db() as conn:
|
|
return [dict(r) for r in conn.execute(query).fetchall()]
|
|
|
|
|
|
def get_connection(connection_id: int) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM forge_connections WHERE id = ?", (connection_id,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def get_connection_by_slug(slug: str) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM forge_connections WHERE slug = ?", (slug,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def create_connection(
|
|
slug: str,
|
|
kind: str,
|
|
name: str,
|
|
base_url: str,
|
|
token_encrypted: str = "",
|
|
account_login: str = "",
|
|
webhook_secret: str = "",
|
|
owner_user_id: int | None = None,
|
|
) -> int:
|
|
with get_db() as conn:
|
|
cur = conn.execute(
|
|
"INSERT INTO forge_connections (slug, kind, name, base_url, "
|
|
"token_encrypted, account_login, webhook_secret, owner_user_id) "
|
|
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
|
|
(slug, kind, name, base_url, token_encrypted, account_login,
|
|
webhook_secret, owner_user_id),
|
|
)
|
|
return int(cur.lastrowid)
|
|
|
|
|
|
def update_connection(connection_id: int, **fields) -> None:
|
|
allowed = {
|
|
"slug", "kind", "name", "base_url", "token_encrypted",
|
|
"account_login", "webhook_secret", "enabled", "owner_user_id",
|
|
}
|
|
items = {k: v for k, v in fields.items() if k in allowed}
|
|
if not items:
|
|
return
|
|
assignments = ", ".join(f"{k} = ?" for k in items)
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
f"UPDATE forge_connections SET {assignments} WHERE id = ?",
|
|
(*items.values(), connection_id),
|
|
)
|
|
|
|
|
|
def delete_connection(connection_id: int) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"DELETE FROM forge_connections WHERE id = ?", (connection_id,)
|
|
)
|
|
conn.execute(
|
|
"DELETE FROM workspace_connections WHERE connection_id = ?",
|
|
(connection_id,),
|
|
)
|
|
conn.execute(
|
|
"DELETE FROM workspace_projects WHERE connection_id = ?",
|
|
(connection_id,),
|
|
)
|
|
|
|
|
|
# ── Utilisateurs (0.24.0, P2) ──────────────────────────────
|
|
|
|
def count_users() -> int:
|
|
with get_db() as conn:
|
|
return int(conn.execute("SELECT COUNT(*) FROM users").fetchone()[0])
|
|
|
|
|
|
def list_users() -> list[dict]:
|
|
with get_db() as conn:
|
|
return [
|
|
dict(r)
|
|
for r in conn.execute("SELECT * FROM users ORDER BY id").fetchall()
|
|
]
|
|
|
|
|
|
def get_user(user_id: int) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM users WHERE id = ?", (user_id,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def get_user_by_username(username: str) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM users WHERE username = ?", (username,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
# --- Jetons d'API (0.25.0) -------------------------------------------------
|
|
# Jetons personnels pour les consommateurs machine (le service d'agents
|
|
# et son client MCP, en premier lieu). Le texte clair (préfixe kbt_)
|
|
# n'existe qu'à la création ; la base ne garde que l'empreinte SHA-256.
|
|
# Un jeton authentifie uniquement les surfaces machine (/mcp, /api/) —
|
|
# jamais l'interface web.
|
|
|
|
API_TOKEN_PREFIX = "kbt_"
|
|
|
|
|
|
def _hash_api_token(plaintext: str) -> str:
|
|
return hashlib.sha256(plaintext.encode("utf-8")).hexdigest()
|
|
|
|
|
|
def create_api_token(user_id: int, name: str) -> tuple[dict, str]:
|
|
plaintext = API_TOKEN_PREFIX + secrets.token_urlsafe(32)
|
|
with get_db() as conn:
|
|
cur = conn.execute(
|
|
"INSERT INTO api_tokens (user_id, name, token_hash) VALUES (?, ?, ?)",
|
|
(user_id, name.strip(), _hash_api_token(plaintext)),
|
|
)
|
|
row = conn.execute(
|
|
"SELECT * FROM api_tokens WHERE id = ?", (cur.lastrowid,)
|
|
).fetchone()
|
|
return dict(row), plaintext
|
|
|
|
|
|
def list_api_tokens(user_id: int) -> list[dict]:
|
|
with get_db() as conn:
|
|
return [
|
|
dict(r)
|
|
for r in conn.execute(
|
|
"SELECT * FROM api_tokens WHERE user_id = ? ORDER BY id",
|
|
(user_id,),
|
|
).fetchall()
|
|
]
|
|
|
|
|
|
def revoke_api_token(user_id: int, token_id: int) -> bool:
|
|
with get_db() as conn:
|
|
cur = conn.execute(
|
|
"UPDATE api_tokens SET revoked_at = datetime('now')"
|
|
" WHERE id = ? AND user_id = ? AND revoked_at IS NULL",
|
|
(token_id, user_id),
|
|
)
|
|
return cur.rowcount > 0
|
|
|
|
|
|
def user_by_api_token(plaintext: str) -> dict | None:
|
|
if not plaintext or not plaintext.startswith(API_TOKEN_PREFIX):
|
|
return None
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT u.* FROM api_tokens t JOIN users u ON u.id = t.user_id"
|
|
" WHERE t.token_hash = ? AND t.revoked_at IS NULL AND u.active = 1",
|
|
(_hash_api_token(plaintext),),
|
|
).fetchone()
|
|
if row is None:
|
|
return None
|
|
conn.execute(
|
|
"UPDATE api_tokens SET last_used_at = datetime('now')"
|
|
" WHERE token_hash = ?",
|
|
(_hash_api_token(plaintext),),
|
|
)
|
|
return dict(row)
|
|
|
|
|
|
def get_user_by_draco(sub: str) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM users WHERE draco_sub = ?", (sub,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def create_user(
|
|
username: str,
|
|
password_hash: str,
|
|
role: str = "member",
|
|
display_name: str = "",
|
|
must_change_password: int = 0,
|
|
) -> int:
|
|
with get_db() as conn:
|
|
cur = conn.execute(
|
|
"INSERT INTO users (username, password_hash, role, display_name, "
|
|
"must_change_password) VALUES (?, ?, ?, ?, ?)",
|
|
(username, password_hash, role, display_name, must_change_password),
|
|
)
|
|
return int(cur.lastrowid)
|
|
|
|
|
|
def update_user(user_id: int, **fields) -> None:
|
|
allowed = {
|
|
"password_hash", "role", "display_name", "draco_sub", "active",
|
|
"must_change_password", "last_login_at",
|
|
}
|
|
items = {k: v for k, v in fields.items() if k in allowed}
|
|
if not items:
|
|
return
|
|
assignments = ", ".join(f"{k} = ?" for k in items)
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
f"UPDATE users SET {assignments} WHERE id = ?",
|
|
(*items.values(), user_id),
|
|
)
|
|
|
|
|
|
# ── Espaces de travail (0.24.0, P3) ────────────────────────
|
|
|
|
def list_workspaces() -> list[dict]:
|
|
with get_db() as conn:
|
|
return [
|
|
dict(r)
|
|
for r in conn.execute(
|
|
"SELECT * FROM workspaces ORDER BY id"
|
|
).fetchall()
|
|
]
|
|
|
|
|
|
def get_workspace(workspace_id: int) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM workspaces WHERE id = ?", (workspace_id,)
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def create_workspace(slug: str, name: str, color: str = "#7c5cfc") -> int:
|
|
with get_db() as conn:
|
|
cur = conn.execute(
|
|
"INSERT INTO workspaces (slug, name, color) VALUES (?, ?, ?)",
|
|
(slug, name, color),
|
|
)
|
|
return int(cur.lastrowid)
|
|
|
|
|
|
def update_workspace(workspace_id: int, **fields) -> None:
|
|
allowed = {"slug", "name", "color"}
|
|
items = {k: v for k, v in fields.items() if k in allowed}
|
|
if not items:
|
|
return
|
|
assignments = ", ".join(f"{k} = ?" for k in items)
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
f"UPDATE workspaces SET {assignments} WHERE id = ?",
|
|
(*items.values(), workspace_id),
|
|
)
|
|
|
|
|
|
def delete_workspace(workspace_id: int) -> None:
|
|
with get_db() as conn:
|
|
for table in (
|
|
"workspace_connections", "workspace_projects",
|
|
"workspace_members", "project_states",
|
|
):
|
|
conn.execute(
|
|
f"DELETE FROM {table} WHERE workspace_id = ?", (workspace_id,)
|
|
)
|
|
conn.execute("DELETE FROM workspaces WHERE id = ?", (workspace_id,))
|
|
|
|
|
|
def workspace_connection_ids(workspace_id: int) -> list[int]:
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT connection_id FROM workspace_connections "
|
|
"WHERE workspace_id = ? ORDER BY connection_id",
|
|
(workspace_id,),
|
|
).fetchall()
|
|
return [r[0] for r in rows]
|
|
|
|
|
|
def set_workspace_connections(workspace_id: int, connection_ids: list[int]) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"DELETE FROM workspace_connections WHERE workspace_id = ?",
|
|
(workspace_id,),
|
|
)
|
|
conn.executemany(
|
|
"INSERT OR IGNORE INTO workspace_connections "
|
|
"(workspace_id, connection_id) VALUES (?, ?)",
|
|
[(workspace_id, cid) for cid in connection_ids],
|
|
)
|
|
|
|
|
|
def workspace_project_selection(workspace_id: int) -> list[tuple[int, str]]:
|
|
"""Explicit project inclusions; empty means "all projects"."""
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT connection_id, repo_full_name FROM workspace_projects "
|
|
"WHERE workspace_id = ? ORDER BY connection_id, repo_full_name",
|
|
(workspace_id,),
|
|
).fetchall()
|
|
return [(r[0], r[1]) for r in rows]
|
|
|
|
|
|
def set_workspace_projects(
|
|
workspace_id: int, selection: list[tuple[int, str]]
|
|
) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"DELETE FROM workspace_projects WHERE workspace_id = ?",
|
|
(workspace_id,),
|
|
)
|
|
conn.executemany(
|
|
"INSERT OR IGNORE INTO workspace_projects "
|
|
"(workspace_id, connection_id, repo_full_name) VALUES (?, ?, ?)",
|
|
[(workspace_id, cid, repo) for cid, repo in selection],
|
|
)
|
|
|
|
|
|
def list_members(workspace_id: int) -> list[dict]:
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT m.user_id, m.role, u.username, u.display_name "
|
|
"FROM workspace_members m JOIN users u ON u.id = m.user_id "
|
|
"WHERE m.workspace_id = ? ORDER BY u.username",
|
|
(workspace_id,),
|
|
).fetchall()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def set_member(workspace_id: int, user_id: int, role: str) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT INTO workspace_members (workspace_id, user_id, role) "
|
|
"VALUES (?, ?, ?) ON CONFLICT(workspace_id, user_id) "
|
|
"DO UPDATE SET role = excluded.role",
|
|
(workspace_id, user_id, role),
|
|
)
|
|
|
|
|
|
def remove_member(workspace_id: int, user_id: int) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"DELETE FROM workspace_members WHERE workspace_id = ? "
|
|
"AND user_id = ?",
|
|
(workspace_id, user_id),
|
|
)
|
|
|
|
|
|
def user_workspaces(user_id: int) -> list[dict]:
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT w.*, m.role AS member_role FROM workspaces w "
|
|
"JOIN workspace_members m ON m.workspace_id = w.id "
|
|
"WHERE m.user_id = ? ORDER BY w.id",
|
|
(user_id,),
|
|
).fetchall()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def workspace_role(workspace_id: int, user_id: int) -> str | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT role FROM workspace_members WHERE workspace_id = ? "
|
|
"AND user_id = ?",
|
|
(workspace_id, user_id),
|
|
).fetchone()
|
|
return row[0] if row else None
|
|
|
|
|
|
# ── États de projets / archives locales (0.24.0, P4) ──────
|
|
|
|
def get_project_state(
|
|
workspace_id: int, connection_id: int, repo_full_name: str
|
|
) -> dict | None:
|
|
with get_db() as conn:
|
|
row = conn.execute(
|
|
"SELECT * FROM project_states WHERE workspace_id = ? "
|
|
"AND connection_id = ? AND repo_full_name = ?",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
).fetchone()
|
|
return dict(row) if row else None
|
|
|
|
|
|
def set_local_archived(
|
|
workspace_id: int,
|
|
connection_id: int,
|
|
repo_full_name: str,
|
|
archived: bool,
|
|
) -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT INTO project_states (workspace_id, connection_id, "
|
|
"repo_full_name, local_archived, updated_at) "
|
|
"VALUES (?, ?, ?, ?, datetime('now')) "
|
|
"ON CONFLICT(workspace_id, connection_id, repo_full_name) "
|
|
"DO UPDATE SET local_archived = excluded.local_archived, "
|
|
"updated_at = excluded.updated_at",
|
|
(workspace_id, connection_id, repo_full_name, 1 if archived else 0),
|
|
)
|
|
|
|
|
|
def list_local_archived(workspace_id: int) -> list[dict]:
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT * FROM project_states WHERE workspace_id = ? "
|
|
"AND local_archived = 1 ORDER BY repo_full_name",
|
|
(workspace_id,),
|
|
).fetchall()
|
|
return [dict(r) for r in rows]
|
|
|
|
|
|
def purge_project_local_state(
|
|
workspace_id: int, connection_id: int, repo_full_name: str
|
|
) -> None:
|
|
"""Drop every local trace of a project in one workspace."""
|
|
with get_db() as conn:
|
|
for table in ("board_state", "project_notes", "today_picks"):
|
|
conn.execute(
|
|
f"DELETE FROM {table} WHERE workspace_id = ? "
|
|
"AND connection_id = ? AND repo_full_name = ?",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
)
|
|
conn.execute(
|
|
"DELETE FROM project_states WHERE workspace_id = ? "
|
|
"AND connection_id = ? AND repo_full_name = ?",
|
|
(workspace_id, connection_id, repo_full_name),
|
|
)
|
|
|
|
|
|
# ── Journal d'administration (0.24.0, P2) ─────────────────
|
|
|
|
def log_admin(user_id: int | None, action: str, details: str = "") -> None:
|
|
with get_db() as conn:
|
|
conn.execute(
|
|
"INSERT INTO admin_audit (user_id, action, details) "
|
|
"VALUES (?, ?, ?)",
|
|
(user_id, action, details),
|
|
)
|
|
|
|
|
|
def list_admin_audit(limit: int = 100) -> list[dict]:
|
|
with get_db() as conn:
|
|
rows = conn.execute(
|
|
"SELECT a.*, u.username FROM admin_audit a "
|
|
"LEFT JOIN users u ON u.id = a.user_id "
|
|
"ORDER BY a.id DESC LIMIT ?",
|
|
(limit,),
|
|
).fetchall()
|
|
return [dict(r) for r in rows]
|