The migrations did not describe this database, and could not be made to.
Three findings, in the order they surfaced:
* 25 functions declared across six migrations — all recorded as applied — did
not exist. Their tables did. Two were reached by the app, so merchant tier
upgrades and post-trade reputation updates had been quietly dead. This only
came to light because a user hit "Could not find the function
public.upsert_user_profile(...)" while toggling a notification setting.
* admin_roles has three conflicting definitions across the set and production
matches none of them. 001 says (id, user_id, role, granted_by, granted_at),
COMBINED says (user_id, role, created_at), production has
(id, user_id, role, permissions, created_at, updated_at).
* Applied to an empty database, five migrations fail. The legacy 0NN filenames
sort before the 14-digit timestamps they depend on — "013" < "20241117054600"
— so 013 runs before the migration creating the table it alters. The set
could never have been replayed from scratch.
So it could not be tested, could not rebuild the database, and did not match what
was running. Widening or patching it would have been dressing up a history that
was already fiction.
The baseline is a pg_dump of the live public schema, which matches production by
construction. Privileges are included deliberately: the REVOKEs on
lock_escrow_internal, release_escrow_internal, refund_escrow_internal and
request_withdraw are the 20260725030000 hardening, and dropping them would hand
fund movement back to anon.
Verified step by step against production before committing:
- applies to an empty database cleanly, after three real obstacles were fixed
(extensions live in the `extensions` schema, supabase_admin membership,
platform-level ALTER DEFAULT PRIVILEGES that cannot apply outside Supabase)
- produces 83 tables / 39 functions / 222 indexes / 360 policies — identical
counts to production
- recorded as applied in supabase_migrations without touching the 37 existing
rows, then dry-run confirmed "up to date — no pending migrations", so the
next deploy will not try to replay it over live tables
- drift check now reports "every declared function is present"; the known-gaps
list drops from 22 entries to zero
The old files move to migrations/archive/ rather than being deleted — they are
the only record of why parts of this look the way they do. Their README says
plainly not to run them, and why.
CI now applies migrations to an empty Postgres on every PR. That check was
impossible while the old set was the starting point; it is the thing that stops
this class of drift from being discovered by a user again.
The function was never used and carried a real hole while sitting there.
`send` took an email address from the request body, did not check who was
calling, and returned the verification token in the response — with a
`// Remove in production` comment on that line. Anyone could mint a token for
another account's address and immediately use it. `Access-Control-Allow-Origin`
was `*`.
It was never reachable in practice: nothing calls `send`, so no token was ever
created, so `verify` could never succeed. The database confirms it —
`email_verification_tokens` has zero rows, ever.
Real email verification runs through Supabase Auth, not this function.
EmailVerification.tsx handles `type=signup` / `email_change` via the URL hash and
`getSession()`, and Dashboard.tsx resends through `auth.resend()`. Those paths
are untouched. The `?token=` branch that called this function was dead code
fed by a token nothing produced, so it goes with it.
Removing it also took the `verifying` state with it: `setVerifying` had no
remaining callers, so the spinner could never render and every `!verifying`
guard was permanently true — conditions that read as if the value could vary.
Fixing this properly instead of deleting it would mean building the email
delivery the function never had ("In production, send email via email service"),
duplicating what Supabase Auth already does correctly.
A user hit "Could not find the function public.upsert_user_profile(...) in the
schema cache" when toggling push notifications. The function was missing, and so
were the profiles columns it writes — while schema_migrations listed both 002 and
004 as applied.
That pattern turned out to be widespread. Comparing every function declared
across the migrations against the live database: 25 are missing, from six
migrations all recorded as applied. Their tables exist; only the function bodies
are absent. Those files carry "Run this in Supabase SQL Editor" headers, so they
were pasted in by hand before apply-migrations.sh existed and a run that stopped
partway was still recorded. The runner has skipped them ever since, which is why
this stayed invisible until it surfaced as a user-facing error.
apply-migrations.sh is not at fault: it wraps each migration and its tracking row
in one transaction with ON_ERROR_STOP, so it cannot half-record anything. It
inherited a dirty history.
Three of the 25 are actually reached by the app, so those are repaired here:
apply_for_tier_upgrade MerchantApplication.tsx:213 - tier upgrades were dead
check_tier_eligibility called by the above
update_p2p_reputation shared/lib/p2p-fiat.ts:803 - reputation never updated
This is forward-only repair, not a replay of the source files. Replaying them
would abort on their bare CREATE POLICY/INDEX/TRIGGER statements now that the
tables exist, and 20241117054602 in particular would overwrite 016's newer
cancel_expired_trades with its own older definition.
Verified before writing: every table the three functions touch is present, and
the whole migration was run inside BEGIN/ROLLBACK against production — three
functions created, signatures matching what the callers pass, then rolled back
leaving nothing behind.
The remaining 22 stay missing on purpose and are listed in known-schema-gaps.txt.
Nothing calls them, and several are trigger bodies whose triggers were never
created either, so creating them would switch on behaviour that has never run.
Also adds a drift check to apply-migrations.sh: after applying, it compares
declared functions against the database and flags anything missing that is not in
the known-gaps list. Being recorded as applied is not proof of having been
applied, and that gap should never again be discovered by a user. The check
already earned itself — it caught update_p2p_reputation, which my own first pass
had missed to a regex that dropped digits from function names.
upsert_user_profile and the profiles notification columns were applied directly
to the database from their existing migrations (002, 004), which are fully
idempotent; no new migration was needed for them.
two-factor-auth and notifications-manager run with the service role, so they
bypass RLS and whatever user id they act on is the entire authorisation
decision. Both read that id from the request body, and neither checked who was
calling. Access-Control-Allow-Origin was '*' on both.
Confirmed against the live endpoint: with only the public anon key and no user
session, from an arbitrary origin,
POST /functions/v1/two-factor-auth {"action":"check","userId":"<any-uuid>"}
-> 200 {"success":true,"enabled":false}
VERIFY_JWT is on and does not stop this, because the anon key is itself a valid
JWT and every browser has it. The same path reaches "disable", so anyone could
turn off another account's 2FA, and read, delete or forge their notifications.
The client already sends the signed-in user's token — functions.invoke puts the
session access token in the Authorization header — so the caller can simply be
read from it. Both functions now do that and ignore the body's userId. An
anon-key call carries no user, so it is rejected with 401.
create is the one action that may target someone else, because AdminPanel
notifies other users; that path now requires admin or super_admin in
admin_roles. CORS drops to the same allowlist the other functions here use.
No frontend change: the body may keep sending userId, it is simply not read.
Note: 2FA is not yet enforced at login — TwoFactorVerify is not referenced
anywhere and Login.tsx never checks it, so enrolling does not currently protect
sign-in. Tracked separately; this change is about the endpoints themselves.
The web app calls these three from TwoFactorSetup, TwoFactorVerify,
NotificationBell, AdminPanel and EmailVerification, but this repo never defined
them and they are not deployed to the self-hosted host the app points at. They
were left behind on the cloud project when everything else moved, so every one
of those calls has been hitting a function that is not there — two-factor auth,
notifications and email verification have all been silently dead.
Recovered from the deployed bundles on the cloud project and added here so the
source is tracked and CI deploys them alongside the rest. The tables they need
(two_factor_auth, notifications, activity_logs, profiles,
email_verification_tokens) already exist on the self-hosted database, so this is
a code move only.
Registry on the host now lists all three under this project.
NOTE: two-factor-auth and notifications-manager take userId straight from the
request body while running with the service role, and neither checks the caller.
That is how they run on the cloud project today, so this change does not make it
worse, but it does mean anyone can disable another account's 2FA or read and
delete their notifications. Fixing it properly needs an admin-role check
alongside the JWT, because AdminPanel legitimately creates notifications for
other users. Tracked separately rather than folded into a move.
Adds a deploy-supabase job to the quality gate so the self-hosted Supabase
side ships the same way as the frontend/backend instead of by hand:
- gated by the existing telegram-gate (owner approves once, in Telegram)
- runs on main-push in parallel with deploy-app, so edge functions, DB
migrations and the new frontend go live in one post-approval window
- rsyncs the edge-function dirs into the edge-runtime volume (hot-reloaded,
no restart; --no-delete preserves the telegram-* functions)
- applies pending migrations via deploy/apply-migrations.sh: idempotent and
transactional, tracked in supabase_migrations.schema_migrations, skips
non-versioned files (COMBINED_*), never half-records a failed migration
- post-deploy health check verifies the fund-custody guards took effect
(anon lost EXECUTE on the escrow/withdraw RPCs; both freeze triggers exist)
- Telegram alert on success/failure
Requires repo secrets SUPABASE_VPS_HOST/USER/SSH_KEY/PORT.
Replicates the Supabase RLS model on a real Postgres engine (pglite) with roles + an
auth.role() JWT-claim stub and proves the harden_financial_rls + escrow-REVOKE
migrations enforce: anon cannot read balances / payment-method PII or execute
release_escrow_internal; the SECURITY DEFINER read path + service_role still work. 6/6.
Together with the freeze-trigger test and the 34 app-auth tests, the fund-custody
security layer is now proven-by-execution (logic); staging deploy is the in-situ step.
Runs the exact freeze trigger from migration 20260725040000 against a real Postgres
engine (pglite) and proves: anon UPDATE of buyer_id/crypto_amount (the escrow-redirect
attack) is BLOCKED; status/proof-only stays allowed; service_role is exempt; the
attacker redirect never persists. 5/5 pass. Upgrades the custody-critical trigger from
code-reviewed to proven-by-execution. RLS/REVOKE role-enforcement still needs a
multi-role staging Postgres (pglite is single-user).
Consolidated gated-deploy runbook for the P2P security remediation. Contains no secret
values — only the names of the env vars/secrets the owner sets on staging/prod.
A bounded adversarial re-verification of the new fund-custody auth found two real
bypasses that the fix rounds missed; both fixed here, plus executable proof.
- BYPASS #1 (CRITICAL, external): p2p_fiat_trades/p2p_fiat_offers kept USING(true)
anon UPDATE, and confirm-payment / admin_resolve_dispute read the fund DESTINATION
(buyer_id), amount and token straight from those rows. An anon attacker could
`UPDATE p2p_fiat_trades SET buyer_id=<attacker>` so the honest seller's correctly-
signed release paid the attacker. Fix: BEFORE UPDATE triggers freeze the fund-routing
columns (offer_id, seller_id, buyer_id, crypto_amount, fiat_amount, price_per_unit,
escrow_locked_amount on trades; seller_id, token, amount_crypto on offers) for any
non-service-role writer — trigger-level, independent of RLS/grants; status/proof/chat
stay open. (Corrects the earlier triage that dismissed these tables as non-custodial.)
- BYPASS #2 (double-release TOCTOU): confirm-payment checked status then released
non-atomically, so two concurrent differently-nonced valid requests could both release
and drain other buyers' escrow. Fix: atomic compare-and-swap of payment_sent->completed
BEFORE the escrow move (loser gets 409; revert on release failure).
- Hardening: REVOKE EXECUTE on the dead legacy resolve_p2p_dispute mover from
PUBLIC/anon/authenticated.
- Proof: 34 executable tests (Deno) — identity-auth unit tests (sr25519 sign/verify,
challenge money-param binding, citizen/visa ownership, nonce single-use+concurrency,
freshness) + per-handler authorization-flow tests (bad-sig/cross-id/wrong-state/replay,
CAS double-release). All pass.
Round 2 — fixes the CRITICAL discovered during round 1 (as severe as the withdrawal
BOLA): lock_/release_/refund_escrow_internal had default PUBLIC EXECUTE and the client
called release_escrow_internal directly with the anon key, so anyone could drain any
victim's LOCKED balance.
- release_escrow_internal now reachable only via new signed confirm-payment edge
function: verifies seller wallet signature (raw + <Bytes>), asserts the wallet owns
the seller identity, derived user_id == trade.seller_id, trade == payment_sent,
single-use nonce, then release runs with the service role. confirmPaymentReceived
now invokes confirm-payment (no more anon rpc).
- lock_escrow_internal now behind new signed lock-escrow edge function (a caller can
only lock its own balance; stops griefing a victim's balance). Removed a zero-amount
no-op lock call.
- Migration 20260725030000: REVOKE EXECUTE on lock_/release_/refund_escrow_internal
from PUBLIC/anon/authenticated; GRANT to service_role only. refund has no direct
client caller (service-role + admin_resolve_dispute only).
- DEPLOY_RUNBOOK.md consolidates the ordered migrations, edge functions and secrets
for both security rounds. Migration 20260725030000 must ship WITH the two new edge
functions + frontend or offer-create/payment-release break.
Remaining (non-fund, Round 2+ follow-up): read RPCs still key on a non-secret user_id
(binding to a signed session would force a sign-prompt on every passive balance read —
deferred); p2p_fiat_offers/trades/messages retain USING(true) (status labels move no
funds now that all escrow movement is service-role-gated).
Critical/high audit remediation on the custodial P2P ledger. Auth model is
wallet-based; identity (citizen/visa) is cryptographically bound to a wallet
(People Chain tiki.citizenNft / active p2p_visa), which enables a correct fix.
- Withdrawal BOLA (CRITICAL): process-withdraw now requires a wallet-SIGNED
challenge; server verifies signature (raw + <Bytes> forms), asserts the signer
OWNS the identity, consumes a single-use nonce (replay), and derives user_id
server-side — the client-supplied user_id is ignored. process-withdrawal (batch)
now requires the service-role key (was: any bearer, incl. public anon key).
request_withdraw is REVOKEd from anon/authenticated + service-role guarded.
- Financial RLS (HIGH): drop the blanket USING(true) SELECT on user_internal_balances,
p2p_balance_transactions, p2p_deposit_withdraw_requests; lock p2p_user_payment_methods
(IBAN PII) + p2p_fiat_disputes UPDATE to service_role; legitimate reads move behind
scoped SECURITY DEFINER RPCs.
- Deposit integrity: verify-deposit now binds the on-chain sender to identity ownership
before crediting.
- Admin dispute (CRITICAL fund-logic): DisputeResolutionPanel relabeled trades without
moving escrow. New admin-signed resolve-dispute function + admin_resolve_dispute RPC
moves escrow (release/refund/split) atomically with correct accounting (avoids the
double-count in the legacy resolve_p2p_dispute). Client isAdmin documented as cosmetic.
DEPLOY RUNBOOK (gated; owner runs): 1) apply migrations 20260225/20260725* in order;
2) deploy edge functions process-withdraw, process-withdrawal, verify-deposit, resolve-dispute;
3) set edge secrets PEOPLE_RPC_ENDPOINT (+ optional ADMIN_WALLETS); 4) ship frontend.
Migrations + functions + frontend must go together or the app breaks.
KNOWN RESIDUAL (Round 2 — as severe as the withdrawal BOLA): release_/lock_/refund_
escrow_internal still have PUBLIC EXECUTE and the client calls release_escrow_internal
directly with the anon key from confirmPaymentReceived -> an anon caller can drain any
victim's LOCKED balance. Fix = a wallet-signed confirm-payment edge function (same
pattern as withdrawals) before revoking PUBLIC execute. Not yet fixed.
Fund-logic hardening (audit remediation):
- TransferModal: source asset id/decimals from the canonical MINTABLE_ASSETS
(wUSDT=1000, wDOT=1001, wETH=1002, wBTC=1003) instead of the wrong hardcoded
ids (BTC=3 was the deprecated "Old USDT", ETH=4, DOT=5). Route every non-native
asset through the Asset Hub api; only native HEZ uses the relay balances pallet.
Removes the latent wrong-asset / wrong-chain transfer.
- Replace BigInt(parseFloat(x)*10**dec) with string-based parseTokenInput in
TransferModal + 3 DEX init modals; BigInt(float) threw RangeError on common
fractional amounts (e.g. 100.3 HEZ), breaking sends and pool/bridge init.
- Presale.contribute now resolves a signer via getSigner and passes { signer }
to signAndSend (was signer-less -> broken for all users).
- presale.ts contribute/refund/claimVested now reject on dropped/invalid/usurped/
retracted tx states instead of hanging the UI forever.
Supabase JS client wraps non-2xx responses as generic FunctionsHttpError
("Edge Function returned a non-2xx status code"), hiding the real error.
Now reads the response body to show the actual error message.
Also adds migration to drop auth.users FK on p2p_withdrawal_limits
(already absent in production, added for migration completeness).
- Upgrade @pezkuwi/api 16.5.11 -> 16.5.36 in supabase edge functions
- Remove manual SS58-to-hex workaround, use native SS58 addresses
- Add kurdistan flag and Dijital Kurdistan images
- Add PezkuwiExplorer to web public assets
- Remove unused react-logo and telegram_welcome images
- Add *.bak to gitignore
Deno npm shim breaks SS58 decoding in @pezkuwi/api type registry,
causing PezspCoreCryptoAccountId32 to receive 48-byte SS58 strings
instead of 32-byte public keys. Added inline ss58ToHex decoder and
explicit hex-based nonce fetching to avoid all SS58 → AccountId32
conversions at the API level. Also adds P2P E2E test script (45/45).
Replace hard fail on unfinalized blocks with a retry loop that polls
every 6 seconds up to 60 seconds. The TX is already fully verified via
events at this point — we just wait for GRANDPA finality before crediting.
DepositModal was building transactions via relay chain API, and all three
edge functions (verify-deposit, process-withdraw, process-withdrawal) had
RPC endpoints hardcoded or defaulting to the relay chain. This caused
deposit verification to fail with "Transaction not yet finalized" and
created a chain mismatch with the withdrawal system which operates on
Asset Hub.
- DepositModal: use assetHubApi instead of api for transfer TX
- verify-deposit: RPC_HTTP/RPC_WS default to asset-hub-rpc (env override)
- process-withdraw: RPC_ENDPOINT default to asset-hub-rpc (env override)
- process-withdrawal: RPC_ENDPOINT default to asset-hub-rpc
All P2P table RLS policies used auth.uid() which returns NULL since users
authenticate via wallet, not Supabase Auth. Replace with open access policies.
Financial security maintained through SECURITY DEFINER RPC functions
(escrow lock/release/refund, process_deposit, request_withdraw).
Replace all supabase.auth.getUser() calls with P2PIdentityContext that
resolves identity from on-chain citizen NFT or off-chain visa system.
- Add identityToUUID() in shared/lib/identity.ts (UUID v5 from citizen/visa number)
- Add P2PIdentityContext with citizen NFT detection and visa fallback
- Add p2p_visa migration for off-chain visa issuance
- Refactor p2p-fiat.ts: all functions now accept userId parameter
- Fix all P2P components to use useP2PIdentity() instead of useAuth()
- Update verify-deposit edge function: walletToUUID -> identityToUUID
- Add P2PLayout with identity gate (wallet/citizen/visa checks)
- Wrap all P2P routes with P2PLayout in App.tsx
- Drop auth.users FK constraints for wallet-based authentication
- Fix deferrable unique constraint on blockchain_tx_hash (ON CONFLICT compat)
- Rewrite block search: HTTP RPC + blake2b instead of WS-only @pezkuwi/api
- Add blockNumber hint for faster verification of older transactions
- Normalize SS58/hex addresses via base58 for reliable comparison
- DepositModal captures approximate block number after tx submission
Removed JWT auth requirement from edge function. Now uses wallet address
from request body and verifies it matches the on-chain transaction sender.
This works with wallet-based auth instead of Supabase Auth.
- NetworkStats now queries Asset Hub and People Chain for collator data
instead of relay chain (where collatorSelection doesn't exist)
- Restrict Edge Functions CORS to app.pezkuwichain.io domains only
- Add Access-Control-Allow-Credentials header for secure cross-origin requests
- Update PLATFORM_ESCROW_ADDRESS in p2p-fiat.ts
- Update PLATFORM_WALLET in verify-deposit edge function
- Use controlled wallet for P2P deposits/withdrawals
- Add process-withdraw Edge Function for blockchain withdrawals
- Update verify-deposit Edge Function with @pezkuwi/api
- Add withdrawal limits (daily/monthly) and fee system
- Add hot wallet configuration with production address
- Add admin roles for dispute resolution
- Add COMBINED SQL migration with full P2P system
- Encrypt payment details with AES-256-GCM
- Prevent TX hash reuse with UNIQUE constraint
- Fixed TypeScript type assertion issues
- Updated imports from api-augment/substrate to api-augment/bizinikiwi
- Fixed imgConvert.mjs header and imports
- Added @ts-expect-error for runtime-converted types
- Fixed all @polkadot copyright headers to @pezkuwi
- Add p2p_platform_escrow table for tracking locked funds
- Implement accept_p2p_offer() with FOR UPDATE lock to prevent race conditions
- Add complete_p2p_trade() and cancel_p2p_trade() atomic functions
- Configure platform escrow wallet: 5DFwqK698vL4gXHEcanaewnAqhxJ2rjhAogpSTHw3iwGDwd3
- Update AdList to show user's own offers with Your Ad badge
- Remove unused getActiveOffers import (ESLint fix)
Added:
- useGovernance hook: Fetches live proposals and referenda from blockchain
- useTreasury hook: Fetches live treasury balance and proposals
- TreasuryOverview: Now uses real blockchain data with loading/error states
- Forum database schema: Admin announcements, categories, discussions, replies, reactions
Features:
- Live data badge shows active blockchain connection
- Automatic refresh every 30 seconds for treasury data
- Secure RLS policies for forum access control
- Admin announcements system with priority and expiry
- Forum reactions (upvote/downvote) support
Next: Complete forum UI with admin banner and moderation panel
