Data Layer & Database

🎯 What You'll Learn

How Dione's SQL Server database is actually structured
JPA/Hibernate configuration quirks and gotchas
Real entity patterns and their historical context
DAO layer implementation and query optimization
Performance bottlenecks and how to address them

Database Overview

⚠️ Database Reality

Dione uses SQL Server 2016+ with JPA/Hibernate for ORM. It's not the latest and greatest, but it's stable, well-understood, and handles the financial workload effectively.

Database Configuration

ngop.properties - Database Configuration

# Primary Database Connection
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;databaseName=NGOP_DB;integratedSecurity=false
jdbc.username=ngop_user
jdbc.password=${DB_PASSWORD}

# Connection Pool Settings (C3P0)
jdbc.initialPoolSize=5
jdbc.minPoolSize=5
jdbc.maxPoolSize=50
jdbc.maxIdleTime=300
jdbc.acquireIncrement=5

# Hibernate Configuration
hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
hibernate.show_sql=false
hibernate.hbm2ddl.auto=validate
hibernate.connection.provider_class=org.hibernate.c3p0.internal.C3P0ConnectionProvider

Database Architecture

Database Schema Overview

NGOP_DB
├── dbo.organizations          # Multi-tenant organization data
├── dbo.customers             # Customer master data
├── dbo.customer_profiles     # Extended customer information
├── dbo.payments              # Payment transactions
├── dbo.fx_trades             # Foreign exchange trades
├── dbo.bank_accounts         # Customer bank account details
├── dbo.audit_log             # Audit trail for compliance
├── dbo.session_data          # Active sessions (rarely used)
├── dbo.configuration         # System configuration
└── dbo.lookup_tables         # Reference data (currencies, countries, etc.)

Multi-Tenant Data Isolation

Every core business table includes an organization_id column for tenant isolation:

Multi-Tenant Pattern

-- Every business query MUST include organization_id
SELECT * FROM customers 
WHERE email = 'user@example.com' 
AND organization_id = 1;  -- Critical for data isolation

-- Bad query (will return cross-tenant data)
SELECT * FROM customers 
WHERE email = 'user@example.com';  -- ❌ NEVER DO THIS

Schema Design Patterns

The database schema reflects years of business evolution. Understanding the patterns helps you work with it effectively.

Core Entity Relationships

Main Entity Relationships

-- Organizations table (tenant master)
CREATE TABLE organizations (
    organization_id INT IDENTITY(1,1) PRIMARY KEY,
    organization_code VARCHAR(10) NOT NULL UNIQUE,  -- 'CD', 'TORFX'
    organization_name VARCHAR(100) NOT NULL,
    active BIT NOT NULL DEFAULT 1,
    created_date DATETIME2 DEFAULT GETDATE()
);

-- Customers table (multi-tenant)
CREATE TABLE customers (
    customer_id INT IDENTITY(1,1) PRIMARY KEY,
    organization_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    active BIT NOT NULL DEFAULT 0,
    kyc_status VARCHAR(20) DEFAULT 'PENDING',
    created_date DATETIME2 DEFAULT GETDATE(),
    last_login_date DATETIME2,
    
    FOREIGN KEY (organization_id) REFERENCES organizations(organization_id),
    INDEX IX_customers_org_email (organization_id, email),
    INDEX IX_customers_org_active (organization_id, active)
);

-- Payments table (transaction master)
CREATE TABLE payments (
    payment_id INT IDENTITY(1,1) PRIMARY KEY,
    customer_id INT NOT NULL,
    organization_id INT NOT NULL,
    reference VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(18,4) NOT NULL,
    from_currency VARCHAR(3) NOT NULL,
    to_currency VARCHAR(3) NOT NULL,
    exchange_rate DECIMAL(10,6),
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_date DATETIME2 DEFAULT GETDATE(),
    processed_date DATETIME2,
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (organization_id) REFERENCES organizations(organization_id),
    INDEX IX_payments_customer (customer_id),
    INDEX IX_payments_org_status (organization_id, status),
    INDEX IX_payments_created_date (created_date)
);

Historical Design Decisions

🔑

Identity Columns

All primary keys use SQL Server IDENTITY for auto-increment. No UUIDs or custom ID generation.

📅

DATETIME2 Everywhere

Modern DATETIME2 columns for better precision and timezone handling.

💰

DECIMAL for Money

All monetary amounts use DECIMAL(18,4) to avoid floating-point precision issues.

🏢

Organization ID Everywhere

Multi-tenant isolation through organization_id on every business table.

JPA Configuration

Dione uses JPA 2.1 with Hibernate as the provider. The configuration is practical rather than cutting-edge.

Hibernate Configuration

services/business/src/main/resources/META-INF/persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.1">
    <persistence-unit name="ngop-pu" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>java:jboss/datasources/NGOPDataSource</jta-data-source>
        
        <!-- Entity Classes -->
        <class>com.currenciesdirect.gtg.ngop.business.entities.Customer</class>
        <class>com.currenciesdirect.gtg.ngop.business.entities.Payment</class>
        <class>com.currenciesdirect.gtg.ngop.business.entities.Organization</class>
        <class>com.currenciesdirect.gtg.ngop.business.entities.BankAccount</class>
        
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.connection.provider_class" 
                     value="org.hibernate.c3p0.internal.C3P0ConnectionProvider"/>
            
            <!-- SQL Server specific settings -->
            <property name="hibernate.connection.isolation" value="2"/>
            <property name="hibernate.jdbc.batch_size" value="25"/>
            <property name="hibernate.order_inserts" value="true"/>
            <property name="hibernate.order_updates" value="true"/>
            
            <!-- Cache settings -->
            <property name="hibernate.cache.use_second_level_cache" value="false"/>
            <property name="hibernate.cache.use_query_cache" value="false"/>
        </properties>
    </persistence-unit>
</persistence>

DataSource Configuration

JBoss standalone.xml - DataSource

<datasource jndi-name="java:jboss/datasources/NGOPDataSource" 
            pool-name="NGOPPool" enabled="true" use-java-context="true">
    <connection-url>jdbc:sqlserver://localhost:1433;databaseName=NGOP_DB</connection-url>
    <driver>sqlserver</driver>
    <security>
        <user-name>ngop_user</user-name>
        <password>${vault.DB_PASSWORD}</password>
    </security>
    <pool>
        <min-pool-size>5</min-pool-size>
        <max-pool-size>50</max-pool-size>
        <prefill>true</prefill>
    </pool>
    <timeout>
        <idle-timeout-minutes>5</idle-timeout-minutes>
    </timeout>
    <statement>
        <prepared-statement-cache-size>100</prepared-statement-cache-size>
        <share-prepared-statements>true</share-prepared-statements>
    </statement>
</datasource>

Entity Patterns

Dione entities follow consistent patterns that reflect the business domain and technical constraints.

Base Entity Pattern

BaseEntity.java

@MappedSuperclass
public abstract class BaseEntity {
    
    @Column(name = "created_date", nullable = false)
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdDate;
    
    @Column(name = "updated_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date updatedDate;
    
    @PrePersist
    protected void onCreate() {
        this.createdDate = new Date();
        this.updatedDate = new Date();
    }
    
    @PreUpdate
    protected void onUpdate() {
        this.updatedDate = new Date();
    }
    
    // Getters and setters
    public Date getCreatedDate() { return createdDate; }
    public void setCreatedDate(Date createdDate) { this.createdDate = createdDate; }
    
    public Date getUpdatedDate() { return updatedDate; }
    public void setUpdatedDate(Date updatedDate) { this.updatedDate = updatedDate; }
}

Customer Entity

Customer.java

@Entity
@Table(name = "customers", 
       indexes = {
           @Index(name = "IX_customers_org_email", columnList = "organization_id, email"),
           @Index(name = "IX_customers_org_active", columnList = "organization_id, active")
       })
public class Customer extends BaseEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Integer customerId;
    
    @Column(name = "organization_id", nullable = false)
    private Integer organizationId;
    
    @Column(name = "email", nullable = false, length = 255)
    private String email;
    
    @Column(name = "password_hash", nullable = false)
    private String password;
    
    @Column(name = "first_name", nullable = false, length = 100)
    private String firstName;
    
    @Column(name = "last_name", nullable = false, length = 100)
    private String lastName;
    
    @Column(name = "active", nullable = false)
    private Boolean active = false;
    
    @Column(name = "kyc_status", length = 20)
    private String kycStatus = "PENDING";
    
    @Column(name = "last_login_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date lastLoginDate;
    
    // Relationships
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "organization_id", insertable = false, updatable = false)
    private Organization organization;
    
    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY)
    private List<Payment> payments = new ArrayList<>();
    
    // Business methods
    public boolean isActive() {
        return Boolean.TRUE.equals(this.active);
    }
    
    public boolean isKycApproved() {
        return "APPROVED".equals(this.kycStatus);
    }
    
    public String getFullName() {
        return firstName + " " + lastName;
    }
    
    // Standard getters and setters...
}

Payment Entity

Payment.java

@Entity
@Table(name = "payments",
       indexes = {
           @Index(name = "IX_payments_customer", columnList = "customer_id"),
           @Index(name = "IX_payments_org_status", columnList = "organization_id, status"),
           @Index(name = "IX_payments_created_date", columnList = "created_date")
       })
public class Payment extends BaseEntity {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "payment_id")
    private Integer paymentId;
    
    @Column(name = "customer_id", nullable = false)
    private Integer customerId;
    
    @Column(name = "organization_id", nullable = false)
    private Integer organizationId;
    
    @Column(name = "reference", nullable = false, unique = true, length = 50)
    private String reference;
    
    @Column(name = "amount", nullable = false, precision = 18, scale = 4)
    private BigDecimal amount;
    
    @Column(name = "from_currency", nullable = false, length = 3)
    private String fromCurrency;
    
    @Column(name = "to_currency", nullable = false, length = 3)
    private String toCurrency;
    
    @Column(name = "exchange_rate", precision = 10, scale = 6)
    private BigDecimal exchangeRate;
    
    @Column(name = "status", nullable = false, length = 20)
    private String status = "PENDING";
    
    @Column(name = "processed_date")
    @Temporal(TemporalType.TIMESTAMP)
    private Date processedDate;
    
    @Column(name = "external_reference", length = 100)
    private String externalReference;
    
    // Relationships
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id", insertable = false, updatable = false)
    private Customer customer;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "organization_id", insertable = false, updatable = false)
    private Organization organization;
    
    // Business methods
    public boolean isPending() {
        return "PENDING".equals(this.status);
    }
    
    public boolean isCompleted() {
        return "COMPLETED".equals(this.status);
    }
    
    public BigDecimal getConvertedAmount() {
        if (exchangeRate != null) {
            return amount.multiply(exchangeRate);
        }
        return null;
    }
    
    // Standard getters and setters...
}

DAO Layer Implementation

The DAO layer provides a clean abstraction over JPA operations with consistent patterns across the application.

Base DAO Pattern

BaseDao.java

@Transactional
public abstract class BaseDao<T, ID> {
    
    @PersistenceContext(unitName = "ngop-pu")
    protected EntityManager entityManager;
    
    private final Class<T> entityClass;
    
    @SuppressWarnings("unchecked")
    public BaseDao() {
        this.entityClass = (Class<T>) ((ParameterizedType) getClass()
                .getGenericSuperclass()).getActualTypeArguments()[0];
    }
    
    public T findById(ID id) {
        return entityManager.find(entityClass, id);
    }
    
    public List<T> findAll() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<T> query = cb.createQuery(entityClass);
        Root<T> root = query.from(entityClass);
        return entityManager.createQuery(query.select(root)).getResultList();
    }
    
    public T save(T entity) {
        if (isNew(entity)) {
            entityManager.persist(entity);
            return entity;
        } else {
            return entityManager.merge(entity);
        }
    }
    
    public void delete(T entity) {
        if (entityManager.contains(entity)) {
            entityManager.remove(entity);
        } else {
            entityManager.remove(entityManager.merge(entity));
        }
    }
    
    protected abstract boolean isNew(T entity);
    
    protected Query createNamedQuery(String queryName) {
        return entityManager.createNamedQuery(queryName);
    }
    
    protected TypedQuery<T> createTypedQuery(String jpql) {
        return entityManager.createQuery(jpql, entityClass);
    }
}

Customer DAO Implementation

NGOPCusDaoImpl.java

@Repository
@Transactional
public class NGOPCusDaoImpl extends BaseDao<Customer, Integer> implements NGOPCusDao {
    
    private static final Logger LOG = LoggerFactory.getLogger(NGOPCusDaoImpl.class);
    
    @Override
    public Customer getCustomerByEmail(String email, Integer organizationId) {
        try {
            TypedQuery<Customer> query = entityManager.createQuery(
                "SELECT c FROM Customer c " +
                "WHERE c.email = :email AND c.organizationId = :orgId",
                Customer.class);
            
            query.setParameter("email", email);
            query.setParameter("orgId", organizationId);
            
            List<Customer> results = query.getResultList();
            return results.isEmpty() ? null : results.get(0);
            
        } catch (Exception e) {
            LOG.error("Error finding customer by email: " + email, e);
            throw new NGOPBaseException("Database error");
        }
    }
    
    @Override
    public List<Customer> getActiveCustomersByOrganization(Integer organizationId) {
        try {
            TypedQuery<Customer> query = entityManager.createQuery(
                "SELECT c FROM Customer c " +
                "WHERE c.organizationId = :orgId AND c.active = true " +
                "ORDER BY c.createdDate DESC",
                Customer.class);
            
            query.setParameter("orgId", organizationId);
            return query.getResultList();
            
        } catch (Exception e) {
            LOG.error("Error finding active customers for org: " + organizationId, e);
            throw new NGOPBaseException("Database error");
        }
    }
    
    @Override
    public Customer updateCustomerKycStatus(Integer customerId, String kycStatus) {
        try {
            Customer customer = findById(customerId);
            if (customer == null) {
                throw new NGOPBaseException("Customer not found");
            }
            
            customer.setKycStatus(kycStatus);
            if ("APPROVED".equals(kycStatus)) {
                customer.setActive(true);
            }
            
            return save(customer);
            
        } catch (Exception e) {
            LOG.error("Error updating KYC status for customer: " + customerId, e);
            throw new NGOPBaseException("Database error");
        }
    }
    
    @Override
    public Long getCustomerCountByOrganization(Integer organizationId) {
        try {
            TypedQuery<Long> query = entityManager.createQuery(
                "SELECT COUNT(c) FROM Customer c WHERE c.organizationId = :orgId",
                Long.class);
            
            query.setParameter("orgId", organizationId);
            return query.getSingleResult();
            
        } catch (Exception e) {
            LOG.error("Error counting customers for org: " + organizationId, e);
            return 0L;
        }
    }
    
    @Override
    protected boolean isNew(Customer entity) {
        return entity.getCustomerId() == null;
    }
}

Query Patterns

Common query patterns used throughout the Dione application, with organization isolation and performance considerations.

Multi-Tenant Query Pattern

Organization-Aware Queries

// Always include organization isolation
public List<Payment> getPaymentsByCustomer(Integer customerId, Integer organizationId) {
    TypedQuery<Payment> query = entityManager.createQuery(
        "SELECT p FROM Payment p " +
        "WHERE p.customerId = :customerId " +
        "AND p.organizationId = :orgId " +  // Critical for multi-tenancy
        "ORDER BY p.createdDate DESC",
        Payment.class);
    
    query.setParameter("customerId", customerId);
    query.setParameter("orgId", organizationId);
    
    return query.getResultList();
}

// Pagination with organization isolation
public Page<Payment> getPaymentsPaginated(Integer organizationId, int page, int size) {
    // Count query
    TypedQuery<Long> countQuery = entityManager.createQuery(
        "SELECT COUNT(p) FROM Payment p WHERE p.organizationId = :orgId",
        Long.class);
    countQuery.setParameter("orgId", organizationId);
    Long total = countQuery.getSingleResult();
    
    // Data query with pagination
    TypedQuery<Payment> dataQuery = entityManager.createQuery(
        "SELECT p FROM Payment p " +
        "WHERE p.organizationId = :orgId " +
        "ORDER BY p.createdDate DESC",
        Payment.class);
    dataQuery.setParameter("orgId", organizationId);
    dataQuery.setFirstResult(page * size);
    dataQuery.setMaxResults(size);
    
    List<Payment> payments = dataQuery.getResultList();
    return new Page<>(payments, total, page, size);
}

Named Queries

SqlServerQueries.java - Query Constants

public class SqlServerQueries {
    
    // Customer Queries
    public static final String LOOKUP_CUSTOMER_BY_EMAIL = 
        "SELECT c FROM Customer c " +
        "WHERE c.email = :email AND c.organizationId = :orgId";
    
    public static final String FIND_ACTIVE_CUSTOMERS_BY_ORG = 
        "SELECT c FROM Customer c " +
        "WHERE c.organizationId = :orgId AND c.active = true " +
        "ORDER BY c.lastLoginDate DESC";
    
    // Payment Queries
    public static final String FIND_PAYMENTS_BY_STATUS = 
        "SELECT p FROM Payment p " +
        "WHERE p.organizationId = :orgId AND p.status = :status " +
        "ORDER BY p.createdDate DESC";
    
    public static final String FIND_PAYMENTS_BY_DATE_RANGE = 
        "SELECT p FROM Payment p " +
        "WHERE p.organizationId = :orgId " +
        "AND p.createdDate BETWEEN :startDate AND :endDate " +
        "ORDER BY p.createdDate DESC";
    
    // Reporting Queries
    public static final String PAYMENT_SUMMARY_BY_CURRENCY = 
        "SELECT p.fromCurrency, COUNT(p), SUM(p.amount) " +
        "FROM Payment p " +
        "WHERE p.organizationId = :orgId " +
        "AND p.status = 'COMPLETED' " +
        "AND p.processedDate BETWEEN :startDate AND :endDate " +
        "GROUP BY p.fromCurrency";
}

Native SQL Queries

Complex Reporting Queries

// Sometimes you need native SQL for complex reporting
public List<PaymentSummary> getPaymentSummaryReport(Integer organizationId, 
                                                   Date startDate, Date endDate) {
    
    String sql = """
        SELECT 
            p.from_currency,
            p.to_currency,
            COUNT(*) as transaction_count,
            SUM(p.amount) as total_amount,
            AVG(p.exchange_rate) as avg_exchange_rate,
            MIN(p.created_date) as first_transaction,
            MAX(p.created_date) as last_transaction
        FROM payments p
        WHERE p.organization_id = :orgId
        AND p.status = 'COMPLETED'
        AND p.created_date BETWEEN :startDate AND :endDate
        GROUP BY p.from_currency, p.to_currency
        ORDER BY total_amount DESC
        """;
    
    Query query = entityManager.createNativeQuery(sql);
    query.setParameter("orgId", organizationId);
    query.setParameter("startDate", startDate);
    query.setParameter("endDate", endDate);
    
    @SuppressWarnings("unchecked")
    List<Object[]> results = query.getResultList();
    
    return results.stream()
        .map(row -> new PaymentSummary(
            (String) row[0],     // from_currency
            (String) row[1],     // to_currency
            (Integer) row[2],    // transaction_count
            (BigDecimal) row[3], // total_amount
            (BigDecimal) row[4], // avg_exchange_rate
            (Date) row[5],       // first_transaction
            (Date) row[6]        // last_transaction
        ))
        .collect(Collectors.toList());
}

Performance & Optimization

Common performance issues and optimization strategies for the Dione database layer.

Common Performance Bottlenecks

🚫 N+1 Query Problem

Lazy loading can cause excessive database queries. Use JOIN FETCH strategically.

🚫 Missing Indexes

Organization ID + common filter columns need indexes for multi-tenant queries.

🚫 Large Result Sets

Payment queries can return thousands of records. Always implement pagination.

🚫 Connection Pool Exhaustion

Long-running transactions can exhaust the connection pool during peak times.

Query Optimization Examples

Optimized Query Patterns

// BAD: N+1 query problem
public List<Customer> getCustomersWithPayments(Integer organizationId) {
    List<Customer> customers = getCustomers(organizationId);
    for (Customer customer : customers) {
        // This triggers a separate query for each customer!
        customer.getPayments().size(); 
    }
    return customers;
}

// GOOD: Use JOIN FETCH to load related data in one query
public List<Customer> getCustomersWithPayments(Integer organizationId) {
    TypedQuery<Customer> query = entityManager.createQuery(
        "SELECT DISTINCT c FROM Customer c " +
        "LEFT JOIN FETCH c.payments " +
        "WHERE c.organizationId = :orgId",
        Customer.class);
    
    query.setParameter("orgId", organizationId);
    return query.getResultList();
}

// For large datasets, use pagination
public Page<Customer> getCustomersWithPaymentsPaginated(Integer organizationId, 
                                                       int page, int size) {
    // First, get customer IDs with pagination
    TypedQuery<Integer> idQuery = entityManager.createQuery(
        "SELECT c.customerId FROM Customer c " +
        "WHERE c.organizationId = :orgId " +
        "ORDER BY c.createdDate DESC",
        Integer.class);
    idQuery.setParameter("orgId", organizationId);
    idQuery.setFirstResult(page * size);
    idQuery.setMaxResults(size);
    
    List<Integer> customerIds = idQuery.getResultList();
    
    if (customerIds.isEmpty()) {
        return new Page<>(Collections.emptyList(), 0L, page, size);
    }
    
    // Then fetch full objects with relationships
    TypedQuery<Customer> query = entityManager.createQuery(
        "SELECT DISTINCT c FROM Customer c " +
        "LEFT JOIN FETCH c.payments " +
        "WHERE c.customerId IN :ids " +
        "ORDER BY c.createdDate DESC",
        Customer.class);
    query.setParameter("ids", customerIds);
    
    List<Customer> customers = query.getResultList();
    
    // Get total count
    Long total = getCustomerCount(organizationId);
    
    return new Page<>(customers, total, page, size);
}

Index Strategy

Critical Database Indexes

-- Multi-tenant isolation indexes (most important)
CREATE INDEX IX_customers_org_email ON customers(organization_id, email);
CREATE INDEX IX_payments_org_status ON payments(organization_id, status);
CREATE INDEX IX_payments_org_customer ON payments(organization_id, customer_id);

-- Date range query indexes
CREATE INDEX IX_payments_created_date ON payments(created_date);
CREATE INDEX IX_payments_org_date ON payments(organization_id, created_date);

-- Status-based queries
CREATE INDEX IX_payments_status_date ON payments(status, created_date);
CREATE INDEX IX_customers_org_active ON customers(organization_id, active);

-- Reporting queries
CREATE INDEX IX_payments_org_currency_date ON payments(organization_id, from_currency, created_date);
CREATE INDEX IX_payments_org_amount ON payments(organization_id, amount) WHERE status = 'COMPLETED';

-- Unique constraints for business rules
CREATE UNIQUE INDEX UX_customers_org_email ON customers(organization_id, email);
CREATE UNIQUE INDEX UX_payments_reference ON payments(reference);

Database Debugging

Tools and techniques for diagnosing database-related issues in the Dione platform.

SQL Server Monitoring

Performance Monitoring Queries

-- Check currently running queries
SELECT 
    s.session_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS query_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.database_id = DB_ID('NGOP_DB')
ORDER BY r.total_elapsed_time DESC;

-- Check blocking queries
SELECT 
    blocking.session_id AS blocking_session,
    blocked.session_id AS blocked_session,
    blocking_text.text AS blocking_query,
    blocked_text.text AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text;

-- Check connection pool usage
SELECT 
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS connection_count,
    SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) AS active_connections,
    SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS idle_connections
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('NGOP_DB')
GROUP BY database_id;

JPA/Hibernate Debugging

Hibernate SQL Logging

# Enable SQL logging in development
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.use_sql_comments=true

# Log parameter values
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

# Monitor connection pool
logging.level.com.mchange.v2.c3p0=DEBUG

Common Database Issues

Troubleshooting Checklist

# 1. Check connection pool health
grep "c3p0" /opt/jboss/standalone/log/server.log | tail -20

# 2. Look for SQL exceptions
grep "SQLException\|JDBCException" /opt/jboss/standalone/log/server.log

# 3. Monitor query performance
grep "slow query" /opt/jboss/standalone/log/server.log

# 4. Check transaction timeouts
grep "TransactionTimeout\|LockTimeoutException" /opt/jboss/standalone/log/server.log

# 5. Monitor entity manager lifecycle
grep "EntityManager\|PersistenceContext" /opt/jboss/standalone/log/server.log

🎯 Database Best Practices

Organization Isolation: Always include organization_id in business queries
Connection Management: Use short-lived transactions and proper connection pooling
Query Optimization: Use indexes effectively and avoid N+1 query problems
Data Types: Use DECIMAL for money, DATETIME2 for timestamps
Monitoring: Log slow queries and monitor connection pool health
Pagination: Always paginate large result sets