Best Practices

📘

TLDR

When using Prism:

• Start from templates — they cover the most common questions out of the box
• Let the AI generate SQL first, then review and refine it before saving
• Always filter by date and status; never query unbounded tables on large date ranges
• Account for UTC when writing date filters — PayMongo timestamps are stored in UTC, not PHT
• Use schedules for recurring reports instead of running queries manually each week
• Save and name queries descriptively — treat them as living documents for your team

Prism is most effective when it's used as a deliberate reporting layer, not just an ad hoc query tool. These best practices cover the five areas where teams get the most value: getting started, writing accurate queries, managing and sharing work, scheduling exports, and understanding performance limits.

Getting Started

Start with templates, not a blank editor

Prism includes pre-built templates for the most common reporting needs — revenue by period, payment method breakdowns, refund summaries, payout reconciliation, and more. Before writing a query from scratch, check whether a template already covers your question. Templates are production-tested, and it's almost always faster to adapt one than to build from nothing.

To browse templates: open Prism, select Templates & Examples, and search by keyword.

Use the natural language feature as a first draft

If you're not comfortable writing SQL, type your question in plain language and let Prism generate the query for you. Treat the output as a starting draft — review the generated SQL before saving or scheduling it, and confirm that the filters and columns match your intent. The AI is a good starting point, but the responsibility for result accuracy is yours.

Explore the schema browser before writing joins

Prism exposes a schema browser showing all available tables and their columns. Spend a few minutes browsing the relevant tables before writing any query that spans multiple tables. Understanding which fields connect tables (e.g., payment_intent_id, payout_id) prevents incorrect joins and saves debugging time later.

Key tables and what they contain:

Writing Accurate Queries

Always filter by date range

Prism queries run against your full historical dataset. Running an unbounded query — one without a WHERE clause on a date column — scans every record you have and will hit performance limits or time out on large accounts. Always include a date filter, even for exploratory queries.

-- ✅ Do this
SELECT *
FROM payments
WHERE created_at >= '2025-01-01'
  AND created_at < '2025-02-01'
 
-- 🚫 Not this
SELECT *
FROM payments

Account for UTC when filtering by date

All timestamps in Prism are stored in UTC. If your team reports in Philippine Time (PHT, UTC+8), you need to offset your date filters to avoid missing or double-counting transactions at the boundaries of a reporting period.

-- To query "today in PHT" (UTC+8), shift the UTC range accordingly
SELECT *
FROM payments
WHERE created_at >= (CURRENT_DATE - INTERVAL '0 day') - INTERVAL '8 hours'
  AND created_at <  (CURRENT_DATE + INTERVAL '1 day') - INTERVAL '8 hours'
  AND status = 'paid'

When in doubt, check your results against the dashboard's daily totals — the dashboard also displays in PHT by default, making it a useful sanity check.

Filter by status, not just date

A date filter alone will include payments in all statuses — paid, failed, awaiting_payment_method, processing, and others. For revenue or reconciliation reports, always add a status filter to avoid counting incomplete or failed transactions.

-- For revenue reporting
WHERE status = 'paid'
 
-- For dispute/chargeback analysis
WHERE status = 'disputed'
 
-- For failure rate analysis
WHERE status = 'failed'

Avoid SELECT * in saved queries

SELECT * retrieves every column in a table, including many you don't need. This slows down queries and makes the output harder to read. In saved and scheduled queries, always specify the columns you actually use. This also makes the query easier for teammates to understand at a glance.

-- ✅ Explicit and readable
SELECT
    id,
    amount,
    currency,
    status,
    payment_method_type,
    created_at
FROM payments
 
-- 🚫 Retrieves everything, harder to maintain
SELECT *
FROM payments

Use CTEs for multi-step logic

If your query involves multiple steps — for example, aggregating payments first and then joining to payout data — use CTEs (Common Table Expressions) rather than deeply nested subqueries. CTEs are easier to read, debug, and share with teammates.

WITH paid_payments AS (
    SELECT
        payout_id,
        SUM(amount) AS total_collected
    FROM payments
    WHERE status = 'paid'
      AND created_at >= '2025-01-01'
    GROUP BY payout_id
)
 
SELECT
    p.id AS payout_id,
    p.arrival_date,
    pp.total_collected
FROM payouts p
LEFT JOIN paid_payments pp ON pp.payout_id = p.id

Managing and Sharing Queries

Use descriptive query names

Treat saved queries as shared documentation. Name them so a teammate who didn't write the query can understand its purpose without reading the SQL.

Save your team's recurring reports

If a teammate asks you to pull the same report more than once, save it. Prism queries are shareable — saving them in a team-accessible location means anyone can run or adapt the report without requesting it again.

Version control significant queries externally

For queries that feed into finance reconciliation, compliance reporting, or executive dashboards, keep a copy of the SQL outside of Prism — in a shared Google Doc, Notion page, or version-controlled file. Prism doesn't have version history; if a saved query is edited or deleted, there's no rollback.

Scheduling and Exports

Schedule recurring reports instead of running them manually

If you're running the same query every week or month — revenue summaries, payout reconciliation, refund rates — set up a scheduled export. Scheduled queries run automatically and deliver results to your email or a webhook endpoint, removing the manual step entirely.

To set up a schedule: save the query, open it, and click Schedule in the query settings.

Use CSV for spreadsheet-based workflows

Export to CSV when results will be opened in Excel or Google Sheets, shared with a finance team, or loaded into a reporting template. CSV is the most compatible format for downstream non-technical use.

Use JSON for programmatic consumption

Export to JSON when results will be consumed by a script, loaded into a database, or processed by an internal tool. JSON preserves data types more reliably than CSV for programmatic use.

Test scheduled queries before activating them

Before activating a schedule on a high-stakes report, run the query manually for the expected date range and verify the output. A date filter off by one day or a missing status condition in an automated report can silently produce incorrect numbers for weeks.

Performance and Limits

Prism is read-only — queries cannot modify data

All Prism queries run in a read-only context. There is no risk of accidentally modifying, deleting, or writing transaction data through Prism. You can explore freely without concern for side effects.

Break large date ranges into smaller chunks

If you need to analyze data over a long historical period — say, 12 months of payments — consider running the query in monthly segments rather than a single query covering the full year. Smaller date ranges return faster and are less likely to hit interactive query limits.

Use scheduled queries for large result sets

Interactive queries in Prism return up to a capped row count. If your query needs to return more rows than the interactive limit allows, schedule it instead. Scheduled queries run asynchronously and can handle larger result sets, delivering the full output to your email or webhook.

If a query is consistently slow, check for missing filters

Slow queries are almost always missing an index-friendly filter — usually a date column or a status column. Review the WHERE clause first before assuming the data volume is the issue.