Repository navigation
pm stack: centralize all service data as shared tables in one database schema #1744
Description
Problem
Every PM-stack service keeps its own private data store today:
- aggregator: JSON files in ~/.local/state/pm-aggregator/ (actions, comments, registry, fleet feed, quota state, snapshots/)
- projector: its own SQLite file (~/.local/state/pm-projector/projector.db) — even though its design already namespaces its tables pmp_ precisely so they can live in a shared database (see pm-stack/projector/README.md: the registry fields are name-spaced so they can live in the fleet's existing database rather than requiring a dedicated one)
- Grist: its own document storage for the roadmap table and the registers
- Kaneo, Pad, ready-for-agent: their own stores again
Consequences: cross-service questions (links between projects, issues, roadmap rows; one event stream) need bespoke joins per pair; freshness depends on each service's own polling/restart behavior (see code#1288 for the Grist live-apply problem); backups and restarts are per-service crafts.
Proposal
Move the stack's service data into ONE database with a shared schema of namespaced table groups:
- One database (libSQL/Turso-shaped, matching the projector's existing storage choice; local file on the laptop with the fleet's existing backup legs) as the single home.
- Table groups per service with clear prefixes (pmp_ projector/link registry, agg_ aggregator stores, plus shared entity tables for items, links, comments, events that more than one service uses).
- Shared entities (items, links, comments, events) are written once and read by every service — no private copies. The projector's disposable-projection principle is preserved: its tables rebuild from GitHub; the registry (link writes) is the only non-rebuildable part and gets the backup legs.
- Services pick up data changes live (subscription or short-interval change polling with lag surfaced per code#1734) instead of on restart — this issue provides the data layer code#1288 needs.
- The event log (code#1739) and roadmap file integration (code#1738) should be designed against this schema, not against per-service files.
Acceptance
- An inventory + schema map: every current store (file, table, owner service, rebuildable or not) and its target table group.
- A migration plan per service, ordered so each step is independently shippable and reversible, with the aggregator's JSON stores and the projector DB as the first two moves.
- After migration: no service holds a private copy of items/links/comments; one backup target covers the stack's data; a data change made through one service is visible to the others without restarting any of them.
Raised by Drew 2026-10-28 (centralized management of all these services' data using shared tables in a db schema). Related: code#1288, code#1734, code#1738, code#1739, code#1199.
Acceptance criteria
- An inventory + schema map exists: every current store (file, table, owner service, rebuildable or not) and its target table group.
- A migration plan per service exists, ordered so each step is independently shippable and reversible, with the aggregator's JSON stores and the projector DB as the first two moves. (Plan: pm-stack/SHARED-SCHEMA-MIGRATION-PLAN.md, PR pm-stack: per-service migration plan for the shared schema (#1744 slice 2) #4881, merged 612ac37.)
- After migration: no service holds a private copy of items/links/comments; one backup target covers the stack's data; a data change made through one service is visible to the others without restarting any of them.
Metadata
Metadata
Assignees
Labels
pm-stack (Pad/Grist) in developing-today/codeLarge: multi-day or open-ended; expect staged check-insNew feature or requestIntake review done: acceptance criteria, links, and normalized labels addedOrg Project gate: queued next — orchestrator adds to the boardImportant and time-sensitiveFiled directly by a humanTriaged and routed (see agent note)Queue packet kind: verify, reproduce, and route this item
Activity
developing-today commentedon Oct 28, 2026
CODE-704
dezren39 commentedon Oct 28, 2026
Intake review
Related: #1288 (Grist live-apply — needs this data layer), #1734 (per-source lag on /health), #1738 (roadmap files integration), #1739 (cross-stack event log), #1199 (projector deployment). Sibling set filed the same day; this is the foundational piece the others should be designed against.
Notes: Verified still live at intake (2026-10-28T17:45Z): no PR or issue implements a shared PM-stack schema; the body's store inventory (aggregator JSON files, projector SQLite, Grist/Kaneo/Pad stores) is the current state. Acceptance criteria section appended formalizing the body's
## Acceptancelist; original body preserved above it.Risks: Largest item in the PM-stack set (effort/l). Migration touches every service's persistence; the registry is the only non-rebuildable part, so backup legs must be proven before any service moves. Scope creep risk if the shared-entity design tries to absorb service-private state too.
Suggested approach: Inventory + schema map first as its own slice, then one service at a time (aggregator JSON, then projector DB), each independently shippable/reversible per the acceptance criteria. Do not start #1738/#1739 storage design against per-service files once this lands.
🤖 Fleet monitor intake enrichment · 2026-10-28
63 remaining items
dezren39 commentedon Nov 4, 2026
agent-note:v1
state: handed-off
queue: repo-health-code-pilot
task: 9318 (triage, worker repo-worker-code, lane code-k), attempt 12
by: repo-worker-code lane code-k
at: 2026-11-04T21:02:00Z
Handed off to the opencode execution lane (assigned_lane=opencode via repo-queue lane). Fresh check: issue #1744 OPEN, priority/high, no hard-stop labels. Note: task 9318 is at its attempt cap (12/12) — if the claimant does not bind it this cycle it may become unclaimable and need a cap raise. The laptop claimant launches the bound opencode session; this lane does not work the task directly. Parked 1200s to release the claim for claimant binding. Labels on #1744 already match parked state: queue/parked + status/parked, work/assigned retained.
dezren39 commentedon Nov 4, 2026
🤖 Agent note · handed off
Merge pull request #5315 from developing-today/queue/9251-1744-pmp-li…
dezren39 commentedon Nov 5, 2026
agent-note:v1
state: handed off
task: 9318
worker: repo-worker-code
lane: code-n
execution_lane: opencode
issue: #1744
at: 2026-11-05T03:39:58Z
summary: Claimed by lane code-n and handed off to the opencode claimant. Fresh revalidation via gh this shift: state and labels checked immediately before this note (see task 9318 checkpoint); attempts 17/24.
dezren39 commentedon Nov 5, 2026
🤖 Agent note · handed off
opencode; handing off to the opencode claimant for the bound session. Fresh revalidation: issue pm stack: centralize all service data as shared tables in one database schema #1744 is OPEN with no hard-stop labels.dezren39 commentedon Nov 5, 2026
agent-note:v1
state: picked_up
task: 9318
worker: repo-worker-code
lane: bf419306-c4ef-470f-a553-beabf93239de (openchamber scheduled lane)
at: 2026-11-05T06:41:36Z
issue: #1744
summary: Claimed for fresh triage verification. GitHub shows #1744 OPEN; no held, user/owned, do-not-merge, or do-not-auto-edit labels. Related PRs #4692, #4881, and #5315 are MERGED; the issue still has criterion 3 unchecked. I am checking whether the remaining work is already queued or owned before routing anything.
dezren39 commentedon Nov 5, 2026
agent-note:v1
state: triaged
task: 9318
worker: repo-worker-code
lane: bf419306-c4ef-470f-a553-beabf93239de (openchamber scheduled lane)
at: 2026-11-05T06:57:03Z
Triage verdict: CONFIRMED; criterion 3 remains unmet
Verified current main
c7a0982d17: #1744 is OPEN; criteria 1 and 2 are complete and criterion 3 remains unchecked. The projector still defaults tofile:data/projector.dband has separatepmp_*tables; the aggregator still stores comments incomments.json; no runtimeshared_*tables were found. The migration plan leaves stack-wide propagation to future moves, and the current backups do not establish one common target for all native service data.PRs #4692 and #4881 are merged (inventory and migration plan); PR #5315 is merged and closes only the
pmp_linksarchive prerequisite. No open PR currently references #1744. Open PRs #4832/#4882 concern #941/#1253 and do not own this migration.The plan's next gate is P1: define/vet the target schema before Move 1. The schema map names groups but lacks column-level contracts, so I filed follow-up issue #6103 and queued implementation task #9916 (agg_actions/shared_events contract and reversible migration only; no live cutover). #1744 remains open; no service migration was performed in this triage.