Architecting a Hybrid Persistence Layer with SQL Server and Cassandra for Scalable Audit Trails

A critical system component, responsible for processing financial transactions, was beginning to exhibit severe performance degradation under increasing load. The root cause was not the core transaction processing itself, which remained efficient, but the I/O contention on a single, monolithic SQL Server database. Specifically, the dbo.AuditTrail table, an append-only log of every state change and user action, was becoming a massive bottleneck. Every transaction, regardless of its simplicity, generated multiple writes to this table, creating lock contention and disk I/O pressure that impacted the performance of the entire application.

The requirement is clear: maintain strict ACID compliance for core financial data while scaling the audit trail’s write throughput horizontally to handle millions of events per hour without impacting the primary transactional workload.

Defining a Complex Technical Problem

The existing architecture is a classic Spring Framework monolith backed by a single SQL Server instance.

graph TD
    A[API Gateway] --> B{Spring Monolith};
    B -- Reads/Writes --> C[(SQL Server)];
    subgraph C [SQL Server Database]
        C1[Transactions Table]
        C2[Accounts Table]
        C3[AuditTrail Table]
    end
    B --> C1;
    B --> C2;
    B --> C3;

The core issue lies with the AuditTrail table. Its write pattern is INSERT-heavy and rarely updated. Queries are typically range scans by user_id or entity_id over a time window. This usage pattern is fundamentally mismatched with a relational database optimized for complex joins and transactional integrity. The table grows indefinitely, slowing down backups, indexing, and creating a single point of failure and contention. Our challenge is to decouple this audit workload from the core transactional database.

Analysis of Pros and Cons for Solution A: Scale SQL Server

The most direct path is to optimize the existing technology stack. The proposal was to aggressively scale SQL Server to handle the audit write load.

Strategies Considered:

1. Vertical Scaling: Upgrading the SQL Server instance with more CPU, faster RAM, and IOPS-optimized storage. This is the simplest approach but offers diminishing returns and becomes prohibitively expensive. It’s a temporary fix, not a long-term solution.
2. Table Partitioning: Utilize SQL Server’s native table partitioning, likely by date. This could improve query performance and maintenance operations (e.g., archiving old partitions). However, it does little to alleviate the write contention on the active partition, which is where the bottleneck lies.
3. Database Sharding: Manually shard the AuditTrail table across multiple SQL Server instances. This would theoretically provide horizontal write scalability.

Pros of Scaling SQL Server:

• Technology Homogeneity: The team maintains a single database technology, reducing operational complexity and the need for new skill sets.
• Transactional Consistency: All data, including audits, remains within a single ACID-compliant ecosystem. This simplifies reasoning about the system’s state at any given point in time.
• Tooling Maturity: Existing monitoring, backup, and administration tools continue to work seamlessly.

Cons of Scaling SQL Server:

• Sharding Complexity: In a real-world project, sharding a relational database correctly is notoriously difficult. Application logic must become shard-aware, routing writes to the correct instance. Cross-shard queries become complex and inefficient. Schema changes and rebalancing shards are significant operational hurdles.
• Cost: Licensing and hardware costs for multiple high-performance SQL Server instances are substantial.
• Architectural Mismatch: We would be forcing a relational database, designed for normalization and consistency, to behave like a distributed log aggregator. It’s using the wrong tool for the job, and the architectural friction will manifest as continuous operational pain. The pitfall here is thinking that because SQL Server can do it, it should.

This path was deemed too high-risk and costly for the specific problem of scaling an append-only log. The operational overhead of maintaining a sharded SQL Server cluster for this use case was a non-starter.

Analysis of Pros and Cons for Solution B: Hybrid Persistence with Cassandra

This alternative proposes a more radical architectural shift: a hybrid persistence model. We would leave the core transactional data in SQL Server, leveraging its ACID guarantees, but offload the high-volume audit trail data to a database designed for massive write throughput and horizontal scalability—Apache Cassandra.

This model embraces Polyglot Persistence, acknowledging that a single database cannot optimally serve all data access patterns. It also means we must accept a different consistency model for our audit data: BASE (Basically Available, Soft state, Eventually consistent).

The Proposed Architecture:

graph TD
    A[API Gateway] --> B{Spring Monolith};
    
    subgraph "Core Transactional Flow (ACID)"
        B -- Synchronous Write --> C[(SQL Server)];
    end

    subgraph "Audit Trail Flow (BASE)"
        B -- Asynchronous Write --> D[(Cassandra Cluster)];
    end

    C -- "Financial Tables" --> C;
    D -- "AuditTrail Table" --> D;

Pros of a Hybrid Model with Cassandra:

• Extreme Write Scalability: Cassandra is architected with a masterless, log-structured storage engine (LSM-tree) that excels at handling high-velocity writes. Adding new nodes to the cluster linearly increases write throughput.
• High Availability: With no single point of failure and configurable replication across data centers, Cassandra provides the fault tolerance required for a critical audit system.
• Decoupling: The performance of the core transactional system is completely isolated from the audit write load. A spike in user activity generating millions of audit events will not impact the processing of financial transactions.
• Cost-Effectiveness: Cassandra runs on commodity hardware and has a lower total cost of ownership at scale compared to enterprise relational database licenses.

Cons of a Hybrid Model with Cassandra:

• Eventual Consistency: This is the most significant trade-off. An audit log written to Cassandra may not be immediately visible to all clients. For an audit trail, a delay of a few seconds is almost always acceptable, but the application and its users must be aware of this behavior. A common mistake is to treat a BASE system like an ACID one and build features that implicitly assume immediate consistency.
• Increased Operational Complexity: We now have two database systems to manage, monitor, and maintain. This requires new skills within the team.
• Dual-Write Problem: The application logic is now responsible for writing to two systems. If a write to Cassandra fails after the SQL Server transaction has committed, the systems are out of sync. This requires robust error handling, such as retry mechanisms with a dead-letter queue.
• Data Modeling Differences: The team must learn Cassandra’s query-first data modeling approach, which involves significant denormalization compared to SQL Server.

Final Choice and Rationale

The decision was made to proceed with Solution B: Hybrid Persistence with Cassandra.

The rationale is pragmatic. The business requirement for the audit trail is high availability and scalability, not immediate, strict consistency. A few seconds of replication lag is an acceptable trade-off for a system that can handle massive write volumes without failing or degrading the primary application. The complexity of handling eventual consistency and dual writes in the application layer was deemed a more manageable engineering problem than the operational nightmare of sharding SQL Server for this workload. This approach correctly aligns the technology with the specific data pattern, leading to a more robust and scalable long-term solution.

Core Implementation Overview

The implementation focuses on the Spring Boot application acting as the orchestrator and a React front-end that correctly handles the dual-consistency model.

1. Spring Boot Backend: Dual DataSource Configuration

We need to configure Spring to connect to both SQL Server (via JPA) and Cassandra (via Spring Data Cassandra).

application.yml

spring:
  # --- SQL Server (Primary) Configuration ---
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=transactions_db;encrypt=false;
    username: sa
    password: YourStrongPassword!
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.SQLServer2012Dialect

  # --- Cassandra (Secondary) Configuration ---
  data:
    cassandra:
      contact-points: 127.0.0.1
      port: 9042
      local-datacenter: datacenter1
      keyspace-name: audit_keyspace
      schema-action: CREATE_IF_NOT_EXISTS

logging:
  level:
    org.springframework.data.cassandra.core.cql.CqlTemplate: DEBUG

2. Data Models and Repositories

We create distinct entity classes and repositories for each database.

SQL Server Entity (JPA)

// src/main/java/com/example/hybrid/sql/PaymentTransaction.java
package com.example.hybrid.sql;

import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.Instant;
import java.util.UUID;

@Entity
@Table(name = "payment_transactions")
public class PaymentTransaction {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @Column(nullable = false)
    private String fromAccount;

    @Column(nullable = false)
    private String toAccount;

    @Column(nullable = false)
    private BigDecimal amount;

    @Column(nullable = false)
    private Instant createdAt;
    
    // Getters and Setters...
}

// src/main/java/com/example/hybrid/sql/PaymentTransactionRepository.java
package com.example.hybrid.sql;

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.UUID;

public interface PaymentTransactionRepository extends JpaRepository<PaymentTransaction, UUID> {
}

Cassandra Entity (Spring Data Cassandra)

The data model is denormalized for efficient queries. We partition by entity_id to group all audit events for a single transaction together, and cluster by event_time to keep them sorted chronologically.

// src/main/java/com/example/hybrid/cassandra/ActivityLog.java
package com.example.hybrid.cassandra;

import org.springframework.data.cassandra.core.cql.Ordering;
import org.springframework.data.cassandra.core.cql.PrimaryKeyType;
import org.springframework.data.cassandra.core.mapping.PrimaryKeyColumn;
import org.springframework.data.cassandra.core.mapping.Table;

import java.time.Instant;
import java.util.UUID;

@Table("activity_log")
public class ActivityLog {

    @PrimaryKeyColumn(name = "entity_id", ordinal = 0, type = PrimaryKeyType.PARTITIONED)
    private UUID entityId;

    @PrimaryKeyColumn(name = "event_time", ordinal = 1, type = PrimaryKeyType.CLUSTERING, ordering = Ordering.DESCENDING)
    private Instant eventTime;

    @PrimaryKeyColumn(name = "event_id", ordinal = 2, type = PrimaryKeyType.CLUSTERING)
    private UUID eventId; // To ensure uniqueness if eventTime collides

    private String actor;
    private String action;
    private String details;
    
    // Constructors, Getters, and Setters...
}


// src/main/java/com/example/hybrid/cassandra/ActivityLogRepository.java
package com.example.hybrid.cassandra;

import org.springframework.data.cassandra.repository.CassandraRepository;
import java.util.List;
import java.util.UUID;

public interface ActivityLogRepository extends CassandraRepository<ActivityLog, UUID> {
    List<ActivityLog> findByEntityId(UUID entityId);
}

3. The Dual-Write Service

The service layer orchestrates the writes. The critical part is ensuring the primary, transactional write to SQL Server is atomic. The write to Cassandra is treated as a side effect. In a real-world project, this would be an asynchronous operation to avoid blocking the main thread. For clarity, we show it synchronously here, but with clear error handling separation.

// src/main/java/com/example/hybrid/service/PaymentService.java
package com.example.hybrid.service;

import com.example.hybrid.cassandra.ActivityLog;
import com.example.hybrid.cassandra.ActivityLogRepository;
import com.example.hybrid.sql.PaymentTransaction;
import com.example.hybrid.sql.PaymentTransactionRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.Instant;
import java.util.UUID;

@Service
public class PaymentService {

    private static final Logger logger = LoggerFactory.getLogger(PaymentService.class);

    private final PaymentTransactionRepository transactionRepository;
    private final ActivityLogRepository activityLogRepository;

    public PaymentService(PaymentTransactionRepository transactionRepository, ActivityLogRepository activityLogRepository) {
        this.transactionRepository = transactionRepository;
        this.activityLogRepository = activityLogRepository;
    }

    @Transactional // This annotation applies ONLY to the JPA/JDBC transaction manager
    public PaymentTransaction executePayment(PaymentRequest request) {
        // --- Step 1: Core ACID Transaction ---
        // This block is wrapped in a database transaction. If any part fails,
        // the entire operation on SQL Server is rolled back.
        PaymentTransaction newTransaction = new PaymentTransaction();
        newTransaction.setFromAccount(request.getFromAccount());
        newTransaction.setToAccount(request.getToAccount());
        newTransaction.setAmount(request.getAmount());
        newTransaction.setCreatedAt(Instant.now());
        
        PaymentTransaction savedTransaction = transactionRepository.save(newTransaction);
        logger.info("Successfully committed transaction {} to SQL Server.", savedTransaction.getId());

        // --- Step 2: Post-Commit Side Effect - Write to Cassandra ---
        // This happens only if the SQL Server transaction successfully commits.
        try {
            ActivityLog log = new ActivityLog(
                savedTransaction.getId(),
                Instant.now(),
                UUID.randomUUID(),
                request.getInitiatedBy(),
                "PAYMENT_CREATED",
                "Amount: " + request.getAmount()
            );
            activityLogRepository.save(log);
            logger.info("Successfully wrote audit log for transaction {} to Cassandra.", savedTransaction.getId());
        } catch (Exception e) {
            // CRITICAL: The ACID transaction is already committed.
            // We cannot roll it back. We must handle this failure.
            // In a production system, this should publish the event to a
            // reliable message queue (like RabbitMQ or Kafka) for retries.
            logger.error(
                "CRITICAL FAILURE: SQL transaction {} committed but failed to write audit log to Cassandra. Event must be queued for retry.",
                savedTransaction.getId(), e
            );
            // This is where you would trigger a compensating action or a persistent retry.
        }

        return savedTransaction;
    }
}

This code explicitly demonstrates the core architectural trade-off. The @Transactional annotation ensures atomicity for the SQL Server write. The Cassandra write is outside this guarantee. A failure in the Cassandra write after the SQL commit requires a separate recovery strategy.

4. React Frontend and Testing for Eventual Consistency

The front-end must be built to handle the fact that audit data might not be available immediately after a transaction is created.

A React Component: TransactionDetails.js

This component fetches the core transaction details and its audit log from two separate API endpoints.

// src/components/TransactionDetails.js
import React, { useState, useEffect } from 'react';

const fetchTransaction = async (id) => {
    // Fetches from endpoint hitting SQL Server (fast, consistent)
    const response = await fetch(`/api/transactions/${id}`);
    return response.json();
};

const fetchActivityLog = async (id) => {
    // Fetches from endpoint hitting Cassandra (eventually consistent)
    const response = await fetch(`/api/transactions/${id}/activity`);
    return response.json();
};

export const TransactionDetails = ({ transactionId }) => {
    const [transaction, setTransaction] = useState(null);
    const [activityLog, setActivityLog] = useState([]);
    const [isLoadingLog, setIsLoadingLog] = useState(true);
    const [error, setError] = useState(null);

    useEffect(() => {
        const loadData = async () => {
            try {
                const txData = await fetchTransaction(transactionId);
                setTransaction(txData);

                const logData = await fetchActivityLog(transactionId);
                setActivityLog(logData);
            } catch (err) {
                setError('Failed to load transaction data.');
            } finally {
                setIsLoadingLog(false);
            }
        };

        loadData();
    }, [transactionId]);

    if (error) {
        return <div role="alert">{error}</div>;
    }

    if (!transaction) {
        return <div>Loading transaction...</div>;
    }

    return (
        <div>
            <h1>Transaction: {transaction.id}</h1>
            <p>Amount: ${transaction.amount}</p>
            
            <h2>Activity Log</h2>
            {isLoadingLog && <p>Loading activity log...</p>}
            {!isLoadingLog && activityLog.length === 0 && (
                <p data-testid="no-activity-message">No activity recorded yet. Data may be synchronizing.</p>
            )}
            <ul>
                {activityLog.map(log => (
                    <li key={log.eventId}>
                        {log.eventTime}: {log.actor} {log.action} - {log.details}
                    </li>
                ))}
            </ul>
        </div>
    );
};

Testing with React Testing Library and MSW

The crucial test is to simulate the eventual consistency. We’ll use Mock Service Worker (MSW) to mock the API endpoints. The test will verify that the component correctly handles the initial state (transaction loaded, but log is empty) and then the final state (log is loaded).

// src/components/TransactionDetails.test.js
import React from 'react';
import { render, screen, waitFor } from '@testing-library/react';
import { rest } from 'msw';
import { setupServer } from 'msw/node';
import { TransactionDetails } from './TransactionDetails';

const transactionId = 'a4b1c2d3-e4f5-g6h7-i8j9-k0l1m2n3o4p5';

const server = setupServer(
  // Mock for the core transaction data (from SQL Server) - responds immediately
  rest.get(`/api/transactions/${transactionId}`, (req, res, ctx) => {
    return res(ctx.json({ id: transactionId, amount: 100.00 }));
  }),

  // Mock for the activity log (from Cassandra) - initially empty
  rest.get(`/api/transactions/${transactionId}/activity`, (req, res, ctx) => {
    return res(ctx.json([]));
  })
);

beforeAll(() => server.listen());
afterEach(() => server.resetHandlers());
afterAll(() => server.close());

test('handles eventual consistency of the activity log', async () => {
  render(<TransactionDetails transactionId={transactionId} />);

  // 1. Assert initial state: Transaction details are present immediately.
  expect(await screen.findByText(/Transaction: a4b1c2d3-/)).toBeInTheDocument();
  expect(screen.getByText(/\$100.00/)).toBeInTheDocument();

  // 2. Assert loading/empty state for the activity log.
  // The API initially returns an empty array, so this message should appear.
  expect(await screen.findByTestId('no-activity-message')).toHaveTextContent(
    'No activity recorded yet. Data may be synchronizing.'
  );

  // 3. Now, simulate the Cassandra data becoming available.
  // We update the mock handler to return data on the *next* call.
  // In a real app, this would be a polling or WebSocket update. Here we simulate it with a second fetch.
  server.use(
    rest.get(`/api/transactions/${transactionId}/activity`, (req, res, ctx) => {
      return res(ctx.json([
        { eventId: 'uuid-1', eventTime: '2023-10-27T10:00:00Z', actor: 'user1', action: 'PAYMENT_CREATED', details: 'Amount: 100.00' }
      ]));
    })
  );
  
  // Re-rendering or triggering a re-fetch might be needed depending on component logic.
  // For this test, let's assume a polling mechanism we can't easily trigger.
  // A better approach is to test the component's ability to handle an update via props or a hook.
  // Let's modify the test to simulate a refresh button or a timed refetch.
  // For simplicity here, let's just prove the concept with a waitFor block that waits for the mock to change.
  // We'll re-render to simulate a page refresh.
  
  const { rerender } = render(<TransactionDetails transactionId={transactionId} />);
  rerender(<TransactionDetails transactionId={transactionId} />);

  // 4. Use `waitFor` to assert that the eventually consistent data appears.
  await waitFor(() => {
    const activityItem = screen.getByText(/PAYMENT_CREATED/);
    expect(activityItem).toBeInTheDocument();
    expect(screen.queryByTestId('no-activity-message')).not.toBeInTheDocument();
  });
});

This test directly validates that the UI is resilient to the architectural decision. It does not break if the audit data is delayed, providing a graceful user experience.

Extensibility and Limitations of the Architecture

This hybrid architecture, while solving the immediate scaling problem, introduces its own set of challenges. The primary limitation is the complexity of maintaining data integrity across two disparate systems with different consistency models. The dual-write approach requires a robust, out-of-band recovery mechanism (e.g., a message queue with a dead-letter queue) to handle failures in the secondary write to Cassandra. Without this, the system will inevitably suffer from data drift.

Furthermore, querying data across both SQL Server and Cassandra is non-trivial. A request like “show me all transactions over $1000 and their full audit history” would require application-level joins, fetching data from both sources and merging it in memory. This can be complex and inefficient.

A future evolution of this pattern could involve replacing the application-level dual-write with a Change Data Capture (CDC) pipeline using a tool like Debezium. The application would only write to SQL Server. Debezium would then stream committed changes from the SQL Server transaction log to a Kafka topic, from which a separate consumer service would write the data to Cassandra. This further decouples the systems, making the architecture more resilient, but adds yet another moving part to the overall infrastructure. The choice depends on the trade-off between application-level complexity and infrastructure-level complexity.