How to Design a PostgreSQL Schema for Stripe Data

Once you have decided to get your Stripe data into PostgreSQL, the first practical question is what the tables should look like. The Stripe API returns tidy, self-describing JSON, so the obvious move is to create one column per field and call it done.

That obvious schema is a trap. The types in a Stripe payload lie to you (timestamps are integers, money is integers, half the interesting fields are nested objects), the payload shape changes as Stripe evolves its API, and a table built from one example payload gives you no plan for what happens on the second sync when the same customer comes back with new values.

This post walks through the three type traps, why the flat one-column-per-field dump ages badly, and the schema pattern that holds up in production. If you want to see finished schemas first, the free Stripe to Postgres Schema tool shows the exact production table for 9 core Stripe objects, side by side with the naive dump so the differences are obvious.

The three type traps in Stripe JSON

Timestamps are unix epoch integers

Every timestamp Stripe sends is epoch seconds: "created": 1782950400. Dump that into a BIGINT column and every date query you ever write needs a conversion wrapped around it. Convert once at insert time instead, with PostgreSQL's to_timestamp(), and store a TIMESTAMPTZ:

-- Once, at insert time:
to_timestamp(1782950400)  -- 2026-07-02 00:00:00+00

-- Forever after, plain SQL just works:
SELECT count(*) FROM stripe_customers
WHERE created >= date_trunc('month', now());

Amounts are integers in minor units

Stripe represents money in the smallest currency unit to avoid floating-point rounding errors, so £29.99 arrives as 2999. Store it as an integer and divide by 100 for display. One exception is documented in Stripe's currency docs: zero-decimal currencies like JPY are already whole units. Stripe also supports a handful of three-decimal currencies like KWD, where the minor unit is 1/1000, so the divisor there is 1000.

The trap is not the storage, it is the reading. An amount column holding 2999 looks like two thousand pounds to anyone querying it cold. Keep amounts as integers (never REAL or FLOAT), and make the minor-units convention loud in your column comments and dashboards.

Nested objects do not flatten

address, invoice_settings, recurring, metadata: a good chunk of every Stripe payload is nested. Flattening them into columns explodes the column count, and dumping each one into its own JSONB column just gives you a pile of fragments. Neither gets you queryable data.

Why one column per JSON field ages badly

Beyond the type traps, the flat dump has three structural problems.

No upsert key. A schema generated from a payload gives you columns, not decisions. The decision that matters most is the primary key, because syncing is repetitive by nature: the same customer comes back on every sync with updated values. Without a primary key there is nothing to ON CONFLICT against, so re-syncs either duplicate rows or fail.

No indexes. The fields you will actually filter on (customer, status, created) get no indexes, so queries crawl as the table grows.

Schema drift. The Stripe API changes shape. In API version 2025-03-31 (basil), Stripe removed current_period_start and current_period_end from the Subscription object (they moved to subscription items), removed the paid boolean from invoices, and restructured invoice line items. A table generated from one payload is only correct for that payload, on that API version, on that day. When Stripe adds or moves a field, the flat dump breaks silently: new fields vanish, and inserts written against the old shape start failing.

The PostgreSQL schema pattern that works: curated columns plus a JSONB catch-all

Here is the actual production table for Stripe customers (this is the template Codeless Sync auto-creates, shown verbatim in the schema tool):

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,

  -- Full payload
  data JSONB,

  -- Sync metadata
  livemode BOOLEAN NOT NULL,
  created TIMESTAMPTZ NOT NULL,
  synced_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

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);

Four deliberate choices are doing the work:

A short set of typed, queryable columns. Only the fields you filter, join, and report on. Timestamps are already TIMESTAMPTZ, amounts are integers, and everything has a real type instead of a guess.
A data JSONB catch-all holding the full payload. Nothing is ever lost, and this is the drift insurance: when Stripe adds a field next quarter, it lands inside data instead of breaking your inserts. You can query it any time with data->>'phone'.
Sync metadata. livemode keeps test and live records separable, and synced_at tells you how fresh every row is, which is the first thing you check when a number looks off.
Indexes on the fields you actually query. Email lookups, date ranges, and delinquency filters stay fast at any table size.

Pick the upsert key before the first insert

Stripe IDs (cus_..., in_..., sub_...) are stable, unique text values, which makes them the natural primary key. With id TEXT PRIMARY KEY in place, re-syncing is one idempotent statement:

INSERT INTO stripe_customers (id, email, name, currency, balance, delinquent, data, livemode, created)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, to_timestamp($9))
ON CONFLICT (id) DO UPDATE SET
  email      = EXCLUDED.email,
  name       = EXCLUDED.name,
  balance    = EXCLUDED.balance,
  delinquent = EXCLUDED.delinquent,
  data       = EXCLUDED.data,
  synced_at  = NOW();

Run it once or a thousand times, the table stays correct. This is the single decision the flat dump never makes for you, and it is why re-syncs against naive schemas end in duplicate rows.

For joins, the reference fields do the same job across tables: stripe_invoices.customer points at stripe_customers.id, stripe_subscriptions.customer likewise. One table per Stripe object, joined on IDs, and metrics queries like the ones in how to calculate MRR, churn, and LTV in PostgreSQL fall out naturally.

Schema drift is the part nobody budgets for

Designing the schema is a one-off. Keeping it correct is not, and this is the cost that surprises teams months later.

Stripe versions its API and moves fields between objects, as the basil changes above show. Your own usage changes too: the day you need subscription analytics, you need the subscriptions and subscription items tables you did not create on day one. And every change lands on you as migration work: alter the table, backfill the column, update the insert code, re-test the sync. It is exactly the kind of recurring, unglamorous maintenance that quietly breaks Stripe-to-Postgres pipelines.

You can absolutely own that chore yourself, and if you are hand-building, this post's pattern (curated columns, JSONB catch-all, upsert key, sync metadata) minimises it, because unknown fields flow into data instead of breaking the pipeline.

The alternative is to not own it at all. Codeless Sync creates these exact tables in one click and keeps them filled on a schedule (hourly, daily, or custom cron on paid tiers), with full and incremental sync modes, timestamps converted, amounts kept in minor units, and the complete payload preserved in data. There are no webhooks or cron jobs to babysit and no drift handling on your plate. The 5-minute walkthrough is in how to sync Stripe data to PostgreSQL, and if you are still weighing approaches, 5 ways to get Stripe data into PostgreSQL compares the options honestly.

Either way, start from the right schema. All nine production templates (customers, invoices, subscriptions, payment intents, products, prices, refunds, invoice line items, subscription items) are free to inspect and copy in the Stripe to Postgres Schema tool.

Frequently Asked Questions

Should I store the whole Stripe payload in one JSONB column?

Not on its own. A single JSONB column preserves everything but makes every query a JSON-path expression, indexes get awkward, and type safety disappears. The hybrid works better: a handful of typed columns for the fields you query, plus one data JSONB column holding the full payload for everything else. You get fast, readable SQL and lose nothing.

Should Stripe amounts be INTEGER, BIGINT, or NUMERIC in PostgreSQL?

Use an integer type and keep Stripe's minor units. INTEGER covers single-object amounts comfortably (its ceiling is over 21 million pounds in pence), and BIGINT is the safe pick for aggregates or if you would rather never think about the ceiling. Avoid REAL and FLOAT entirely for money, and only convert to NUMERIC at display or reporting time when you divide by 100 (or 1000 for three-decimal currencies).

How do I store Stripe metadata in PostgreSQL?

As JSONB. Stripe's metadata object is arbitrary user-defined keys, so it has no fixed shape to model as columns. Query it with data->'metadata'->>'your_key', and if you filter on metadata often, add a GIN index on the JSONB column or an expression index on the specific key you query.

Do I need a separate table for every Stripe object?

Yes, one table per object type: customers, invoices, subscriptions, payment intents, and so on. Stripe objects reference each other by ID, so separate tables joined on those IDs (for example stripe_invoices.customer to stripe_customers.id) mirror the API's own structure and keep queries simple. The schema tool shows the table for each of the 9 core objects.

How do I keep my Stripe tables up to date when the API changes?

Three habits if you are hand-rolling: pin your Stripe API version so changes only arrive when you choose, watch the Stripe API changelog for moved or removed fields, and keep a full-payload JSONB column so new fields are captured even before you model them. Or hand the chore off entirely: a managed sync like Codeless Sync owns the schema, the conversions, and the drift handling, so your tables stay current without migration work.

Related: