// Query hits Supabase READ REPLICA
const document = await prisma.documents.findUnique({
  where: { id: documentId },
  include: {
    line_items: true, // ❌ Returns [] due to replica lag!
  },
});

// Can't distinguish between:
// 1. Legitimate empty line items
// 2. Missing line items due to replica lag
if (document.line_items.length === 0) {
  // ❌ Skips GL entry creation or creates incomplete entries
}

// packages/database/src/direct-client.ts (NEW)
import { PrismaClient } from "../generated/client";

export const prismaDirect = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DIRECT_URL, // Port 5432 (primary)
    },
  },
});

// packages/core/src/documents/get-document-by-id.ts
export async function getDocumentById({
  id,
  organizationId,
  useDirect = false, // ✅ New parameter
}: GetDocumentByIdParams) {
  // Switch between pooled and direct connection
  const client = useDirect ? prismaDirect : prisma;

  const document = await client.documents.findUnique({
    // ... includes line_items and all relations
  });
}

// packages/core/src/services/create-accounting-impact.ts
// CRITICAL: Always use direct connection for accounting operations
const document = await getDocumentById({
  id: documentId,
  organizationId,
  useDirect: true, // ✅ Bypasses replicas - gets complete data
});

// Also use direct connection for allocations
const allocations = await prismaDirect.document_payment_allocations.findMany({
  where: { document_id: documentId },
});

// packages/temporal-workflows/src/retry-configs.ts
export const READ_AFTER_WRITE_RETRY: RetryPolicy = {
  initialInterval: "1s", // Quick first retry
  backoffCoefficient: 1.5, // Gentle backoff
  maximumInterval: "10s", // Cap at 10 seconds
  maximumAttempts: 8, // 8 attempts over ~40 seconds
};

// BEFORE: Sleep happened AFTER re-query (useless)
const docs = await findExistingDocs(...);
await workflow.sleep("500ms"); // ❌ Too late!

// AFTER: Sleep happens BEFORE re-query (effective)
await workflow.sleep("500ms"); // ✅ Gives replicas time
const docs = await findExistingDocs(...);

# Pooled connection via Supavisor (read replicas)
DATABASE_URL=postgresql://postgres.xxx:6543/postgres?pgbouncer=true

# Primary-routed Supavisor transaction connection
DIRECT_URL=postgresql://postgres.xxx:<password>@<region>.pooler.supabase.com:6543/postgres?pgbouncer=true

# Build database package
cd packages/database && pnpm run build

# Build core package
cd packages/core && pnpm run build

# Build temporal workflows
cd packages/temporal-workflows && pnpm run build

# Deploy workers
cd apps/temporal-workers && pnpm run build

# Production - Conservative (serverless)
DIRECT_URL=postgresql://...?connection_limit=3&pool_timeout=30

# Staging - Moderate (dedicated workers)
DIRECT_URL=postgresql://...?connection_limit=5&pool_timeout=20

# Development - Relaxed (local/dedicated)
DIRECT_URL=postgresql://...?connection_limit=10&pool_timeout=15

// This error means queries waited too long for a connection
PrismaClientKnownRequestError: Timed out fetching a new connection from the pool
Error code: P2024

-- Check current database connections
SELECT
  datname,
  usename,
  application_name,
  client_addr,
  state,
  COUNT(*) as connection_count
FROM pg_stat_activity
WHERE datname = 'postgres'
GROUP BY datname, usename, application_name, client_addr, state
ORDER BY connection_count DESC;

-- Check connection limits
SELECT
  setting as max_connections
FROM pg_settings
WHERE name = 'max_connections';

-- Calculate current utilization
SELECT
  COUNT(*) as current_connections,
  (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_connections,
  ROUND(COUNT(*)::numeric / (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') * 100, 2) as utilization_percent
FROM pg_stat_activity
WHERE datname = 'postgres';

SELECT
  d.id,
  d.number,
  d.created_at,
  ot.label as document_type,
  COUNT(gl.id) as gl_entries,
  COUNT(li.id) as line_items
FROM documents d
LEFT JOIN general_ledger gl ON gl.document_id = d.id
LEFT JOIN line_items li ON li.document_id = d.id
LEFT JOIN object_types ot ON ot.id = d.object_type_id
WHERE d.created_at > NOW() - INTERVAL '7 days'
  AND d.is_deleted = FALSE
  AND ot.label IN ('Sales Invoice', 'Sales Credit Note', 'Payment')
GROUP BY d.id, d.number, d.created_at, ot.label
HAVING COUNT(gl.id) = 0 AND COUNT(li.id) > 0
ORDER BY d.created_at DESC;

-- Run on PRIMARY to check replica lag
SELECT
  client_addr,
  state,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
  NOW() - pg_stat_get_wal_receiver_info() AS lag_time
FROM pg_stat_replication;

SELECT
  application_name,
  client_addr,
  state,
  COUNT(*) as connections
FROM pg_stat_activity
WHERE datname = 'postgres'
GROUP BY application_name, client_addr, state
ORDER BY connections DESC;

// Test script: packages/database/src/test-direct.ts
import { prisma } from "@cona/database";
import { prismaDirect } from "@cona/database/direct";

async function testConnections() {
  // Create document with line items
  const doc = await prisma.documents.create({
    data: {
      // ... document data
      line_items: {
        create: [
          { name: "Item 1", quantity: 1, unit_price: 100 },
          { name: "Item 2", quantity: 2, unit_price: 50 },
        ],
      },
    },
  });

  // Query via pooled (may hit replica)
  const docPooled = await prisma.documents.findUnique({
    where: { id: doc.id },
    include: { line_items: true },
  });

  // Query via direct (hits primary)
  const docDirect = await prismaDirect.documents.findUnique({
    where: { id: doc.id },
    include: { line_items: true },
  });

  console.log("Pooled line items:", docPooled?.line_items?.length);
  console.log("Direct line items:", docDirect?.line_items?.length);
  // Direct should ALWAYS have all line items (2 in this case)
}

num_physical_cpus * 2 + 1

// packages/database/src/direct-client.ts
connection_limit: 5; // Low limit - direct connections are precious
pool_timeout: 20; // 20s timeout for complex financial operations
connect_timeout: 10; // 10s for initial connection

// Regular prisma client uses Supabase defaults
connection_limit: Auto-scaled by Supavisor
pool_timeout: 10         // Standard Prisma default

// ❌ BAD: Fetches all fields (including large text fields)
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
});

// ✅ GOOD: Only fetch needed fields
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
  select: {
    id: true,
    number: true,
    total_amount: true,
    status_id: true,
    // Exclude: logo_url, default_memo, large JSON fields
  },
});

// ❌ BAD: Fetches all documents (could be thousands)
const allDocs = await prisma.documents.findMany({
  where: { org_id: organizationId },
});

// ✅ GOOD: Offset-based pagination
const pageSize = 100;
const docs = await prisma.documents.findMany({
  take: pageSize,
  skip: (page - 1) * pageSize,
  where: { org_id: organizationId },
  orderBy: { created_at: "desc" },
});

// ✅ BETTER: Cursor-based pagination (no offset, scales better)
const docs = await prisma.documents.findMany({
  take: pageSize,
  cursor: lastDocumentId ? { id: lastDocumentId } : undefined,
  skip: lastDocumentId ? 1 : 0, // Skip the cursor itself
  where: { org_id: organizationId },
  orderBy: { created_at: "desc" },
});

// ❌ BAD: N individual queries (N round trips)
for (const item of lineItems) {
  await prisma.line_items.create({
    data: item,
  });
}

// ✅ GOOD: Single batch query (1 round trip)
await prisma.line_items.createMany({
  data: lineItems,
  skipDuplicates: true, // Useful for idempotency
});

// ✅ GOOD: Batch updates
await prisma.documents.updateMany({
  where: {
    id: { in: documentIds },
  },
  data: {
    status_id: newStatusId,
  },
});

// ❌ BAD: Multiple separate transactions
await prisma.documents.create({ data: docData });
await prisma.line_items.createMany({ data: lineItems });
await prisma.activity_logs.create({ data: logData });

// ✅ GOOD: Single transaction (atomic + faster)
await prisma.$transaction([
  prisma.documents.create({ data: docData }),
  prisma.line_items.createMany({ data: lineItems }),
  prisma.activity_logs.create({ data: logData }),
]);

// ✅ BETTER: Interactive transaction (for complex logic)
await prisma.$transaction(async (tx) => {
  const doc = await tx.documents.create({ data: docData });

  // Use created doc ID in subsequent queries
  await tx.line_items.createMany({
    data: lineItems.map((item) => ({
      ...item,
      document_id: doc.id,
    })),
  });

  await tx.activity_logs.create({
    data: { ...logData, documents_id: doc.id },
  });
});

// ❌ BAD: N+1 queries (1 for documents + N for line items)
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
});

for (const doc of documents) {
  const lineItems = await prisma.line_items.findMany({
    where: { document_id: doc.id },
  });
  // Process line items...
}

// ✅ GOOD: Single query with JOIN
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
  include: {
    line_items: true,
  },
});

// For financial operations, include with direct connection
const documents = await prismaDirect.documents.findMany({
  where: { org_id: organizationId },
  include: {
    line_items: true, // Gets complete data from primary
  },
});

-- Explain query plan
EXPLAIN ANALYZE
SELECT * FROM documents
WHERE org_id = 'xxx' AND created_at > '2024-01-01';

-- Add index if missing (in migration)
CREATE INDEX idx_documents_org_created
ON documents(org_id, created_at DESC);

model documents {
  id            String   @id @default(cuid())
  org_id        String
  created_at    DateTime @default(now())

  @@index([org_id, created_at(sort: Desc)])
  @@index([org_id, status_id])
  @@index([org_id, object_type_id, created_at(sort: Desc)])
}

// packages/database/src/client.ts
const prisma = new PrismaClient({
  log: [
    { level: "query", emit: "event" },
    { level: "error", emit: "stdout" },
    { level: "warn", emit: "stdout" },
  ],
});

// Log slow queries (> 100ms)
prisma.$on("query", (e) => {
  if (e.duration > 100) {
    console.warn("Slow query detected:", {
      query: e.query,
      duration: `${e.duration}ms`,
      params: e.params,
    });
  }
});

// If using Accelerate
import { PrismaClient } from "@prisma/client/edge";
import { withAccelerate } from "@prisma/extension-accelerate";

const prisma = new PrismaClient().$extends(withAccelerate());

// Cached queries
const users = await prisma.user.findMany({
  cacheStrategy: {
    ttl: 60, // Cache for 60 seconds
    swr: 10, // Stale-while-revalidate
  },
});

// ❌ BAD: Loads large JSON fields unnecessarily
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
  // Loads ALL fields including large custom_properties JSON
});

// ✅ GOOD: Exclude large fields when not needed
const documents = await prisma.documents.findMany({
  where: { org_id: organizationId },
  select: {
    id: true,
    number: true,
    total_amount: true,
    // Exclude: custom_properties, default_memo, logo_url
  },
});

// ✅ When you DO need large fields, only fetch for specific records
const fullDocument = await prisma.documents.findUnique({
  where: { id: documentId },
  // Now fetch everything including large fields
});

// ❌ BAD: Sequential queries (total time = sum of all)
const documents = await prisma.documents.findMany({ where: { ... } });
const customers = await prisma.entities.findMany({ where: { ... } });
const accounts = await prisma.general_ledger_accounts.findMany({ where: { ... } });

// ✅ GOOD: Parallel queries (total time = slowest query)
const [documents, customers, accounts] = await Promise.all([
  prisma.documents.findMany({ where: { ... } }),
  prisma.entities.findMany({ where: { ... } }),
  prisma.general_ledger_accounts.findMany({ where: { ... } }),
]);

// Don't exhaust the limited direct connection pool
// Max 5 concurrent queries with prismaDirect
const [doc1, doc2, doc3] = await Promise.all([
  prismaDirect.documents.findUnique({ where: { id: id1 } }),
  prismaDirect.documents.findUnique({ where: { id: id2 } }),
  prismaDirect.documents.findUnique({ where: { id: id3 } }),
]); // Uses 3 of 5 connections temporarily