Migrate AMS's local-store layer (node:sqlite) to the shared pg-adapter/SqliteDriver seam

[JSONbored]

Problem

AMS's ~13 local stores (packages/loopover-miner/lib/local-store.js and siblings — portfolio-queue.js, claim-ledger.js, event-ledger.js, governor-state.js, run-state.js, etc.) use node:sqlite's synchronous DatabaseSync, one file per machine. Hosted AMS containers have ephemeral local disk (confirmed against Cloudflare's own Containers docs — state must live in an external durable store, not container-local files), so this needs to move to a real external database regardless of any tenant-scoping concern.

Already researched in packages/loopover-miner/docs/ams-storage-abstraction-research.md (for the now-closed #5216) — this issue is that research's recommendation, actually implemented.

Area

packages/loopover-miner/lib/local-store.js and every store built on it.

Proposal

Adopt ORB's existing SqliteDriver seam (src/selfhost/d1-adapter.ts + src/selfhost/pg-adapter.ts) rather than inventing a new abstraction — Postgres-lead, per the research doc's finding that AMS's batchClaim-style interactive transactions (BEGIN IMMEDIATE → read → conditional per-row write → COMMIT) need real transactional Postgres; D1's batch() only runs predetermined statements and can't express read-then-conditional-write.

Two real costs, both already scoped by the research doc:

• Universal: convert every store's synchronous .prepare().run()/.get()/.all() calls to await-based async calls against the new adapter.
• Postgres-specific: verify AMS-only SQL constructs against the existing pg-dialect translator (translateSql/translateDdl) — most of the needed translation already exists from ORB's own use of it.

The existing api_base_url composite-key scoping (#5563) is the natural insertion point for a tenant_id column, matching the pattern ORB's own hosted tables use.

Deliverables

• All ~13 stores migrated to the async Postgres-backed adapter
• batchClaim (portfolio-queue.js) and attempt-log.js's interactive transactions preserved via the adapter's runOn(client) pattern (pg-adapter.ts)
• Lease-liveness reclaim (leased_at) moved from local-only staleness checks to lease-expiry-by-time, since this now runs across real concurrent workers, not one machine
• Self-host mode unaffected — this only changes AMS's storage backend for the hosted path; self-host keeps node:sqlite as its default

Boundaries

• Does not itself add tenant-scoping/kill-switch logic — that's a separate, maintainer-owned issue (see global-singleton-tenant-audit.md's Group 1/Group 2 findings) since it touches trust/safety boundaries this migration doesn't need to resolve on its own.

Links & Resources

• Design: shared hosting control-plane for ORB + AMS SaaS (provisioning, secrets, billing, health) #7173 (parent — shared hosting control-plane)
• packages/loopover-miner/docs/ams-storage-abstraction-research.md — the research this implements
• src/selfhost/d1-adapter.ts, src/selfhost/pg-adapter.ts, src/selfhost/pg-queue.ts — the seam to reuse

[linear-code]

JSO-4933

[RealDiligent]

Working on this — starting with part 1: introduce the SqliteDriver/D1 adapter seam in loopover-miner (node:sqlite default) and migrate run-state onto it. Will open a PR as Advances #7175 (not closing the full migration).