Modernizing Payments with PSD2 & PSD3

At their core, PSD2 (Payment Services Directive) and PSD3 redefine how payments work in Europe. They challenge financial institutions, PSPs, and TPPs to think bigger: to create systems that are secure, transparent, and innovative while protecting consumers and enabling competition. Here’s the big picture:

1. Enhanced Security: Strong Customer Authentication (SCA), fraud detection, and liability shifts put security at the center.
2. Open Banking: APIs give third-party providers (TPPs) real-time access to customer data. It's about creating a connected ecosystem.
3. Consumer Transparency: Clear, detailed transaction data, no hidden fees. Simple. Honest.
4. Operational Excellence: Faster transaction monitoring, scalable data-sharing, and compliance automation.
5. Market Competition: Level the playing field—access to payment systems for everyone.

Now here’s the question: How do you make it all work?

The answer: CrateDB. A distributed SQL database that scales effortlessly, analyzes data in real time, and handles both structured and semi-structured data like a pro. It’s built to meet the demands of PSD2 and PSD3. Let’s break it down.

1. Strong Customer Authentication (SCA) & Fraud Prevention

The future of payments is secure by design. CrateDB makes it happen with real-time processing and fraud detection. 

Example: Storing SCA Events in CrateDB

Every login attempt, device detail, and geolocation needs to be stored securely and queried instantly. With CrateDB: 

CREATE TABLE sca_events (
    user_id TEXT, 
    login_time TIMESTAMP, 
    device_id TEXT, 
    ip_address TEXT, 
    location GEO_POINT, 
    result TEXT INDEX USING FULLTEXT 
); 

-- Insert data in real time 
INSERT INTO sca_events (user_id, login_time, device_id, ip_address, location, result)
VALUES ('user123', '2024-12-29T10:15:00Z', 'device987', '192.168.1.1', [40.7128, -74.0060], 'SUCCESS'); 

CrateDB can process millions of these events per second, enabling real-time fraud analysis. 

Learn More: CrateDB Real-Time Performance

2. Fraud Prevention: IBAN Verification

Matching IBANs with account names is crucial to prevent fraud. CrateDB handles large-scale joins across datasets. 

Example: IBAN Verification Query

SELECT * 
FROM iban_registry  
WHERE iban = 'DE89370400440532013000' AND account_name = 'John Doe';

This query executes instantly—even on billions of records—thanks to CrateDB’s distributed SQL engine. 

Learn More: CrateDB for Fraud Detection 

3. Open Banking and API Access

APIs are the bridge between banks and TPPs. CrateDB ensures secure, fast, and reliable data exchange. 

Example: Real-Time Consent Management 
CrateDB stores and updates customer consent data in real time, empowering PSPs to provide transparency through a dashboard.

CREATE TABLE consent_management (
   user_id TEXT PRIMARY KEY,
   tpp_id TEXT,
   consent_granted BOOLEAN,
   granted_at TIMESTAMP
);

-- Real-time update when user revokes access
UPDATE consent_management
SET consent_granted = FALSE
WHERE user_id = 'user123' AND tpp_id = 'tpp456';

 Learn More: CrateDB API Use Cases

4. Data Sharing for Open Banking

Sharing data across PSPs is a cornerstone of PSD3. CrateDB’s distributed architecture ensures data is always available and replicated securely. 

Example: JSON Data for Open Banking APIs 

CrateDB supports semi-structured JSON, making it perfect for storing and querying TPP-related data.

CREATE TABLE tpp_requests (
    request_id TEXT, 
    tpp_id TEXT, 
    user_id TEXT, 
    request_payload OBJECT (DYNAMIC),
    timestamp TIMESTAMP 
); 

-- Query a specific TPP request 
SELECT request_payload['transaction_details'] 
FROM tpp_requests 
WHERE tpp_id = 'tpp456' AND user_id = 'user123';

Learn More: CrateDB and JSON 

5. Consumer Transparency

Give consumers what they deserve: clear, detailed transaction data. CrateDB enables real-time storage and querying for transparency. 

Example: Storing ATM Charges

CREATE TABLE atm_charges ( 
    transaction_id TEXT, 
    user_id TEXT, 
    amount DOUBLE, 
    fee DOUBLE, 
    currency TEXT, 
    timestamp TIMESTAMP 
); 

-- Query fees for a specific user 
SELECT transaction_id, fee  
FROM atm_charges  
WHERE user_id = 'user123';

Dynamic dashboards built with CrateDB + Kibana help display this information to consumers instantly.

6. Enhanced Transaction Monitoring

Transactions don’t just happen—they tell a story. CrateDB’s time-series capabilities help you analyze every detail.

Example: Monitoring Transactions in Real Time 

CREATE TABLE transactions ( 
    transaction_id TEXT, 
    user_id TEXT, 
    timestamp TIMESTAMP, 
    amount DOUBLE, 
    currency TEXT, 
    location GEO_POINT 
); 

-- Query for suspicious transactions (e.g., over €10,000) 
SELECT *  
FROM transactions  
WHERE amount > 10000.00;

And with tools like Grafana, CrateDB powers dashboards that display anomalies instantly. 

Learn More: CrateDB Time-Series Analytics 

7. Compliance and Audit Trails

Compliance isn’t optional—it’s a necessity. CrateDB logs every action and event for full traceability.

Example: Audit Log 

CREATE TABLE audit_log ( 
    event_id TEXT PRIMARY KEY, 
    user_id TEXT, 
    action TEXT, 
    timestamp TIMESTAMP 
); 
 
-- Store an audit event 
INSERT INTO audit_log (event_id, user_id, action, timestamp) 
VALUES ('evt001', 'user123', 'LOGIN_ATTEMPT', '2024-12-29T10:15:00Z'); 

CrateDB ensures these logs are immutable, meeting strict compliance requirements.

Learn More: CrateDB and Log Analysis 

Why CrateDB Stands Out

• Real-Time Analytics: Process billions of rows of data instantly.
• Scalable and Distributed: Built to grow with your business. 
• JSON + SQL: The flexibility of JSON with the power of SQL. 
• Cost-Effective: Designed for massive workloads without massive costs