idx_escrows_beneficiary_account and idx_escrows_unlock_due are dead indices — no code lets a beneficiary/arbiter discover their escrows

[abayomicornelius]

Overview

migrations/008_add_escrows.sql creates two indices that anticipate query patterns the actual EscrowService/routes/escrows.rs code never performs:

CREATE INDEX IF NOT EXISTS idx_escrows_beneficiary_account
    ON escrows (beneficiary_account);

-- Keeper query: escrows past their unlock time still awaiting release.
CREATE INDEX IF NOT EXISTS idx_escrows_unlock_due
    ON escrows (unlock_time)
    WHERE status = 'active';

idx_escrows_beneficiary_account: EscrowService exposes exactly one list method, list_for_user (src/services/escrow.rs:104-113), which filters WHERE depositor_id = $1 — i.e. by the internal StellarSend user who created the escrow. There is no method anywhere that queries escrows by beneficiary_account or arbiter_account. get_escrow (routes/escrows.rs:52-64) does support fetching a single escrow by its UUID regardless of caller role (its own doc comment explains this is deliberate: "the beneficiary and arbiter ... also need to be able to check escrow status"), but that only helps if the beneficiary/arbiter already knows the escrow's UUID out-of-band. A beneficiary or arbiter — who is frequently not the StellarSend account holder that created the escrow, and may not even have a StellarSend account at all — has no API-level way to discover which escrows they're party to. The only way they learn an escrow exists is if the depositor separately communicates the UUID to them through some out-of-band channel the API itself provides no support for. idx_escrows_beneficiary_account exists purely to support a query that was apparently planned but never built.

idx_escrows_unlock_due: its own comment says "Keeper query: escrows past their unlock time still awaiting release" — but escrow release/refund is explicitly not keeper-executed. This file's own extensive doc comments explain why: the on-chain release_escrow/refund_escrow contract functions call caller.require_auth(), so "a keeper (a service account with its own key) cannot sign on behalf of any of those parties" (escrow.rs:176-184) — unlike subscription execution, which uses a pre-granted allowance a keeper can invoke on its own. Confirmed: run_keeper_loop in main.rs only ever calls SubscriptionService::run_due_executions; there is no equivalent escrow-sweeping loop, and no code anywhere queries escrows by unlock_time. This index was written for a keeper-driven escrow feature that, per the very same file's own design rationale, cannot exist in the current on-chain authorization model — it's indexing a query the codebase has already explained why it will never run.

Together these two indices are pure write overhead (every INSERT/UPDATE on escrows maintains them) for query patterns that don't exist, and they're a real signal of a genuine product gap: there's no way today for a beneficiary/arbiter to discover their pending escrows, and no reminder/notification mechanism for an escrow that's sat past unlock_time without being released or refunded.

Requirements

• Either implement the missing discovery feature — a list_by_beneficiary/list_by_arbiter method on EscrowService and a corresponding route (e.g. GET /api/escrows?role=beneficiary&account=G...), publicly accessible or lightly authenticated given beneficiaries/arbiters may not hold StellarSend accounts — which would make idx_escrows_beneficiary_account a real, used index; or drop the index if beneficiary/arbiter discovery is out of scope.
• Either implement (or explicitly scope out and remove) a due-escrow notification/reminder mechanism that would make idx_escrows_unlock_due meaningful — note this is not the same as keeper-executing the release/refund itself (which this file correctly explains isn't possible), just surfacing "this escrow is past unlock_time and still active" for a client/notification system to act on.
• Whichever direction is chosen for each index, make the decision explicit (code + a migration if removing, or a shipped feature if keeping) rather than leaving orphaned schema that misleads future readers about what the API actually supports.

Acceptance Criteria

• idx_escrows_beneficiary_account is either backed by a real, callable query path, or removed via a new migration.
• idx_escrows_unlock_due is either backed by a real, callable query path (a "due for release" listing, not keeper execution), or removed via a new migration, and its comment is corrected regardless (it currently describes a keeper query pattern this codebase's own design explicitly rules out).
• If beneficiary/arbiter discovery is implemented: a test confirms a beneficiary account with no corresponding users row (i.e. not a registered StellarSend user) can still discover escrows naming them as beneficiary through the new endpoint, mirroring the intentionally-permissive design of get_escrow.

Additional Notes

Edge cases

• If beneficiary/arbiter discovery is implemented without authentication (since beneficiaries may not have accounts), consider whether exposing "list all escrows for Stellar address X" publicly leaks anything sensitive beyond what get_escrow's already-public-to-any-authed-user single-record lookup exposes — likely fine given the existing design's stance, but worth a deliberate sentence in the PR description either way.

Testing strategy

• A straightforward integration test seeding a few escrows with varying beneficiary_account/arbiter_account values and asserting the new listing method returns exactly the matching subset.

Cross-references

• Distinct from EscrowService::list_for_user and SubscriptionService::list_for_user have no pagination, unlike transactions #27 ("EscrowService::list_for_user ... has no pagination") — EscrowService::list_for_user and SubscriptionService::list_for_user have no pagination, unlike transactions #27 is about the existing depositor-scoped list endpoint lacking pagination; this issue is about entirely missing list endpoints for the other two parties to an escrow (beneficiary, arbiter), a different gap in different code paths that happen to share the word "list."