SQL mi, NoSQL mi? — Önce Erişim Desenini Yaz

Questions the payments team will ask the data, written before choosing a database.

1. Move money between two accounts: debit, credit and ledger lines change
   together or not at all.                          -> relational, ACID transaction
2. How many payments did this card make in the last few minutes?
   Asked on every card authorization; the answer may expire.  -> key-value
3. KYC documents: ID card, passport, residence permit. Each type has its own
   fields, but compliance also reports across customers.     -> relational + JSONB
4. Questions nobody has asked yet (finance, the regulator).  -> relational, SQL

1
CREATE TABLE account (
2
    iban        varchar(34)   PRIMARY KEY,
3
    customer_id bigint        NOT NULL REFERENCES customer (id),
4
    currency    char(3)       NOT NULL,
5
    balance     numeric(19,4) NOT NULL CHECK (balance >= 0)
6
);
7

8
CREATE TABLE ledger_entry (
9
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
10
    transfer_id uuid          NOT NULL,
11
    iban        varchar(34)   NOT NULL REFERENCES account (iban),
12
    amount      numeric(19,4) NOT NULL,   -- negative = debit, positive = credit
13
    currency    char(3)       NOT NULL
14
);
15

16
-- Common fields as columns; fields that differ per document type in JSONB.
17
CREATE TABLE kyc_document (
18
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
19
    customer_id bigint NOT NULL REFERENCES customer (id),
20
    doc_type    text   NOT NULL,          -- ID_CARD, PASSPORT, RESIDENCE_PERMIT
21
    expires_on  date   NOT NULL,
22
    details     jsonb  NOT NULL
23
);
24
CREATE INDEX idx_kyc_details ON kyc_document USING gin (details);
25

26
-- Question 4, answered without planning for it: passports issued abroad
27
-- that expire this month.
28
-- SELECT customer_id FROM kyc_document
29
--  WHERE doc_type = 'PASSPORT' AND details @> '{"issuingCountry": "DE"}'
30
--    AND expires_on < date_trunc('month', now()) + interval '1 month';

# Question 2. One counter per card per 5-minute window, bumped on every authorization.
INCR   velocity:card:7f3c9a:202609251010
EXPIRE velocity:card:7f3c9a:202609251010 300

# The authorization service compares the returned count with the card's limit.
# Losing this counter in a restart is acceptable; losing a ledger line is not,
# which is why the money itself stays in PostgreSQL.

1
-- Cassandra. Designed for exactly one question: a card's transactions, newest first.
2
CREATE TABLE card_tx_by_card (
3
    card_token  text,
4
    month       text,
5
    tx_time     timestamp,
6
    tx_id       uuid,
7
    merchant_id text,
8
    amount      decimal,
9
    currency    text,
10
    PRIMARY KEY ((card_token, month), tx_time, tx_id)
11
) WITH CLUSTERING ORDER BY (tx_time DESC, tx_id ASC);
12

13
-- Six months later, fraud asks: every transaction at this merchant yesterday.
14
SELECT * FROM card_tx_by_card WHERE merchant_id = 'M-1042';
15
-- Rejected: merchant_id is not part of the key. ALLOW FILTERING would run it by
16
-- reading every partition. The usual answer is a second table, card_tx_by_merchant,
17
-- written alongside the first.