Redis Caching Implementation Plan

Status: Ready for Implementation
Expected Impact: 10-30x performance improvement (22 → 600 documents/min)
Estimated Effort: 2-3 days
Monthly Cost: ~$50 for Redis hosting

📋 Overview

This document outlines where to add Redis caching and cache invalidation across CONA to achieve the performance goals outlined in the Stage 1 optimization plan. Two Layers of Caching:

Core Package Functions (CRITICAL for Temporal workers) - packages/core/src/services/
Webapp Server Actions (For UI/API) - apps/webapp/app/lib/actions/

All affected files have been marked with inline TODO comments showing exactly where to add caching logic. ⚠️ IMPORTANT: Most queries come from Temporal workers, so caching in the core package has the biggest impact!

🎯 Core Package Caching (FOR TEMPORAL WORKERS)

Priority 0: MOST CRITICAL (For Workflow Performance)

These are called by Temporal workflows/activities and have the BIGGEST impact:

fetchPostingMatrixRules() - Core Package
- File: packages/core/src/services/fetch-posting-matrix-rules.ts
- Function: fetchPostingMatrixRules()
- Cache Key: posting_matrix_rules:${organizationId}:${dimensionLabel}:${triggerEventSlug}
- TTL: 3600 seconds (1 hour)
- Impact: Called 600+ times per 100 documents (100ms → 5ms = 20x faster)
- Savings: 57 seconds per 100 documents
- Status: ✅ TODO comments added (lines 54-94 & 191-206)
fetchGlDimensions() - Core Package
- File: packages/core/src/services/fetch-gl-dimensions.ts
- Function: fetchGlDimensions()
- Cache Key: gl_dimensions_list:${organizationId}
- TTL: 3600 seconds (1 hour)
- Impact: Called once per document (30ms → 3ms = 10x faster)
- Savings: 2.7 seconds per 100 documents
- Status: ✅ TODO comments added (lines 8-38)

See TEMPORAL_WORKER_CACHING_GUIDE.md for detailed worker-specific implementation guide.

🎯 Implementation Priority

Priority 1: CRITICAL PATH (🔴 Red Priority)

Impact: 100ms → 5ms per call (20x faster) These are called for EVERY document during accounting impact calculation:

GL Dimensions with Posting Matrix
- File: apps/webapp/app/lib/actions/gl_dimensions/get-gl-dimensions.ts
- Function: getGlDimensions()
- Cache Key: gl_dimensions:${organizationId}:${triggerEventId}:${includePostingMatrix}:${includeDeleted}
- TTL: 3600 seconds (1 hour)
Individual Posting Matrix Lookup
- File: apps/webapp/app/lib/actions/posting_matrix/get-posting-matrix.ts
- Function: getMatrix(id)
- Cache Key: posting_matrix:${id}:${organizationId}
- TTL: 3600 seconds (1 hour)
Chart of Accounts List
- File: apps/webapp/app/lib/actions/chart_of_accounts/list-chart-of-accounts.ts
- Function: listChartOfAccounts()
- Cache Key: chart_of_accounts:list:${organizationId}:${onlyReconcileAccounts}
- TTL: 1800 seconds (30 minutes)
Chart of Accounts Search
- File: apps/webapp/app/lib/actions/chart_of_accounts/search-chart-of-accounts.ts
- Function: searchChartOfAccounts()
- Cache Key: chart_of_accounts:search:${organizationId}
- TTL: 1800 seconds (30 minutes)

Priority 2: HIGH FREQUENCY (🟡 Yellow Priority)

Impact: 20-50ms → 2-5ms per call These are called frequently but not on every document:

Organization Details
- File: apps/webapp/app/lib/actions/organization/get-organisation-details.ts
- Function: getOrganizationDetails()
- Cache Key: organization:details:${organizationId}
- TTL: 3600 seconds (1 hour)
Tool/Integration Settings
- File: apps/webapp/app/lib/actions/tools/get-tool-settings.ts
- Function: getToolSettings(toolSlug)
- Cache Key: tool_settings:${organizationId}:${toolSlug}
- TTL: 1800 seconds (30 minutes)
Chart of Account by ID
- File: apps/webapp/app/lib/actions/chart_of_accounts/get-chart-of-account-by-id.ts
- Function: getChartOfAccountById()
- Cache Key: chart_of_accounts:id:${organizationId}:${id}
- TTL: 1800 seconds (30 minutes)
Chart of Account by Number
- File: apps/webapp/app/lib/actions/chart_of_accounts/get-chart-of-account-id-by-number.ts
- Function: getAccountIdByNumber()
- Cache Key: chart_of_accounts:number:${organizationId}:${accountNumber}
- TTL: 1800 seconds (30 minutes)
Posting Matrices Grouped by Trigger Event
- File: apps/webapp/app/lib/actions/posting_matrix/get-posting-matrix-grouped-by-trigger-event.ts
- Function: getPostingMatricesGroupedByTriggerEvent()
- Cache Key: posting_matrices_grouped:${organizationId}
- TTL: 3600 seconds (1 hour)

🔄 Cache Invalidation Map

When Posting Matrix Changes

Affected Actions:

save-posting-matrix.ts (create/update)
delete-posting-matrix.ts (delete)
create-posting-matrix.ts (create)

Invalidate These Cache Patterns:

// 1. Webapp server action caches
await redis.del(`posting_matrix:${matrixId}:${organizationId}`);
await redis.del(`posting_matrices_grouped:${organizationId}`);
const glKeys = await redis.keys(`gl_dimensions:${organizationId}:*`);
if (glKeys.length > 0) await redis.del(...glKeys);

// 2. Temporal worker caches (CRITICAL - don't forget these!)
const postingMatrixRuleKeys = await redis.keys(`posting_matrix_rules:${organizationId}:*`);
if (postingMatrixRuleKeys.length > 0) await redis.del(...postingMatrixRuleKeys);
await redis.del(`gl_dimensions_list:${organizationId}`);

Files to Modify:

✅ apps/webapp/app/lib/actions/posting_matrix/save-posting-matrix.ts (line 142)
✅ apps/webapp/app/lib/actions/posting_matrix/delete-posting-matrix.ts (line 65)
✅ apps/webapp/app/lib/actions/posting_matrix/create-posting-matrix.ts (line 29)

IMPORTANT: These mutations must invalidate BOTH webapp AND worker cache keys!

When Chart of Accounts Changes

Affected Actions:

create-chart-of-account.ts (create)
update-chart-of-account.ts (update)
Any chart deletion actions

Invalidate These Cache Patterns:

// Wildcard approach (simpler, recommended)
const chartKeys = await redis.keys(`chart_of_accounts:*:${organizationId}:*`);
if (chartKeys.length > 0) await redis.del(...chartKeys);

// ALSO invalidate GL dimensions if chart is referenced in posting rules
// (update-chart-of-account.ts updates posting_matrix_rule, lines 62-70)
const glKeys = await redis.keys(`gl_dimensions:${organizationId}:*`);
if (glKeys.length > 0) await redis.del(...glKeys);

Files to Modify:

✅ apps/webapp/app/lib/actions/chart_of_accounts/create-chart-of-account.ts (line 48)
✅ apps/webapp/app/lib/actions/chart_of_accounts/update-chart-of-account.ts (line 75)
✅ apps/webapp/app/lib/actions/chart_of_accounts/delete-chart-of-account.ts (line 31)

When Organization Settings Change

Affected Actions:

update-organisation-onboarding-complete.ts
Any organization update actions (name, currency, timezone, etc.)

Invalidate This Cache Key:

await redis.del(`organization:details:${organizationId}`);

Files to Modify:

✅ apps/webapp/app/lib/actions/organization/update-organisation-onboarding-complete.ts (line 31)
🔲 Search for other organization update actions

When Tool Settings Change

Affected Actions:

update-tool-settings.ts

Invalidate This Cache Key:

if (result.success) {
  await redis.del(`tool_settings:${organizationId}:${toolSlug}`);
}

Files to Modify:

✅ apps/webapp/app/lib/actions/tools/update-tool-settings.ts (line 40)

🏗️ Implementation Pattern

Basic Caching Pattern

export async function myServerAction(params: Params): Promise<ActionResponse<Data>> {
  try {
    const { organizationId } = await requireAuth();

    // 1. Build cache key
    const cacheKey = `my_resource:${organizationId}:${param1}:${param2}`;

    // 2. Try to get from cache
    const cached = await redis.get(cacheKey);
    if (cached) {
      const data = JSON.parse(cached);
      return {
        success: true,
        message: "Retrieved from cache",
        data,
      };
    }

    // 3. Fetch from database (existing logic)
    const data = await prisma.myTable.findMany({ ... });

    // 4. Store in cache with TTL
    await redis.setex(cacheKey, 3600, JSON.stringify(data));

    // 5. Return data
    return {
      success: true,
      message: "Retrieved from database",
      data,
    };
  } catch (error) {
    // ... existing error handling
  }
}

Cache Invalidation Pattern

export async function updateMyResource(data: UpdateData): Promise<ActionResponse<Data>> {
  try {
    const { organizationId } = await requireAuth();

    // ... existing database update logic ...

    // AFTER successful update, invalidate caches
    const cacheKeysToInvalidate = [
      `my_resource:${organizationId}:${resourceId}`,
      `my_resource_list:${organizationId}`,
    ];

    await redis.del(...cacheKeysToInvalidate);

    // For wildcard invalidation:
    const keys = await redis.keys(`my_resource:${organizationId}:*`);
    if (keys.length > 0) await redis.del(...keys);

    return {
      success: true,
      message: "Resource updated",
      data: result,
    };
  } catch (error) {
    // ... existing error handling
  }
}

🛠️ Setup Requirements

1. Get Redis Credentials

If using Supabase Redis/Upstash Integration:

Go to your Supabase Dashboard → Project Settings → Integrations
Find Redis/Upstash integration
Copy connection details:
- Redis Host (e.g., your-project.upstash.io)
- Redis Port (usually 6379)
- Redis Password

Connection URL format:

redis://default:your-password@your-host.upstash.io:6379

For SSL/TLS (Upstash uses this):

rediss://default:your-password@your-host.upstash.io:6379

2. Create Redis Client

Create a Redis client utility:

// apps/webapp/app/lib/redis/client.ts
import { Redis } from "ioredis";

const redis = new Redis({
  host: process.env.REDIS_HOST || "localhost",
  port: parseInt(process.env.REDIS_PORT || "6379"),
  password: process.env.REDIS_PASSWORD,
  // For Upstash, enable TLS
  tls: process.env.REDIS_TLS === "true" ? {} : undefined,
  retryStrategy: (times) => {
    const delay = Math.min(times * 50, 2000);
    return delay;
  },
});

export default redis;

Or use connection URL (simpler for Upstash):

// apps/webapp/app/lib/redis/client.ts
import { Redis } from "ioredis";

const redis = new Redis(process.env.REDIS_URL || "redis://localhost:6379");

export default redis;

3. Environment Variables

Add to .env: Option A: Individual credentials

REDIS_HOST=your-project.upstash.io
REDIS_PORT=6379
REDIS_PASSWORD=your-secure-password
REDIS_TLS=true  # For Upstash/production

Option B: Connection URL (recommended for Upstash)

REDIS_URL=rediss://default:your-password@your-project.upstash.io:6379

For Temporal Workers: Add the same environment variables to your worker configuration (apps/temporal-workers/.env)

4. Create Redis Client for Core Package (Temporal Workers)

Since workers use core package functions, also create a Redis client there:

// packages/core/src/utils/redis-client.ts (NEW FILE)
import { Redis } from "ioredis";

const redis = new Redis(process.env.REDIS_URL || "redis://localhost:6379");

export default redis;

🚨 Important Considerations

1. Cache Invalidation is Critical

Always invalidate related caches when data changes
Missing invalidation = stale data = bugs
Use wildcard invalidation for safety when unsure

2. Error Handling

try {
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);
} catch (cacheError) {
  // Log but don't fail - fall through to database
  await log({
    message: "Redis cache error, falling back to database",
    level: "warning",
    error: cacheError,
  });
}
// Continue with database fetch

3. Cache Key Consistency

Always include organizationId in cache keys
Use consistent separator (: recommended)
Document cache key patterns

4. TTL Selection

Posting matrix rules: 1 hour (rarely change)
Chart of accounts: 30 minutes (moderate change)
Organization settings: 1 hour (rarely change)
Adjust based on real-world usage

📚 Reference Files

All files with inline TODO comments:

Read Operations (Add Caching)

apps/webapp/app/lib/actions/gl_dimensions/get-gl-dimensions.ts (line 64)
apps/webapp/app/lib/actions/posting_matrix/get-posting-matrix.ts (line 30)
apps/webapp/app/lib/actions/posting_matrix/get-posting-matrix-grouped-by-trigger-event.ts (line 31)
apps/webapp/app/lib/actions/chart_of_accounts/list-chart-of-accounts.ts (line 18)
apps/webapp/app/lib/actions/chart_of_accounts/search-chart-of-accounts.ts (line 14)
apps/webapp/app/lib/actions/chart_of_accounts/get-chart-of-account-by-id.ts (line 18)
apps/webapp/app/lib/actions/chart_of_accounts/get-chart-of-account-id-by-number.ts (line 18)
apps/webapp/app/lib/actions/organization/get-organisation-details.ts (line 28)
apps/webapp/app/lib/actions/tools/get-tool-settings.ts (line 16)

Write Operations (Add Cache Invalidation)

apps/webapp/app/lib/actions/posting_matrix/save-posting-matrix.ts (line 142)
apps/webapp/app/lib/actions/posting_matrix/delete-posting-matrix.ts (line 65)
apps/webapp/app/lib/actions/posting_matrix/create-posting-matrix.ts (line 29)
apps/webapp/app/lib/actions/chart_of_accounts/create-chart-of-account.ts (line 48)
apps/webapp/app/lib/actions/chart_of_accounts/update-chart-of-account.ts (line 75)
apps/webapp/app/lib/actions/chart_of_accounts/delete-chart-of-account.ts (line 31)
apps/webapp/app/lib/actions/tools/update-tool-settings.ts (line 40)
apps/webapp/app/lib/actions/organization/update-organisation-onboarding-complete.ts (line 31)

🎯 Success Criteria

Performance Metrics

✅ Posting matrix fetch: 100ms → 5ms
✅ Chart of accounts fetch: 50ms → 5ms
✅ Overall processing: 400ms → 150ms per document
✅ Throughput: 22 → 600 documents/min (27x improvement)

Reliability Metrics

✅ Cache hit rate > 90% after warm-up
✅ No stale data bugs in production
✅ Database connection usage reduced by 80%+
✅ Redis availability > 99.9%

📌 Important: Redis FDW vs Direct Connection

Supabase Redis Wrapper (FDW) - Documentation

❌ Not for this use case
Purpose: Query Redis data FROM Postgres using SQL
Use case: SELECT * FROM redis.my_data; in SQL queries
Read-only access

Direct Redis Connection (What we need)

✅ This is what we’re implementing
Purpose: Application-level caching from Node.js/Next.js
Use case: await redis.get('cache_key') in TypeScript code
Read and write access
Bypasses Postgres entirely

However: If you have Supabase Redis integration, use those same credentials for direct connection!

Getting Started

Core CONA Logic

Development Setup

Guides

E2E Tests

Integrations

RFC

Redis caching implementation plan

Redis Caching Implementation Plan

📋 Overview

🎯 Core Package Caching (FOR TEMPORAL WORKERS)

Priority 0: MOST CRITICAL (For Workflow Performance)

🎯 Implementation Priority

Priority 1: CRITICAL PATH (🔴 Red Priority)

Priority 2: HIGH FREQUENCY (🟡 Yellow Priority)

🔄 Cache Invalidation Map

When Posting Matrix Changes

When Chart of Accounts Changes

When Organization Settings Change

When Tool Settings Change

🏗️ Implementation Pattern

Basic Caching Pattern

Cache Invalidation Pattern

🛠️ Setup Requirements

1. Get Redis Credentials

2. Create Redis Client

3. Environment Variables

4. Create Redis Client for Core Package (Temporal Workers)

🚨 Important Considerations

1. Cache Invalidation is Critical

2. Error Handling

3. Cache Key Consistency

4. TTL Selection

📚 Reference Files

Read Operations (Add Caching)

Write Operations (Add Cache Invalidation)

🎯 Success Criteria

Performance Metrics

Reliability Metrics

📌 Important: Redis FDW vs Direct Connection

​Redis Caching Implementation Plan

​📋 Overview

​🎯 Core Package Caching (FOR TEMPORAL WORKERS)

​Priority 0: MOST CRITICAL (For Workflow Performance)

​🎯 Implementation Priority

​Priority 1: CRITICAL PATH (🔴 Red Priority)

​Priority 2: HIGH FREQUENCY (🟡 Yellow Priority)

​🔄 Cache Invalidation Map

​When Posting Matrix Changes

​When Chart of Accounts Changes

​When Organization Settings Change

​When Tool Settings Change

​🏗️ Implementation Pattern

​Basic Caching Pattern

​Cache Invalidation Pattern

​🛠️ Setup Requirements

​1. Get Redis Credentials

​2. Create Redis Client

​3. Environment Variables

​4. Create Redis Client for Core Package (Temporal Workers)

​🚨 Important Considerations

​1. Cache Invalidation is Critical

​2. Error Handling

​3. Cache Key Consistency

​4. TTL Selection

​📚 Reference Files

​Read Operations (Add Caching)

​Write Operations (Add Cache Invalidation)

​🎯 Success Criteria

​Performance Metrics

​Reliability Metrics

​📌 Important: Redis FDW vs Direct Connection

Redis Caching Implementation Plan

📋 Overview

🎯 Core Package Caching (FOR TEMPORAL WORKERS)

Priority 0: MOST CRITICAL (For Workflow Performance)

🎯 Implementation Priority

Priority 1: CRITICAL PATH (🔴 Red Priority)

Priority 2: HIGH FREQUENCY (🟡 Yellow Priority)

🔄 Cache Invalidation Map

When Posting Matrix Changes

When Chart of Accounts Changes

When Organization Settings Change

When Tool Settings Change

🏗️ Implementation Pattern

Basic Caching Pattern

Cache Invalidation Pattern

🛠️ Setup Requirements

1. Get Redis Credentials

2. Create Redis Client

3. Environment Variables

4. Create Redis Client for Core Package (Temporal Workers)

🚨 Important Considerations

1. Cache Invalidation is Critical

2. Error Handling

3. Cache Key Consistency

4. TTL Selection

📚 Reference Files

Read Operations (Add Caching)

Write Operations (Add Cache Invalidation)

🎯 Success Criteria

Performance Metrics

Reliability Metrics

📌 Important: Redis FDW vs Direct Connection