---
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](../50-decisions/ADR-0002-postgres-storage-and-versioning.md):
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](https://git.gnexus.space/root/gnexus-handbook/raw/master/README.md)
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

```text
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.
