Answer free-text analytics questions about Stripe data stored in this project's D1 database (stripe_sync) by generating SQL, running it via wrangler, and returning a results table (plus optional chart). Use this skill whenever the user asks anything analytical or aggregate about Stripe entities — revenue, products, prices, subscriptions, customers, segments, charges, payments, refunds, disputes, payouts, top-N rankings, time-of-purchase patterns, period-over-period comparisons, conversion rates, in-store vs online, MRR/ARR — even if they don't explicitly mention SQL, D1, or the database. Trigger on questions like "which products generate the most revenue", "top 10 products last month", "subscription mix by plan", "what hour of day do customers buy most", "in-store vs online revenue", "churned customers this quarter", "average order value by country".
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Answer free-text analytics questions about Stripe data stored in this project's D1 database (stripe_sync) by generating SQL, running it via wrangler, and returning a results table (plus optional chart). Use this skill whenever the user asks anything analytical or aggregate about Stripe entities — revenue, products, prices, subscriptions, customers, segments, charges, payments, refunds, disputes, payouts, top-N rankings, time-of-purchase patterns, period-over-period comparisons, conversion rates, in-store vs online, MRR/ARR — even if they don't explicitly mention SQL, D1, or the database. Trigger on questions like "which products generate the most revenue", "top 10 products last month", "subscription mix by plan", "what hour of day do customers buy most", "in-store vs online revenue", "churned customers this quarter", "average order value by country".
Stripe schema query
This project mirrors Stripe objects into a Cloudflare D1 database called stripe_sync (binding DB). The Drizzle schema is the source of truth at src/db/schema.ts — read it whenever you need exact column names. Columns evolve; do not guess.
Workflow
Pick the database — ask if unspecified. Local dev DB and prod DB return different results. If the user hasn't said, ask: "Local (your dev DB) or remote (production)?" Default to local if pressed.
Read src/db/schema.ts for the tables involved. JSON columns vs scalar columns matter — see Conventions below.
Translate the question into SQL, applying the conventions and the business-concept mappings.
Run it via wrangler (see Running queries).
Render results as a Markdown table.
Offer a chart when the answer is naturally visual (rankings, distributions, time series).
Read-only
Only emit queries. Never , , , , , , or . If the user asks to mutate data, decline and direct them to a Drizzle migration or themselves.
SELECT
INSERT
UPDATE
DELETE
DROP
ALTER
CREATE
ATTACH
wrangler d1 execute
Stripe data conventions in this schema
These bite if you forget. Apply them in every query.
Amounts are integer cents. Divide by 100.0 when presenting money. Currency is in a separate currency column (lowercase ISO).
Multi-currency is real. Don't sum amount across rows with different currencies. Either filter to one (WHERE currency = 'usd') or GROUP BY currency. If you're unsure, group by currency and report each.
Timestamps are unix seconds (integer).datetime(created, 'unixepoch') for readability. unixepoch('now', '-30 days') for relative ranges. strftime('%H', datetime(created, 'unixepoch')) for hour-of-day, '%w' for day-of-week (0=Sun…6=Sat). Times are UTC — adjust with '-5 hours' etc. if the user wants a local zone.
Status matters for revenue.charges rows exist for failed attempts. Real revenue: charges.status = 'succeeded' and subtract amount_refunded. Paid invoices: invoices.status = 'paid', use amount_paid. Completed Checkout: checkout_sessions.payment_status = 'paid'. Active subs: subscriptions.status = 'active'.
JSON columns (anything mode: 'json' in schema.ts) need json_extract(col, '$.path'). Common paths:
Payment channel from a charge: json_extract(payment_method_details, '$.type') → 'card', 'card_present', 'us_bank_account', …
Checkout path:checkout_session_line_items.price (a JSON blob containing the price object — extract product via json_extract(price, '$.product')).
MRR (recurring revenue):subscription_items × prices (filter recurring.interval = 'month', scale yearly by /12) for subscriptions.status = 'active'.
Customers / segments
"Segment" isn't a Stripe primitive. Common proxies — pick one and name it:
Country:json_extract(address, '$.country') on customers.
Subscribed plan/product: join via subscriptions → subscription_items → prices.product.
Tag in metadata:json_extract(metadata, '$.segment') (or whatever key the user uses).
If "segment" is unqualified, ask the user which they mean.
Identifying a "customer" when no Customer object exists
Guest-checkout-heavy datasets often have an empty customers table and no customer value on charges, payment_intents, or checkout_sessions. Before grouping by customer, check fill rates on each candidate field — don't assume customers.id works. In this project's data, the reliable email key is on the charge itself:
Avoid:checkout_sessions.customer_email — sparsely populated; misses ~95% of paid sessions and silently drops top spenders.
Canonical pattern:
COALESCE(charges.receipt_email, json_extract(charges.billing_details, '$.email')) AS customer_email
Diagnostic to run when a customer query returns empty or surprisingly small results:
SELECTSUM(CASEWHEN customer ISNOT NULLTHEN1END) AS has_customer_id,
SUM(CASEWHEN receipt_email ISNOT NULLTHEN1END) AS has_receipt_email,
SUM(CASEWHEN json_extract(billing_details, '$.email') ISNOT NULLTHEN1END) AS has_billing_email
FROM charges WHERE status ='succeeded';
Whichever column has the highest fill rate is your customer key. State the proxy out loud in your reply.
In-store vs online
Stripe Terminal transactions surface as payment_method_details.type = 'card_present' on charges. Everything else (card, link, us_bank_account, …) is online. Use this as the proxy and say so.
SELECTCASEWHEN json_extract(payment_method_details, '$.type') ='card_present'THEN'in_store'ELSE'online'ENDAS channel,
currency,
SUM(amount - amount_refunded) /100.0AS revenue
FROM charges
WHERE status ='succeeded'GROUPBY channel, currency;
Time-of-purchase patterns
Hour of day: GROUP BY strftime('%H', datetime(created, 'unixepoch')) on charges.
Day of week: '%w' (0=Sun … 6=Sat).
Month/year: '%Y-%m', '%Y'.
Refunds / disputes
Refund rate: SUM(amount_refunded) / SUM(amount) on charges (succeeded).
Dispute rate: count disputes / count charges over the same window.
Chargeback losses: SUM(amount) on disputes where status IN ('lost', 'charge_refunded').
Top-N
Almost every question reduces to ORDER BY <metric> DESC LIMIT N. Default LIMIT 10 for "top" questions, LIMIT 20 for general listings.
Worked examples
Q: Which products generate the most revenue?
SELECT p.name, p.id, inv.currency, SUM(ili.amount) /100.0AS revenue
FROM invoice_line_items ili
JOIN invoices inv ON inv.id = ili.invoice
JOIN prices pr ON pr.id = json_extract(ili.pricing, '$.price_details.price')
JOIN products p ON p.id = pr.product
WHERE inv.status ='paid'GROUPBY p.id, inv.currency
ORDERBY revenue DESC
LIMIT 20;
Q: Top 10 products last month
SELECT p.name, SUM(ili.amount) /100.0AS revenue
FROM invoice_line_items ili
JOIN invoices inv ON inv.id = ili.invoice
JOIN prices pr ON pr.id = json_extract(ili.pricing, '$.price_details.price')
JOIN products p ON p.id = pr.product
WHERE inv.status ='paid'AND inv.created >= unixepoch('now', 'start of month', '-1 month')
AND inv.created < unixepoch('now', 'start of month')
GROUPBY p.id
ORDERBY revenue DESC
LIMIT 10;
Q: What hour of day do customers buy most?
SELECT strftime('%H', datetime(created, 'unixepoch')) AS hour_utc,
COUNT(*) AS purchases,
SUM(amount) /100.0AS volume
FROM charges
WHERE status ='succeeded'GROUPBY hour_utc
ORDERBY hour_utc;
Q: Most popular subscription plans
SELECT p.name AS product, pr.id AS price_id, pr.nickname,
json_extract(pr.recurring, '$.interval') ASinterval,
COUNT(si.id) AS active_subscribers
FROM subscription_items si
JOIN subscriptions s ON s.id = si.subscription
JOIN prices pr ON pr.id = si.price
JOIN products p ON p.id = pr.product
WHERE s.status ='active'GROUPBY pr.id
ORDERBY active_subscribers DESC
LIMIT 20;
Running queries
# Local dev DB (Miniflare SQLite)
bunx wrangler d1 execute stripe_sync --local --json --command"SELECT …"# Production
bunx wrangler d1 execute stripe_sync --remote --json --command"SELECT …"
--json makes output parseable for formatting and charting. Without it you get a pretty ASCII table.
For multi-line queries, write to /tmp/query.sql and use --file /tmp/query.sql (cleaner than escaping a long string).
Quote shell-sensitive characters carefully when using --command.
When queries fail
"no such table" → table not backloaded yet, or wrong DB target. Confirm --local vs --remote with the user.
"no such column" → you guessed a name. Re-read src/db/schema.ts for that table.
JSON path returns NULL → verify path against a real row: SELECT pricing FROM invoice_line_items LIMIT 1. Stripe object shapes vary by Stripe API version.
Empty result with no error → check livemode, status, and the time window. Often the data is there but filtered out.
Output format
Reply with:
Approach (1–3 sentences): which tables and why. If you're using an approximation (e.g., proxying "segment" by country, or "in-store" by card_present), state it here plainly so the user can correct you.
SQL in a sql code block.
Results as a Markdown table — top ~20 rows; note if truncated. Format money as $1,234.56 (or appropriate symbol) and timestamps as YYYY-MM-DD HH:MM UTC.
Offer a chart when it would help: "Want a bar chart of these?" — bar for rankings, line for time series, doughnut for share-of-total.
Charts (on request)
Write a standalone HTML file using Chart.js via CDN, save to /tmp/chart-<topic>.html, and open it. Skeleton:
