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:
- Enhanced Security: Strong Customer Authentication (SCA), fraud detection, and liability shifts put security at the center.
- Open Banking: APIs give third-party providers (TPPs) real-time access to customer data. It's about creating a connected ecosystem.
- Consumer Transparency: Clear, detailed transaction data, no hidden fees. Simple. Honest.
- Operational Excellence: Faster transaction monitoring, scalable data-sharing, and compliance automation.
- 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: Large transaction table containing millions of rows and need to join it with an IBAN registry to validate account details while applying filters to extract specific data.
SELECT t.transaction_id, t.transaction_date, t.transaction_amount, ir.iban, ir.account_name FROM transactions t //millions of rows INNER JOIN iban_registry ir ON t.iban = ir.iban WHERE ir.iban = 'DE89370400440532013000' AND ir.account_name = 'John Doe';
This query executes instantly—even on billions of records—thanks to CrateDB’s distributed SQL engine combined with the power of columnar storage and indexes on all columns by default.
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. Here some key benefits of CrateDB in consent management:
- Time-Series Optimization: Tracks consent changes efficiently over time.
- JSON Flexibility: Easily stores and queries dynamic consent data structures.
- Geospatial Insights: Adds location-based analysis for consent events.
- High Scalability: Handles millions of records with ease.
- Retention Policies: Automatically manages storage of expired records.
Below is an example of how consent management implemented using CrateDB.
CREATE TABLE consent_management ( user_id TEXT NOT NULL, -- Unique identifier for the user consent_id TEXT PRIMARY KEY, -- Unique identifier for the consent record consent_data OBJECT(DYNAMIC) NOT NULL, -- JSON object to store consent details status TEXT NOT NULL, -- Status: granted/revoked/expired timestamp TIMESTAMP NOT NULL, -- Time of consent action geo_location GEO_POINT, -- Optional: Geo-location of consent action metadata OBJECT(DYNAMIC) -- Optional: Additional metadata ) WITH ( number_of_replicas = 1, -- Adjust based on redundancy needs column_policy = 'dynamic' -- Allows flexible JSON fields ); -- Below some example records to track consent actions over time INSERT INTO consent_management ( user_id, consent_id, consent_data, status, timestamp, geo_location, metadata ) VALUES ( '12345', 'consent_001', { "consent_type": "email_marketing", "details": "Subscribed to promotional emails" }, 'granted', '2025-01-12T14:32:00Z', [40.7128, -74.0060], -- New York City coordinates { "device": "mobile", "browser": "Chrome" } ), ( '67890', 'consent_002', { "consent_type": "data_sharing", "details": "Consent to share anonymized data" }, 'revoked', '2025-01-10T10:20:00Z', [51.5074, -0.1278], -- London coordinates { "device": "desktop", "browser": "Firefox" } );
Learn More: CrateDB API Use Cases
4. Data Sharing for Open Banking
CrateDB supports semi-structured JSON, making it perfect for storing and querying TPP-related data. Each payment rail (ACH, card, check, cross-border, etc.,) has its unique data structure. Using JSON in CrateDB allows modeling this diversity while keeping the schema flexible and adaptable to changes.
Example: JSON Data for Open Banking APIs
CrateDB supports semi-structured JSON, making it perfect for storing and querying TPP-related data. Each payment rail (ACH, card, check, cross-border, etc.,) has its unique data structure. Using JSON in CrateDB allows modeling this diversity while keeping the schema flexible and adaptable to changes.
CREATE TABLE payment_rails ( payment_id TEXT PRIMARY KEY, -- Unique payment identifier user_id TEXT NOT NULL, -- Associated user payment_type TEXT NOT NULL, -- Payment rail (ACH, card, check, etc.) payment_data OBJECT (DYNAMIC) NOT NULL, -- JSON data for specific rail details tpp_id TEXT, -- Third-party provider (TPP) identifier timestamp TIMESTAMP NOT NULL -- Payment initiation timestamp ) WITH ( number_of_replicas = 1, -- Adjust replication for availability column_policy = 'dynamic' -- Allows adding flexible fields in JSON ); -- Query a specific TPP request INSERT INTO payment_rails ( payment_id, user_id, payment_type, payment_data, tpp_id, timestamp ) VALUES -- ACH Payment ( 'payment_001', 'user123', 'ACH', { "account_number": "123456789", "routing_number": "987654321", "amount": 500.00, "currency": "USD", "description": "Monthly rent payment" }, 'tpp_001', '2025-01-12T10:00:00Z' ), -- Card Payment ( 'payment_002', 'user123', 'Card', { "card_number": "**** **** **** 4242", "amount": 150.25, "currency": "USD", "merchant": "Online Store", "transaction_id": "txn_12345" }, 'tpp_002', '2025-01-12T11:00:00Z' ), -- Cross-Border Payment ( 'payment_003', 'user456', 'Cross-Border', { "iban": "DE89370400440532013000", "swift_code": "COBADEFFXXX", "amount": 1000.00, "currency": "EUR", "beneficiary_name": "John Doe" }, 'tpp_003', '2025-01-12T12:00:00Z' ), -- Check Payment ( 'payment_004', 'user789', 'Check', { "check_number": "1001", "amount": 250.00, "currency": "USD", "payee_name": "Electric Utility Co.", "issue_date": "2025-01-10" }, NULL, -- No TPP involved '2025-01-12T09:00:00Z' );
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';
And with tools like Grafana, CrateDB powers dashboards that display anomalies 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
PSD2 and PSD3 aren’t just regulations—they’re opportunities. With CrateDB, you’re ready to create a secure, transparent, and scalable payment ecosystem. It’s time to innovate.
