2.25.0

20260930120000_pin_function_search_path.sql sets search_path = public on the 22 functions in public that had none — the one-line SQL helpers behind AI credits, free-tier limits, quest-status and task-origin checks, and the set_updated_at, prevent_quest_owner_change and update_user_location_consent_updated_at triggers — matching the convention the other ~250 functions already follow, so the advisor's function_search_path_mutable lint (0011) reports nothing. None sits in an index, default or constraint; the only side effect is that these SQL functions are no longer inlined. Proven by supabase/tests/security_advisors.sql. (#695)

20260930130000_tighten_always_true_policies.sql replaces the two policies whose WITH CHECK was the constant true (0024). "Guild owners can update their guilds." now checks the outgoing row: its owner_id must be the caller or an accepted member of that guild, so a stranger can never become owner even if the transfer trigger were dropped; the trigger keeps its 23514 when RLS is bypassed, and a stranger transfer through RLS now fails with 42501 before the trigger sees it (guilds.sql updated accordingly). "Anyone can submit a beta signup." still lets anon insert, but only a row whose email looks like an email (≤ 254 chars) with each survey answer ≤ 2,000 chars and source ≤ 100; the API rate limit remains the volume guard. (#695)

20260930140000_lock_down_definer_execute.sql revokes EXECUTE from public, anon and authenticated on every trigger function in public (a loop over pg_proc, so none is missed), revokes anon (and the PUBLIC grant six of them still had) on the 24 helpers that need a signed-in caller — the RLS predicates has_privilege, has_role, is_admin, user_can_access_quest, user_can_manage_quest_party, user_is_quest_party_member, and the RPCs whose result depends on auth.uid() — and makes record_free_tier_action_for() service-role only, as its comment always said. Twelve SECURITY DEFINER functions keep their anon grant because a public page or an anon-evaluated policy needs them (the invitation previews, the unsubscribe-by-token setters, get_quest_with_tasks, ensure_quest_jam_cycles, get_public_quest_owner_profiles, the get_quest_jam_* readers, and user_owns_quest / user_owns_task, which PUBLIC-scoped policies on tasks and task_investigations call); they now hold that grant directly rather than through PUBLIC. Default privileges are left alone — the advisor step already fails a PR that adds an exposed definer. (#695)

20260930150000_drop_pg_graphql.sql drops the pg_graphql extension and graphql_public leaves [api].schemas in supabase/config.toml. Nothing in the web app or the worker calls /graphql/v1, and while the extension is installed every table anon or authenticated can SELECT is discoverable — names, columns, relationships — through GraphQL introspection even with RLS on, which the advisor reports per table and role (0026/0027: 79 findings on this schema, and the reason the advisor step from #696 had failed on every run since it landed — those two lints only fire where pg_graphql is installed, which the seeding sandbox was not). The hosted project's exposed-schema list lives in the dashboard (Settings → API) and needs graphql_public removed by hand; security_advisors.sql asserts the extension is absent. (#695)

20261001120000_auto_revert_inactive_public_quests.sql adds quests.last_activity_at (NOT NULL, backfilled from the quest's own dates, its tasks' updated_at and its investigations' created_at, and added to the quest column allowlist 20260914120000_due_date_privacy.sql introduced — a column left off it is not only unreadable but makes select * fail, and when a quest was last worked on is not private the way a deadline is; due_at remains the only quest column clients may not read), the six triggers that keep it current — a quest created or edited, a task inserted, updated or deleted, an investigation created; a deletion is the one kind of task activity that leaves no row to read a date from, so it stamps now(), and without it an owner who tidied up a shared quest could watch the sweep unshare it the same day, revert_inactive_public_quests(_inactivity_days integer default 30) and the daily revert-inactive-public-quests-daily pg_cron job at 09:45 UTC. The function unshares every public quest idle beyond the window and returns the rows it changed; it writes is_public and nothing else — not even updated_at, which set_updated_at() would otherwise stamp, sending every quest the sweep hides to the top of its owner's list, since listQuests() orders by it — exempts quests owned by an Admin or SuperAdmin and the public_quest_catalog system user, skips soft-deleted rows, is serialised on an advisory lock, rejects a window below one day, and is revoked from anon and authenticated — with _inactivity_days => 1 it would otherwise empty the browse page for anyone holding the anon key. The column is bounded at both ends: it only moves forward, so a back-dated task import cannot rewind it, and never past now(), because tasks_set_updated_at fires only on UPDATE and an INSERT through PostgREST keeps whatever updated_at the client sent — one task dated 2030 would otherwise hold a quest on the browse page until then, and an investigation's created_at is the same. quest_last_activity_from_history(uuid) — which seeded the column and is the repair if it is ever doubted — clamps the same way, because every operand it reads is client-writable and a row posted from the future before this migration would seed the column there; it is a function rather than an expression inside the backfill UPDATE so that the clamp can be asserted. Sharing a quest counts as activity and unsharing does not, which is what lets the job leave the rows it has just made private with an honest date and makes re-sharing restart the clock; and a client writing the column itself is ignored on insert and on update, because quests is granted UPDATE at table level and there is no column privilege to revoke without re-granting the other thirty. quests.updated_at is not part of the ongoing definition — set_updated_at() writes timezone('utc', now()) into a timestamptz, which is not comparable to now() off UTC — so the trigger notices a quest edit directly; the backfill still reads it, clamped with least(…, now()), as the only record of past edits. The window is the function's default argument and the cron command passes none. (#693)

The Supabase workflow now runs the dashboard's Security and Performance advisors against the local stack on every pull request that touches supabase/, and fails on any WARN or ERROR finding that is not allowlisted. scripts/db-advisors.sh (also pnpm supabase:advisors) fetches splinter at a pinned release tag, runs it with psql in one transaction with pgrst.db_schemas set to the schemas supabase/config.toml exposes, keeps the WARN/ERROR rows and matches each key against supabase/advisor-allowlist.txt — exact keys and # comments, every entry standing for exactly one finding, so a second overload of a listed function fails rather than hiding behind its line. The one key the script rewrites is multiple_permissive_policies: splinter's names a role the hosted project has and the local stack does not, so the script drops the role and keys on the policy set instead, and a policy added to an already-listed table is a new finding. The workflow's path filter includes the script, so a pin bump or matcher change runs against the local stack. Before this, supabase db lint was the only database check and it is plpgsql_check, not the advisor lints, so every migration could add a mutable search_path or an anon-callable SECURITY DEFINER function and the finding surfaced in the dashboard after db push. The allowlist is seeded from the 2026-09-19 advisor run — 304 entries, five of them intentional and commented, the rest a backlog the script reports as stale once each fix lands — so the guard goes green today and only stops the backlog growing; it does not shrink it. (#696)

supabase/tests/security_advisors.sql (37 assertions) pins the security-advisor state: every function in public has a search_path, no trigger function is executable by anon or authenticated, no SECURITY DEFINER function is reachable through a PUBLIC grant, and the set anon may execute is exactly the twelve intended functions; then, as each role, that anon cannot call set_user_plan_tier(), that the body refuses an anon JWT even when EXECUTE is granted and refuses a signed-in caller recording someone else's free-tier action, that the service role keeps both, that anon still reads a public quest's tasks, loads it through get_quest_with_tasks() and mints Quest Jam cycles, that a guild owner cannot hand the guild to a non-member but can to an accepted one (and the trigger still rejects a non-member when RLS is bypassed), and that a beta signup with a malformed email or oversized text is refused while a well-formed one is not. rls.sql gains six regression pins for the same grants and policies; guilds.sql's non-member transfer now expects the policy's 42501. Each of the three migrations was removed in turn and its assertions failed. (#695)

scripts/db-advisors.test.mjs pins the advisor guard: the workflow step runs after db lint and before the stack stops with the same skip guard as its neighbours, pnpm supabase:advisors points at an executable script, SPLINTER_REF is a release tag rather than a branch, the workflow's push and pull_request path filters include the script, the script's schema list equals [api].schemas and its default port equals [db].port in supabase/config.toml, and every allowlist entry is an exact key naming one lint with no wildcard. (#696)

scripts/dev-tui/env-flags.test.mjs pins the branded confirmation template: the [auth.email.template.confirmation] section, its subject, a content_path that resolves from the repository root, and the {{ .ConfirmationURL }} placeholder inside the file. (#670)

scripts/release-tags.test.mjs removes its temporary repositories with retries. A bare remote a test has just pushed to can still have a detached git gc --auto writing under it when the test ends, which made one CI run fail its cleanup hook with ENOTEMPTY on a change that never touched the script. (#692)

supabase/tests/public_quest_auto_revert.sql (42 assertions) runs the job against seven quests that differ only in owner and idleness: the idle one is unshared and reported with its owner and idle date, the ten-day-old one, the one whose task was written today, the Admin-owned one, the Questify Team one and the soft-deleted one are all left alone, a second run returns nothing and rewrites no row (proven by ctid, since now() does not move inside a transaction), a window of zero or null raises 22023, re-sharing restarts the clock and survives the next run, and an owner writing last_activity_at through PostgREST is ignored. The trigger half asserts what counts as activity — a quest edit, a task added, worked on or deleted, an investigation — and what does not: hiding a quest by hand, a task imported with a back-dated updated_at (set on the INSERT, because tasks_set_updated_at overwrites it on every UPDATE), a task or investigation posted with a future timestamp, and a quest created with a future last_activity_at. Seven more cover the later review rounds: quest_last_activity_from_history() clamps a future-dated task to now() rather than discarding it and is not a client RPC, the sweep leaves updated_at alone, a hand edit still stamps it, and an owner deleting a task moves the clock. supabase/tests/public_quest_auto_revert_schedule.sql (15 assertions) pins the job after migrations alone — command, schedule, active, registered once — plus the function grants, that the trigger functions are not client RPCs, that the window is the function's default, that the column is NOT NULL, and that it is readable by anon and authenticated while due_at still is not. Twelve mutations of the migration were each caught by a different assertion. (#693)

docs/schema.md lists the three #695 migrations, states the EXECUTE model (who may run trigger functions, which twelve functions anon may execute and why, how the target-user helpers guard themselves) under Helper functions, and describes the beta-signup WITH CHECK; docs/ops/database-advisors.md records that the backlog shrank from 304 to 184 entries, what the INTENTIONAL section now holds, that the 0011 fix is set search_path = public rather than '', and the dashboard step for leaked-password protection (Authentication → Providers → Email → "Prevent use of leaked passwords"), which no migration can set. supabase/advisor-allowlist.txt drops the 120 keys those migrations cleared, moves the eight anon-callable functions public pages need into INTENTIONAL with a reason each, and groups the 83 signed-in RPCs (0029) as the accepted API surface. (#695)

docs/ops/database-advisors.md explains what the advisor step runs, how to read a failure and the migration line that clears each of the four common lints, how to allowlist an intentional finding with a comment, how the backlog section ratchets down, and how to bump the splinter pin. The add-migration skill adds pnpm supabase:advisors to its fresh-database step and verification checklist, and docs/cicd.md's workflow table names the step. (#696)

docs/notifications.md gains a "Supabase Auth templates" section describing the confirmation template, how the local stack picks it up and why production needs the dashboard step; docs/ops/deployments.md adds that step to the Supabase checklist. (#670)

Added a tool-agnostic skill set under .agents/skills/ for the routine procedures every coding agent kept rediscovering from prose: precommit-gate, changelog-entry, open-pr, add-migration, add-public-route, add-env-flag and cut-release, each a SKILL.md with a trigger description, the step-by-step procedure, the reason each step exists and a verification checklist. AGENTS.md names the directory as the canonical home for procedures and points at the relevant skill from each section; CLAUDE.md drops its duplicated pre-commit section for a pointer; .github/copilot-instructions.md, which still described the retired Nuxt/Prisma layout, is now a short pointer to AGENTS.md and the skills. docs/agent-skills.md lists the skills, when to use each and how to add one. (#688)

Retargeted repo copy from the completed Questify 2.0 rebuild to Questify 3.0: the README title and intro, AGENTS.md, the package.json description, .env.example, CODEOWNERS, docs/cicd.md, docs/schema.md and the Supabase config and rls.sql header comments. The issue templates now link the 3.0 epic (#689) as the parent-epic example and contact link. The README's v2 roadmap keeps its historical references to epic #269 and issues #243–#268. (#690)

docs/schema.md documents quests.last_activity_at, the six triggers that maintain it and revert_inactive_public_quests() under the quests section, lists the migration and adds the RLS-coverage bullet; docs/ops/scheduled-jobs.md adds the job to the running-jobs table and says what it decides, which column it reads and that it sends no email. (#693)