p24-infra Docs

plan-5364-priorities-supabase-table

10 min read

Plan: Migrate docs/priorities.md off git to a Supabase table

Issue: #5364 Type: Code-change-design (plan only — no implementation PR yet) Status: Reviewed against the live tree — worker session bms4-cw-1 on bms-4 (dev-coder) Last updated: 2026-08-04 Author: Claude Opus 4.8 (worker session bms4-cw-1) Related: #4559 (Phase 3.5 status-sync — docs/plans/plan-4559-priorities-status-sync.md, the machinery this plan simplifies) · #3178 (SOPS reorg — same “one god-file, many writers” failure class)

0. Summary — recommendation

The issue’s core mechanism is sound and I recommend building it: row-level DB writes eliminate the concurrent-checkout merge-collision class that docs/priorities.md documents against itself. DB load is a non-issue exactly as the issue argues (~5–20 writes/day against a project already doing ~720 dispatcher reads/day). The prior-art reuse (copy RLS from dev_r_status_snapshots, reuse dev_r_agent_sessions for the narrative log, mirror scripts/priorities_sync.py style) is the right call — this is engineering surface area, not a load problem.

Five of the plan’s stated details do not survive a read of the current tree. None is fatal; two simplify the work and three are corrections that prevent a broken migration or data loss. They are listed in §1 (Live-tree verification) and folded into the design in §2–§7.

This is a plan-only deliverable. The implementation PR (migration + script + backfill + touchpoint cutover) follows in a second issue/PR after /review-plan sign-off, per the plan-label workflow.

1. Live-tree verification — deltas from the drafted plan

Every claim in the issue body was checked against main at commit c60d02b. Results:

#Plan claimLive-tree findingImpact
1”reuse dev_r_agent_sessions.summary; add 'interactive' to the type CHECK constraint (small migration). It currently only gets rows from type IN ('worker','subagent').”There is no CHECK constraint on dev_r_agent_sessions.type. supabase/migrations/027_dev_r_agent_sessions.sql:10 defines type TEXT NOT NULL DEFAULT 'worker' — free text, no enum guard. The only CHECK in 027 is the RLS policy’s WITH CHECK (true). No migration in the repo adds a type CHECK later, and no migration contains the string interactive.Simplifies — no migration is needed to allow type='interactive'; the column already accepts it. BUT a CHECK may have been applied to the live DB out-of-band (not tracked in a migration file). The implementer must verify the live catalog (information_schema / Management API) before backfilling; if a CHECK exists there, add the migration; if not, skip it. status on this table does have a CHECK (worker-issue.md Step 11 relies on 'completed'/'failed'), so do not confuse the two columns.
2RLS lockdown history cited as 038_revoke_anon_lock_down_dev_r_tables.sql and 039_lock_down_devops_schema_and_public_views.sql.Neither filename exists. The actual anon-lockdown migrations are 20260730115455_revoke_anon_execute_definer_functions_4663.sql and 20260730160516_revoke_anon_grants_rls_exposed_tables_4672.sql.Correction only — the plan’s point is fully supported (there is a documented history of dev_r_* tables shipping open-to-anon and needing a dedicated lockdown pass; #4663/#4672). Keep the rule: anon gets nothing from the first migration. Cite the correct filenames.
3New table dev_r_priorities with tier text CHECK (P0/P1/P2/P3) models the whole file.The file has six H2 data sections, not four: ## 🔴 P0, ## 🟠 P1, ## 🟡 P2, ## 🟢 P3, ## 🟢 Pending Human Actions (columns | Item | Notes | Issue | — a different 3-column shape), and ## References (a static link list, not tabular). plan-4559-priorities-status-sync.md §2 already documents that its parser “deliberately excludes ## Pending Human Actions (middle column is Notes, not Status) and ## References”.Correction — the tier CHECK must include a value for the Pending-Human-Actions rows (recommend tier IN ('P0','P1','P2','P3','human-action')) with a nullable notes covering that section’s middle column, or those rows stay in git / a separate handling. ## References is static content, not data — the render step must reproduce it from a template, not from the table.
4status text CHECK (open/in_progress/resolved).The live Status column is rich free text with emoji semaphores carrying real operational nuance — e.g. 🟡 PR-E shipped — PR-D/PR-G need human coordination, 🟠 DEFERRED to 07-13 — workflows disabled, 🟢 CLOSED, 🟡 PARTIAL — 11 keys pending. A three-value enum discards this.Correction — keep a coarse status text CHECK (open/in_progress/resolved) for machine logic plus a free-text status_detail text column that round-trips the human-facing status string, so render reproduces the current file faithfully.
5027_dev_r_agent_sessions.sql pairs its grafana_readonly_select RLS policy with an explicit GRANT SELECT ... TO grafana_readonly, whereas dev_r_status_snapshots shows none — “verify which is load-bearing; when in doubt include the explicit GRANT.”Confirmed accurate. 027:48 has GRANT SELECT ON dev_r_agent_sessions TO grafana_readonly;, and a separate follow-up migration 20260625_agent_sessions_grafana_readonly_grant.sql exists specifically because the sibling agent_sessions table shipped its policy without the grant and read access silently failed.Keep the caution — it is well-founded. dev_r_priorities must ship the explicit GRANT SELECT ON dev_r_priorities TO grafana_readonly; alongside the RLS policy in the same migration. A policy alone does not grant table privileges in Postgres.

Additional observations (non-blocking):

  • File size: docs/priorities.md is 158 KB (202 lines, very long session-log paragraphs), not the “several KB” the issue estimates. The backfill parser must split the ## Recent session log into per-entry rows — entries are - **YYYY-MM-DD (…) — title …** bullets, newest first, some ~5 KB each. Still trivial for Postgres; the estimate of “+10–20 MB/year” stands.
  • scripts/priorities_sync.py is a structural parser, not a regex scrubber (its own docstring and plan-4559 §3 say so). Its resolution safety gate is precise: a pull/N ref resolves only when state==MERGED; an issues/N ref only when state==CLOSED and milestone.title=='Main'; any non-gh ref (GitLab MR, doc path, prose) or any gh error ⇒ skip the row (fail-safe). The simplified DB resolve path must preserve this exact gate — moving to an UPDATE removes the git-write apparatus, not the safety criteria.

2. Schema — supabase/migrations/<ts>_dev_r_priorities.sql

<ts> = date +%Y%m%d%H%M%S. Single migration, additive only.

CREATE TABLE IF NOT EXISTS devops.dev_r_priorities (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tier         text NOT NULL CHECK (tier IN ('P0','P1','P2','P3','human-action')),
  item         text NOT NULL,                 -- the "Item" cell (markdown allowed)
  status       text NOT NULL DEFAULT 'open'
                 CHECK (status IN ('open','in_progress','resolved')),
  status_detail text,                          -- verbatim human status string incl. emoji (delta #4)
  notes        text,                           -- "Notes" cell for human-action rows (delta #3)
  issue_refs   jsonb NOT NULL DEFAULT '[]'::jsonb,  -- [{kind:'issue'|'pr'|'gitlab'|'doc', number, url}]
  sort_order   int  NOT NULL DEFAULT 0,        -- preserve intra-tier ordering for render
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  resolved_at  timestamptz
);
 
ALTER TABLE devops.dev_r_priorities ENABLE ROW LEVEL SECURITY;
 
-- service_role: full access (write path). Pattern from dev_r_status_snapshots.
CREATE POLICY "service_role_all" ON devops.dev_r_priorities
  FOR ALL USING (true) WITH CHECK (true);
 
-- grafana_readonly: SELECT only. Policy AND explicit grant (delta #5 — both are load-bearing).
CREATE POLICY "grafana_readonly_select" ON devops.dev_r_priorities
  FOR SELECT TO grafana_readonly USING (true);
GRANT SELECT ON devops.dev_r_priorities TO grafana_readonly;
 
-- anon gets NOTHING — no policy, no grant (delta #2). Do not add an anon lockdown follow-up later.
 
CREATE INDEX IF NOT EXISTS idx_dev_r_priorities_tier_status
  ON devops.dev_r_priorities (tier, status);
CREATE TRIGGER trg_dev_r_priorities_updated_at
  BEFORE UPDATE ON devops.dev_r_priorities
  FOR EACH ROW EXECUTE FUNCTION devops.set_updated_at();  -- reuse existing helper if present; else inline

Schema placement: confirm whether dev_r_* tables live in the devops schema or public — check an existing dev_r_* migration’s CREATE TABLE target and mirror it exactly (the lockdown migrations #4663/#4672 touched a devops schema; verify before writing the DDL). The set_updated_at trigger helper may or may not exist — grep the migrations; if absent, set updated_at = now() in the script’s UPDATE statements instead of a trigger.

Register in dev_r_services in the same migration (pattern: the INSERT INTO ... dev_r_services at the tail of 027_dev_r_agent_sessions.sql). Per CLAUDE.md §Compliance: compliance_workbook='yes', workbook_url → this plan doc. No new cron, no new AI system — nothing to register in dev_r_ai_systems.

3. Narrative log — reuse dev_r_agent_sessions (delta #1)

  • No CHECK migration is needed for type='interactive' unless the live DB carries an out-of-band CHECK (verify first — §1 delta #1). If it does, add a one-line migration widening it; if not, the backfill and the log subcommand write type='interactive' directly.
  • Historical backfill: dev_r_agent_sessions requires session_id NOT NULL UNIQUE and carries live- session columns (started_at, ended_at, branch) the markdown log never had. Backfill each session-log entry with a synthetic session_id = 'backfill-YYYY-MM-DD-N', type='interactive', tags = ARRAY['backfill'], summary = the entry prose, and best-effort started_at from the entry’s date. This keeps them distinguishable from live tracking.
  • Note the status CHECK on this table only allows 'completed'/'failed' — backfill rows should use 'completed'.

4. Write path — scripts/priorities.py

Sync Python, no framework (mirrors scripts/priorities_sync.py style). Uses the Management-API-SQL pattern from docs/playbooks/supabase-management-api-sql-windows.md — never raw psql. Subcommands:

SubcommandAction
add --tier --item [--status] [--refs] [--notes]INSERT a priority row
update <id> [--status] [--status-detail] [--item] [--refs]PATCH a row, bump updated_at
resolve <id>set status='resolved', resolved_at=now() — gated by the §1 safety criteria when --check-refs is passed (issue CLOSED+milestone==‘Main’ / PR MERGED)
log --summary [--date]INSERT a type='interactive' row into dev_r_agent_sessions
renderSELECT all rows + narrative, emit the full docs/priorities.md markdown (P0–P3 tables, Pending Human Actions, static References footer, session log) to stdout

This becomes the only sanctioned write path. Error path per CLAUDE.md §Error Notification (Discord webhook P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL + gh issue label bug) on any failure.

5. Generated read-only docs/priorities.md

scripts/priorities.py render > docs/priorities.md. Stops being hand-edited; regenerated/committed periodically or via a light CI step (not on every write). Header gains a <!-- GENERATED — do not edit; run scripts/priorities.py --> banner so a human doesn’t hand-edit it.

6. Simplify Phase 3.5 / priorities_sync.py (delta note)

Replace the clone + GitHub-App-token + branch + PR + diff-shape-gate flow (plan-4559 §5) with a direct scripts/priorities.py resolve --check-refs. Keep the human-visible report (Discord/log) before any row flips to resolved, and keep the exact resolution safety gate (§1). Drop only the git-write apparatus that existed solely because the target was a file. NightlyTriagePhaseFailed metric + Discord error wiring are unchanged.

7. Touchpoints to rewire (p24-infra only — phase 1 scope)

FileChangeIn git / PR?
CLAUDE.md §docs/priorities.md (lines ~548–554)“edit the file” → “run scripts/priorities.py”yes
infra/agent-prompts/worker-issue.md (lines 21–32)prohibition text “don’t edit the file” → “don’t write to dev_r_priorities”yes
.claude/commands/nightly-devops-triage.md Phase 3.5 (lines ~260–270, ~1800)swap git-write flow for priorities.py resolveyes
docs/playbooks/changelog-priorities-pattern.mdnote p24-infra is on the DB pattern; other 5 repos stay on markdown (phase 1 scope)yes
/sr, /p24-status skillsgrep found no direct docs/priorities.md reference in .claude/skills/ — verify at implementation time; likely no change neededyes if any
C:\Users\konar\.claude-ecotrans\CLAUDE.md §Session Prioritiesflip the generic ALL-PROJECTS “hand-edit docs/priorities.md” instructionNO — local-only, outside git. Manual follow-up for the user (see §10).

8. Cutover risk & rollback

  • Single-PR cutover: ship table + script + full backfill (every current row + every session-log entry) and flip the p24-infra CLAUDE.md instruction in one PR — no window where both paths are “correct”. Announce the cutover commit hash so in-flight sessions rebase past it before their next priorities edit.
  • Rollback: purely additive (no file/column dropped). Revert the CLAUDE.md-instruction + script-touchpoint changes to resume hand-editing the markdown; leave the table in place unused. No cleanup required.

9. Compliance

  • No new cron, no new AI system → nothing to register in dev_r_ai_systems; docs/eu-ai-act-compliance.md unchanged. Phase 3.5 keeps its existing error-notification wiring.
  • New table → dev_r_services row (in-migration INSERT) + this ops/plan doc satisfy CLAUDE.md §Compliance.
  • Do NOT ALTER dev_r_services — it is a VIEW (CLAUDE.md §Do-NOT / #4293→#4442→#4447); the registration is an INSERT into its underlying relation, exactly as 027 does.

10. Open decisions (for /review-plan + human)

  1. Local file (C:\Users\konar\.claude-ecotrans\CLAUDE.md) — outside the repo/sandbox; the worker cannot edit it. Manual follow-up for the user/architect, needed in the same sitting as the p24-infra cutover so the generic ALL-PROJECTS rule stops telling sessions to hand-edit the now-generated file.
  2. Schema (devops vs public) and set_updated_at helper existence — resolve by reading an existing dev_r_* migration before writing DDL (§2 note).
  3. Live type CHECK on dev_r_agent_sessions — verify via Management API before deciding whether a widening migration is needed (§1 delta #1).
  4. Pending-Human-Actions rows — confirm tier='human-action' modelling vs leaving that section in git.

11. Dependencies

  • docs/playbooks/supabase-management-api-sql-windows.md (SQL execution pattern — no raw psql).
  • docs/playbooks/supabase-migrations.md (migration apply + the dev_r_services-is-a-VIEW hazard).
  • SUPABASE_ACCESS_TOKEN (Management API PAT — canonical home secrets/administration.env.sops, per CLAUDE.md SOPS map). Referenced by NAME only; a secret-manager/sys-admin path supplies it at run time — not this dev-coder plan’s concern.

12. Not in scope (phase 1)

Migrating the other 5 repos’ docs/priorities.md; any UI beyond the rendered markdown.

Graph View