Free Stripe to Postgres Schema Tool
See the exact PostgreSQL table for 9 core Stripe objects — the curated schema Codeless Sync creates and keeps synced in production, next to the naive JSON dump so the timestamp, amount, and nesting gotchas are obvious. Free, instant, and 100% client-side.
Customer accounts: email, name, balance, delinquency
{
"id": "cus_QhT2mXfLbN8Kw3",
"object": "customer",
"address": {
"city": "London",
"country": "GB",
"line1": "1 Long Lane",
"postal_code": "EC1A 4PS"
},
"balance": -500,
"created": 1719878400,
"currency": "gbp",
"default_source": null,
"delinquent": false,
"description": "Acme Ltd primary billing contact",
"email": "jenny@example.com",
"invoice_settings": {
"custom_fields": null,
"default_payment_method": "pm_1PZkFy2eZvKYlo2C",
"footer": null
},
"livemode": false,
"metadata": {
"internal_ref": "ACME-042"
},
"name": "Jenny Rosen",
"phone": "+442071234567",
"preferred_locales": [
"en-GB"
],
"shipping": null,
"tax_exempt": "none"
}data JSONB catch-all with the full payload, sync metadata, and indexes.-- Codeless Sync: Stripe Customers Table -- This table stores your Stripe customer data -- Documentation: https://codelesssync.com/docs CREATE TABLE IF NOT EXISTS stripe_customers ( -- Core queryable fields id TEXT PRIMARY KEY, email TEXT, name TEXT, description TEXT, currency TEXT, balance INTEGER DEFAULT 0, delinquent BOOLEAN DEFAULT false, -- Fields data JSONB, -- Sync metadata livemode BOOLEAN NOT NULL, created TIMESTAMPTZ NOT NULL, synced_at TIMESTAMPTZ DEFAULT NOW() NOT NULL ); -- Performance indexes CREATE INDEX IF NOT EXISTS idx_stripe_customers_email ON stripe_customers(email); CREATE INDEX IF NOT EXISTS idx_stripe_customers_created ON stripe_customers(created); CREATE INDEX IF NOT EXISTS idx_stripe_customers_livemode ON stripe_customers(livemode); CREATE INDEX IF NOT EXISTS idx_stripe_customers_delinquent ON stripe_customers(delinquent);
Sourced verbatim from Codeless Sync's production templates — this is exactly the table CLS auto-creates when you sync customers.
-- One column per field of the Stripe customer JSON -- (the "just dump it" approach, shown for contrast) CREATE TABLE stripe_customers ( id TEXT, object TEXT, address JSONB, -- ⚠ nested object dumped into JSONB, not queryable columns balance BIGINT, -- ⚠ integer amount in minor units (pence/cents), not a decimal created BIGINT, -- ⚠ unix epoch seconds dumped as a number instead of TIMESTAMPTZ currency TEXT, default_source TEXT, -- ⚠ null in the example, so the real type cannot be inferred delinquent BOOLEAN, description TEXT, email TEXT, invoice_settings JSONB, -- ⚠ nested object dumped into JSONB, not queryable columns livemode BOOLEAN, metadata JSONB, -- ⚠ nested object dumped into JSONB, not queryable columns name TEXT, phone TEXT, preferred_locales JSONB, -- ⚠ array dumped into JSONB shipping TEXT, -- ⚠ null in the example, so the real type cannot be inferred tax_exempt TEXT ); -- ⚠ No PRIMARY KEY, no indexes, no synced_at sync metadata. -- ⚠ Silently drifts the moment Stripe adds, renames, or nests a field.
18 columns inferred from one payload shape — no primary key, no indexes, and it only stays correct until Stripe changes the payload.
How Stripe JSON Maps to PostgreSQL Types
The same handful of type-mapping decisions come up for every Stripe object. Here is how the naive dump handles them versus what a curated table does:
| Stripe JSON value | Naive mapping | Curated mapping (what CLS does) |
|---|---|---|
| "id": "cus_..." | TEXT column, no key | TEXT PRIMARY KEY — enables UPSERTs on re-sync |
| "created": 1719878400 | BIGINT of unix seconds | TIMESTAMPTZ — converted with to_timestamp(), so date queries just work |
| "amount": 2999 | BIGINT, easy to misread as 2,999 dollars | INTEGER kept in minor units (pence/cents) by design — divide by 100 for display (1,000 for three-decimal currencies) |
| "address": { ... } | A JSONB column per nested object | One curated data JSONB catch-all holding the full payload — nothing is ever lost |
| (not in the payload) | Nothing | Sync metadata: livemode, synced_at — plus indexes on the fields you actually filter by |
Using QuickBooks, Xero, or Paddle instead? Codeless Sync ships fixed, curated templates for those too — the same approach shown here (QuickBooks and Xero even use composite primary keys for multi-company and multi-tenant data).
Frequently Asked Questions
What is the best PostgreSQL schema for Stripe data?
A curated hybrid works best: a small set of typed, queryable columns for the fields you actually filter and join on (id, customer, status, amounts, timestamps), one JSONB column holding the full raw payload so nothing is lost, sync metadata (livemode, synced_at), and indexes on the common query fields. That is exactly the shape of the templates on this page.
A one-column-per-field dump looks complete but ages badly: it has no natural UPSERT key, stores timestamps and amounts in raw form, and breaks quietly whenever Stripe adds or nests a field.
Why not create one column for every field in the Stripe JSON?
Three reasons. First, the types are traps: Stripe sends timestamps as unix epoch seconds and money as integer minor units, so raw columns invite wrong queries. Second, nested objects (address, invoice_settings, recurring) do not flatten cleanly — you end up with a pile of JSONB columns anyway. Third, the Stripe API evolves; a table generated from one payload shape drifts out of date the moment a new field appears.
Keeping a few curated columns plus a full-payload JSONB catch-all gives you fast queries and forward compatibility at the same time.
Why are Stripe amounts integers instead of decimals?
Stripe represents all money in the smallest currency unit — pence, cents, and so on — so £29.99 arrives as 2999. This avoids floating-point rounding errors. Keep amounts as integers in PostgreSQL and divide by 100 for display (1,000 for three-decimal currencies like KWD; zero-decimal currencies like JPY are already whole units).
How do I convert Stripe's unix timestamps to PostgreSQL TIMESTAMPTZ?
Use to_timestamp(created) — it turns epoch seconds like 1719878400 into a proper timezone-aware timestamp. Storing the converted value in a TIMESTAMPTZ column (as the templates here do) means date filtering, sorting, and formatting work natively in SQL with no casting in every query.
Do I have to create these tables myself?
No. Codeless Sync auto-creates the exact tables shown on this page in one click when you set up a sync — and then keeps them filled and up to date on a schedule (hourly, daily, or custom cron on paid tiers). You can also copy the SQL and run it manually in Supabase, Neon, AWS RDS, Railway, or any PostgreSQL database if you prefer.
Want the full walkthrough on the type traps, the upsert key, and the JSONB catch-all pattern behind these schemas? Read: How to Design a PostgreSQL Schema for Stripe Data.
Don't just create the table — keep it synced
Codeless Sync auto-creates these exact tables and keeps them up to date on a schedule — full and incremental syncs, timestamps and amounts handled, schema drift covered. No webhooks, no cron jobs, no maintenance. Set up in about 5 minutes.