Proof Card: Reconciliation Center

Status: production Scope: API surface + NetSuite↔BC cadence dispatch (real schedule claim + run-lifecycle + comparison engine; connectors mocked in tests, live fetch gated on integration config; embedded operator UI shipped (guest-context iframe surface); full schedule CRUD (create/list/update/delete) API with config-content validation at creation/update; integration_config_id is DB NOT NULL (migration 056); cross-surface embedded-CSP static-shadowing fix shipped) Last verified: 2026-05-29

Claim

ReconciliationCenterService (src/services/reconciliationCenter/ReconciliationCenterService.ts) persists tenant-scoped reconciliation exceptions in reconciliation_exceptions (migration 048) and exposes an operator API surface via GET /api/reconciliation-center/exceptions. Resolution is recorded with actor attribution via POST /api/reconciliation-center/exceptions/:id/resolve: the row's resolved_by column stores the resolving user (sourced from req.user.id under the F3 strict mount), resolved_at stamps the resolution time, and resolution_note carries the operator note. A row that does not exist (wrong id) or that belongs to another tenant causes the repository to throw ReconciliationExceptionNotFoundError, which the route maps to 404 exception_not_found so silent success cannot mask operator errors. A recurring ReconciliationScheduleJob drives runDueSchedules, which reads due reconciliation_schedules, atomically claims each (a conditional UPDATE that advances next_run_at and inserts a running reconciliation_runs row in one transaction — multi-replica safe per AGENTS.md Tier-B), looks up a Reconciler by the schedule's handler_key, invokes the production connector API (ConnectorManager.getConnector(...).list('invoice')) — the same connector-consumption path as the shipped FinanceCentral operator surface, with no demo-data fallback — persists coalesced reconciliation_exceptions, and marks the run completed/failed. v1 ships one handler — netsuite_business_central_invoice_reconciliation — reconciling invoice amounts between the two production ERP connectors (NetSuite ↔ Business Central), resolved by their registry systemType keys (netsuite / businesscentral). Amounts are compared in integer minor units (ISO-4217 exponent map, 2-decimal default); a fetched invoice missing its key/amount/currency field fails the run rather than inventing a delta. What this PR proves: the dispatch lifecycle (claim → run-row writes → coalesced exceptions → complete/fail) and the comparison engine are real and exercised end-to-end with the connectors mocked. What it does NOT prove: a credentialed live fetch — see Known Gaps (connector initialization is the tenant-integration lifecycle's job, shared with all connector consumers; an unconfigured connector makes the run fail cleanly, never fabricate data).

Source

• Implementation: src/services/reconciliationCenter/ReconciliationCenterService.ts
• Repository: src/services/reconciliationCenter/ReconciliationExceptionRepository.ts
• Schedule job: src/services/reconciliationCenter/ReconciliationScheduleJob.ts
• Types: src/services/reconciliationCenter/ReconciliationCenterTypes.ts
• Route: src/routes/reconciliationCenterRoutes.ts (entry: GET /exceptions, POST /exceptions/:id/resolve)
• Schema migration: src/database/migrations/048-create-reconciliation-center-tables.ts
• Lifecycle wiring: src/index.ts (start after CostTransparencyDailyJob, stop before HTTP close)
• DI: src/inversify/inversify.config.ts, src/inversify/types.ts
• Schedule/run repository: src/services/reconciliationCenter/ReconciliationScheduleRepository.ts
• Reconcilers: src/services/reconciliationCenter/reconcilers/Reconciler.ts, src/services/reconciliationCenter/reconcilers/NetSuiteBusinessCentralInvoiceReconciler.ts
• Comparison engine: src/services/reconciliationCenter/invoiceComparison.ts; money/cadence helpers: src/services/reconciliationCenter/money.ts, src/services/reconciliationCenter/cadence.ts
• Schema migrations: src/database/migrations/052-add-handler-key-to-reconciliation-schedules.ts (adds handler_key); src/database/migrations/053-reconciliation-schedule-next-run-at-tztz.ts (converts next_run_at to TIMESTAMPTZ so the cadence math + optimistic-claim equality are zone-stable on non-UTC Postgres hosts); 054-add-reconciliation-runs-stale-sweep-index.ts adds a partial index on reconciliation_runs(started_at) WHERE status='running' so the per-tick reclaim sweep is an index lookup rather than a full scan as run history grows; 055-add-integration-config-id-to-reconciliation-schedules.ts adds the (then-nullable) integration_config_id reference (FK-less — IntegrationConfigs are file-backed); 056-reconciliation-schedules-integration-config-not-null.ts flips it to NOT NULL (dialect-split backfill to __unconfigured__ + deactivate; SQLite table-rebuild preserving CHECK/defaults/both indexes / Postgres ALTER COLUMN SET NOT NULL)
• Embedded operator router: src/routes/embedded/embeddedReconciliationRouter.ts (guest-context UI surface; companion public/embedded/reconciliation.{html,js})
• Schedule CRUD API: src/routes/reconciliationCenterRoutes.ts (POST + GET + PATCH + DELETE /schedules[/:id]); repository ReconciliationScheduleRepository.createSchedule/listSchedules/getScheduleById/updateSchedule/deleteSchedule (+ ReconciliationScheduleNotFoundError); service ReconciliationCenterService.createSchedule/listSchedules/updateSchedule/deleteSchedule; view + UpdateReconciliationScheduleInput types in ReconciliationCenterTypes.ts
• Config-content validation: Reconciler.validateConfig (throws-only interface contract in reconcilers/Reconciler.ts; concrete NetSuiteBusinessCentralInvoiceReconciler.validateConfig returns ValidatedNetSuiteBusinessCentralConfig and is reused by run()), invoked by createSchedule/updateSchedule before persisting → route maps ReconcilerConfigError to 400 invalid_config
• integration_config_id NOT NULL: src/database/migrations/056-reconciliation-schedules-integration-config-not-null.ts (dialect-split backfill to the __unconfigured__ sentinel + deactivate, then SQLite table-rebuild / Postgres SET NOT NULL); runtime sentinel constant src/services/reconciliationCenter/constants.ts
• CSP static-shadowing fix: src/middleware/embeddedHtmlRoutes.ts (CSP-routed basename allowlist + skipEmbeddedHtml wrapper), wired in src/middleware/setup/MiddlewareSetup.ts

Tests

• Migration: tests/unit/database/migrations/048-create-reconciliation-center-tables.test.ts (5 tests — table shape + indexes including idx_reconciliation_runs_tenant_schedule)
• Service: tests/unit/services/reconciliationCenter/ReconciliationCenterService.test.ts (5 tests — ingest, list, resolve, missing-amount, scheduler stub)
• Repository: tests/unit/services/reconciliationCenter/ReconciliationExceptionRepository.test.ts (6 tests — CRUD against in-memory SQLite, tenant isolation on list AND update, resolved_by stamping, NotFoundError on missing-id + cross-tenant)
• Job: tests/unit/services/reconciliationCenter/ReconciliationScheduleJob.test.ts (6 tests — start idempotency, interval-driven inflight drain under fake timers, concurrent stop() drain, error swallow, never-started stop, initial-not-running check)
• Routes: tests/unit/routes/reconciliationCenterRoutes.test.ts (8 tests — list, resolve happy path, list 401 + resolve 401 on missing identity, resolve 401 on req.tenantContext-only path, resolve 401 on 'unknown' JWT fallback, 404 mapping for ReconciliationExceptionNotFoundError, missing-note coercion)
• Cadence handler units: tests/unit/services/reconciliationCenter/{money,cadence,invoiceComparison,ReconciliationScheduleRepository}.test.ts + reconcilers/{ReconcilerRegistry,NetSuiteBusinessCentralInvoiceReconciler}.test.ts + migration tests/unit/database/migrations/052-add-handler-key-to-reconciliation-schedules.test.ts. ReconciliationCenterService.test.ts now covers the real dispatch (claim, coalesce, complete/fail, multi-schedule isolation, persist-failure logging).
• Schedule CRUD + config validation: validateConfig (static, no network) + no-double-lookup regression in reconcilers/NetSuiteBusinessCentralInvoiceReconciler.test.ts; updateSchedule + deleteSchedule + getScheduleById block in ReconciliationScheduleRepository.test.ts (getById null/tenant-isolation, partial-patch no-clobber, updated_at bump, delete leaves runs intact, NotFound for unknown/cross-tenant); createSchedule/updateSchedule/deleteSchedule service tests (validate-before-persist, revalidate-on-config-change incl. same-value skip, NotFound propagation); PATCH/DELETE route blocks in reconciliationCenterRoutes.test.ts (full validation matrix + 404/invalid_config/204).
• Migration 056 (NOT NULL flip): tests/unit/database/migrations/056-reconciliation-schedules-integration-config-not-null.test.ts (backfill+deactivate, NOT NULL enforced, both indexes + CHECK + handler_key default preserved, sentinel-constant drift pin, replay-safe).
• Coverage: see .core-coverage-budget.json (re-stamped in Task 6 of this PR if reconciliation files land in the core-coverage collectCoverageFrom)
• Embedded operator UI: tests/unit/routes/embedded/embeddedReconciliationRouter.test.ts (12 — list/resolve happy paths, 401 guards incl. synthetic-identity rejection across the full denylist, 404 mapping, note coercion, middleware short-circuit) + tests/integration/reconciliationEmbeddedOperator.routes.test.ts (CSP frame-ancestors header on the served HTML page)

Live vs Fixture

• Real HTTP wired? N/A — this is an internal operator queue; data is persisted to local SQLite (test) or the production-bound Postgres instance, and the routes are consumed by Bearer-JWT callers (integration tests, future operator UI). No outbound HTTP from this surface.
• Demo-mode toggle? No — there is no fake-data path. An empty queue (listExceptions returns []) yields {exceptions: []} and the route is unconditional.
• Production credential test on file? N/A — does not authenticate against an external system. Operator authentication flows through the F3 strict mount (mountReconciliationCenterRoutes: authMiddleware + tenant-lifecycle kill switch, unconditional) and the route's req.user identity guard + the SYNTHETIC_OPERATOR_USER_IDS resolve-side guard.

Known Gaps

• Schedule CRUD — shipped (create/list/update/delete). All on the Bearer /api/reconciliation-center mount (F3 strict: authMiddleware + tenant-lifecycle kill switch via mountReconciliationCenterRoutes, same req.user tenant guard as /exceptions). POST /schedules creates, GET /schedules lists, PATCH /schedules/:id updates (name/cadence/active/integrationConfigId; handlerKey is immutable; empty body → 400 no_updates; 400 name_required/invalid_cadence/invalid_active/integration_config_required on bad fields; 404 schedule_not_found on unknown/cross-tenant id), DELETE /schedules/:id hard-deletes (204, or 404 schedule_not_found; FK-less reconciliation_runs rows are intentionally left intact). On create AND on an integrationConfigId change AND on (re)activation (active: true), the service runs config-content validation via Reconciler.validateConfig (using the existing row's immutable handlerKey and the effective config patch.integrationConfigId ?? existing.integrationConfigId on update): config existence + NetSuite↔BC system-pair + per-side auth, mapped to 400 invalid_config { reason } (config_not_found / config_system_pair_mismatch / config_missing_auth); validation runs BEFORE any persist so an invalid reference never becomes a row. The activation check enforces the "active implies a valid config" invariant, closing the migration-056 bypass where a deactivated __unconfigured__ sentinel row could otherwise be flipped back to active without supplying a real config. handler_key is validated by registry.get (400 unknown_handler). Trimmed values are what get persisted. integration_config_id is now DB NOT NULL (migration 056) — legacy null rows were backfilled to the __unconfigured__ sentinel and deactivated. Tests: schedule routes + PATCH/DELETE blocks in tests/unit/routes/reconciliationCenterRoutes.test.ts; createSchedule + listSchedules + updateSchedule + deleteSchedule + getScheduleById blocks in ReconciliationScheduleRepository.test.ts + ReconciliationCenterService.test.ts; validateConfig (static, no network) block in reconcilers/NetSuiteBusinessCentralInvoiceReconciler.test.ts; tests/unit/database/migrations/056-reconciliation-schedules-integration-config-not-null.test.ts. Residual follow-ups: schedule-list pagination/filtering; recompute next_run_at on cadence change (currently left unchanged — documented in updateSchedule); changing a schedule's handler (delete + recreate is the path).
• Stale running run rows after a crash — reclaimed by a TTL sweep. The atomic claim advances next_run_at BEFORE connector work, so a process crash (or a DB failure persisting the run status) after the claim but before completeRun/failRun leaves an orphaned running row while the schedule has already moved on. runDueSchedules now reclaims these as step 0 of every scheduler tick: ReconciliationScheduleRepository.reclaimStaleRuns(cutoff) marks every running row older than cutoff = now − ReconciliationCenterService.STALE_RUN_THRESHOLD_MS (2h) as failed with the stable [stale-run-reclaim] run exceeded max duration; marked failed by sweep message (no status migration — reuses the existing failed status). The sweep is a single atomic, idempotent, cross-tenant UPDATE backed by a partial index on running rows (migration 054), so it is an index lookup rather than a full table scan as run history grows (multi-replica safe — the age threshold never reaps a freshly-started run on another replica; strict < excludes a row exactly at the cutoff), and is isolated in its own try/catch so a reclaim DB error logs and still lets the claim loop run. Accepted residual: a legitimately long-running reconciliation that exceeds the 2h TTL while still in flight would be falsely reclaimed to failed (its later completeRun/failRun then writes onto an already-failed row); the threshold is deliberately conservative (2× the interval, far above any realistic invoice-list reconciliation, which does bounded connector list calls). A first-class abandoned status with a heartbeat is a future refinement if real run durations ever approach the TTL. Tests: reclaimStaleRuns block in ReconciliationScheduleRepository.test.ts (boundary/idempotency/cross-tenant/in-flight-crosses-TTL) + reclaim-isolation test in ReconciliationCenterService.test.ts.
• v1 invoice matching is identifier-based — compareInvoices matches on a shared invoice key (NetSuite tranId ↔ BC number/external document number). If the two systems don't share an identifier, invoices surface as missing on both sides (honest, not silently dropped). A key that appears more than once on the same side is surfaced as an explicit duplicate_key discrepancy and excluded from amount matching (never silently collapsed). A cross-reference mapping is a follow-up.
• Connector field names are best-effort — the reconciler resolves amount/key/currency from an ordered candidate list of raw connector field names; against a live sandbox the exact names may need adjustment. The strict-fail behavior makes this safe (it fails loudly rather than mis-scaling).
• Live connector readiness — wired via the schedule's integration config. A schedule carries integration_config_id (migration 055, nullable). At run time the reconciler resolves the tenant's config (ConfigurationService.getConfigurationForTenant), validates the system pair is NetSuite↔Business Central (canonicalizing the business_central alias to businesscentral), resolves each side's effective auth (sourceAuthentication/targetAuthentication with the authentication.source/target fallback), and initialize()s each connector (keyed ${systemType}_${config.id}) BEFORE list('invoice'). Any of {null ref, config not found, system-pair mismatch, missing auth} fails the run cleanly via a typed ReconcilerConfigError (stable reasonCode), and a getConnector/initialize() failure via ReconcilerConnectorError — never fabricating data. Still not proven here: a credentialed live fetch against a real NetSuite sandbox — that is defensible-A item 5 (production NetSuite credentials, account-gated). This PR proves the wiring with ConfigurationService + connectors mocked. The schedule-creation API that sets integration_config_id is now shipped (see "Schedule creation — shipped" above); it enforces the reference as required at the API boundary, though the DB column stays nullable until all rows flow through it.
• Embedded operator UI — shipped via a dedicated embedded router. R13 added an iframe surface and R16 removed it (it could not bootstrap an authenticated session against the Bearer mount). This follow-up ships the working surface as a SEPARATE router GET /api/embedded/reconciliation/exceptions + POST /api/embedded/reconciliation/exceptions/:id/resolve gated by validateGuestContext (embedded session + same-origin), mirroring the lineage/approvals pattern — the Bearer /api/reconciliation-center route is unchanged. Tenant + actor identity come from the embedded session (tenant_id scopes the lookup, user_id attributes the resolve), never the JWT, so a missing/invalid Bearer cannot fall through to SYSTEM_IDENTITY. Resolve fails closed (401 operator_identity_required) when user_id is missing/empty OR is any synthetic placeholder (__embedded_anonymous__ host-bootstrap sentinel, __system__, unknown) via the shared SYNTHETIC_EMBEDDED_OPERATOR_USER_IDS set, so a placeholder identity is never written to resolved_by. The Bearer route rejects missing operator identity and the system sentinel, while subject-less JWTs are rejected upstream by authMiddleware/optionalAuthMiddleware before req.user is populated. The companion UI is public/embedded/reconciliation.{html,js} (HTML served through embeddedCspMiddleware; reconciliation.js is an external script so no CSP hash is needed; guest-bootstrap.js is reused unchanged). v1 is intentionally open-only (the service exposes listOpen + resolveException). Tests: tests/unit/routes/embedded/embeddedReconciliationRouter.test.ts (12) + tests/integration/reconciliationEmbeddedOperator.routes.test.ts (CSP header) + tests/playwright/embedded/reconciliation.spec.ts (four hermetic real-browser DOM scenarios serving the real reconciliation.{html,js} from an ephemeral local server with the embedded API stubbed — the happy path (render → resolve → row clears) plus three negative paths: GET 500 → error state without crash, resolve POST 500 → row stays + error surfaced, empty first load → empty state immediately).
• Tenant isolation is enforced at the query layer (WHERE tenant_id = ? on every read AND update). There is no row-level security at the DB layer. The F3 strict mount (authMiddleware + kill switch) plus the route's req.user.tenantId guard are the barriers to a tenant requesting another tenant's data — the handler-level SYSTEM_IDENTITY fallback is gone.
• CSP static-shadowing of /embedded/*.html — FIXED (cross-surface). Previously express.static(public/) (registered in setupMiddleware() before setupRoutes() mounted the CSP-wrapped /embedded/*.html routes) resolved the raw file first, so a direct GET /embedded/approvals.html (etc.) was served WITHOUT the frame-ancestors header. The root static handler is now wrapped with skipEmbeddedHtml(...) (src/middleware/embeddedHtmlRoutes.ts): GET/HEAD requests for the five CSP-routed basenames (session-expired, sync-error-triage, approvals, lineage, reconciliation) fall through to the CSP route handlers, so every CSP-routed embedded page keeps its frame-ancestors header. The allowlist is anchored + single-segment (a nested /embedded/foo/bar.html is NOT punted, avoiding surprise 404s). host-reference.html is a deliberate dev-only static exemption (not an embeddable module, no CSP route). Proven by HTTP-outcome tests in tests/integration/embeddedHtmlCspGuard.routes.test.ts (CSP present on all four disk-backed pages + the string-served session-expired.html; absent on reconciliation.js and host-reference.html) + tests/unit/middleware/embeddedHtmlRoutes.test.ts. The X-Embedded-Session-Id validation on the /api/embedded/* routes remains the primary access boundary; this CSP gate is defense-in-depth.
• auth.ts synthetic-'unknown' sentinel cleanup — DONE. src/middleware/auth.ts (both authMiddleware and optionalAuthMiddleware) now rejects a signature-valid JWT carrying no usable sub/id claim with 401 Invalid or expired token, instead of fabricating req.user.id = 'unknown'. Every real issuer sets a subject (generateJWT({ sub }), OAuth2Service sub: userId; embedded tokens don't use the JWT_SECRET path), so no legitimate caller is affected — only malformed tokens, which fail closed. With the literal no longer producible, the route-local SYNTHETIC_OPERATOR_USER_IDS set in src/routes/reconciliationCenterRoutes.ts now carries only SYSTEM_IDENTITY.userId; the route still fails closed on missing/system identity. (The originally-planned approach — make req.user.id optional + guard ~15 req.user!.id sites in rbac.ts — proved unnecessary: rejecting at the auth layer keeps id non-optional for every authenticated request. authentication.ts's OAuth path was tightened the same way, dropping its 'unknown' audit fallbacks via a discriminated AuthenticationResult union.)

Verification (60-second AI-reviewer recipe)

npm test -- tests/unit/database/migrations/048-create-reconciliation-center-tables.test.ts
npm test -- tests/unit/services/reconciliationCenter/
npm test -- tests/unit/routes/reconciliationCenterRoutes.test.ts
grep -n "reconciliation_exceptions\|CHECK (severity" src/database/migrations/048-create-reconciliation-center-tables.ts
grep -n "jwtOperatorIdentity\|identity_required" src/routes/reconciliationCenterRoutes.ts
# curl -H "Authorization: Bearer <jwt>" http://localhost:3003/api/reconciliation-center/exceptions