[Low] Launch hardening & cleanup batch (from launch-readiness audit)

[novaspatial]

Severity: Low — batch of hardening/cleanup items from the launch-readiness audit. Split out if any grows.

Items

1. Enable leaked-password protection in Supabase Auth (security advisor: currently disabled) — checks passwords against HaveIBeenPwned.

2. No Stripe idempotencyKey on paymentIntents.create (src/app/api/portal/projects/checkout/route.ts:381-406) — a double-submit mints two intents + rows (bounded only by the 3/min limit). Add an idempotency key.

3. Observability gaps: webhook/poll metadata-mismatch events log-and-200 with no alerting surface, and absent metadata passes the cross-checks; no error-reporting service (Sentry/OTel) is wired at all (src/app/error.tsx:15 has only a scaffold comment). Wire error reporting + a mismatch alert.

4. Discount RPC surface: lookup_discount_code is granted to authenticated with no in-body guard and returns full code params incl. reserved/redeemed counts — a per-guess oracle (supabase/migrations/20260715_consume_discount_codes.sql); reserve_/restore_first_mix_discount retain the default EXECUTE TO PUBLIC and their identity guard is bypassed for anon (auth.uid() null passes). Bounded today (0 codes; dormant flag). Route lookup through the service client and revoke the first-mix RPCs to service_role only, matching the catalog trio.

5. Thin confirm route + error leakage: files/[fileId]/confirm/route.ts uses requireApiUser only (no state/uploader check, blind upload_status='uploaded'); files/route.ts:91 forwards the raw storage/PG error object to the client on 500. Tighten the guard; stop leaking internal error details.

6. Stripe API version drift: pinned 2026-03-25.dahlia is two releases behind the installed SDK's 2026-05-27.dahlia, and the as ApiVersion cast suppresses drift detection (src/lib/stripe/server.ts:16-20). Re-pin deliberately.

7. Cosmetic/config: dead footer newsletter form promising a welcome discount (no submit handler); STRIPE_RESTRICTED_KEY documented but unused; package.json still named tailwind-plus-studio with no engines field; contact page shows mix@nova-spatial.com while CONTACT_INBOX_TO=contact@nova-spatial.com; RESEND_FROM_EMAIL fallback is the wrong brand ("Atmos onboarding@resend.dev"); several handlers await request.json() unguarded (500 instead of 400 on malformed bodies); enforce_studio_only_archive lacks the service-role escape its sibling fences have; purge drains only 20 projects/day with mayHaveMore ignored (irrelevant at launch volume).

8. Orphan rows: no sweeper exists for stale pending project_files rows (register → abandoned PUT) or for registered-but-never-submitted comment-attachment objects. Harmless for /listen (filters upload_status='uploaded') but accumulates.

[novaspatial]

This was generated by AI while working the issue.

First pass shipped in 137dba9. Item-by-item status — this issue stays open for the rest.

Done:

• 2. Stripe idempotency key. The intent is created with a key derived from the owner, the order's shape (amount, currency, counts, add-ons, billing region, coupon), and a 5-minute bucket. A double-submit returns Stripe's original intent instead of minting a second one plus a second pending project; a genuinely different order, or the same order later, still gets its own. Five tests on the key builder.
• 5. confirm route + error leakage. Confirm is now restricted to the row's registrant (403 otherwise) and to the pending → uploaded edge — re-confirming is a no-op, any other state is a 400 — with a CAS on the update. The register endpoint no longer forwards raw storage/Postgres error objects on 500; they are logged and the client gets a generic message.
• 6. Stripe API version. Re-pinned to 2026-05-27.dahlia, the installed SDK's own version, and the as ApiVersion cast that let the pin drift (and hid the drift) is gone.
• 7, partly. The contact and about pages advertised mix@nova-spatial.com while inquiries route to contact@nova-spatial.com — corrected. The RESEND_FROM_EMAIL fallback was another brand's sandbox address (Atmos <onboarding@resend.dev>); it is now NOVA Spatial <noreply@nova-spatial.com>. The unguarded request.json() on the contact endpoint was fixed under [Medium] Contact form: no rate limit, length caps, email validation, or captcha #51.
• Bonus, from [Medium] Contact form: no rate limit, length caps, email validation, or captcha #51: the rls_policy_always_true advisor finding on contact_inquiries is gone — the table is deny-all with service-only writes now.

Still open:

• 1. Leaked-password protection — a Supabase Auth dashboard toggle, not a repo change. Still showing in the advisors. Ops.
• 3. Observability — no error-reporting service is wired, and the webhook/poll metadata-mismatch path still logs-and-200s with no alert. Sentry/OTel needs an account and a DSN before any code is worth writing. Needs a decision on the vendor, then a follow-up.
• 4. Discount RPC grants — routing lookup_discount_code through the service client and revoking the first-mix RPCs to service_role means threading a service client through resolveSubmittedCode and reserveOrderDiscount, which touches the checkout money path. Deliberately not rushed into the same pass as the rest; it deserves its own unit with its own probes. Bounded today (0 catalog codes, dormant flag).
• 7, remainder — dead footer newsletter form, unused STRIPE_RESTRICTED_KEY, package.json still named tailwind-plus-studio with no engines, the enforce_studio_only_archive fence missing the service-role escape its siblings have, and the purge's ignored mayHaveMore.
• 8. Orphan sweeper for stale pending project_files rows and registered-but-unsubmitted attachment objects.

Suite 969 green, build green, CI green; security advisors show no ERROR-level findings.

[novaspatial]

This was generated by AI while working the issue.

Work Plan — slice: discount-RPC grant tightening (item 4)

Unit: ships alone as its own unit per the devplan ruling ("touches the money path — its own unit"). Two-step landing: one code commit deployed first, then the migration — the four-state deploy matrix shows migration-first would 500 every checkout on the old build, while the new code works identically under both grant states (service_role already holds EXECUTE on all three RPCs).

Grounding: CLAUDE.md's client-choice rule (service client for sessionless authority, session client for RLS-scoped user reads) and the 20260715 catalog-trio precedent: holds are system-managed, the verified order — not the session — is the authority, ACL as first fence + in-body guard as the drift-proof floor (a future DROP+CREATE re-opens default EXECUTE grants).

Urgency worth recording: verified against live pg_proc.proacl that reserve_first_mix_discount / restore_first_mix_discount are EXECUTE-able by anon (default-privilege auto-grant, never revoked), and their 20260702 identity guards deliberately pass through when auth.uid() is null — so an unauthenticated PostgREST caller can burn or re-arm any client's welcome flag today. This slice closes that.

Changes:

• src/lib/portal/orderDiscount.ts — resolveSubmittedCode and reserveOrderDiscount take a required service client (lookup + first-mix reserve move onto it; hasPriorPaidProject stays on the session client so RLS keeps applying to the user-tied read); restoreFirstMix/restoreUnpaidOrderDiscount run on the service client, null → log-and-skip (existing catalog posture).
• Call sites: checkout route (passes the service client it already builds), discount-codes/validate (builds one, throw → the existing 503 answer), project DELETE (passes its existing nullable service client).
• Migration 20260731_service_only_discount_rpcs.sql — CREATE OR REPLACE both first-mix functions with the 20260715 system-managed guard (42501 for any non-null non-studio uid; restore's consumed-check kept verbatim), then REVOKE public/anon/authenticated + GRANT service_role on both, plus REVOKE lookup_discount_code FROM authenticated (uniform grant model across all six discount functions; kills the per-guess catalog oracle). -- down: re-grant snippet in the header; post-migration code rollbacks must pair with it.

Call-site audit: grepped every non-test caller of the three RPCs — checkout (no-code + catalog paths), validate, the delete route's restore, and the release() closures. Webhook / payment-status / dev-bypass already run on the service client. No browser-side callers.

Verification: pre-migration positive controls with random UUIDs (side-effect-free — the guarded UPDATEs match no row): anon reserve/restore succeeds today, session lookup returns rows today; after: 42501 / permission denied on all three, service key still succeeds. End-to-end: no-code, WELCOME, and catalog checkout, validate endpoint, unpaid-discounted delete → hold restored. Then lint, npx vitest run, npm run build (pipefail), get_advisors(security) — expect the two first-mix WARNs to disappear — and CI on main.

Out of scope: the leaked-password toggle and error-reporting items (separate slices); hasPriorPaidProject's client choice (unchanged by design); the abandoned-checkout coupon-capacity residual (stays accepted).

[novaspatial]

This was generated by AI while working the issue.

Item 4 — discount RPC grants: shipped

Shipped in 8a9462a (code) + migration 20260731_service_only_discount_rpcs (applied to production after the deploy drained).

What it turned out to be. Not just ACL hygiene — a live unauthenticated hole. pg_proc.proacl showed =X/postgres (PUBLIC) plus anon on both first-mix functions: Supabase's default function privileges auto-grant anon/authenticated/service_role, and 20260422 never revoked them. Their 20260702 identity guards deliberately pass when auth.uid() IS NULL for service contexts — but an anon PostgREST caller has a null uid too. So an unauthenticated request could burn any client's welcome flag, or re-arm one (the consumed-check only blocks re-arming after a paid discounted order).

Verification — evidence, not adjectives. Probes used a random UUID that matches no row, so every call was side-effect-free (both guarded UPDATEs match nothing).

Pre-migration positive controls (anon key, production):

POST /rpc/reserve_first_mix_discount  -> http=200  false     <- the hole
POST /rpc/restore_first_mix_discount  -> http=204            <- the hole
POST /rpc/lookup_discount_code        -> http=401  42501     (anon already revoked in 20260715)

ACL before: reserve/restore_first_mix_discount = {=X/postgres, postgres, anon, authenticated, service_role}, lookup_discount_code = {postgres, authenticated, service_role}.

Post-migration, same probes:

POST /rpc/reserve_first_mix_discount  -> http=401  42501 permission denied for function
POST /rpc/restore_first_mix_discount  -> http=401  42501 permission denied for function
POST /rpc/lookup_discount_code        -> http=401  42501 permission denied for function

App-path invariant (service_role key, same random UUID) — all three still execute: 200 false, 204, 200 []. ACL after, all three: {postgres=X/postgres, service_role=X/postgres}.

get_advisors(security): the three RPCs have dropped out of the anon_/authenticated_security_definer_function_executable findings entirely (they were listed before); no ERROR-level findings. Gates: lint clean, npx vitest run 971 passed, npm run build clean, CI green on main.

Deploy ordering, as planned. Four-state analysis said old-code x new-grants is the only breaking cell (it 500s every no-code checkout), so the code shipped and drained first, then the migration. A code rollback past this point must be paired with the -- down: re-grant snippet in the migration header — flagged there.

Not claimed: no end-to-end checkout was run against production (that would mint a real payment intent on a live site, and there is no test account); the money path is covered by the unit suites plus the service-role probes above. The hasPriorPaidProject D5 read deliberately stays on the session client so RLS keeps applying to it.

Remaining on this issue: leaked-password toggle (ops), Sentry wiring (ruled 2026-07-31 — code next, DSN is ops), config cosmetics, orphan sweeper.

[novaspatial]

This was generated by AI while working the issue.

The technical half of this batch is complete — every code item shipped in 8a9462a, 2597d8f, and 5252247. Item by item against the original eight:

• 1. Leaked-password protection — ops, see below.
• 2. Stripe idempotency key — shipped in the first pass.
• 3. Error reporting + money-path alert — code shipped, DSN is ops (below). src/lib/observability/report.ts is now the one place errors leave the app; all four Stripe-metadata mismatch branches route through alertMoneyPathAnomaly, captured at error level with a fingerprint per kind and identifiers only — never amounts or emails.
• 4. Discount RPC grants — service_role-only across all six. This was the live anon hole documented earlier in this thread.
• 5. No raw error details to clients — shipped in the first pass.
• 6. Stripe API version re-pinned — shipped in the first pass.
• 7. Config cosmetics — newsletter form made real (it forwards to signup with the promo token, like the promo popup, instead of silently discarding a submission after promising a discount); package.json renamed with an engines range matching the CI pin; STRIPE_RESTRICTED_KEY dropped from .env.example and CLAUDE.md (nothing read it); the purge cron now warns when its batch fills, so a capped run stops looking identical to a complete one; enforce_studio_only_archive gained the null-uid escape its five sibling fences have.
• 8. Orphan sweeper — /api/cron/sweep-orphans, daily 06:30 UTC.

Two scope notes. The sweeper needed a schema change I want on the record: created_at could not carry the staleness cutoff, because #57 made registration idempotent and a re-upload flips an existing row back to pending without touching any timestamp — a sweep keyed on created_at would have deleted a live re-upload's row and its object mid-transfer. upload_registered_at is stamped on every registration. And the sweeper deliberately never names the projects table, with a test asserting it: abandoned pending-payment checkouts holding coupon capacity is a deliberate residual, and this must not quietly become that sweep. Separately, one advisor finding outside this issue was fixed in the same batch — the public blog-assets bucket had a broad SELECT policy letting any anon caller enumerate it (probed: a list returned the folder tree before, nothing after; public image reads still 200).

---

Remaining human/ops actions before this issue can close:

• Supabase Auth → leaked-password protection. Dashboard → Authentication → Providers → Password → enable "Check against HaveIBeenPwned". Receipt: re-run get_advisors(security) and confirm the auth_leaked_password_protection warning is gone — I can verify that for you once it's flipped.
• Sentry: create the project and set NEXT_PUBLIC_SENTRY_DSN in the Vercel env (Production, and Preview if you want preview errors too). The code ships inert — with no DSN nothing initializes and errors reach the console only, exactly as before — so setting the variable and redeploying is the entire activation. The CSP's connect-src token for the ingest host is derived from the same DSN at build time, so no separate policy edit is needed. Receipt: trigger any error and confirm it appears in Sentry; isErrorReportingConfigured() is the machine-checkable version.

The issue stays open until these are ticked.