Cloud repair should backfill and report legacy mutation required fields

[IrrealV]

📋 Pre-flight Checks

• I have searched existing issues and this is not a duplicate
• I understand this issue needs status:approved before a PR can be opened

📝 Bug Description

This is the product-repair slice split out from umbrella issue #299, per maintainer feedback to separate product repair behavior from wrapper/script hardening.

Legacy local SQLite databases can contain frozen sync_mutations.payload snapshots that violate current cloud canonicalization requirements. Current engram cloud upgrade doctor / repair does not give users a complete local-only repair path for the observed required-field blockers.

Observed blocker classes:

• session payloads with missing or empty directory.
• sessions.directory='' rows that should be backfilled before repairing corresponding mutation payloads.
• observation payloads with required fields missing or empty, observed as type='' in the WSL/Beta repair and content missing/empty in the macOS confirmation comment on Cloud sync legacy repair misses empty project/type mutations and autosync can fail silently #299.
• legacy/project-consolidation cases where sync_mutations.project no longer matches the canonical current project name, leaving repair inference incomplete unless aliases or project mappings are considered.
• old mutations with project='' / directory='' that need to be reported or canonicalized before project filtering hides them from project-scoped sync diagnostics.

🔄 Steps to Reproduce

1. Start from a legacy Engram database with pending sync_mutations rows captured before current cloud-required payload fields existed.
2. Include one or more rows such as:
   • entity='session' with payload.directory='' or missing.
   • local sessions.directory='' for the same project/session.
   • entity='observation' with payload.type='' or missing required content/title/scope fields.
   • sync_mutations.project='' for old imported/projectless mutations.
3. Run engram cloud upgrade doctor --project <project> and/or engram sync --cloud --project <project>.
4. Observe blocked sync requiring manual SQL rather than a complete dry-run/apply repair report.

✅ Expected Behavior

Product code should provide an explicit, local-first repair/diagnostic path that:

• dry-runs by default and supports an explicit --apply mode before mutating SQLite;
• backfills sessions.directory when an inferable canonical directory exists;
• updates frozen sync_mutations.payload snapshots for repairable required fields, including session.directory and observation required fields;
• reports unfixable rows with seq/entity/op/project/entity_key and the exact missing/empty field;
• handles project rename/consolidation cases through explicit alias/mapping input or clear manual guidance;
• reports projectless (project='') legacy mutations instead of letting project-scoped sync hide them;
• never edits last_acked_seq, never deletes mutations silently, and never auto-repairs during autosync.

❌ Actual Behavior

Real repairs required manual SQLite edits:

• WSL/Beta: 17 engram sessions with directory='', 5 old project='' / directory='' mutations, and one observation with type=''.
• Windows/Beta: 5 old project='' / directory='' mutations blocking scheduled sync for opencode and engram.
• macOS confirmation on Cloud sync legacy repair misses empty project/type mutations and autosync can fail silently #299: 560 sync_mutation_required_fields blockers reduced to 9 via manual SQLite repair; 558 were sessions missing directory, and 2 observations were missing content.

Operating System

Windows (WSL)

Engram Version

1.15.x / Beta cloud sync environments

Agent / Client

OpenCode

📋 Relevant Logs

# macOS confirmation from #299
sync_mutation_required_fields blocked findings: 560
entity=session, missing_fields=[directory]: 558
entity=observation, missing_fields=[content]: 2
blocked findings after manual SQL repair: 9

💡 Additional Context

Parent umbrella: #299.

Related but distinct issues:

• engram sync --cloud fails on legacy mutation payloads (existing users skip cloud-upgrade preflight) #230: legacy payload repair gap for engram sync --cloud, closed.
• Cloud sync fails: session mutations missing directory field when MCP server upserts sessions #249/engram save CLI creates sessions with directory='' when not in a git repo, blocks bootstrap #278: new empty session-directory sources from MCP/CLI, closed.
• Bootstrap creates orphan observations in cloud_mutations, breaks pull on other clients with FOREIGN KEY constraint #293: server-side orphan observations in cloud_mutations causing pull FK failures.

Suggested product shape from #299 discussion: a local SQLite repair command such as engram cloud repair backfill-directory --project <name> [--dry-run|--apply], potentially generalized to cloud repair legacy-mutations, plus improved cloud upgrade doctor reporting.

This issue intentionally excludes operator Bash/PowerShell wrapper behavior; that is split into a separate child issue.

[ricardoarz-dev]

+1, me pasó hoy en mac 1.15.9. Tres mutations relation/upsert (not_conflict del judge automático cross-agent) me bloquearon el sync de un project entero. El repair --apply tiró applied: false con manual-action-required, así que terminé borrando las filas a mano:

DELETE FROM sync_mutations
WHERE entity='relation' AND op='upsert' AND acked_at IS NULL AND project='X';

Después de eso el sync pasó OK, 10 mutations en un chunk.

Lo raro es que las 3 mutations se crearon en la misma ráfaga de 2 segundos, hoy mismo, después de haber actualizado el binario a 1.15.9. Sospecho que los procesos MCP que tenía corriendo cacheaban el binario viejo y siguieron escribiendo con schema antiguo hasta reiniciarlos.

Si querés payload de las mutations o más data, avisame.

[ricardoarz-dev]

Following up on diegoazh's macOS observation in #299 — managed to trace the empty-content observations to a specific tool, and have a clean repro that doesn't depend on legacy state. It's mem_session_summary MCP tool generating them on every call.

Setup

Mac, engram 1.15.9, MCP via Claude Code. No legacy DB — fresh writes today.

Repro

Call the MCP tool with all 5 args populated:

{
  "goal": "any text here",
  "discoveries": "any text here",
  "accomplished": "any text here",
  "next_steps": "any text here",
  "relevant_files": "any text here"
}

Tool returns Session summary saved for project "<X>". Looks fine.

Inspect

SELECT id, type, title, length(content) FROM observations ORDER BY id DESC LIMIT 1;
-- 775 | session_summary | Session summary: <X> | 0

SELECT seq, entity, op, payload FROM sync_mutations ORDER BY seq DESC LIMIT 1;
-- 3178 | observation | upsert | {"sync_id":"...","type":"session_summary","title":"Session summary: <X>","content":"",...}

All 5 input args are dropped before persistence. The save succeeds with empty content, and a malformed mutation is enqueued — which is what eventually trips cloud sync per #299.

Cleanup gotcha

Direct DELETE FROM observations WHERE id=775 fails with unsafe use of virtual table "observations_fts". Workaround: drop the 3 FTS triggers (obs_fts_insert/delete/update), delete, then INSERT INTO observations_fts(observations_fts, rowid, ...) VALUES('delete', 775, ...) for the FTS index, then recreate the triggers. The repair --apply path didn't catch these in my case.

Note

This feels like the upstream cause for the empty-content class of mutations the repair work in this issue is trying to clean up. Looks like the tool handler isn't compacting the args into the content field before save.

Happy to open a separate fix(mcp) issue if it's better tracked apart from the repair scope here.

[ricardoarz-dev]

Update — turns out my earlier comment was a usage error on my side, not an upstream bug. Apologies for the noise.

The tool only accepts content and session_id. My client was passing goal/discoveries/accomplished/next_steps/relevant_files as separate top-level args, which the handler ignores silently — so content arrived empty and got persisted that way. The empty-content observations I reported were caused by my caller building the call wrong, not by the tool.

Verified today on engram 1.15.12: calling mem_session_summary with the documented single content markdown string works correctly, content gets persisted with full length and no malformed mutations.

Probable cause on my end: a wrapper or cached schema somewhere built the call with an old args shape that never existed in the tool definition. Investigating that locally.

The other items in this thread (empty project, legacy mutation backfill, relation mutations from #379) are separate from this — leaving #340 untouched on those.

[Alan-TheGentleman]

Retested against main. The payload-level relation repair landed (7af10b9), but the core ask here is still open: there is no backfill for sessions.directory on legacy rows, and no reporting of projectless (project="") mutations before project-scoped sync hides them. Approving to track that. A --dry-run/--apply backfill plus the projectless report is the scope.