> ## Documentation Index
> Fetch the complete documentation index at: https://docs.straddle.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Investigate payment data

> Find local payment records with full-text search and answer aggregate questions with SQL.

Answer a specific question about your payments using a refreshed local dataset. Use search to locate records by text, then use SQL for counts, amounts, or comparisons.

## Before you begin

* Install the [Straddle CLI](/developer-tools/cli/install) and configure an API key for the resources you want to inspect.
* Confirm your [environment, integration type, and acting account](/developer-tools/cli/auth-context).
* Decide which records the question needs. The examples use payments; you can also synchronize customers, paykeys, or funding events for related questions.
* Keep the same context and database throughout. Add the same `--db PATH` to each `sync`, `search`, and `sql` command if you use a custom database.

## Run the workflow

<Steps>
  <Step title="Confirm the account and environment">
    ```bash theme={null}
    straddle agent-context --pretty
    ```

    Check `runtime_context.environment`, `runtime_context.integration_type`, and `runtime_context.acting_account`. For SaaS or marketplace, select the account if needed and check again:

    ```bash theme={null}
    straddle use-account ACCOUNT_ID
    straddle agent-context --pretty
    ```
  </Step>

  <Step title="Refresh the records for your question">
    ```bash theme={null}
    straddle sync --resources payments --full --max-pages 0 --strict --json
    ```

    Confirm a `sync_complete` event and a final `sync_summary` with `success: 1`, `warned: 0`, and `errored: 0`. Review any `sync_warning` or `sync_anomaly`, including findings that accompany exit code zero, before treating the dataset as complete.

    To include related records, change the resource list to `payments,customers,paykeys,funding-events` and confirm completion for all four resources. In a marketplace integration, customer and paykey reads cover platform-owned records even when an acting account is selected. See [local data](/developer-tools/cli/local-data) for synchronization and storage details.
  </Step>

  <Step title="Find payments by text">
    Replace `SEARCH_TERM` with a word from the record you want to find:

    ```bash theme={null}
    straddle search "SEARCH_TERM" --type payments --data-source local --limit 20 --json
    ```

    Search uses the local full-text index. The result contains a `results` array of matching records and `meta` describing the data source. The limit bounds the returned matches; use SQL for a complete count.
  </Step>

  <Step title="Summarize payments by status">
    ```bash theme={null}
    straddle sql "SELECT json_extract(data, '$.status') AS status, COUNT(*) AS payment_count, SUM(json_extract(data, '$.amount')) AS amount_cents FROM payments GROUP BY json_extract(data, '$.status') ORDER BY payment_count DESC" --json
    ```

    This returns one row for each stored status, with its payment count and total amount in cents. It includes all stored dates in the selected context.

    To inspect the largest stored payments:

    ```bash theme={null}
    straddle sql "SELECT id, json_extract(data, '$.payment_type') AS payment_type, json_extract(data, '$.status') AS status, json_extract(data, '$.amount') AS amount_cents FROM payments ORDER BY amount_cents DESC LIMIT 10" --json
    ```

    SQL runs a single read-only `SELECT` or `WITH` statement against the selected context. The original resource body is in the `data` JSON column.
  </Step>

  <Step title="Verify a record before acting">
    Use the payment type and ID from your results to retrieve the current details:

    ```bash theme={null}
    straddle charges get PAYMENT_ID --data-source live --no-cache --json
    ```

    For a payout:

    ```bash theme={null}
    straddle payouts get PAYMENT_ID --data-source live --no-cache --json
    ```
  </Step>
</Steps>

## Interpret the results

Search returns matching resource objects under `results`; `meta.source` is `local` for the explicit local search above. SQL returns an array of row objects, with keys matching your selected column names or aliases. An amount of `12500` cents represents \$125.00.

Both methods reflect the records stored for the selected API origin and acting account. A missing search match can result from the search terms, result limit, sync coverage, or account selection. Use a known record ID and a current API read to investigate a discrepancy, then refresh the local data as needed.

When comparing totals, preserve the query's date, type, and status conditions. The example status summary adds charges and payouts together within each status; separate `payment_type` values when the direction matters to your question.

## Complete the investigation

Save the query or search terms, relevant record IDs, environment, acting account, and refresh time with your findings. Continue with [reconciliation](/playbooks/reconcile-payments) for funding questions, [payment progress](/playbooks/payment-progress) for lifecycle questions, or [failure and return investigation](/playbooks/returns) for unsuccessful payments.


## Related topics

- [Payment operations playbooks](/playbooks/overview.md)
- [Investigate failed and returned payments](/playbooks/returns.md)
- [Build your Straddle integration with AI](/developer-tools/overview.md)
- [Reporting and exporting payment data](/guides/payments/reports.md)
- [Synchronize and query local data](/developer-tools/cli/local-data.md)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.