The reward arm of the transaction search, as a view.
Same motivation as depositTransactionView (see schema/deposit.ts): the
inline 12-arm UNION serialises past RDS Proxy's 16KB parse limit and pins the
client session to a connection. Behind a view the runtime statement is tiny.
Lives here rather than in reward_ledger.ts (which owns the table the body
selects FROM) only because reward_claim.ts already imports
reward_ledger.ts for its FK: putting the view the other way round makes the
two modules cyclic, and since the barrel schema/index.ts re-exports
reward_claim first, rewardClaimTable would still be in its TDZ when the
view body evaluated — a module-load crash, not a type error.
Surfaces points earned / spent directly from reward_ledger (method =
REWARD). Double-entry: every reward movement writes a CUSTOMER row and an
offsetting IDENTITY row. Only the IDENTITY rows are surfaced — the CUSTOMER
rows are the offsetting half and would double-count.
transaction_type is already 'CREDIT' (points earned) / 'DEBIT' (points
spent / claimed), so it maps straight to the row type. Three shapes, all
from transaction_type + the optional reward_claim:
CREDIT -> a reward credit (reward account up)
DEBIT, no claim -> an account debit (reward account down)
DEBIT, claim with signature -> a reward claim (account down + MOVEUSD
paid on-chain to the wallet)
Three sub-arms, because a claim settles through its own record. The row
above carries sub_type = 'POINTS' and is the reward account's own off-chain
movement; the payout is a ledger_account_withdrawal (redwd_) the claim
workflow creates with the claim's id as its idempotency_key, and it gets two
more:
no sub_type — the MOVEUSD debit leaving the ledger account. Ordinary
money movement, which is why this is the bare one and POINTS is annotated;
sub_type = 'RETURN' — the credit back when that withdrawal is voided.
Both carry the reward ledger row's rew_ id, not the redwd_ one — the
withdrawal is the claim's internal mechanism, not a transaction an operator
looks up.
They are NOT the only way a claim settles, and they are the rarer way. When
the target address resolves to a ledger account CFX custodies,
claimRewardWorkflowActivities writes a ledger_account_transfer instead, and
the transfer arms carry that claim's debit AND its credit, each naming its own
account. Only a payout to a wallet we do not custody becomes a
ledger_account_withdrawal and reaches the two sub-arms here — 1 of 710
claims on prod as of 2026-09-20. A transfer-settled claim therefore shows one
row from this arm (the POINTS leg) and its money movement under redtfr_.
The points row is off-chain: credits and account debits never touch a wallet,
so its wallet_address is NULL and its pre_balance / post_balance stay
NULL — the row tracks the reward account, not a wallet, so on-chain balances
must not be computed against it. It still carries the claim's settlement
signature from reward_claim, which no other arm surfaces, so it stays
searchable by it. The two payout sub-arms DO resolve a wallet and its
balances: they move MOVEUSD off the ledger account's own wallet.
The caller-supplied filters live in
transaction-services/queries/reward.ts as predicates on this view's own
projected columns, because a view takes no parameters. They are plain
contract-column comparisons emitted by the shared
transactionScalarConditions, so what matters here is what each filtered
column projects:
customer_id — the reward ledger row's own customer_external_id copy.
signature — reward_claim.receipt_id gated on receipt_type = 'SOLANA_TRANSACTION_SIGNATURE'. The two writers of a claim receipt
(RewardClaimAdminService, reward-services/internal.ts) only ever set
receipt_id together with that one receipt_type, so the gate never masks
a row a signature search should have matched.
Changing what those two project silently changes the filters. The column list
must also stay identical to the sibling arms, or the UNION breaks.
securityInvoker is required, not cosmetic — every underlying table has RLS
enabled, and without it the view would run with the owner's row visibility and
silently bypass the querying role's policies.
The reward arm of the transaction search, as a view.
Same motivation as
depositTransactionView(seeschema/deposit.ts): the inline 12-arm UNION serialises past RDS Proxy's 16KB parse limit and pins the client session to a connection. Behind a view the runtime statement is tiny.Lives here rather than in
reward_ledger.ts(which owns the table the body selects FROM) only becausereward_claim.tsalready importsreward_ledger.tsfor its FK: putting the view the other way round makes the two modules cyclic, and since the barrelschema/index.tsre-exportsreward_claimfirst,rewardClaimTablewould still be in its TDZ when the view body evaluated — a module-load crash, not a type error.Surfaces points earned / spent directly from
reward_ledger(method =REWARD). Double-entry: every reward movement writes a CUSTOMER row and an offsetting IDENTITY row. Only the IDENTITY rows are surfaced — the CUSTOMER rows are the offsetting half and would double-count.transaction_typeis already'CREDIT'(points earned) /'DEBIT'(points spent / claimed), so it maps straight to the rowtype. Three shapes, all fromtransaction_type+ the optionalreward_claim:Three sub-arms, because a claim settles through its own record. The row above carries
sub_type = 'POINTS'and is the reward account's own off-chain movement; the payout is aledger_account_withdrawal(redwd_) the claim workflow creates with the claim's id as itsidempotency_key, and it gets two more:sub_type— the MOVEUSD debit leaving the ledger account. Ordinary money movement, which is why this is the bare one and POINTS is annotated;sub_type = 'RETURN'— the credit back when that withdrawal is voided.Both carry the reward ledger row's
rew_id, not theredwd_one — the withdrawal is the claim's internal mechanism, not a transaction an operator looks up.They are NOT the only way a claim settles, and they are the rarer way. When the target address resolves to a ledger account CFX custodies,
claimRewardWorkflowActivitieswrites aledger_account_transferinstead, and the transfer arms carry that claim's debit AND its credit, each naming its own account. Only a payout to a wallet we do not custody becomes aledger_account_withdrawaland reaches the two sub-arms here — 1 of 710 claims on prod as of 2026-09-20. A transfer-settled claim therefore shows one row from this arm (the POINTS leg) and its money movement underredtfr_.The points row is off-chain: credits and account debits never touch a wallet, so its
wallet_addressis NULL and itspre_balance/post_balancestay NULL — the row tracks the reward account, not a wallet, so on-chain balances must not be computed against it. It still carries the claim's settlement signature fromreward_claim, which no other arm surfaces, so it stays searchable by it. The two payout sub-arms DO resolve a wallet and its balances: they move MOVEUSD off the ledger account's own wallet.The caller-supplied filters live in
transaction-services/queries/reward.tsas predicates on this view's own projected columns, because a view takes no parameters. They are plain contract-column comparisons emitted by the sharedtransactionScalarConditions, so what matters here is what each filtered column projects:customer_id— the reward ledger row's owncustomer_external_idcopy.signature—reward_claim.receipt_idgated onreceipt_type = 'SOLANA_TRANSACTION_SIGNATURE'. The two writers of a claim receipt (RewardClaimAdminService,reward-services/internal.ts) only ever setreceipt_idtogether with that onereceipt_type, so the gate never masks a row a signature search should have matched.Changing what those two project silently changes the filters. The column list must also stay identical to the sibling arms, or the UNION breaks.
securityInvokeris required, not cosmetic — every underlying table has RLS enabled, and without it the view would run with the owner's row visibility and silently bypass the querying role's policies.