zapier-decisions/plans/2026-10-03-libsql-multihost-architecture.md

295 lines
16 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# Verae × Zapier Multi-Host Architecture & Implementation Plan
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (or executing-plans) task-by-task. Steps use checkbox (`- [ ]`) syntax.
**Goal:** Run the Verae × Zapier commercial stack across **3–5 machines** with **libSQL** as the system of record for all business/billing/customer/pricing/accounting data, **JetStream only for work queues**, and proven end-to-end userflows under continuous QA.
**Architecture:** Public HTTPS doors → zappier-edge (meter) → middleware (stamp) → `api.veraetime.net`; private NATS for workers; shared libSQL for money & CRM; Go timestamping DBs stay local to timestamping-svc.
**Tech Stack:** Node (edge, middleware, access planes, CS/sales/acct), NATS JetStream (3-node lab → production), libSQL / go-libsql or `@libsql/client`, better-sqlite3 only as local/dev adapter, Litestream optional for libSQL file primary backup, Jest + gate scripts + E2E userflow harness.
**Spec / SoT docs:**
- Catalog: https://zapier.georgelambert.org/
- Map: `/tmp/zapier-system-docs-map.md`
- Decisions: D001–D006 (`packages/zapier-decisions/decisions/`)
- Composition: `docs/02-architecture/composition.md`
- NATS: `docs/02-architecture/nats-subjects.md`, `overview/03-nats-cluster.md`
- Ops: `packages/verae-ops/GETTING-STARTED.md`
- Digests-only stamps: mcp.verae.ai + D006 note + live swagger
**Forgejo org:** https://git.georgelambert.org/marchon/ (modules listed in §2)
## Global Constraints
- **D006:** Business/billing/customers/pricing/accounting/usage ledger → **libSQL only** (shared).
- **JetStream:** work queues as necessary (`ZAPIER_JOBS`, `ZAPIER_WEBHOOKS`, archive put/query). Not money SoT.
- **Core NATS only** for `verae.ts.completed` / `verae.ts.receipt` (no JetStream on that path).
- **D001 refined:** single prepaid **writer path**; durable store is libSQL (not per-host `books.json` / `billing.db`).
- **D002:** public credential = zappier `x-api-key`; `veraeUserId` join; JWT = `tokenRef` server-side.
- **D003:** only access planes are public HTTP; edge loopback (except intentional public `zap.verae.ai` edge).
- **D004:** leaf must not touch billing.
- **Digests only** for stamp submit (`POST /api/batch/timestamp` multihash; MCP shape).
- **Never** modify Go timestamping-svc SQLite (`blockchain.db`, etc.) as part of this plan.
- **WAL** (or libSQL equivalent durability) on all SQL stores.
- Credits language in product copy; ledger may keep `*_cents` column names as credit units.
- Deploy never rsyncs live `*.db*` over production data.
- **ZAPIER_USAGE** / `verae.zapier.usage` is telemetry or export only — never usage/money SoT.
- Litestream (if used) backs up a **single** libSQL primary; never multi-writer fabric.
- **Single meter/prepaid writer** process owns `usage_entries` append + balance adjust; other hosts call that API.
- Dual-run: libSQL primary for writes; file/SQLite fallback is **read-only** (or kill-switched).
- Auth (`admin_users`, `portal_sessions`): v1 remains edge-local `zappier.db`; not in `verae_business`.
- Product edge bind on 138: `127.0.0.1:3040` (`zap.verae.ai`); `ZAPPIER_UPSTREAM` defaults to `http://127.0.0.1:3130` for live lab smoke (not `:3100` mock unless intentional).
- Catalog docs that call account-balance “prepaid SoT” are stale after D006; Phase 0 patches them.
---
## 1. System map (from catalog + live lab)
```text
Internet
├─ Zapier cloud ──HTTPS──► access-zapier :3024 ──► zappier-edge (meter) ──► middleware :3100
├─ Browsers ────HTTPS──► access-web :3021 /portal
├─ Customer API ─HTTPS──► access-api :3022
├─ Leaf S2S ────HTTPS──► access-leaf :3023 (no billing)
└─ Staff ───────HTTPS──► access-staff :3025 + IAM :3028
Private
├─ NATS JetStream 10.10.10.21–23 (jobs.watch, webhooks.deliver, archive.*)
├─ Core NATS (verae.ts.completed, verae.ts.receipt, billing request-reply)
├─ libSQL (TARGET) ← customers, pricing, usage chain, invoices, balances
├─ Workers job-poller, webhook-deliver, archive-worm×N, tree-node×N
└─ api.veraetime.net / MOCK_VERAE
```
### Critical userflows (must pass QA)
1. **Signup → API key → Statement** (portal via access-web).
2. **Stamp digests (single + batch)** via zap door → edge meter → middleware → Verae → 202 jobIds → credits headers.
3. **Async complete → Zapier REST Hook** (`jobs.watch` → poll → `jobs.events` → `webhooks.deliver`).
4. **CS credit adjust → balance visible** on portal/admin (single prepaid writer).
5. **Sales channel×segment quote → meter charge** matches rate card.
6. **Accounting export** sees payments/usage consistent with libSQL.
7. **Leaf plane** can stamp/authz but **cannot** adjust billing (D004).
---
## 2. Forgejo source elements (implementation clones)
| Concern | Repo | Notes |
|---------|------|-------|
| Billing edge | `marchon/zappier-edge` | Live `/opt/verae/zappier-edge` on 138; also catalog `packages/zappier` |
| Prepaid mailbox (legacy) | `marchon/zappier-account-balance` | `books.json` → migrate into libSQL |
| CS / Sales / Acct | `zappier-customer-service`, `zappier-sales-pricing`, `zappier-accounting-export` | |
| Identity | `zappier-identity` | |
| Middleware | `marchon/verae-middleware` (catalog: verae-zapier-middleware) | |
| Zapier apps | `verae-zapier-app`, `verae-activate` | |
| Access planes | `verae-access-{web,api,leaf,zapier,staff,authz}` | |
| Jobs/archive | `verae-jobs-events`, `verae-archive-*`, `verae-tree-node`, `verae-request-splitter` | |
| Ops | `verae-ops`, `verae-bootstrap`, `verae-fleet`, `verae-keep`, `verae-nats-cluster` | |
| Decisions | `zapier-decisions` | D001–D006 |
| Monorepo mirror | `master-zapier-plan-draft` | docs + packages composition |
---
## 3. Conflict register (resolve before/with build)
| ID | Conflict | Resolution (locked) |
|----|----------|---------------------|
| C1 | D001 `books.json` SoT vs live `billing.db` vs D006 libSQL | **libSQL is SoT**; migrate both books + billing.db schema; account-balance becomes libSQL-backed service or folds into billing API |
| C2 | README “imported, not yet wired” vs STATUS lab up | Treat STATUS + live doors as operational truth; update README composition status in Phase 0 docs |
| C3 | overview `zmw_` Bearer vs composition `x-api-key` | **Public = x-api-key** (D002/composition); fix overview/models |
| C4 | Edge ports `:3000` / `:13000` / live `:3040` (`zap.verae.ai`) | Document env matrix; lab doors proxy to loopback; zap.verae.ai may remain public edge |
| C5 | JetStream `jobs.events` vs ban on JetStream for job-complete | **Different subjects:** Zapier job lifecycle may use JetStream `verae.zapier.jobs.events`; Verae chain notify `verae.ts.*` stays **core NATS** |
| C6 | TimestampRequest “Hello world” data vs digests-only | Digests-only + multihash batch (already fixed on live swagger; sync catalog models) |
| C7 | Dual prepaid writers (edge SQLite + account-balance) | One writer API on libSQL; edge caches only |
| C8 | Per-host SQLite on 3–5 machines | Forbidden for business data; libSQL shared |
| C9 | Go `blockchain.db` in scope creep | Out of scope — do not touch |
| C10 | `ZAPIER_USAGE` vs libSQL usage ledger | Demote to telemetry/export only; never money SoT |
| C11 | Dual middleware `:3100` mock vs `:3130` product upstream | Env matrix; product smoke uses **3130** |
| C12 | D001 file still says books.json SoT | D001 addendum → libSQL (done with this amend) |
| C13 | Auth store edge-local vs libSQL | **v1 edge-local** `zappier.db` |
| C14 | Concurrent hash-chain append across hosts | Single meter writer / serialized append |
| C15 | GETTING-STARTED calls account-balance SoT | Phase 0 doc patch → libSQL |
| C16 | Live `:3010` + `billing.db` dual writers | Cutover runbook: one authoritative adjust path |
| C17 | Litestream multi-writer temptation | Litestream = backup of one primary only |
| C18 | Digests-only vs upload-file stamp docs | Client hasher + digests-only only |
---
## 4. Target data ownership (libSQL)
**Database:** `verae_business` (name TBD) on shared libSQL.
| Domain | Tables / collections | Writer |
|--------|----------------------|--------|
| Customers / identity bind | `customers`, identity map | billing API + identity service |
| Pricing | `tiers`, `price_endpoints`, `channel_rates`, `channel_rate_meta` | admin / sales-pricing |
| Metered ledger | `usage_entries` (+ `prev_hash`/`row_hash` chain) | **single** meter writer |
| Prepaid | balances, credits, payments (from books.json) | billing adjust API |
| Invoices | `invoices`, `invoice_lines` | admin / Stripe jobs |
| Auth (optional split) | `admin_users`, `portal_sessions` | may stay edge-local or move to libSQL `auth` schema |
### 4.1 Launch topology (3–5 machines)
- One libSQL primary (dedicated host or named primary among the 3–5).
- Replicas if any are read-only; writers always target primary URL (`LIBSQL_URL`).
- Backup: filesystem snapshot and/or Litestream of the **primary only**; tested restore before Phase 4.
- Fail closed: metered stamp paths return **503** if BusinessStore write unavailable (no silent local SQLite fallback in production).
- Network: libSQL on private fabric only (same class as NATS `10.10.10.0/24`); not public.
- Schema: `verae_business`; auth stays out for v1 (C13).
**Interface:** `BusinessStore` (TS) with drivers: `LibsqlBusinessStore` (prod), `SqliteFileBusinessStore` (dev/test).
---
## 5. NATS usage matrix
| Subject / stream | Transport | Purpose |
|------------------|-----------|---------|
| `verae.zapier.jobs.watch` | JetStream | Poll Verae status |
| `verae.zapier.jobs.events` | JetStream | Terminal Zapier-pipeline job events |
| `verae.zapier.usage` / `ZAPIER_USAGE` | JetStream optional | **Telemetry/export only; NOT ledger SoT** |
| `verae.zapier.webhooks.deliver` | JetStream | Hook delivery retries |
| `verae.archive.*` | JetStream / fanout | WORM/tree |
| `verae.billing.*` | Core request-reply (+ optional pub) | Statement/balance; eventually thin facade over libSQL |
| `verae.ts.completed`, `verae.ts.receipt` | **Core NATS only** | Chain job-complete notify |
| `verae.access.*`, `verae.identity.*` | Core / as designed | Authz & bind |
---
## 6. Constraint (TOC)
> The system constraint is **shared business SoT cutover (libSQL + single prepaid writer)** because every multi-host userflow (meter, CS credit, sales quote, portal statement, accounting export) blocks on one consistent customer/balance/usage store; JetStream and doors already exist in the lab.
Critical chain: **Conflict freeze → BusinessStore+libSQL → Migrate data → Rewire edge/CS/sales/acct → E2E userflow tests → QA whattochange loop**.
Feeding chains (parallel): digests-only docs sync; NATS subject clarification; access-plane smoke; Forgejo clone inventory.
---
## 7. Phased implementation (PM DAG)
### Phase 0 — Docs & conflict freeze
- [ ] Publish this plan to catalog `docs/plans/` + `zapier-decisions/plans/`
- [ ] Patch conflict C2/C3/C5/C6 in overview + models + README status blurb
- [ ] Architecture review sign-off artifact: `plans/reviews/2026-10-03-arch-review.md`
### Phase 1 — BusinessStore + libSQL skeleton (TDD)
- [ ] Define `BusinessStore` interface (customers, usage.record+verifyChain, rates, invoices, balances)
- [ ] Failing tests against interface
- [ ] `LibsqlBusinessStore` + docker/libsql-server or `:memory:`/file for CI
- [ ] Keep `SqliteFileBusinessStore` wrapping current repos for lab
### Phase 2 — Migrate lab data
- [ ] One-shot migrator: `billing.db` + `books.json` → libSQL
- [ ] Preserve usage hash-chain tip integrity (verify before/after); refuse cutover if verifyChain fails
- [ ] Dual-run: writes libSQL only when `BUSINESS_STORE=libsql`; file fallback **read-only**
- [ ] Account-balance becomes libSQL-backed facade for `verae.billing.*` **or** is stopped after HTTP BusinessStore cutover (pick one in Phase 3)
### Phase 3 — Rewire writers
- [ ] zappier-edge meter → BusinessStore only
- [ ] account-balance service reads/writes libSQL (or deprecate mailbox in favor of HTTP billing API)
- [ ] CS / sales / accounting-export point at same store
- [ ] Remove second writer paths (C7)
### Phase 4 — Multi-host topology
- [ ] Deploy libSQL on dedicated host (or primary of 3–5)
- [ ] Edge/middleware/CS on separate hosts with `LIBSQL_URL`
- [ ] JetStream cluster already present — verify workers only
- [ ] Confirm leaf cannot call billing adjust
### Phase 5 — Userflow E2E + gates
- [ ] Automate flows 1–7 (§1) in `harness/` or `packages/verae-zapier-simulator` extensions
- [ ] Jest unit + integration + gate scripts green
- [ ] Live smoke on 138 with MOCK_VERAE then optional live Verae
### Phase 6 — QA loop (repeat until pass)
- [ ] QA finds bugs → `/whattochange` pack (a/b/c/d + todos)
- [ ] Engineers implement; PM reorders PreReqs
- [ ] Re-run full suite + userflows
- [ ] Exit when all critical userflows pass twice consecutively
---
## 8. Test requirements (“tests for everything”)
| Layer | Must cover |
|-------|------------|
| Unit | BusinessStore ops, hash-chain append/tamper, channel rate resolve, digests-only batch mapping, D004 leaf deny |
| Integration | edge→middleware mock stamp; billing adjust→statement; JetStream watch→event (testcontainers/nats) |
| Contract | OpenAPI digests-only examples; NATS payload schemas |
| E2E userflow | Flows 1–7 against lab compose or NS1 |
| Regression | Existing zappier-edge Jest suite must stay green during driver swap |
| Non-goals | Go timestamping-svc blockchain tests |
---
## 9. Roles (agent orchestration)
| Role | Responsibility | Artifact |
|------|----------------|----------|
| **Architect** | Review plan, expand conflict register, approve SoT | `plans/reviews/*-arch-review.md` |
| **PM** | DAG, task assignment, merge gates, buffer | `plans/reviews/*-pm-board.md` |
| **Engineers** | Implement Phases 1–5 in worktrees | PRs / commits citing this plan |
| **QA** | Break userflows; whattochange packs; retest | `plans/qa/*` + NotImplemented leftovers |
| **Orchestrator (this session)** | Launch agents, merge gates, enforce D006/JetStream rules | STATUS updates |
---
## 10. Acceptance criteria (project buffer)
1. One libSQL URL serves customers/pricing/usage/invoices/balances for all commercial services.
2. No production business writes to per-host `billing.db` / `books.json`.
3. JetStream used only for work/archive streams listed in §5.
4. `verae.ts.completed` / `verae.ts.receipt` remain core NATS.
5. Digests-only multihash batch path green with credit headers.
6. Userflows 1–7 pass twice in a row under QA.
7. Go timestamping DBs untouched.
8. Catalog docs match runtime (conflicts C2–C6, C10–C18 closed or owned).
9. `ZAPIER_USAGE` is non-SoT; no service writes authoritative balances there.
10. Production metered paths fail closed if BusinessStore writer is down.
11. Concurrent append contract test proves single-writer (or serialized) hash-chain safety.
12. Live env matrix documents `:3040` edge, `:3130` product upstream, `:3100` mock, `:3010` facade/retire.
13. D001 file contains D006 addendum; GETTING-STARTED / 01-system no longer call books.json SoT.
---
## 11. Out of scope
- Replacing api.veraetime.net / Go chain storage
- Zapier Platform public directory listing (private push may remain operator-gated)
- Peergos Seaweed fleet redesign (consume existing billing APIs only)
- lan-134 enablement (D005)
- Any JetStream binding for `verae.ts.completed` / `verae.ts.receipt`
- Editing `/var/lib/timestamping-svc/data/blockchain.db` or timestamping-svc test `results.db`
- Multi-primary libSQL or Litestream multi-writer
- Moving portal auth to libSQL in the same cutover as billing (defer)
### Architecture review
See `plans/reviews/2026-10-03-arch-review.md` (**approve-with-changes**; A1–A8 merged herein).
---
## Task checklist (engineer entry)
### Task 1: BusinessStore interface + failing tests
**Files:** Create under zappier-edge `src/db/business-store.ts`, `tests/business-store.contract.test.ts`
**Produces:** Interface used by later tasks
- [ ] Write contract tests for recordUsage/verifyChain/findCustomer/getBalance/adjustBalance
- [ ] Run tests — expect fail
- [ ] Minimal SqliteFile adapter pass
- [ ] Commit citing this plan
### Task 2: LibsqlBusinessStore
- [ ] Add `@libsql/client`
- [ ] Implement same contract
- [ ] CI uses file or embedded libsql
- [ ] Commit
### Task 3: Migrator billing.db + books.json → libSQL
- [ ] Script + tests with fixtures
- [ ] Chain verify pre/post
- [ ] Commit
*(PM expands Tasks 4+ after arch review.)*