diff --git a/00-overview/postgres-migration-roadmap.md b/00-overview/postgres-migration-roadmap.md new file mode 100644 index 0000000..3dec662 --- /dev/null +++ b/00-overview/postgres-migration-roadmap.md @@ -0,0 +1,481 @@ +--- +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 | + +Notes: + +- The default backend remains `file`. Set `GNEXUS_BOOK_STORAGE_BACKEND=db` to serve + reads from PostgreSQL. +- Document writes are blocked in database mode (the API and MCP return a clear error) + and still use the file backend until Phase 2. Validation and freshness still read + 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 2: versioned documents and changesets. diff --git a/50-decisions/ADR-0002-postgres-storage-and-versioning.md b/50-decisions/ADR-0002-postgres-storage-and-versioning.md new file mode 100644 index 0000000..a838523 --- /dev/null +++ b/50-decisions/ADR-0002-postgres-storage-and-versioning.md @@ -0,0 +1,295 @@ +--- +owner: gmikcon +status: draft +last_reviewed: 2026-11-19 +review_interval: 30d +confidence: medium +source_of_truth: analysis-2026-11-19 +--- + +# ADR-0002: PostgreSQL storage with built-in versioning + +## Status + +Draft, proposed on 2026-11-19. Not implemented and not accepted. + +## Context + +Gnexus Book currently keeps its knowledge base as Markdown and YAML inside a Git +checkout. The FastAPI server reads those files, applies proposed changes, validates +the whole repository, and creates local Git commits. The UI and MCP server are thin +layers over the same file-backed repositories. + +Current characteristics: + +- effectively single-user: no authentication, no actor attribution, no per-user data; +- Git is both the storage format and the version-control mechanism; +- every write is a filesystem mutation followed by a whole-repository validation; +- inventory items are YAML records validated by JSON Schema; +- pending changes are JSON files under `90-maintenance/pending-changes/`; +- the MCP server exposes 17 tools and 9 resources over stdio or SSE; +- the Vue UI has four read-oriented tabs and two write paths (apply change, commit). + +Owner direction (2026-11-19): + +1. Back up the current knowledge base before changing anything. +2. Move primary storage to a database. +3. Build version control into the service instead of relying on Git. +4. Keep the existing knowledge base as-is until the new system is tested, then migrate it. + +Platform-wide rules in [gnexus-handbook](https://git.gnexus.space/root/gnexus-handbook/raw/master/README.md) +also apply. They are not repeated here; the relevant sections are linked below. + +## Decisions + +### 1. PostgreSQL is the primary store + +PostgreSQL becomes the system of record for documents, inventory, versions, users, +changesets, and audit data. Git stops being the write path. + +Reasons: multi-user concurrency and transactions, relational integrity, full-text +search, JSONB for the flexible inventory records, mature backup/restore tooling. + +### 2. Versioning is application-level, not a Git reimplementation + +The service stores immutable document and inventory versions grouped into changesets. +It does **not** implement branches, merges, packfiles, remotes, or a general-purpose +distributed VCS. The current functionality does not require them. + +Required semantics: + +- every applied change creates a new immutable version; +- a changeset groups one or more versions and is applied atomically; +- author, actor, timestamp, summary, and reason are recorded; +- diff is computed between two versions on demand; +- rollback restores content by creating a new version, never by rewriting history; +- a failed validation aborts the transaction and leaves no partial state. + +### 3. Identity and access follow gnexus-handbook + +- User authentication is delegated to gnexus-auth (OAuth 2.0 PKCE). The service does + not own a user database or a login page; it mirrors the `sub` principal and profile. +- Browser sessions follow the cookie-session pattern (or the Synapse Bearer variant) + described in `10-platform/auth.md`. +- `user.*`, `global_logout`, `session.revoked`, and role/permission webhooks are + required, with HMAC verification. +- Locale preference is stored per user and defaults to the gnexus-auth account locale. + +### 4. MCP follows gnexus-handbook + +- The service is its own MCP server over **streamable HTTP** at `/mcp-protocol/` + (trailing slash significant). SSE is a legacy fallback only; stdio is not the + production transport. +- Agents authenticate with per-user `mcp_*` keys, not gnexus-auth access tokens. +- Keys are stored as SHA-256 hash plus tail hint; one active key per + (`user_id`, `consumer`); revocation is `revoked_at`, never deletion. +- The UI exposes an "MCP keys" page. Internal `/internal/keys` provisioning endpoints + are required (not published through nginx, service-to-service secret + allowlist). +- The guard is a pure ASGI class, returns a uniform `401`, and updates `last_used_at`. +- Mutating tools accept `is_user`; REST accepts `X-Actor: user`; audit records + `actor_kind`, `actor_name`, and `via`. +- Tool implementations share the service layer with REST routes, including validation + and error codes; tool errors are returned as data. + +### 5. Backups + +The database is dumped on a schedule to a **separate host**, with a documented restore +procedure and periodic restore drills. A dump that has never been restored does not +count as a backup. This follows `20-deployment/backups.md`. + +There is no Markdown/JSONL export from the database in the first version (owner +decision). The dumps are the only safety net, so retention, off-host storage, and +restore verification are mandatory. + +### 6. Existing files remain canonical until cutover + +The current repository stays the canonical source until the new backend passes its +tests and a migration has been verified. The importer is a later task and must be +repeatable. + +## Draft data model + +```text +users -- mirror of gnexus-auth principals + id, sub (unique), email, display_name, profile jsonb, locale, + created_at, updated_at, blocked_at + +user_sessions -- cookie-session pattern, if used + id, user_id, created_at, expires_at + +oauth_states -- PKCE state + +documents + id, slug (unique), title, created_at, deleted_at + +doc_versions + id, document_id, version_no, parent_version_id, content_hash, + raw text, body text, frontmatter jsonb, + author_user_id, actor_kind, actor_name, via, changeset_id, created_at + unique (document_id, version_no) + +blobs + hash pk, content text -- content-addressed deduplication + +inventory_types + name pk, schema_id + +schemas + id, name, version, body jsonb, created_at + +inventory_items + id, type, item_id, data jsonb, current_version_id, deleted_at + unique (type, item_id) among live rows + +inventory_item_versions + id, inventory_item_id, version_no, data jsonb, + author_user_id, changeset_id, created_at + +changesets + id, author_user_id, summary, reason, + status (draft | applied | rejected), created_at, applied_at, validation jsonb + +change_items + id, changeset_id, kind (doc | inventory-item), target, payload jsonb, status + +validation_runs + id, changeset_id, status, issues jsonb, created_at + +mcp_tokens + id, user_id, consumer, name, role, scopes jsonb, + token_hash (unique), token_hint, created_at, last_used_at, revoked_at + partial unique (user_id, consumer) where revoked_at is null + +user_prefs + user_id, locale + +audit_log + id, actor_user_id, actor_kind, actor_name, via, + action, entity_type, entity_id, before jsonb, after jsonb, created_at +``` + +Search is a `tsvector` index on document versions plus a trigram/substring path to +preserve the current "find a line containing the query" behavior. Relationships are +computed from `inventory_items.data` as they are today; materialized edges are an +optimization, not a v1 requirement. + +## Functional coverage + +The new system must close the current functionality before the migration. This table +is the acceptance checklist. + +| Capability | Current implementation | New implementation | +|---|---|---| +| List documents | Recursive `*.md` scan, title from first H1 or file stem, frontmatter dict | Query current document versions | +| Read document | `path` → `{path, frontmatter, body, raw}` | Lookup by slug/id; same response shape | +| Search | Case-insensitive substring, up to 5 matching lines per document | FTS plus substring/line-match path; keep response shape | +| Create/update document | Pending change → write file → validate → rollback on failure | Draft changeset → apply transaction → validate | +| Path safety | Reject escapes, non-`.md`, denied directories | Slug/route validation; no filesystem paths | +| Inventory types | `*.yml` files | `inventory_types` rows | +| Inventory read | raw YAML, parsed list, item by `id` | JSONB records; raw view is a defined serialization | +| Inventory create/update | `patch` merge or `create` append, immutable `id` | Change item applied in a transaction | +| JSON Schema validation | Draft 2020-12, per-type mapping | Same schemas stored in `schemas` | +| Unique inventory IDs | Per-file scan | Unique constraint per type among live items | +| Secret scanning | Regex over Markdown/YAML/JSON/dotenv | Same scan on every candidate version | +| Freshness | Frontmatter presence, stale by interval, missing `docs` targets | SQL queries over versions and inventory | +| Relationship graph | Nodes `/`, edges from known fields, unresolved refs | Same computation over inventory data; optional materialized edges | +| Pending changes | JSON files, `pending/applied/rejected`, apply + rollback | `changesets` + `change_items`, transactional apply | +| Git status/diff/commit | Working tree, explicit file list, local commit | Draft changesets, version diff, publish/apply changeset | +| MCP | 17 tools, 9 resources, stdio/SSE, static environment | Same domain tools over HTTP with per-user keys | +| UI | Four tabs, metrics, apply/commit forms | Same views plus history/diff, auth gate, MCP keys, i18n | +| Health | `{"status": "ok", "repo_root": ...}` | Handbook contract: no auth, cheap, `status` plus optional `checks.db` | +| Deployment | systemd user units, nginx, scripts | Postgres service, migrations, systemd, nginx | +| Tests | 38 tests, real-repo fixtures | Parameterized against DB fixtures; keep behavior contracts | + +Deliberately absent today and still absent unless separately requested: document +delete/rename, full-file inventory replacement, reject/approval workflow, generated +inventory index, visual topology, embedding search. + +## Handbook compliance gaps + +| Area | Handbook requirement | Current state | Action | +|---|---|---|---| +| Auth | gnexus-auth SSO, mirrored `sub` | None | Add PKCE client, session, webhooks | +| MCP transport | Streamable HTTP `/mcp-protocol/` | stdio/SSE | Add HTTP endpoint; keep stdio for dev/tests only | +| MCP keys | Per-user `mcp_*`, hash + hint, revoke | None | Add `mcp_tokens`, guard, UI page | +| Key provisioning | `/internal/keys`, service secret, allowlist | None | Add later, before external consumers | +| Actor | `is_user` / `X-Actor`, audit fields | None | Add to write paths and audit | +| UI | Kit, auth gate, drawer nav, `GnPageHeader` | Kit only | Add gate, nav, history views | +| i18n | en/uk/ru, account locale | English only | Add i18n module and dictionaries | +| Health | No auth, cheap, status contract | Exposes repo path | Remove topology, add DB check | +| Backups | Separate host, verified restore | Same disk, verified once | Add cron dump, off-host copy, restore drill | +| Secrets | gnexus-creds, `.env`, references only | Scanner in validation | Keep scanner; align docs | +| Monitoring | hard-panel + Synapse alerts | None | Add when deployed | +| Notifications | Synapse envelopes | None | Add only if the service emits alerts | +| Docs | Service page in gnexus-book | Page exists | Keep current | +| Git commits | No co-authorship lines | No agent commits yet | Follow `10-platform/git.md` | + +## API and MCP compatibility + +Read contracts can be kept almost unchanged. Write contracts need an explicit +decision because the working-tree model disappears: + +- `propose_*` → create a draft changeset/change item; +- `apply_pending_change` → apply the changeset transactionally; +- `git_status` → list draft changesets and pending items; +- `git_diff` → diff a draft against the current version; +- `commit_changes` → publish/apply a changeset (or become a deprecated alias). + +The recommendation is to introduce the new names and keep the old tools as aliases for +one transition period, so existing agents do not break. The alternative is a clean +rename with updated MCP instructions and tests. + +## Migration plan + +Phases, acceptance criteria, dependencies, and decision gates are in +[../00-overview/postgres-migration-roadmap.md](../00-overview/postgres-migration-roadmap.md). + +1. Accept this ADR and resolve the open questions below. +2. Add PostgreSQL, migrations, and a shared service layer; keep the file repositories + as a development/test backend behind the same interfaces. +3. Implement versioned documents and inventory, changesets, validation, audit. +4. Implement SSO, user mirroring, MCP keys, provisioning, and actor tracking. +5. Update the UI: auth gate, navigation, history/diff, MCP keys page, i18n. +6. Write the importer: current Markdown/YAML → documents/inventory; optionally replay + Git history as changesets. +7. Dual-run and compare read results; fix discrepancies. +8. Cut over writes, freeze the file write path, document backups and restore. +9. Decommission the Git write path and archive the original repository. + +## Risks + +- The database becomes a single point of failure; dumps without off-host copies and + restore drills are not sufficient. +- Versioning scope can creep into a VCS rewrite; branches and merges stay out of v1. +- Migration can silently change data (YAML formatting, frontmatter types, references). +- Auth and MCP key handling add security-critical code that must not be improvised. +- Losing filesystem access removes grep, IDE search, and ad-hoc scripting workflows. + +## Open questions + +1. Import the 34 Git commits as history, or import only the current state? +2. Keep old MCP tool names as aliases, or rename and update consumers? +3. May agents publish changesets directly, or do access/security/backup changes require + owner review? +4. Document identity: keep mutable `path` as the public key, or switch to UUID + slug + with redirects? +5. What should "raw inventory" return once comments and formatting no longer exist? +6. Will the service stay LAN-only, or get a `*.gnexus.space` subdomain and public MCP + endpoint? +7. Are delete and rename in scope for the first database version? + +## References + +- [gnexus-handbook README](https://git.gnexus.space/root/gnexus-handbook/raw/master/README.md) +- [Auth](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/auth.md) +- [MCP](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/mcp.md) +- [Key provisioning](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/key-provisioning.md) +- [Secrets](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/secrets.md) +- [UI](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/ui.md) +- [i18n](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/i18n.md) +- [Health](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/health.md) +- [Git](https://git.gnexus.space/root/gnexus-handbook/raw/master/10-platform/git.md) +- [Backups](https://git.gnexus.space/root/gnexus-handbook/raw/master/20-deployment/backups.md) +- Current implementation: `server/app/main.py`, `server/app/mcp_server.py`, + `server/app/pending_changes.py`, `server/app/validation.py`, `server/app/freshness.py`, + `server/app/relationships.py`, `ui/src/main.js` diff --git a/README.md b/README.md index 86da251..55d9942 100644 --- a/README.md +++ b/README.md @@ -7,6 +7,8 @@ - [Project implementation notes](00-overview/project-implementation-notes.md) - [MVP implementation plan](00-overview/mvp-implementation-plan.md) +- [PostgreSQL migration roadmap](00-overview/postgres-migration-roadmap.md) +- [ADR-0002: PostgreSQL storage and versioning](50-decisions/ADR-0002-postgres-storage-and-versioning.md) ## Foundation