Implement subscribed_tools custom access-token hook (subscription gate 403s all users)

[ckrough]

Summary

Deployed Retriever chat returns 403 {"detail":{"error":"subscription_required","module":"retriever"}} for a fully authenticated user with a valid token. The subscription enforcement chain is only half-implemented: the subscriptions table and the backend gate exist, but the Supabase custom access-token hook that projects the subscribed_tools claim into the JWT was never built. As a result the claim is never present on any token, and require_subscription(...) rejects every user on every gated route.

This surfaced while diagnosing a deploy-time auth failure. The prior 401 Invalid or expired token (hosted evermore-auth was on HS256; the Retriever's SUPABASE_URL pointed at the wrong project) has been resolved by rotating evermore-auth to ES256 and aligning both SUPABASE_URL (Retriever Worker) and PUBLIC_SUPABASE_URL (Stacker) to evermore-auth. Auth now validates cleanly; this 403 is the remaining blocker.

Evidence

• docs/subscriptions.md states a Supabase auth hook "projects the user's active module ids into a subscribed_tools claim on the JWT itself (per-request, at token-issue time)."
• No such hook exists: no SQL function defining subscribed_tools or a custom_access_token hook in any migration or SQL file (apps/stacker/supabase/migrations/ contains only 20260702000000_subscriptions.sql; seed.sql has no hook).
• apps/stacker/supabase/config.toml:279-282 — the [auth.hook.custom_access_token] block is the commented-out stock template, never enabled.
• packages/auth/src/evermore_auth/dependencies.py:84 — AuthUser defaults the claim to empty when absent (tuple(payload.get("subscribed_tools", []))), so a token with no claim yields subscribed_tools = () and require_subscription("retriever") raises 403 (dependencies.py:98-102).

Root cause

The token-side half of the subscription model (the access-token hook) was documented but never implemented, so subscribed_tools is never on any token. Every module-gated route 403s regardless of the subscriptions table contents.

Where the fix lives

Everything belongs in the evermore-auth project (the subscriptions table references auth.users, and a custom access-token hook runs at token-issue time in the auth project's Postgres). The apps/stacker/supabase/ migrations are the source for that project.

Acceptance Criteria

• A Postgres function (custom access-token hook) is added as a migration under apps/stacker/supabase/migrations/ that, given the hook event's user_id, reads public.subscriptions for rows with status in ('active','trialing') and adds a subscribed_tools array claim (module ids) to the token claims. Absent/empty subscriptions yield an empty array, not an error.
• The function is granted to supabase_auth_admin (and execute is revoked from authenticated/anon/public as appropriate), per Supabase custom-access-token-hook requirements.
• apps/stacker/supabase/config.toml enables [auth.hook.custom_access_token] pointing at the function (uncomment + set the pg-functions:// uri) so local dev issues tokens with the claim.
• The hook is enabled on the hosted evermore-auth project (Auth → Hooks), and this hosted step is documented (ties into the config-as-code work, Configure production SMTP (Cloudflare Email Service) for Supabase auth emails #157).
• docs/subscriptions.md is corrected/expanded so the hook it describes matches the implemented function (it currently describes the hook as if it exists).
• A fresh login on deployed Stacker for a user with an active retriever subscription row carries subscribed_tools: ["retriever"] in the token, and Retriever chat returns 200 instead of 403.
• Verify against changelog/current Supabase docs before implementing (custom access-token hook API and grant requirements have changed across versions).

Notes / test path

• For testing without a real Stripe purchase, insert a row directly in evermore-auth: insert into public.subscriptions (user_id, module_id, status) values ('<uuid>', 'retriever', 'active'); then re-login (the claim is baked in at token issue).
• Quick triage on any failing token: decode it and check for subscribed_tools. Absent ⇒ hook not live; present-but-empty ⇒ hook live, missing subscription row.

[ckrough]

daedalus lead claimed

[ckrough]

daedalus lead: PR #289 opened (non-draft, Closes #288). Pipeline: implement (Sonnet) → test-validator (0 blocking) → review-gate APPROVE → simplify NO-OP → gates test+lint exit 0 → security review.

Security review disposition (3 LOW findings):

• Remediated in PR: least-privilege column-scoped grant select (user_id, module_id, status) (was table-wide, exposed stripe_subscription_id); NULL-safe coalesce(event->'claims','{}'::jsonb) so the hook never returns SQL NULL.
• Accepted risk: claim-based entitlement lags token TTL (a canceled subscription's already-issued JWT keeps access until expiry). Inherent to the documented claim-based design; bounded by the 1h access-token lifetime. Not fixed.

Operator handoff (out of code scope, needs prod access + deploy):

1. Enable the hook on hosted evermore-auth (Auth → Hooks → Custom Access Token → custom_access_token_hook); config-as-code tracked in Configure production SMTP (Cloudflare Email Service) for Supabase auth emails #157.
2. Verify on deployed Stacker: seed an active retriever subscription row, fresh login should carry subscribed_tools:["retriever"] and Retriever chat should return 200.