v2/user-policies returns 503 for logged-in users: reforms cannot be saved #3977

Activity

1. PavelMakarchuk commented on Oct 20, 2026

   

   PavelMakarchuk

   on Oct 20, 2026

   CollaboratorAuthor

   More actions

   Findings from the production logs (project policyengine-api, service policyengine-api):

   • Revision policyengine-api-00156-mag (created 2026-10-20 05:32 UTC, service.version 5.5.7).
   • 4 v2_user_persistence_unavailable errors, all POST /v2/user-policies, 12:33:58–12:35:57 UTC today (the user's save attempts).
   • error_type: ProgrammingError, raised from SQLAlchemy _execute_on_connection → psycopg cursor.execute. So the database rejected the SQL itself (for example an undefined column or table, or an ON CONFLICT without a matching constraint). It is not a connection or availability problem.
   • The release that created this revision ran "Seed production v2 database / Upgrade schema and seed v2 database" successfully, so the Alembic upgrade did run.

   The Postgres error text is not in the API log (the log is secret-safe), so the exact column or constraint needs the Supabase Postgres logs for that window, or a replay against the production v2 schema.

   @anth-volk, could you check this? Related: #3959 (it keeps native v2 routes at 503 on a database failure), and #3962 / #3963 (association columns and v2 tables). Is any of those changes already applied to the production v2 database?

   Impact: logged-in users of app v3 (now the default US app) cannot save reforms.
2. anth-volk

   self-assigned this

   on Oct 20, 2026
3. anth-volk commented on Oct 20, 2026

   

   anth-volk

   on Oct 20, 2026

   Collaborator

   More actions

   Root cause

   The production database role the Public API uses for v2 data, policyengine_v2_runtime, had no privileges on legacy_policy_mappings. Every POST /v2/user-policies reads that table, so PostgreSQL rejected the query with permission denied for table legacy_policy_mappings. psycopg raises that as ProgrammingError, and user_resource_operation turns it into 503 "V2 user persistence is unavailable".

   Signed-in and signed-out users were both affected. In the US and UK, app v3 saves signed-out work to /v2/user-* as the browser's unauthenticated user (the PolicyEngine-Unauthenticated-User header), not to browser storage. Report saves (POST /v2/user-reports) kept working because they never read this table.

   Why the role had no access

   • The table belongs to Stage 10 dual-write. legacy_policy_mappings was created by migration 711ec2f0a5a5 (Stage 10, 2026-09-01). It maps a v1 policy ID to that policy's v2 copy. Only the v1 policy routes write it, and only under DB_WRITE_POLICY=dual_write. Production runs cloud_sql, so this table and policies are empty in production.
   • Stage 12A started reading it. /v2/user-policies creates and updates look up the v2 copy of legacy_policy_id there (resolve_user_policy_reference → read_mirrored_policy_ids). That's when the runtime role first needed access.
   • Privileges aren't managed in the repository. No v2 migration contains a GRANT. Both Supabase projects now have default privileges giving policyengine_v2_runtime SELECT, INSERT, UPDATE and DELETE on tables policyengine_v2_migrator creates. In production that default must have been set after these tables were created: legacy_policy_mappings and legacy_household_mappings had no access list at all, and the runtime role had no grant on policies or households. Staging's runtime role could use all four, so the same requests succeed there.
   • The production smoke test never saves anything. It only checks that /v2/me and /v2/user-* reject missing or malformed credentials.

   Runtime role privileges on the tables these routes use, before the fix:

   Table	Staging	Production
   legacy_policy_mappings	SELECT, INSERT, UPDATE, DELETE	none
   legacy_household_mappings	SELECT, INSERT, UPDATE, DELETE	none
   policies	SELECT, INSERT	none
   households	SELECT, INSERT	none
   users, user_identities, user_policies, user_household_associations, user_simulation_associations, user_report_associations, legacy_user_mappings, legacy_user_policy_mappings	SELECT, INSERT, UPDATE, DELETE	same as staging

   /v2/user-households creates read legacy_household_mappings the same way, so they would have failed too. App v3 doesn't call that route yet.

   Fix

   Applied by hand on 2026-10-20, shortly before 13:07 UTC, in the production v2 Supabase project:

   GRANT SELECT ON public.legacy_policy_mappings, public.legacy_household_mappings,
                   public.policies, public.households
     TO policyengine_v2_runtime;

   Read access covers every /v2/user-* route. Write access to these tables would only be needed if production turned on dual_write, and we plan to retire these Stage 10 tables instead.

   Verification:

   • has_table_privilege('policyengine_v2_runtime', <table>, 'SELECT') returns true for all four tables.
   • Production request logs: POST /v2/user-policies returned 201 at 13:07:02 and 13:10:44 UTC, each followed by POST /v2/user-reports 201. Before the grant, all 8 reform saves since 12:33 UTC had returned 503.

   Answers to the questions above

   • Make dual-write log failed v2 copies instead of returning HTTP 503 #3959, Stop using the simulation-domain v2 tables and retired association columns #3962 and Drop the empty simulation-domain tables and retired association columns from the v2 schema #3963 are all unmerged, and none of them was applied to the production database. They weren't involved.
   • The fix was a manual change, and table privileges still aren't tracked in the repository. Declare v2 database table privileges in the repository and check them in CI and deploys #3980 covers recording them and checking them in CI and deploys.
4. anth-volk

   closed this as completedon Oct 20, 2026