Newer
Older
gnexus-book / 00-overview / postgres-migration-roadmap.md
@Eugene Sukhodolskiy Eugene Sukhodolskiy 1 day ago 23 KB docs: record Phase 3 completion

owner: gmikcon status: draft last_reviewed: 2026-11-19 review_interval: 30d confidence: medium

source_of_truth: analysis-2026-11-19

PostgreSQL migration roadmap

Execution plan for ADR-0002: move Gnexus Book from file + Git storage to PostgreSQL with built-in versioning, handbook-compliant authentication and MCP, and a later one-time migration of the existing knowledge base.

Platform rules are not repeated here. Read the gnexus-handbook and the sections linked from the ADR before starting a phase.

1. Goal and success criteria

The migration is complete when:

  1. PostgreSQL is the system of record for documents, inventory, versions, users, changesets, and audit data.
  2. Every current capability listed in the ADR coverage matrix works against the database with the same observable contract.
  3. Every applied write is an immutable version grouped into an atomic changeset, with author, actor, timestamp, summary, and reason.
  4. Users authenticate through gnexus-auth; permissions are enforced; the actor of every write is recorded.
  5. MCP is served over streamable HTTP with per-user keys and provisioning.
  6. The UI keeps its current capabilities and gains auth, history/diff, MCP keys, and en/uk/ru localization.
  7. Database dumps go to a separate host and have been restored at least once.
  8. The old file write path is removed; the Git repository remains as a read-only archive.

2. Scope

In scope

  • PostgreSQL schema and migrations.
  • Application-level versioning: immutable versions, changesets, diff, rollback.
  • Documents, inventory, relationships, validation, freshness, search.
  • gnexus-auth SSO, user mirror, roles, actor attribution, audit.
  • MCP streamable HTTP, per-user keys, key provisioning, key-management UI.
  • UI parity plus auth gate, navigation, i18n, history/diff.
  • One-time import of the existing Markdown/YAML knowledge base.
  • Backups, health, monitoring, deployment, and service documentation.

Out of scope

  • Branches, merges, rebases, or a general-purpose distributed VCS.
  • Markdown/JSONL export from the database (owner decision; dumps only).
  • Visual topology map, embedding search, generated inventory index.
  • Editing inventory/docs directly from the UI beyond the existing proposal flow.
  • Public exposure decision; the service stays LAN-only until separately decided.

3. Constraints and principles

  • No big bang. The file backend stays working until the database backend passes parity tests and the owner approves cutover.
  • Dual-run before cutover. The same read queries must be run against both backends and compared.
  • Safety-first. Backup and rollback plan before every risky phase; destructive actions are announced first.
  • Handbook compliance is not a later phase. Schema and interfaces must leave room for SSO, per-user keys, actor fields, i18n, and off-host backups from the start.
  • The database is a single point of failure. Off-host dumps and restore drills are part of Phase 1, not a cleanup task.
  • Agent contracts matter. Existing MCP tool names remain usable during a transition period unless the owner chooses otherwise.

4. Target architecture

Browser (cookie session)        AI agent (Bearer mcp_* key)
        |                                  |
        +----------------+-----------------+
                         v
                  FastAPI / ASGI
        +----------------+-----------------+
        |                                  |
   REST /api/*                  MCP /mcp-protocol/
        |                                  |
        +------------ service layer --------+
                         |
              repositories / unit of work
                         |
                    PostgreSQL
                         |
        +----------------+-----------------+
        |                |                 |
   pg_dump cron      gnexus-auth       hard-panel / Synapse
   -> separate host  (SSO, webhooks)   (health, alerts)

Implementation rules:

  • Routers and MCP tools call the same service functions, with the same validation and error semantics.
  • Repositories are split into interfaces with file-backed and database-backed implementations until cutover.
  • All writes go through a changeset service. No repository exposes an unversioned write.
  • Validation is a service used by apply/publish and by the read-only report endpoints.
  • Search, relationships, and freshness are database queries, not filesystem scans.

5. Workstreams

Stream Covers Main phases
A. Storage and schema PostgreSQL, migrations, repositories, JSONB inventory 1, 3
B. Versioning and change management Versions, changesets, diff, rollback, audit 2
C. Validation, search, relationships Schemas, secret scan, FTS, freshness, graph 3
D. Identity and access gnexus-auth SSO, users, roles, actor 4
E. MCP Streamable HTTP, keys, provisioning, tool parity 5
F. UI Auth gate, navigation, i18n, history/diff, MCP keys 6
G. Migration and cutover Importer, dual-run, reconciliation, freeze 7
H. Operations and documentation Backups, health, monitoring, docs, decommission 1, 8

6. Decision gates

Resolve these before the phase that depends on them. On 2026-11-19 the owner instructed implementation to proceed, so the recommended defaults below are adopted as implementation defaults. They can still be overridden before the dependent phase.

ID Decision Recommended default Needed by
D1 Import Git history or current state only? Import the current state as one import changeset; optionally replay Git history later Phase 7
D2 Keep old MCP tool names? New names primary, old names as deprecated aliases for one transition period Phase 5
D3 May agents publish without review? Publish content changes; require owner review for access, security, network exposure, backup, and critical-service changes Phase 4
D4 Document identity UUID primary key + mutable slug with a redirect table; keep path in the API as an alias Phase 1
D5 What is "raw inventory"? Canonical YAML serialized from JSONB, explicitly marked as generated Phase 3
D6 Service exposure LAN-only first; decide on a *.gnexus.space subdomain and public MCP later Phase 6
D7 Delete/rename scope Rename with redirects and soft delete in v1; hard delete out of scope Phase 2

7. Roadmap phases

Effort is relative: S (days), M (1–2 weeks), L (several weeks). These are planning sizes, not commitments.

Phase 0 — Baseline, backup, decisions (S)

Goal: start from a known, recoverable state.

Tasks:

  • Copy today's backup archive and Git bundle to a separate host or NAS; verify the copy and perform a restore test there.
  • Resolve decision gates D1–D7 or explicitly defer each one.
  • Record the test baseline: current suite result and the known date-dependent freshness failure.
  • Decide how to handle that test: review documents whose content is actually verified, or relax the assertion to ignore expected stale-document warnings. Do not fake last_reviewed.
  • Choose the PostgreSQL deployment topology for development and production (containers or native service), and where dumps go.
  • Confirm the first database version and migration tool (recommended: SQLAlchemy 2.0 with Alembic and psycopg).

Acceptance:

  • Backup exists off-host and has been restored there.
  • Decisions D1–D7 are recorded in this document or the ADR.
  • The team knows which tests are expected to fail and why.

Rollback: none needed; this phase only adds safety.

Phase 1 — Database foundation and read mirror (M)

Goal: serve every read from PostgreSQL while the file backend remains canonical for writes.

Deliverables:

  • PostgreSQL service for development and production; .env configuration; DB credentials from the platform secret process.
  • SQLAlchemy models and the first Alembic migration for: documents, doc_versions, blobs, inventory_types, schemas, inventory_items, inventory_item_versions, changesets, change_items, validation_runs, audit_log, plus reserved users/actor columns.
  • Repository interfaces and two implementations: existing file-backed and new database-backed.
  • Importer v0: current Markdown/YAML/schemas → database.
  • Read paths (/docs, /docs/read, /search, /inventory, /relationships, /validate, /health/freshness) switchable by configuration.
  • Parity test harness that runs the same queries against both backends.
  • pg_dump job to a separate host, retention policy, and a restore script.

Acceptance:

  • Every read endpoint returns equivalent results from both backends on the current corpus.
  • Import is repeatable and produces the same checksums.
  • A dump from the new database has been restored on the separate host.
  • No write path has been switched yet.

Rollback: change the configuration back to the file backend; the database can be dropped and re-imported.

Phase 2 — Versioned documents and changesets (M)

Goal: replace the document write path and Git commit semantics.

Deliverables:

  • Service layer with document reads and writes, validation, and changeset handling.
  • Draft → apply/publish flow in one transaction; failure leaves no partial state.
  • Immutable versions with parent pointers, author, actor, timestamps, summary, reason.
  • Diff between versions and against the current version.
  • Rollback as a new version pointing at old content.
  • Audit entries for every applied changeset.
  • API compatibility: keep POST /changes, GET /changes, GET /changes/{id}, POST /changes/{id}/apply; add explicit changeset/publish endpoints.
  • MCP compatibility: propose_doc_change, apply_pending_change, update_doc keep working; commit_changes maps to publish or becomes a deprecated alias (D2).

Acceptance:

  • Document create/update through the proposal flow works against the database.
  • Validation failure rolls the transaction back and leaves the previous version current.
  • Concurrent writes do not lose updates; conflicting edits are rejected or serialized.
  • History, diff, and rollback are covered by tests.
  • The file backend is no longer used for document writes in the database mode.

Rollback: keep the file write path behind a feature flag until Phase 7.

Phase 3 — Inventory, relationships, search, freshness (M)

Goal: reach full functional parity for inventory and derived views.

Deliverables:

  • Inventory read/write services over JSONB; per-type schemas stored in schemas.
  • Unique (type, item_id) among live items; immutable item IDs.
  • Patch/update and create semantics matching today's behavior.
  • Schema validation, duplicate detection, and the existing secret scan applied to candidate versions.
  • Relationship graph computed from inventory data, including unresolved references.
  • Search: PostgreSQL FTS plus a substring/line-match path that preserves the current response shape.
  • Freshness queries: required metadata, staleness by interval, missing docs targets.
  • Importer v1 for inventory and schemas.

Acceptance:

  • The ADR coverage matrix is satisfied for docs and inventory.
  • validate_repository is clean on the imported database.
  • Relationships match the file backend exactly on the current corpus.
  • Search returns the same matching paths and line content for the parity fixture set.

Rollback: database mode remains read-only for inventory writes until parity is signed off.

Phase 4 — Identity, access, audit actor (M)

Goal: make the service genuinely multi-user and handbook-compliant.

Deliverables:

  • gnexus-auth OAuth 2.0 PKCE client; /auth/login, /auth/callback, /auth/logout, GET /me.
  • Cookie session tables and PKCE state storage (or the Synapse Bearer variant, if chosen).
  • Local user mirror keyed by sub; profile is the source of truth.
  • Webhooks with HMAC verification: profile update, global logout, session revocation, user blocked/archived/deleted, role/permission changes.
  • Roles and permissions; policy for read, propose, publish, and administration (D3).
  • Actor fields on writes: actor_kind, actor_name, via; X-Actor: user for REST and is_user for MCP write tools.
  • Locale preference storage and effective-locale resolution.

Acceptance:

  • Unauthenticated API calls are rejected; /health stays public.
  • A user without publish permission cannot publish.
  • A blocked user loses sessions; webhook handlers are tested.
  • Every write records the correct actor independently of the key owner.
  • No custom password storage or login page exists.

Rollback: authentication can be disabled in development (auth-off pattern), but the production database must not be exposed without it.

Phase 5 — MCP compliance and key management (M)

Goal: make agents first-class, per-user consumers of the service.

Deliverables:

  • Streamable HTTP MCP endpoint at /mcp-protocol/ (trailing slash preserved).
  • Legacy SSE adapter only if needed for old clients; stdio kept for tests and local development only.
  • ASGI bearer guard: hash lookup, uniform 401, WWW-Authenticate, last_used_at.
  • mcp_tokens storage with hash, hint, consumer, role/scopes snapshot, revoke.
  • Per-user key management UI/API; one active key per (user_id, consumer).
  • /internal/keys provisioning endpoints with service-to-service auth, allowlist, rate limiting, and audit; not exposed through nginx.
  • All existing tools and resources exposed over HTTP; mutating tools accept is_user; tool errors returned as data.
  • Tool names: new names primary, old names as aliases (D2).
  • Rate limits for sensitive operations.

Acceptance:

  • A personal key authenticates an MCP request; a revoked or blocked key gets 401.
  • Reissue returns plaintext once and revokes the old key; replayed create returns 409.
  • Provisioning is idempotent and audited.
  • Tool catalog and resource behavior match the tool contract tests.
  • /internal is unreachable from outside the allowed network.

Rollback: keep the old MCP entry point available read-only during transition; revoke new keys if necessary.

Phase 6 — UI parity and platform conventions (M)

Goal: the UI covers the current functionality and follows the platform shell.

Deliverables:

  • Auth gate with splash, /me, denied/ready states, and explicit login click.
  • Drawer navigation; GnPageHeader on every page; kit components only.
  • i18n module with en, uk, ru, account locale as default, per-service override.
  • Documents: list, search, metadata, body, history, diff.
  • Changesets: draft list, review, apply/publish, validation results.
  • Inventory: types, records, JSON detail, raw view with the generated-format notice.
  • Validation and freshness reports, including issue details.
  • MCP keys page.
  • Optional: Markdown rendering, traffic-route view, relationship graph.

Acceptance:

  • All current UI capabilities are present.
  • Login/logout, locale switch, history/diff, and key management work end-to-end.
  • No visual styles are copied into the service; changes go to the UI kit.
  • Accessibility and phone-width behavior are checked.

Rollback: the UI can keep using the old endpoints until the new ones are proven; no data is at risk.

Phase 7 — Migration, dual-run, cutover (L)

Goal: move the existing knowledge base into the new system and make it canonical.

Deliverables:

  • Importer v1 with validation, idempotency, and a dry-run mode.
  • Optional Git history replay (D1): one changeset per commit, authors and timestamps preserved, deleted documents represented as soft-deleted versions.
  • Reconciliation report: document count, inventory count, version count, checksums, validation and freshness parity.
  • Full read parity run over the real corpus.
  • Cutover runbook: announce, freeze writes, final import, switch configuration, verify, unfreeze.
  • Rollback runbook: switch back to the file backend and restore from the pre-cutover dump.

Acceptance:

  • The importer produces a database whose read results match the file backend for every endpoint.
  • Validation is clean; freshness differences are explained.
  • Cutover and rollback have been rehearsed on a copy.
  • The owner signs off.

Rollback: pre-cutover dump + file backend still present and readable.

Phase 8 — Operations, documentation, decommission (M)

Goal: operate the new system sustainably and remove the old path.

Deliverables:

  • Automated off-host dumps, retention, alerting on failures, and a scheduled restore drill; restore procedure in the service documentation.
  • Health endpoint per the handbook contract: public, cheap, status, checks.db, service/version/timestamp, no topology or personal data.
  • hard-panel probe and Synapse alert route.
  • systemd units and nginx configuration for the database-backed service and MCP endpoint.
  • Service page update in gnexus-book: access, infrastructure path, deployment, backups, monitoring, MCP.
  • Remove the file write path and unused Git adapter code.
  • Archive the original repository and note it in the service page.

Acceptance:

  • A restore drill has succeeded within the agreed retention window.
  • Monitoring reports the service and alerts on failure.
  • Documentation matches the running system.
  • Legacy file-write code is gone; tests pass against the database only.

Rollback: retain the final Git archive and the last pre-decommission dump.

8. Milestones

Milestone Meaning
M0 Off-host verified backup and decisions D1–D7 recorded
M1 All reads served from PostgreSQL with parity tests
M2 Document writes, history, diff, rollback work in the database
M3 Inventory, relationships, search, validation, freshness at parity
M4 SSO, roles, actor attribution, audit enforced
M5 MCP over streamable HTTP with per-user keys and provisioning
M6 UI parity plus auth, i18n, history/diff, key management
M7 Existing knowledge base migrated; cutover rehearsed and signed off
M8 Operations, documentation, and decommission complete

9. Test strategy

  • Unit tests for versioning, changeset application, rollback, validation, and relationship computation.
  • Integration tests against a disposable PostgreSQL instance (containers or a dedicated test database); every migration runs from zero in CI.
  • Parity harness: file backend and database backend run the same read requests; results are normalized and compared.
  • Contract tests for REST and MCP: tool names, input schemas, error codes, and resource URIs.
  • Concurrency tests: parallel applies, conflicting edits, unique key constraints.
  • Security tests from the handbook's key-provisioning checklist: creation, reissue, revocation, blocked user, role snapshot, no plaintext storage, uniform 401.
  • Backup tests: dump, off-host copy, restore, and checksum verification.
  • Performance smoke tests: search and document reads within an agreed budget for the current corpus; /health under 500 ms.
  • Migration tests: importer idempotency and checksum stability.

10. Risks and mitigations

Risk Impact Mitigation
Database loss Loss of the knowledge base Off-host dumps from Phase 1, retention, restore drills
Versioning scope creep Delays, bugs No branches/merges; only versions and changesets
Import drift Silent data corruption Checksums, dry runs, parity harness, pre-cutover dump
Broken agent contracts Agents fail after release Keep old MCP names as aliases; contract tests
Auth/key implementation bugs Security incident Use gnexus-auth client library; follow handbook tests; no fallbacks
Loss of filesystem workflows Harder ad-hoc search/editing Keep a read-only Git archive; provide search and history in the UI
Performance regressions Slow UI/agents Indexes from the start; FTS; measure current corpus in Phase 3
Operational overload Missed backups or alerts Automate from Phase 1; monitor the backup job itself

11. Definition of done

  • The ADR coverage matrix is fully checked off.
  • All tests pass against PostgreSQL; no test depends on the file backend for writes.
  • SSO, roles, actor attribution, and audit are enforced.
  • MCP follows the handbook transport, key, provisioning, and actor rules.
  • UI follows the platform shell and supports en/uk/ru.
  • Off-host backups and a restore drill are documented and verified.
  • Health, monitoring, and alerts are live.
  • The service page in gnexus-book is current.
  • The old file write path is removed and the Git repository is archived read-only.

12. Immediate next steps

  1. Copy the 2026-11-19 backup to a separate host and restore-test it there.
  2. Resolve D1–D7 or mark them explicitly deferred.
  3. Start Phase 1: local PostgreSQL, SQLAlchemy models, first Alembic migration, repository interfaces, importer v0, and parity harness.

13. Implementation status

Date Item Status
2026-11-19 Off-host backup of the knowledge base Copied to ~/NetTera8/service/backups/gnexus-book/2026-11-19_181859; Git bundle, archive, and SHA256 verified on the NFS host
2026-11-19 Decision gates D1–D7 Adopted as implementation defaults
2026-11-19 Phase 1: PostgreSQL foundation Dev cluster on 127.0.0.1:5433; SQLAlchemy models; Alembic initial migration; database-backed read repositories; importer v0
2026-11-19 Phase 1: import and parity 29 documents, 84 inventory items, 12 inventory types, 13 schemas imported; parity check reports 0 differences
2026-11-19 Phase 1: backups scripts/backup-db.sh produced a custom-format dump on the off-host NFS; scripts/restore-db.sh restored it into a temporary database with matching counts
2026-11-19 Phase 2: versioned writes Database-backed draft/apply changesets; immutable document and inventory versions; transactional validation with rollback; audit entries
2026-11-19 Phase 2: history, diff, restore API and MCP expose document_history, document_version, document_diff, and restore_document_version; restore creates a new version
2026-11-19 Phase 2: validation GET /validate and the MCP validation tool validate the database in db mode
2026-11-19 Phase 2: tests Full suite: 56 passed, 1 known stale-freshness failure
2026-11-19 Phase 3: inventory and relationships Database read/write for inventory, schema validation from stored schemas, unique live item IDs, relationship graph over database records
2026-11-19 Phase 3: freshness Database freshness report matches the file backend issue-for-issue
2026-11-19 Phase 3: search Substring/line-match path at parity; PostgreSQL FTS index deferred to a performance phase
2026-11-19 Phase 3: tests Full suite: 57 passed, 1 known stale-freshness failure

Notes:

  • The default backend remains file. Set GNEXUS_BOOK_STORAGE_BACKEND=db to use PostgreSQL for reads and writes.
  • In database mode, applying a changeset publishes it; commit_changes is a no-op and POST /commit is blocked.
  • Search keeps the current substring semantics for exact response parity. A full-text index is a later optimization, not a correctness requirement.
  • The date-dependent freshness test still fails because 25 documents are past their review interval. This is the recorded baseline, not a regression.
  • The next phase is Phase 4: gnexus-auth SSO, users, roles, actor attribution, and audit enforcement.