Database Layer

5.1 Connection Pattern

Single connection, shared across requests:

// lib/db/index.ts
import Database from 'better-sqlite3';

let db: Database | null = null;

export function getDatabase(): Database {
  if (!db) {
    db = new Database('database/maediia.db');
    db.pragma('journal_mode = WAL');  // Better concurrency
  }
  return db;
}

5.2 Query Pattern

Parameterized queries (prevents SQL injection):

// BAD - SQL injection risk
const query = `SELECT * FROM clients WHERE email = '${email}'`;

// GOOD - Parameterized
const stmt = db.prepare('SELECT * FROM clients WHERE email = ?');
const client = stmt.get(email);

5.3 Query Organization

Split by domain:

// lib/db/client-queries.ts
export const ClientQueries = {
  getById: (id: string) => { ... },
  getByEmail: (email: string) => { ... },
  create: (data: InsertClient) => { ... },
  update: (id: string, data: UpdateClient) => { ... },
  delete: (id: string) => { ... },
  list: (options: ListOptions) => { ... },
};

// lib/db/admin-queries.ts
export const AdminQueries = {
  getDashboardStats: () => { ... },
  getRevenueByMonth: () => { ... },
  // ...
};

5.4 Transactions

For multi-step operations:

export function transferService(clientId: string, newPlan: string) {
  const db = getDatabase();
  
  // Start transaction
  const transaction = db.transaction(() => {
    // Step 1: Update client
    const updateClient = db.prepare('UPDATE clients SET plan = ? WHERE id = ?');
    updateClient.run(newPlan, clientId);
    
    // Step 2: Create invoice
    const createInvoice = db.prepare('INSERT INTO invoices ...');
    createInvoice.run(...);
    
    // Step 3: Log activity
    const logActivity = db.prepare('INSERT INTO audit_logs ...');
    logActivity.run(...);
    
    // All succeed or all fail
  });
  
  transaction();
}