> ## Documentation Index
> Fetch the complete documentation index at: https://cona.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Redis caching implementation plan

# 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:**

1. **Core Package Functions** (CRITICAL for Temporal workers) - `packages/core/src/services/`
2. **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:**

1. **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)

2. **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:

1. **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)

2. **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)

3. **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)

4. **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:

5. **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)

6. **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)

7. **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)

8. **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)

9. **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:**

```typescript theme={null}
// 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:**

```typescript theme={null}
// 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:**

```typescript theme={null}
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:**

```typescript theme={null}
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

```typescript theme={null}
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

```typescript theme={null}
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:**

1. Go to your Supabase Dashboard → Project Settings → Integrations
2. Find Redis/Upstash integration
3. 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:

```typescript theme={null}
// 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):**

```typescript theme={null}
// 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**

```bash theme={null}
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)**

```bash theme={null}
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:

```typescript theme={null}
// 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

```typescript theme={null}
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)

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

### Write Operations (Add Cache Invalidation)

1. `apps/webapp/app/lib/actions/posting_matrix/save-posting-matrix.ts` (line 142)
2. `apps/webapp/app/lib/actions/posting_matrix/delete-posting-matrix.ts` (line 65)
3. `apps/webapp/app/lib/actions/posting_matrix/create-posting-matrix.ts` (line 29)
4. `apps/webapp/app/lib/actions/chart_of_accounts/create-chart-of-account.ts` (line 48)
5. `apps/webapp/app/lib/actions/chart_of_accounts/update-chart-of-account.ts` (line 75)
6. `apps/webapp/app/lib/actions/chart_of_accounts/delete-chart-of-account.ts` (line 31)
7. `apps/webapp/app/lib/actions/tools/update-tool-settings.ts` (line 40)
8. `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](https://supabase.com/docs/guides/database/extensions/wrappers/redis)

* ❌ **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!