feat(server): AuditLog table, DB indexes, security hardening, observability
Comprehensive server-side hardening pass: closes auth/security gaps,
adds an append-only AuditLog for security-relevant events, indexes
frequently queried columns, centralizes ctx typing, and lands shared
auth helpers.
## Database (already deployed to prod via prisma migrate deploy)
- New `AuditLog` table for append-only security audit trail
(auth flows, wallet/transaction mutations, privilege grants, signer
changes). Five indexes for the common access patterns.
- Btree indexes on Wallet/NewWallet `ownerAddress`, Signable+Transaction
`walletId`/`state`/`(walletId, state)`, Proxy `walletId`/`userId`/
`(walletId, isActive)`/`(userId, isActive)`, Ballot `walletId`,
BalanceSnapshot `walletId`/`(walletId, snapshotDate)`.
- GIN indexes on Wallet/NewWallet `signersAddresses` (array_ops) — the
signer-membership query was a full table scan.
- Restored `Crowdfund` model declaration (production drift: table exists
in prod but was never declared in main's schema; see PR description
for full archaeology). Marked as retained-but-unused.
- WalletBotAccess: `@@unique` -> `@@id` to match prod (drift from
PR #207 / commit 1facdc3 where schema and migration disagreed at
landing).
- Ballot.updatedAt: restored `@default(now()) @updatedAt` to match
prod's column default (drift accumulated across multiple commits).
- Ballot.anchorUrls / anchorHashes: added `DEFAULT ARRAY[]::TEXT[]` to
match the schema's `@default([])` annotation.
## Observability primitives
- `src/lib/observability/audit.ts` — `audit(db, event)` emitter; never
throws (audit miss must not break user flow); redacts secrets in
metadata before write.
- `src/lib/observability/logger.ts` — structured logger; JSON in prod,
human-readable in dev; never logs raw tokens/signatures/cookies.
## Security fixes (Wave 1-3)
- Closed `ownerAddress === "all"` bypass in `assertWalletAccess`. The
string "all" was being treated as a wildcard owner — any session
could claim ownership of any wallet whose `ownerAddress` happened to
contain that literal.
- `lookupMultisigWallet`: validate stake-credential-hash format before
query (prevents prefix-match abuse and full-table scans on malformed
input).
- Centralized rate-limit and request-guard surface (`src/lib/security/
rateLimit.ts`, `requestGuards`). Bot routes now use bot-scoped
rate limit; user routes use IP-scoped.
- `verifyJwt`: stricter token-type narrowing; explicit `isBotJwt`
predicate.
- `walletSession`: tighter expiry handling, no implicit refresh.
## Auth helpers (Wave 8)
- New `src/server/api/auth.ts` consolidates `requireSessionAddress`,
`getSessionAddresses`, and wallet-access checks that were duplicated
in nearly every router. One source of truth, one place to extend.
- All routers and v1 API handlers migrated.
## ctx typing (Wave 2)
- New `AuthCtx` and `TRPCContext` exported from `src/server/api/trpc.ts`.
- All router helpers use `AuthCtx` instead of `any`.
- `protectedProcedure` middleware: type-narrows `sessionWallets`,
`primaryWallet`, `sessionAddress` correctly.
## Audit emitters (Wave 5)
Wired into:
- auth flow (login success/failure, JWT mint, bot auth)
- wallet mutations (create, update, archive, transfer, signer changes)
- signable + transaction mutations (sign, reject, broadcast)
- bot privilege grants
All emitters fire after the underlying action and never block it.
## SSRF defense for `/api/v1/og`
The OG metadata endpoint now:
- requires https, denies non-allowlisted hosts
- DNS-resolves and rejects private/loopback/link-local addresses
- denies upstream redirects (no auto-follow)
`OG_ALLOWED_HOSTS` env var configures the allow list; "*" allows any
public host (still SSRF-guarded).
## Test infrastructure
- jest.config.mjs — moduleNameMapper for CSS, transformIgnorePatterns
for ESM-only deps (superjson, @trpc, @meshsdk, jose, etc.)
- setupEnv.cjs — pre-test env bootstrap (SKIP_ENV_VALIDATION=1, dummy
DB/JWT/Blockfrost values) so `src/env.js` doesn't throw on import.
- Frozen wall clock (`Date.now`/`new Date`) for byte-identical test
runs; real timer APIs preserved.
- `__mocks__/styleMock.cjs` — CSS imports mock for jest.
## Tests
- New: `og.test.ts` (SSRF tripwire suite — 9 cases for the og handler).
- New: `signing.test.ts` (source tripwires preventing the
`return true ? signature : undefined` regression and similar).
- Updated existing tests to match Jest 30 strict mock typing
(jest.fn<...>() generics) and new ctx fields.
## Verification
- Typecheck clean
- All 165 staged-suite tests pass deterministically across two runs
- Migration `20260510160404_audit_log_and_indexes` already applied to
the multisig Supabase production DB — `prisma migrate deploy` on
this branch is a no-op (idempotent).
Depends on: #236 (build fix; without it `next build --webpack` will
crash on `/wallets/[wallet]/transactions/new`).
Co-Authored-By: Claude Opus 4.7 (1M context) <[email protected]>
![dependabot[bot]](https://avatars.githubusercontent.com/in/29110?v=4&s=80)
