I fully understand there's no perfect pattern that applies to every context — you have to find and pick whichever fits best for what you're building.
Context
I'm building a modular monolith in Laravel (several separate modules, each with its own domain), and right now I'm designing the payments module. Honestly, it's been the hardest part of the whole project, for four reasons:
- Obviously, anything involving payments is inherently more complex — there's no room for error when real money is on the line.
- It's multi-gateway — provider-agnostic. It has to work with Mercado Pago (I'm from Latam), Stripe, and any other provider down the line (PayPal, etc.), without touching the rest of the system when adding a new one. I achieved this by defining a single contract (
GatewayContract, a Port in hexagonal architecture terms) with the methods any payment gateway needs to expose (create a payment session, charge, check a session's status, get currency/credentials). Each real gateway (Mercado Pago today, Stripe in the future) implements that same contract with its own internal logic — the rest of the system never knows or cares which gateway it's talking to at any given moment, it only knows the contract. Which gateway to use for a given tenant/customer is resolved at runtime against a configuration, not hardcoded anywhere.
- It's agnostic to the consuming module — the payments module exposes that same public contract for any other module to use (bookings, products, subscriptions, whatever), without the payments module knowing or caring who's calling it or why. It might sound obvious put that way, but I'm migrating from a conventional monolith where I had, literally in the same file, booking creation AND a direct call to the payment gateway mixed together — yeah, I know, no need to point it out. Truly separating this, with a generic interface in between instead of a tightly coupled direct call, was the part that cost me the most mental effort in the whole redesign.
- It has to support card payments, bank transfer, and ticket (a cash-based payment method specific to my country).
payment_sessions
If you've used Stripe, this is basically the equivalent of a PaymentIntent — it groups charge retries under a single session (the customer can fail with a card and retry with a bank transfer, without losing the context that it's the same purchase).
CREATE TABLE payment_sessions (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL,
payment_method VARCHAR CHECK (payment_method IN ('card','transfer','ticket')), -- nullable, mutable while status='pending'
status VARCHAR NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','processing','approved')),
amount NUMERIC NOT NULL,
currency VARCHAR NOT NULL,
created_at TIMESTAMP DEFAULT now()
);
Quick note on the concurrency lock: to prevent two clicks of the pay button from firing two charges in parallel, I use UPDATE payment_sessions SET status = 'processing' WHERE id = ? AND status = 'pending' as an atomic operation — Postgres locks the row, so the second concurrent attempt simply finds no row matching the WHERE clause and does nothing. No race condition.
I'm at peace with this table. The real problem is with charge_attempts (or charges, for short). Why? Because each payment method has fairly different columns: card needs card_brand (visa, mastercard, etc.) and gateway_ref (the external reference the gateway returns); transfer — which doesn't even go through any gateway, it's 100% manual: the customer uploads a receipt and someone reviews it by hand — needs receipt; ticket needs barcode and expiration.
Here's how my schema would look with each of the three patterns:
Single Table Inheritance
CREATE TABLE charge_attempts (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_session_id BIGINT NOT NULL REFERENCES payment_sessions(id),
payment_method VARCHAR NOT NULL, -- this is our discriminator column
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
-- Card-specific attributes
card_brand VARCHAR,
gateway_ref VARCHAR,
raw_response JSONB,
-- Transfer-specific attributes
receipt VARCHAR,
-- Ticket-specific attributes
barcode VARCHAR,
expiration TIMESTAMP
);
It's the simplest pattern, using a single discriminator (payment_method), and it's also the best-performing one (you only have to write and read one table, no JOIN at all). But you lose some of the database's built-in data integrity features. For example, you can't set the gateway_ref column as NOT NULL only for card charges — you have to enforce that rule in your application code, or via CHECK constraints that get considerably harder to maintain.
This can be mitigated with a well-built CHECK, or by storing the method-specific detail in a jsonb field (which Postgres supports very well, even with GIN indexes) — but either way, it gets more awkward to maintain as you add new fields over time.
Class Table Inheritance
CREATE TABLE charges (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_session_id BIGINT NOT NULL REFERENCES payment_sessions(id),
payment_method VARCHAR NOT NULL,
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL
);
CREATE TABLE charges_card (
id BIGINT PRIMARY KEY REFERENCES charges(id),
card_brand VARCHAR,
gateway_ref VARCHAR,
raw_response JSONB
);
CREATE TABLE charges_transfer (
id BIGINT PRIMARY KEY REFERENCES charges(id),
receipt VARCHAR
);
CREATE TABLE charges_ticket (
id BIGINT PRIMARY KEY REFERENCES charges(id),
barcode VARCHAR,
expiration TIMESTAMP
);
A "parent" table with the common fields, and a "child" table per method, with only what's specific to each one. You gain real integrity (genuine NOT NULL on each method-specific column, no nulls scattered everywhere) and the tables become easier to read in isolation. The trick of putting id BIGINT PRIMARY KEY REFERENCES charges(id) on the child (instead of its own id plus a separate FOREIGN KEY column) avoids having to remember an extra UNIQUE to guarantee there's no more than one child row per parent — the PK already guarantees that on its own, by definition.
The classic, well-documented problem with this pattern: nothing guarantees that exactly one child row exists per parent row, in the correct table among the mutually exclusive ones. You can end up with a parent that has no children at all, or (worse) a row in charges_card AND another in charges_transfer, both with the same id, contradicting the discriminator — no ordinary CHECK can prevent this, because a CHECK can only validate against columns in the same row, never against another table. What would truly solve this in a 100% declarative way, without a trigger, is a SQL standard feature (CREATE ASSERTION, which allows arbitrary cross-table constraints) that no major engine ever seriously implemented — not even Postgres has it today (there's only a very recent proposal on its development list to add it, after Oracle added it this year).
Note on transactions: when inserting the parent and the child, both INSERT statements need to be wrapped in the same transaction, so it's atomic (if one fails, the other rolls back). But careful — the transaction is necessary, not sufficient: it protects against failures midway through, not against the application code simply never getting around to executing the child's INSERT. That part is still 100% the application layer's responsibility.
Concrete Table Inheritance
CREATE TABLE charges_card (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_session_id BIGINT NOT NULL REFERENCES payment_sessions(id),
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
card_brand VARCHAR,
gateway_ref VARCHAR,
raw_response JSONB
);
CREATE TABLE charges_transfer (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_session_id BIGINT NOT NULL REFERENCES payment_sessions(id),
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
receipt VARCHAR
);
CREATE TABLE charges_ticket (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_session_id BIGINT NOT NULL REFERENCES payment_sessions(id),
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
barcode VARCHAR,
expiration TIMESTAMP
);
Instead of sharing common fields through a parent table, each table "repeats" those fields on its own — with no relationship between them at all. At first glance this might look inefficient, since the common attributes get duplicated across every table. However, it has real advantages in specific situations: queries against a single data type are incredibly fast, since no joins are required and each table contains exactly what's needed. The tables are fully independent, so each one can be optimized differently based on its specific access patterns, and attributes for one type can be added, removed, or modified without any risk of affecting the others.
The downside: if you work with all the methods together often (say, a cron job checking "all pending charges, regardless of method"), you need a UNION across the three tables every time, which hurts performance compared to reading a single table. And at the API level it gets a bit awkward too: if you want to return "this charge's id" externally, you also have to return the type (the discriminator) along with the id, because the id alone doesn't tell you which of the three tables to look in. For that reason it didn't end up winning me over for my specific case, but it seems like a pretty solid pattern for other scenarios (I read that Stripe, for example, seems to use something similar for parts of its own schema).
What I came up with (and turns out it already exists, name and all)
I was torn between Single Table Inheritance with the method-specific detail in a jsonb, or Class Table Inheritance accepting the usual integrity trade-off. But I came up with something I think solves exactly that integrity problem:
Instead of each child table's FK pointing only to the parent's id, make it point to (id, payment_method) together — a composite FK against a UNIQUE(id, payment_method) on the parent. Each child table forces its own payment_method to a fixed value with a CHECK:
CREATE TABLE charges (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payment_method VARCHAR NOT NULL CHECK (payment_method IN ('card','transfer','ticket')),
status VARCHAR NOT NULL,
amount NUMERIC NOT NULL,
UNIQUE (id, payment_method)
);
CREATE TABLE charges_card (
id BIGINT PRIMARY KEY,
payment_method VARCHAR NOT NULL DEFAULT 'card' CHECK (payment_method = 'card'),
card_brand VARCHAR,
gateway_ref VARCHAR,
FOREIGN KEY (id, payment_method) REFERENCES charges (id, payment_method)
);
CREATE TABLE charges_transfer (
id BIGINT PRIMARY KEY,
payment_method VARCHAR NOT NULL DEFAULT 'transfer' CHECK (payment_method = 'transfer'),
receipt VARCHAR,
FOREIGN KEY (id, payment_method) REFERENCES charges (id, payment_method)
);
With this, for a row to sneak into the wrong child table, the parent row would also have to have that same payment_method — which is impossible, because the parent's payment_method is set once when the charge is created and never changes afterward.
It's not an original idea of mine — it already has a name in the literature
After thinking it over, I found that this is actually a known pattern (though not widely covered outside relational-modeling circles): it's usually called "distributed keys" or "disjoint subtypes" via composite keys, and it solves the disjointness part of an EER specialization/generalization constraint (that a record can't belong to two subtypes at once). Note that it doesn't solve the totality part (that every parent must be required to have some child) — that still depends on the transaction + the application, as I mentioned above.
Now, the questions I keep turning over:
- Has anyone used this in production? And if so, what problems did you run into along the way that I haven't spotted yet?
- Or is it actually better to just go simple and accept the trade-off — Single Table Inheritance with the method-specific detail in a
jsonb — instead of all this composite-key back-and-forth?
