Manual payment authority audit

Status: Active Last reviewed: 2026-09-07 Scope: Raw dues, payment evidence and manual session settlement; migrations 00629–00631.

Plan and research

This slice follows the venue pipeline with a focused audit of the financial write boundary. It read the existing adapters, table grants, RLS policies, triggers, confirmation and refund functions, then checked their actual local catalog. The implementation preserves the current trusted RSVP/hold, payer attestation, independent confirmation and refund acknowledgment workflows.

The repair uses existing tables and one same-signature confirmation RPC. It adds invoker guards and a narrowly authorized private deletion predicate. PostgreSQL's row locking and transaction isolation inform the parent-lock and post-wait validation boundaries. Separate agents authored and reviewed runtime SQL; another authored permanent fixtures, which root inspected and reran before integration.

Resulting behavior

• Raw dues creation starts unpaid, with no recognized balance or historical settlement stamps. Club, payer, amount, period and currency cannot be rewritten through ordinary updates. Omitted currency derives from the authoritative parent.
• Payers may record pending evidence with explicit null confirmation fields. A manager cannot bypass independent confirmation by inserting a confirmed row. Confirmation revalidates the stored receipt, actual parent, payer, recorder, currency and remaining amount under the parent lock; self-confirmation remains denied.
• Confirmed evidence and historical settlement stamps survive raw editing and deletion attempts. Existing note/method corrections and removal of pending-only evidence remain allowed. A status reset cannot authorize deleting old settlement evidence, including malformed legacy children hidden by receipt RLS.
• Participation-fee rows originate through the trusted RSVP hold path. Free-event cost splitting keeps the editor's chosen amount. Manual paid/waived actions require the existing independent manager or visible literal-host authority and use server actor/time stamps. They cannot forge payer, amount, provider evidence, submission or refund fields.
• Pending and acknowledged refund evidence cannot be overwritten by a raw paid/waived update. The existing payer acknowledgment remains available after club departure and resolves its refund notice.

The private deletion predicate returns only an authorized boolean, sees hidden historical evidence and locks the parent. Only the exact UUID signature receives authenticated execution for compiled RLS evaluation; private-schema USAGE remains absent. PUBLIC, anonymous and same-name overload grants remain violations.

Independent findings and validation

The first draft removed currency defaults, which would unnecessarily change the generated insert contract. Typed null defaults now preserve optional insertion while the guard derives a non-null parent currency. Actual postgres-meta 0.96.6 generation from the amended schema produced identical public type declarations; no client type change is needed.

Further testing found a legacy cascade hole: a malformed confirmed child hidden from its parent manager, combined with an already stale zero parent balance, could disappear with its parent. The private policy predicate now checks all child evidence without widening receipt visibility. The original failed probe and each earlier candidate remain retained.

The accepted runtime passed 167 assertions across 14 cases and 147 authenticated HTTP requests through local PostgREST 14.4. Independent review checked the source, actual function catalog and preserved helpers. Another 19 independently authored SQL controls passed unchanged. Concurrent confirmations used separate real HTTP connections; that run did not enforce a SQL blocking barrier and is not claimed as deterministic lock-race proof.

Seven permanent pgTAP suites pass 218 assertions in both author and independent fresh retained clones. They use actual authenticated roles and test valid writes, forged bindings, hidden legacy evidence, policy grants, trusted nested roles, refunds and departure. All test data and instrumentation roll back; global roles, schema grants, function definitions and template catalogs remain unchanged. Two existing suites receive narrow contract updates; five new suites are standalone.

The first broad SQL run exercised all 111 existing suites: 102 passed and 2,660 assertions were reached. Two failures were the intended historical-deletion and private-helper contract changes, now covered by the updated passing suites. Seven others reproduced unchanged without these migrations: absent managed Storage/cron schemas, missing canonical seed rows, and prior rehearsal fixture state. They remain recorded environment/fixture limitations; the full 111-suite run is not presented as passing.

Local source-bound evidence is under .audit/backend-pipelines-20260906/ in money-authority-draft/, money-authority-review/, money-authority-tests-draft/ and money-authority-integration/. Permanent tests live in supabase/tests/ and import no ignored artifacts.

Remaining work

The integration yarn check passed on unchanged runtime and test sources: 758 script tests (two existing skips), 393 Jest suites / 5,614 cases, workspace types and the repository's architecture/documentation gates. The source-bound receipt is money-authority-integration/repository-check-1/receipt.json. This page does not claim deployment or live financial acceptance. All rehearsals use synthetic local data; there were no hosted mutations, payment-provider calls or transfers. The backend handoff separates the completed repair from retained drafts and deferred work.

RLS-filtered updates can still return success with zero affected rows to existing void-returning adapters. Client acknowledgment is the next financial slice. Mutable idempotency fields also remain distinct from immutable action receipts: stale-intent/CAS, provider verification and callbacks, cancellation/expiry/capacity races, refund aggregation and an audited correction/void workflow need further work. The wider backend audit continues independently of this bounded repair.