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:
- PostgreSQL is the system of record for documents, inventory, versions, users, changesets, and audit data.
- Every current capability listed in the ADR coverage matrix works against the database with the same observable contract.
- Every applied write is an immutable version grouped into an atomic changeset, with author, actor, timestamp, summary, and reason.
- Users authenticate through gnexus-auth; permissions are enforced; the actor of every write is recorded.
- MCP is served over streamable HTTP with per-user keys and provisioning.
- The UI keeps its current capabilities and gains auth, history/diff, MCP keys, and en/uk/ru localization.
- Database dumps go to a separate host and have been restored at least once.
- 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.
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.
- Copy the 2026-11-19 backup to a separate host and restore-test it there.
- Resolve D1–D7 or mark them explicitly deferred.
- 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 |
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. Freshness still reads files until Phase 3.
- 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 3: inventory, relationships, search, and freshness at database parity.