bug(store): Database maintenance toolset. Multiple data inconsistencies found.

[carolitascl]

📋 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

🔍 Problem Description

Reviewed Engram database contents and found issues that engram options currently cannot address. Multiple data inconsistencies were detected across tables.

💡 Proposed Solution

Add database maintenance toolset for Engram to address data inconsistencies.

1. Project deletion: Delete a specified project and all associated records across every related table. A project deletion must be fully cascading so that no residual records remain in sessions, observations, prompts, memory_relations, sync_enrolled_projects, sync_mutations, or any other project-bearing table. Currently, deleted projects can reappear in "engram tui" until "--hard" is used, indicating that standard deletion leaves orphaned records behind.

2. Project rename: Rename a specified project and propagate the new name consistently across all tables that store the denormalized project field. Since there is no master projects table, every affected record must be updated atomically to prevent project fragmentation.

3. Directory-to-project mapping repair: Rebuild the project field from the directory field for sessions where the project value is missing, empty, or inconsistent with the directory basename. This repairs records where a valid directory exists but the associated project identifier is absent or incorrect.

4. Project name normalization: Enforce canonical project naming by applying case-insensitive matching and stripping version suffixes at write time. This prevents project fragmentation caused by variants such as ProjectName, projectname, ProjectName-0.0.1, and ProjectName-0.0.1-2 being treated as separate projects.

5. Phantom record cleanup: Detect and optionally purge stale, unreachable, or invalid records, including:

• Soft-deleted observations whose deleted_at retention period has expired.
• Zero-size observations, sessions, or prompts.
• memory_relations entries referencing missing sessions, observations, prompts, or projects.
• sync_enrolled_projects entries with no corresponding sessions or content.
• Other dangling or unreachable records discovered through referential integrity checks.

6. Project cleanup: Detect and optionally batch delete orphaned projects that have no sessions, observations, prompts, or other meaningful content. This includes projects that were never active, projects whose primary records were already removed, and projects that exist only because of stale metadata.

7. Sync mutation audit and repair: Audit sync_mutations for unresolved or malformed entries and optionally repair or purge them. This includes:

• Removing mutations where acked_at is NULL and the mutation is no longer actionable.
• Backfilling missing required fields such as directory from the corresponding session or observation record.
• Identifying mutations that cannot be repaired and would otherwise accumulate indefinitely.
  Unresolved mutations can silently block cloud synchronization, particularly when required fields are missing.

8. Data integrity review: Perform a comprehensive cross-table consistency audit covering sessions, observations, prompts, sync_enrolled_projects, sync_mutations, and memory_relations. Report mismatches, orphaned references, inconsistent project identifiers, missing relationships, and other integrity violations without modifying data. This serves as the final verification step after cleanup and repair operations and helps explain discrepancies such as engram projects list and engram tui displaying different project sets.

9. Project name with spaces: Projects whose names contain spaces are not being detected correctly.

10. Export data of a project: Export all data associated to a single project

11. Database lifecycle management: Commands to start the database as a daemon and stop it cleanly. Ensures the Engram database service can be started as a background daemon and stopped safely, guaranteeing a clean shutdown that prevents WAL corruption, incomplete writes, or orphaned locks.

📦 Affected Area

Store (database, queries)

🔄 Alternatives Considered

Migration to a new db

📎 Additional Context

Result rrunning engram doctor

Engram Doctor: blocked
Checks: 4 ok=3 warnings=0 blocked=1 errors=0

[ok] manual_session_name_project_mismatch — No issues detected.
  why: The evaluated store evidence matches expected operational invariants.
  next: No action required.
[ok] session_project_directory_mismatch — No issues detected.
  why: The evaluated store evidence matches expected operational invariants.
  next: No action required.
[ok] sqlite_lock_contention — No issues detected.
  why: The evaluated store evidence matches expected operational invariants.
  next: No action required.
[blocked] sync_mutation_required_fields — 64 finding(s) detected.
  why: A pending sync mutation with missing required fields can block safe cloud replication and must fail loudly instead of being silently dropped.
  next: Run engram cloud upgrade doctor and inspect the mutation payload before any manual repair.
  
sync_mutation_payload_missing_required_fields: session payload missing required fields: directory
  evidence: {"entity":"session","entity_key":"manual-save-test","missing_fields":["directory"],"op":"upsert","project":"test","seq":2,"target_key":"cloud"}

This checks failed when tried to ran

engram doctor repair failed: unsupported repair check sync_mutation_required_fields
engram doctor repair failed: unsupported repair check sqlite_lock_contention 

This is what finally works, but only for one project

engram cloud enroll test_project
engram cloud upgrade repair --project test_project --apply