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 points
row above is the reward account's 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:
sub_type = 'CLEARING' — the MOVEUSD debit leaving the ledger account,
the settlement leg of the points debit, exactly as the card arm's clearing
leg settles its authorization;
sub_type = 'RETURN' — the credit back when that withdrawal is voided.
Those two are the ONLY read-model surface for ledger_account_withdrawal: the
table's other rows are the blockchain-withdrawal engine twins, already
represented by the withdrawal arm's own wd_ row. 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.
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 — customer.external_id.
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 points row above is the reward account's movement; the payout is a
ledger_account_withdrawal(redwd_) the claim workflow creates with the claim's id as itsidempotency_key, and it gets two more:sub_type = 'CLEARING'— the MOVEUSD debit leaving the ledger account, the settlement leg of the points debit, exactly as the card arm's clearing leg settles its authorization;sub_type = 'RETURN'— the credit back when that withdrawal is voided.Those two are the ONLY read-model surface for
ledger_account_withdrawal: the table's other rows are the blockchain-withdrawal engine twins, already represented by the withdrawal arm's ownwd_row. Both carry the reward ledger row'srew_id, not theredwd_one — the withdrawal is the claim's internal mechanism, not a transaction an operator looks up.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—customer.external_id.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.