Live Stream on Jan 23rd: Unlocking Real Time Insights in the Renewable Energy Sector with CrateDB

Register now
Skip to content
Blog

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:  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 
Let’s Build the Future of Payments.
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.