# Customer access controls

Migration `202609290005_customer_access.sql` adds independently configurable trading and withdrawal permissions plus `active`, `suspended`, and `closed` account states. `closed` is a reversible logical closure. It never deletes an Auth user, profile, balance, document, order, or activity record.

## Contract

`marketplace_my_access()` reads the signed-in subject only and remains available with a restricted JWT so the application can display the reason and clear its session. Its flat response is:

- `user_id`, `status`, `trading_enabled`, `withdrawals_enabled`, `version` (initially 0)
- `reason`, `updated_at`, `updated_by`
- `login_enabled`, `effective_trading`, `effective_withdrawals` (status-aware derived values)

`marketplace_admin_customer_access(p_user)` and `marketplace_admin_customer_access_update(p_user,p_version,p_status,p_trading_enabled,p_withdrawals_enabled,p_reason)` require the existing owner + AAL2 role. Both return `{access, protected, history}`. History entries contain `id` (string), `version`, `before`, `after`, `reason`, `actor_id`, and `created_at`, newest first. The update requires the exact current version, an actual state/permission change, and a reason of 5–1000 characters. The acting administrator and every account in the staff-members table are protected from modification.

Errors: `not_authorized`, `customer_not_found`, `access_protected_account`, `invalid_access_change`, `access_version_conflict`, `access_no_change`. Customer guards use `account_suspended`, `account_closed`, `trading_disabled`, and `withdrawals_disabled`.

## Enforcement and retention

Each existing and newly created Auth user receives a retained access row with a restrictive foreign key. Auth deletion therefore cannot cascade into customer records. Profiles, account records, document history, customer activity, reviews, and access history have delete/truncate guards; append-only activity/history also rejects updates. These controls protect against application/service operations, not a database owner deliberately dropping schema protections or destroying a database/backups.

Suspending or closing an account sets an Auth `banned_until` far in the future. Reactivation restores the pre-existing ban if this feature still owns the current ban; it does not clear a later ban placed by another trusted system. Existing JWTs cannot call the guarded customer RPCs or read/write protected storage. The access-status RPC remains available. Administrator access to the subject's profile, reviews, documents, and notifications is not blocked by the subject's restriction.

Public customer RPCs retain their existing signatures and business logic behind private copies with no client execute grants. These wrappers run the current-account guard first. They are installed idempotently; a future migration that replaces one of those public RPC bodies must retain the guard or explicitly rewrap the changed body. Service-only wallet challenge/attempt/complete and new-deposit initiation are also wrapped, checking their trusted `p_user` argument rather than the service JWT subject. This closes direct Edge Function calls that bypass the application routes; their service-only execute grants are preserved. Deposit bind/credit remain available to record already initiated and provider-confirmed incoming funds. Newly introduced customer RPCs must also call `marketplace_private.customer_access_assert(auth.uid())`.

Order insertion/filling and purchase/sale transaction writes check trading permission; withdrawal creation/confirmation checks withdrawal permission, including service-role settlement writes. Shared row locks serialize these checks with administrator access changes. Cancelling/rejecting a pending operation remains possible after restriction. Incoming deposits remain recordable so already received funds are never lost from accounting. Future payment/settlement integrations must check this permission before any irreversible external payout, and must not regard a UI-disabled button as authorization.

A change, the Auth ban, immutable history, the customer's activity timeline, and the user inbox notification are committed atomically. Missing notification infrastructure fails the entire operation. Selecting controls in the admin UI does not itself call this mutation.

## Validation

`node --test tests/customer-access-db.test.mjs` uses local PostgreSQL-compatible fixtures with synthetic customers. It checks owner/AAL2 and staff protection, optimistic locking/no-op handling, exact state/history/notification outputs, blocked RPC/storage access with existing JWTs, Auth-ban preservation, reversible closure without record deletion, separate trade/withdraw restrictions at settlement, migration replay, rollback on notification failure, direct service-role wallet/deposit bypass attempts, and exactly-once incoming deposit credit while restricted. No real account restriction, approval, trade, withdrawal, or external provider call is performed by these tests.

Deployment: applied to project `wlsxzyeernwtsetzyltq` on 2026-09-29 in the Supabase SQL editor; observed `Success. No rows returned`. The migration adds active/default access records without suspending or closing any existing account. No real customer restriction was exercised during verification.

Live read-only post-deploy check: all Auth accounts retained = true, restricted accounts = 0, access decisions = 0, guarded function copies = 24. The customer profile remains accessible.