Documentation

ARCHITECTURE_PLAN

docs/ARCHITECTURE_PLAN.md

Petunia Architecture Migration Plan

Version: 2.0.0 Last Updated: 2025-12-13 Reference: See ARCHITECTURE.md for ideal state

This document contains the complete migration plan to align the current Petunia codebase with the ideal architecture. Items are prioritized P0 (critical) through P5 (documentation).


✅ Migration Status Summary

PriorityTotal✅ Done🔄 Partial❌ PendingProgress
P0121200100%
P115121287%
P21291279%
P31281371%
P41432929%
P5101000100%
Total755451679%

Quick Status Reference

P0 - Critical (12/12 Complete) ✅

IDItemStatusNotes
P0-1Client.companyId FK✅ DoneFK relation added to schema
P0-2Fix Orphaned CompanyIds✅ Donecron/orphan-cleanup/route.ts runs hourly
P0-3Delete Orphaned Records✅ DoneHandled by orphan-cleanup cron job
P0-4Encrypt MFA Secret✅ Donemfa-secret-encryption.ts + tests + migration
P0-5Encrypt OAuth Tokens✅ Doneoauth-token-encryption.ts + middleware
P0-6Encrypt Integration Tokens✅ Doneintegration-token-encryption.ts + middleware
P0-7Fix Billing Race Condition✅ Donecredit-wallet-service.ts uses $transaction
P0-8Portal.clientId Required FK✅ DoneCommit cca045791
P0-9Portal→Client→Company Chain✅ DoneChain validated via P0-1 + P0-8
P0-10Atomic Webhook Processing✅ DoneidempotencyKey with @unique in schema
P0-11Request Signing✅ Doneinternal-api-auth.ts + request-signing.ts with HMAC-SHA256
P0-12Session Token Security✅ DonesessionRotation.ts with privilege change detection

P1 - High (12/15 Complete)

IDItemStatusNotes
P1-1Redis Rate Limiting✅ Donerate-limiter.ts with Upstash Redis, sliding window
P1-2Product.businessId → companyId❌ PendingRequires schema migration
P1-3Service.businessId → companyId❌ PendingRequires schema migration
P1-4Lead Merge Transaction✅ DoneleadService.prisma.ts:929 uses $transaction
P1-5Missing FK Indexes✅ Done50+ @@index directives in schema
P1-6PortalAnalyticsSummary clientId🔄 PartialNOT NULL constraint pending
P1-7Demo Portal Data Chain✅ DoneDemo data structure exists
P1-8Seed KnowledgeBaseEntry✅ DoneKnowledge base seeding in place
P1-9Standardize API Response✅ Doneapi-response.ts with requestId and pagination
P1-10VACUUM ANALYZE✅ DoneAutomated via database maintenance
P1-11Connection Pool Monitoring✅ DoneprometheusMetrics.ts tracks pool stats
P1-12Graceful Shutdown✅ Donegraceful-shutdown.ts with SIGTERM handler
P1-13Database Query Timeout✅ Donedatabase.ts has statement_timeout configured
P1-14Circuit Breaker✅ Doneapi-circuit-breaker.ts + circuit-breaker.ts
P1-15Structured Error Codes✅ Doneerror-codes.ts with domain-specific codes

P2 - Medium (9/12 Complete)

IDItemStatusNotes
P2-1Remove Type Safety Bypasses❌ PendingOngoing improvement
P2-2Deduplication Algorithm✅ Donededuplication-service.ts with confidence scoring
P2-3Admin Status Cache TTL✅ DonewithAuth.ts has 60-second cache
P2-4Device Fingerprinting✅ Donedevice-fingerprint.ts with trust levels
P2-5Portal→PortalAIConfig 1:1✅ DoneCommit 818039065
P2-6Rename Business Model🔄 AssessmentDecision needed on naming
P2-7CommissionTransaction Cleanup❌ PendingPolymorphic design intentional
P2-8Multi-Tenant Middleware✅ Donemulti-tenant-middleware.ts
P2-9TypeScript Strict Mode✅ DoneStrict mode enabled in tsconfig
P2-10Date/Time Handling✅ Donedates.ts with comprehensive timezone support
P2-11Zod Validation✅ Done102+ routes have Zod validation
P2-12Proper Enum Types✅ DonePrisma enums in schema

P3 - Standard (8/12 Complete)

IDItemStatusNotes
P3-1Clean Unused Indexes✅ DoneIndex audit completed, unused indexes removed
P3-2Standardize Pagination✅ DoneStandard pagination pattern across APIs
P3-3Audit Log Retention❌ PendingRetention policy definition needed
P3-4API Versioning Doc✅ DoneAPI_VERSIONING.md exists
P3-5Distributed Tracing🔄 PartialSentry tracing configured
P3-6SLO/SLA Definitions✅ DoneapiSlo.ts defines critical SLOs
P3-7Test Coverage Report❌ PendingCoverage reporting setup needed
P3-8Dead Code Audit❌ PendingNeeds tooling setup
P3-9Request ID in Logs✅ DonerequestContext.server.ts
P3-10Log Aggregation✅ DoneCentralized logging via Sentry + Vercel
P3-11Health Check Dependencies✅ Donehealth-checker.ts with comprehensive checks
P3-12Runbooks✅ DoneVarious runbooks created

P4 - Lower (3/14 Complete)

IDItemStatusNotes
P4-1Rename organizationId to portalId❌ PendingSchema migration needed
P4-2KnowledgeBaseEntry companyId❌ Pending
P4-3Vector Namespace Restructure❌ Pending
P4-4Lead.clientId FK🔄 PartialRelation exists, optional
P4-5Consolidate Subscription Fields❌ Pending
P4-6Knowledge Tier Architecture✅ DoneKNOWLEDGE_ARCHITECTURE.md
P4-7Input Validation Consistency🔄 PartialVia P2-11
P4-8N+1 Query Patterns❌ Pending
P4-9Soft Delete Models❌ Pending
P4-10Created/Updated By Fields❌ Pending
P4-11Phone Number Normalization✅ Donephone.ts with E.164 format support
P4-12Email Normalization✅ Doneemail.ts with validation + domain extraction
P4-13Status Enum Consolidation❌ Pending
P4-14Optimistic Locking❌ Pending

P5 - Documentation (10/10 Complete)

IDItemStatusNotes
P5-1Webhook Documentation✅ DoneMerged into API_INTEGRATION_GUIDE.md
P5-2Integration Monitoring✅ DoneINTEGRATION_MONITORING.md
P5-3Voice AI Setup✅ DoneVOICE_AI_SETUP.md
P5-4Call Recording Storage✅ DoneCALL_RECORDINGS.md
P5-5RLS Policies✅ DoneRLS_POLICIES.md
P5-6Developer Guide✅ DoneDEVELOPER_GUIDE.md
P5-7Background Jobs✅ DoneBACKGROUND_JOBS.md
P5-8API Integration Guide✅ DoneAPI_INTEGRATION_GUIDE.md
P5-9Database Migrations✅ DoneDATABASE_MIGRATIONS.md
P5-10Security Guide✅ DoneSECURITY_GUIDE.md

Table of Contents

  1. Priority Overview
  2. P0: Critical - Data Integrity & Security
  3. P1: High - Core Infrastructure
  4. P2: Medium - Type Safety & Model Refinement
  5. P3: Standard - Cleanup & Observability
  6. P4: Lower - Schema & Validation
  7. P5: Documentation
  8. Dependencies Graph
  9. Migration Execution Notes

Priority Overview

PriorityItemsFocus AreaRisk LevelStatus
P012FK constraints, encryption, race conditionsHIGH - Data loss/security✅ 100%
P115Rate limiting, model migration, indexesMEDIUM - Performance/stability✅ 87%
P212Type safety, model renaming, access controlMEDIUM - Code quality✅ 79%
P312Cleanup, standardization, observabilityLOW - Technical debt✅ 71%
P414Schema refinement, validation, optimizationLOW - Enhancement🔄 29%
P510Documentation, runbooks, guidesLOW - Knowledge✅ 100%

Total: 75 migration items | 54 Complete | 5 Partial | 16 Pending (79% overall)


P0: Critical - Data Integrity & Security

These items address data integrity issues, security vulnerabilities, and race conditions that could cause data loss or security breaches.

P0-1: Fix Client.companyId Foreign Key

Problem: Client.companyId is a String @default("default") with no FK constraint. This allows orphaned clients and breaks the entity chain.

Current State:

model Client {
  companyId String @default("default")  // No FK!
}

Target State:

model Client {
  companyId String
  Company   Company @relation(fields: [companyId], references: [id])
}

Steps:

  1. Audit all clients with companyId = "default" (currently 22+)
  2. Create "orphan" company for existing orphans OR assign to correct companies
  3. Add FK constraint in Prisma schema
  4. Run migration with prisma migrate dev
  5. Verify no broken references

Files: /prisma/schema.prisma Depends On: P0-2 (data migration first) Effort: Medium


P0-2: Data Migration - Fix Orphaned Client CompanyIds

Problem: 22+ orphaned clients have companyId = "default" which will break when FK is added.

Steps:

  1. Query all clients with companyId = "default"
  2. For each, determine correct company via UserCompany or create one
  3. Update companyId to valid company
  4. Verify all clients have valid company references
  5. Document any clients that couldn't be assigned

Files: New migration script or use /scripts/cleanup-test-users-raw.ts pattern Depends On: None Effort: Medium


P0-3: Delete Orphaned Test Records

Problem: 22 test clients and associated records pollute production data.

Steps:

  1. Run cleanup script in dry-run mode
  2. Review accounts to delete
  3. Execute with --execute flag
  4. Verify cleanup completed
  5. Run VACUUM ANALYZE

Files: /scripts/cleanup-test-users-raw.ts Depends On: P0-2 Effort: Low


P0-4: Encrypt User.mfaSecret with AES-GCM

Problem: User.mfaSecret stores TOTP secrets that should be encrypted at rest.

Current State: Plaintext in database Target State: AES-256-GCM encrypted with CREDENTIALS_ENCRYPTION_KEY

Steps:

  1. Create encryption utility using existing /lib/auth/encryption.ts pattern
  2. Add mfaSecretEncrypted field to User
  3. Write migration to encrypt existing secrets
  4. Update TOTP verification to decrypt on-the-fly
  5. Remove plaintext field after verification period
  6. Add key rotation support

Files: /prisma/schema.prisma, /lib/utils/totp.ts, /lib/auth/encryption.ts Depends On: None Effort: Medium


P0-5: Encrypt Account OAuth Tokens

Problem: Account.access_token and refresh_token store OAuth tokens in plaintext.

Steps:

  1. Add encrypted token fields
  2. Migrate existing tokens (encrypt on read, write encrypted)
  3. Update OAuth flows to use encrypted tokens
  4. Remove plaintext after migration period
  5. Implement token refresh with encryption

Files: /prisma/schema.prisma, /lib/auth/* Depends On: None Effort: Medium


P0-6: Encrypt Integration OAuth Tokens

Problem: Yelp, Google, Facebook connections store tokens in plaintext.

Models Affected:

  • YelpConnection.accessToken
  • GoogleConnection.accessToken, refreshToken
  • FacebookConnection.pageAccessToken, longLivedToken

Steps:

  1. Use same encryption utility as P0-4/P0-5
  2. Add encrypted fields to each connection model
  3. Migrate existing tokens
  4. Update integration services
  5. Remove plaintext fields

Files: /prisma/schema.prisma, /lib/services/yelp/*, /lib/services/google/*, /lib/services/facebook/* Depends On: P0-4 (shared encryption utility) Effort: High


P0-7: Fix Billing Race Condition

Problem: Wallet credit operations not wrapped in transaction, risking double-spend or lost credits.

Current State:

// Separate queries, not atomic
const wallet = await prisma.creditWallet.findUnique(...);
await prisma.creditWallet.update({ credits: wallet.credits - amount });

Target State:

// Atomic transaction
await prisma.$transaction(async (tx) => {
  const wallet = await tx.creditWallet.findUnique(...);
  if (wallet.credits < amount) throw new InsufficientFundsError();
  await tx.creditWallet.update({ credits: { decrement: amount } });
  await tx.walletTransaction.create(...);
});

Steps:

  1. Identify all wallet mutation operations
  2. Wrap each in $transaction
  3. Add optimistic locking or use decrement operator
  4. Add tests for concurrent operations
  5. Monitor for deadlocks

Files: /lib/services/platform-usage-tracker.ts, /app/api/payments/* Depends On: None Effort: Medium


P0-8: Add Portal.clientId Required FK

Problem: Portal→Client relationship is implicit through UserPortalAccess, not direct FK.

Target State:

model Portal {
  clientId String
  Client   Client @relation(fields: [clientId], references: [id])
}

Steps:

  1. Add optional clientId field first
  2. Migrate data to populate clientId
  3. Make field required
  4. Add FK constraint
  5. Update Portal creation to require clientId

Files: /prisma/schema.prisma Depends On: P0-1, P0-2 (Client integrity first) Effort: Medium


P0-9: Fix Portal→Client→Company Linkage Chain

Problem: The entity chain Portal→Client→Company is broken, making tenant hierarchy unclear.

Steps:

  1. Complete P0-1 (Client.companyId FK)
  2. Complete P0-8 (Portal.clientId FK)
  3. Create database view for full chain
  4. Update TenantAccessService to use chain
  5. Add validation for chain integrity

Files: /lib/services/tenant-access.ts, /prisma/schema.prisma Depends On: P0-1, P0-8 Effort: High


P0-10: Add Atomic Webhook Processing

Problem: Webhook processing has no atomic check-and-create, risking duplicate processing.

Current State: SELECT then INSERT (race window) Target State: INSERT with ON CONFLICT or transaction with locking

Steps:

  1. Add unique constraint on (externalId, providerId, portalId)
  2. Use prisma.create with skipDuplicates or upsert
  3. Add Redis-based idempotency cache for high-volume webhooks
  4. Implement idempotency key header support
  5. Add metrics for duplicate detection

Files: /app/api/webhooks/*/route.ts Depends On: None Effort: Medium


P0-11: Implement Request Signing for Internal APIs

Problem: Internal API calls (cron → API) rely on CRON_SECRET but lack request signing.

Steps:

  1. Implement HMAC-SHA256 request signing
  2. Add timestamp to prevent replay attacks
  3. Create signing middleware
  4. Update all cron routes to verify signatures
  5. Document signing protocol

Files: /app/api/cron/*/route.ts, /lib/middleware/* Depends On: None Effort: Medium


P0-12: Fix Session Token Storage Security

Problem: Session tokens stored without rotation mechanism being enforced consistently.

Steps:

  1. Audit session rotation implementation
  2. Ensure rotation on privilege change (role change, password change)
  3. Add session binding (fingerprint, IP)
  4. Implement session revocation on logout
  5. Add monitoring for session anomalies

Files: /lib/auth/sessionRotation.ts, /proxy.ts Depends On: None Effort: Medium


P1: High - Core Infrastructure

These items improve system stability, performance, and reliability.

P1-1: Implement Redis-Backed Rate Limiting

Problem: Current rate limiting uses Upstash with per-request overhead. Need more robust solution.

Steps:

  1. Design rate limit tiers (auth, api, heavy, webhook)
  2. Implement sliding window algorithm
  3. Add rate limit headers to responses
  4. Create bypass for internal services
  5. Add monitoring dashboard

Files: /proxy.ts, new /lib/rateLimit/* Depends On: None Effort: Medium


P1-2: Move Product.businessId → Product.companyId

Problem: Products belong to Business (Portal-scoped) but should belong to Company (persistent).

Migration Strategy:

  1. Add companyId field (optional)
  2. Populate via Business→Portal→Client→Company chain
  3. Make companyId required
  4. Deprecate businessId
  5. Remove after migration period

Files: /prisma/schema.prisma, product-related services Depends On: P0-9 (chain must be fixed first) Effort: High


P1-3: Move Service.businessId → Service.companyId

Problem: Same as P1-2, Services should persist at Company level.

Steps: Same migration strategy as P1-2

Files: /prisma/schema.prisma, service-related services Depends On: P0-9, P1-2 (same pattern) Effort: High


P1-4: Wrap Lead Merge in Transaction

Problem: Lead merge operations (moving activities, soft delete) not atomic.

Steps:

  1. Wrap merge in $transaction
  2. Add rollback on failure
  3. Add audit log for merge
  4. Test concurrent merge attempts
  5. Add merge history table

Files: /lib/services/lead/leadService.prisma.ts Depends On: None Effort: Low


P1-5: Add Missing FK Indexes

Problem: 5 foreign key columns lack indexes, causing slow JOINs.

Columns Identified:

  1. Message.conversationId
  2. LeadActivity.leadId
  3. ConversationParticipant.conversationId
  4. CallRecord.portalId
  5. WalletTransaction.walletId

Steps:

  1. Add @@index directives to schema
  2. Run migration
  3. Verify query plans improved
  4. Monitor query performance

Files: /prisma/schema.prisma Depends On: None Effort: Low


P1-6: Fix PortalAnalyticsSummary NULL ClientId Records

Problem: Analytics records with NULL clientId can't be attributed to clients.

Steps:

  1. Query records with NULL clientId
  2. Resolve clientId via Portal→Client relationship
  3. Update records
  4. Add NOT NULL constraint
  5. Fix analytics ingestion to require clientId

Files: /prisma/schema.prisma, analytics services Depends On: P0-8 (Portal.clientId must exist) Effort: Medium


P1-7: Create Complete Demo Portal Data Chain

Problem: Demo portal exists but lacks complete data chain for testing.

Required Data:

  • Demo Company
  • Demo Client
  • Demo Portal (PID-DEMO-001)
  • Demo Business (PortalAIConfig)
  • Demo Conversations
  • Demo Leads/Contacts

Steps:

  1. Create seed script for demo data
  2. Include sample conversations
  3. Include sample leads with pipeline stages
  4. Include sample reviews
  5. Add to CI/CD for fresh environments

Files: /prisma/seed.ts, /lib/demo/* Depends On: P0-9 Effort: Medium


P1-8: Seed KnowledgeBaseEntry for Demo Portal

Problem: Demo portal has no knowledge base, limiting AI testing.

Steps:

  1. Create demo knowledge entries
  2. Index in Upstash Vector
  3. Test retrieval
  4. Add sample Q&A pairs
  5. Document demo knowledge structure

Files: /prisma/seed.ts, knowledge services Depends On: P1-7 Effort: Low


P1-9: Standardize API Response Format

Problem: API responses inconsistent across 415+ routes.

Target Format:

interface ApiResponse<T> {
  success: boolean;
  data?: T;
  error?: { code: string; message: string; details?: unknown };
  meta?: { timestamp: string; requestId: string; pagination?: {...} };
}

Steps:

  1. Create response helper functions
  2. Audit existing routes for format
  3. Update routes incrementally
  4. Add response validation in tests
  5. Document in OpenAPI spec

Files: /lib/utils/apiResponse.ts, all route handlers Depends On: None Effort: High (many routes)


P1-10: Run VACUUM ANALYZE on Bloated Tables

Problem: Large tables need maintenance for query performance.

Tables:

  • Message
  • Conversation
  • LeadActivity
  • AuditLog
  • ErrorLog

Steps:

  1. Check table bloat levels
  2. Schedule VACUUM ANALYZE during low traffic
  3. Monitor query performance before/after
  4. Set up autovacuum tuning
  5. Document maintenance schedule

Files: Database maintenance scripts Depends On: None Effort: Low


P1-11: Add Connection Pool Monitoring

Problem: No visibility into Prisma connection pool health.

Steps:

  1. Add pool metrics (active, idle, waiting)
  2. Expose via Prometheus endpoint
  3. Add alerting thresholds
  4. Create Grafana dashboard
  5. Document pool tuning

Files: /lib/services/monitoring/prometheusMetrics.ts Depends On: None Effort: Low


P1-12: Implement Graceful Shutdown

Problem: No graceful shutdown for long-running jobs on deploy.

Steps:

  1. Add SIGTERM handler
  2. Drain BullMQ jobs before exit
  3. Close database connections cleanly
  4. Add health check transition to unhealthy
  5. Test with Vercel deployment

Files: /lib/queue/bullmq-queue.ts, entry points Depends On: None Effort: Medium


P1-13: Add Database Query Timeout

Problem: No query timeout, slow queries can block connections.

Steps:

  1. Add statement_timeout to connection string
  2. Implement query timeout in Prisma middleware
  3. Add timeout metrics
  4. Alert on frequent timeouts
  5. Identify and optimize slow queries

Files: Database connection config, Prisma middleware Depends On: None Effort: Low


P1-14: Implement Circuit Breaker for External APIs

Problem: No circuit breaker for Yelp, Google, Facebook APIs.

Steps:

  1. Implement circuit breaker pattern
  2. Add to all external API calls
  3. Add fallback behavior
  4. Add circuit state metrics
  5. Document failure modes

Files: /lib/services/yelp/*, /lib/services/google/*, /lib/services/facebook/* Depends On: None Effort: Medium


P1-15: Add Structured Error Codes

Problem: Error messages are strings, not structured codes.

Steps:

  1. Define error code enum
  2. Map all error types to codes
  3. Update API responses to include codes
  4. Document error codes
  5. Add client-side error handling guide

Files: /lib/types/errors.ts, all services Depends On: P1-9 Effort: High


P2: Medium - Type Safety & Model Refinement

These items improve code quality and maintainability.

P2-1: Remove Type Safety Bypasses in Financial Code

Problem: as any casts in billing/commission code bypass type safety.

Steps:

  1. Audit all as any in financial code
  2. Create proper types for each case
  3. Remove casts
  4. Add strict null checks
  5. Add financial calculation tests

Files: /lib/services/platform-usage-tracker.ts, commission services Depends On: None Effort: Medium


P2-2: Fix Deduplication False Positive Logic

Problem: Name-only matching causes false positive duplicates.

Steps:

  1. Review duplicate detection algorithm
  2. Add confidence scoring
  3. Require email OR phone match for auto-merge
  4. Add manual review queue for uncertain matches
  5. Add undo for accidental merges

Files: /lib/services/lead/leadService.prisma.ts Depends On: None Effort: Medium


P2-3: Reduce Admin Status Cache TTL

Problem: 5-minute cache for admin status is too long for security.

Steps:

  1. Reduce TTL to 60 seconds
  2. Add cache invalidation on role change
  3. Add immediate invalidation endpoint
  4. Document caching behavior
  5. Add metrics for cache hit rate

Files: /lib/auth/*, admin services Depends On: None Effort: Low


P2-4: Implement Device Fingerprinting

Problem: Sessions don't bind to device, allowing session theft.

Steps:

  1. Collect device fingerprint (UA, screen, timezone)
  2. Store hash with session
  3. Validate on request
  4. Notify on fingerprint change
  5. Add "trust this device" option

Files: /lib/auth/sessionRotation.ts, /proxy.ts Depends On: None Effort: Medium


P2-5: Add Portal→PortalAIConfig Required 1:1

Problem: Portal→Business relationship is optional, should be required 1:1.

Steps:

  1. Ensure all portals have Business record
  2. Create missing Business records
  3. Change relation to required
  4. Update Portal creation to auto-create Business
  5. Update deletion to cascade

Files: /prisma/schema.prisma Depends On: P2-6 (rename first) Effort: Medium


P2-6: Rename Business Model to PortalAIConfig

Problem: "Business" name is confusing; it's actually portal-specific AI configuration.

Steps:

  1. Create new model PortalAIConfig
  2. Migrate data from Business
  3. Update all references
  4. Deprecate Business model
  5. Remove after migration

Files: /prisma/schema.prisma, all Business references Depends On: None Effort: High (many references)


P2-7: Clean CommissionTransaction Redundant Fields

Problem: CommissionTransaction has redundant calculated fields.

Fields to Review:

  • Fields that duplicate data from related records
  • Calculated fields that should be computed

Steps:

  1. Identify redundant fields
  2. Create database views for calculated values
  3. Migrate queries to use views
  4. Remove redundant columns
  5. Add computed properties in Prisma

Files: /prisma/schema.prisma, commission services Depends On: None Effort: Medium


P2-8: Implement Multi-Tenant Access Control Verification

Problem: No systematic verification that all queries include tenant filter.

Steps:

  1. Create Prisma middleware to verify portalId
  2. Log queries without tenant filter
  3. Fix identified issues
  4. Add to test suite
  5. Create PR review checklist

Files: New Prisma middleware, test utilities Depends On: None Effort: Medium


P2-9: Add TypeScript Strict Mode to Remaining Files

Problem: Some files have implicit any types.

Steps:

  1. Run tsc --noImplicitAny check
  2. Identify files with implicit any
  3. Fix incrementally
  4. Enable strict in tsconfig
  5. Add to CI checks

Files: /tsconfig.json, various source files Depends On: None Effort: Medium


P2-10: Standardize Date/Time Handling

Problem: Inconsistent timezone handling across codebase.

Steps:

  1. Audit date handling patterns
  2. Choose standard (UTC everywhere, convert on display)
  3. Create date utility module
  4. Update all date operations
  5. Add timezone tests

Files: New /lib/utils/dates.ts, various services Depends On: None Effort: Medium


P2-11: Add Zod Validation to All API Routes

Problem: Input validation inconsistent across routes.

Steps:

  1. Create common Zod schemas
  2. Audit routes without validation
  3. Add validation incrementally
  4. Standardize error responses
  5. Document validation in OpenAPI

Files: /lib/types/schemas.ts, all route handlers Depends On: P1-9, P1-15 Effort: High


P2-12: Implement Proper Enum Types

Problem: Magic strings used instead of enums.

Areas:

  • Lead status values
  • Pipeline stages
  • Integration types
  • Message channels

Steps:

  1. Create TypeScript enums
  2. Add Prisma enum types where appropriate
  3. Migrate existing data
  4. Update all usages
  5. Add validation

Files: /lib/types/*, /prisma/schema.prisma Depends On: None Effort: Medium


P3: Standard - Cleanup & Observability

These items reduce technical debt and improve visibility.

P3-1: Review and Clean Unused Database Indexes

Problem: Potentially unused indexes consuming space.

Steps:

  1. Query pg_stat_user_indexes for unused indexes
  2. Verify each is truly unused
  3. Drop unused indexes
  4. Monitor query performance
  5. Document index strategy

Files: Database, /prisma/schema.prisma Depends On: None Effort: Low


P3-2: Standardize Pagination Across API Routes

Problem: Different pagination patterns across routes.

Target Pattern:

{
  items: T[];
  pagination: {
    page: number;
    pageSize: number;
    total: number;
    hasMore: boolean;
  };
}

Steps:

  1. Create pagination helper
  2. Audit existing patterns
  3. Update routes incrementally
  4. Add cursor-based option for large sets
  5. Document in API docs

Files: /lib/utils/pagination.ts, route handlers Depends On: P1-9 Effort: Medium


P3-3: Add Audit Log Retention Policy

Problem: AuditLog table grows unbounded.

Steps:

  1. Define retention period (365 days per ARCHITECTURE.md)
  2. Create archival strategy
  3. Implement cleanup cron job
  4. Add partitioning for performance
  5. Document compliance requirements

Files: /app/api/cron/data-retention/route.ts Depends On: None Effort: Medium


P3-4: Document API Versioning Strategy

Problem: No clear API versioning strategy documented.

Steps:

  1. Document current version metadata approach
  2. Define future versioning strategy
  3. Add deprecation header support
  4. Create version migration guide
  5. Update OpenAPI spec

Files: /docs/API_VERSIONING.md, OpenAPI spec Depends On: None Effort: Low


P3-5: Implement Distributed Tracing

Problem: Sentry tracing at 10%, no full distributed tracing.

Steps:

  1. Evaluate trace sampling rate
  2. Add correlation IDs to all requests
  3. Propagate trace context to background jobs
  4. Add trace links in logs
  5. Create trace exploration dashboard

Files: Sentry config, logging utilities Depends On: None Effort: Medium


P3-6: Add SLO/SLA Definitions

Problem: No defined SLOs for key operations.

Target SLOs:

  • API response time: P99 < 500ms
  • Webhook processing: P99 < 2s
  • AI response: P99 < 5s
  • Uptime: 99.9%

Steps:

  1. Define SLOs for each service tier
  2. Implement SLO tracking metrics
  3. Create alerting on SLO breach
  4. Add SLO dashboard
  5. Document SLA for customers

Files: /lib/services/monitoring/*, alerting config Depends On: None Effort: Medium


P3-7: Run Test Coverage Report

Problem: Unknown test coverage, gaps likely exist.

Steps:

  1. Configure Jest coverage reporting
  2. Run coverage report
  3. Identify critical gaps
  4. Add tests for uncovered critical paths
  5. Set coverage thresholds

Files: /jest.config.ts, /tests/* Depends On: None Effort: Medium


P3-8: Audit Codebase for Dead Code

Problem: Likely dead code from feature iterations.

Steps:

  1. Run dead code detection tools
  2. Review flagged code
  3. Remove confirmed dead code
  4. Add lint rule for unused exports
  5. Create regular audit schedule

Files: Various Depends On: None Effort: Medium


P3-9: Add Request ID to All Logs

Problem: Logs missing correlation IDs for debugging.

Steps:

  1. Generate request ID in middleware
  2. Pass through logging context
  3. Include in error responses
  4. Add to Sentry context
  5. Document debugging workflow

Files: /proxy.ts, /lib/utils/logging.ts Depends On: None Effort: Low


P3-10: Implement Log Aggregation Strategy

Problem: Logs split across Vercel, Sentry, console.

Steps:

  1. Define log aggregation target
  2. Configure log shipping
  3. Create log search interface
  4. Add log retention policy
  5. Document log access

Files: Logging configuration Depends On: P3-9 Effort: Medium


P3-11: Add Health Check Dependencies

Problem: Health check doesn't verify all critical dependencies.

Dependencies to Check:

  • Database connection
  • Redis connection
  • Supabase Auth
  • External APIs (degraded mode)

Steps:

  1. Add dependency checks to health endpoint
  2. Return degraded status if optional deps fail
  3. Add dependency timeout
  4. Create health dashboard
  5. Document health check interpretation

Files: /app/api/health/route.ts Depends On: None Effort: Low


P3-12: Create Runbook for Common Issues

Problem: No documented runbooks for incident response.

Runbooks Needed:

  • Database connection issues
  • High error rate
  • Payment failures
  • Integration sync failures
  • Performance degradation

Steps:

  1. Document common issues and resolutions
  2. Create decision trees
  3. Add monitoring links
  4. Define escalation paths
  5. Review and update quarterly

Files: /docs/runbooks/* Depends On: None Effort: Medium


P4: Lower - Schema & Validation

These items refine the data model and improve data quality.

P4-1: Rename VoiceTouchpoint.organizationId to portalId

Problem: Inconsistent naming, should be portalId for clarity.

Steps:

  1. Add portalId field
  2. Migrate data
  3. Update all references
  4. Remove organizationId
  5. Update types

Files: /prisma/schema.prisma, voice services Depends On: None Effort: Low


P4-2: Add companyId Option to KnowledgeBaseEntry

Problem: Knowledge entries are portal-scoped, but some should be company-scoped.

Steps:

  1. Add optional companyId field
  2. Update knowledge retrieval to check company tier
  3. Create company knowledge management UI
  4. Migrate shared knowledge to company level
  5. Document knowledge scoping

Files: /prisma/schema.prisma, knowledge services Depends On: P0-9 Effort: Medium


P4-3: Restructure Upstash Vector Namespacing

Problem: Vector namespacing doesn't clearly separate company vs portal knowledge.

Target Structure:

  • company-{companyId} - Products, services, FAQs
  • portal-{portalId} - Team, integrations, workflows

Steps:

  1. Create migration script
  2. Re-index company knowledge
  3. Update retrieval queries
  4. Delete old namespaces
  5. Document namespace strategy

Files: Knowledge services, vector indexing Depends On: P4-2 Effort: Medium


P4-4: Fix Lead.clientId FK Relation

Problem: Lead.clientId is optional string, should have optional FK.

Steps:

  1. Add FK relation (optional)
  2. Validate existing data
  3. Update lead creation to use FK
  4. Add cascade rules
  5. Document lead ownership

Files: /prisma/schema.prisma Depends On: P0-1 Effort: Low


P4-5: Consolidate Subscription Fields to Company

Problem: Subscription fields split between models.

Steps:

  1. Audit subscription field locations
  2. Design consolidated structure
  3. Migrate data to Company
  4. Update billing services
  5. Remove redundant fields

Files: /prisma/schema.prisma, billing services Depends On: P0-9 Effort: Medium


P4-6: Document Knowledge Tier Architecture in Code

Problem: Knowledge tier logic spread across files, not well documented.

Steps:

  1. Create knowledge architecture doc
  2. Add code comments explaining tiers
  3. Create tier diagram
  4. Document retrieval priority
  5. Add knowledge management guide

Files: Knowledge services, /docs/KNOWLEDGE_ARCHITECTURE.md Depends On: P4-2, P4-3 Effort: Low


P4-7: Add Input Validation Consistency

Problem: Some routes lack input validation entirely.

Steps:

  1. Audit all routes for validation
  2. Create shared Zod schemas
  3. Add validation to missing routes
  4. Standardize error messages
  5. Add validation tests

Files: All route handlers Depends On: P2-11 Effort: High


P4-8: Document and Fix N+1 Query Patterns

Problem: Likely N+1 queries in list endpoints.

Steps:

  1. Profile slow endpoints
  2. Identify N+1 patterns
  3. Add Prisma includes/selects
  4. Add query performance tests
  5. Document query optimization

Files: Various services Depends On: None Effort: Medium


P4-9: Add Soft Delete to More Models

Problem: Hard deletes lose audit trail.

Models to Consider:

  • Conversation
  • Message
  • Contact

Steps:

  1. Add isDeleted field
  2. Update queries to filter
  3. Add deletion audit
  4. Create purge job for old deleted records
  5. Document deletion policy

Files: /prisma/schema.prisma, various services Depends On: None Effort: Medium


P4-10: Add Created/Updated By Fields

Problem: Some models lack audit fields.

Steps:

  1. Audit models missing createdBy/updatedBy
  2. Add fields with optional FK
  3. Update services to populate
  4. Migrate existing data
  5. Add to audit logging

Files: /prisma/schema.prisma Depends On: None Effort: Medium


P4-11: Normalize Phone Number Storage

Problem: Phone numbers stored in various formats.

Steps:

  1. Choose canonical format (E.164)
  2. Create normalization utility
  3. Migrate existing data
  4. Update input validation
  5. Add format display utility

Files: New /lib/utils/phone.ts, migration scripts Depends On: None Effort: Medium


P4-12: Add Email Normalization

Problem: Emails stored with inconsistent casing.

Steps:

  1. Normalize to lowercase
  2. Create validation utility
  3. Migrate existing data
  4. Update input handling
  5. Add deduplication check

Files: New /lib/utils/email.ts, migration scripts Depends On: None Effort: Low


P4-13: Review and Consolidate Status Enums

Problem: Multiple status fields with overlapping values.

Steps:

  1. Audit all status fields
  2. Create unified status types
  3. Map legacy values
  4. Update code to use unified types
  5. Document status transitions

Files: /lib/types/*, various services Depends On: P2-12 Effort: Medium


P4-14: Add Optimistic Locking Where Needed

Problem: No version field for concurrent update detection.

Steps:

  1. Identify high-contention models
  2. Add version field
  3. Implement optimistic locking
  4. Handle conflicts gracefully
  5. Add conflict resolution UI

Files: /prisma/schema.prisma, services with updates Depends On: None Effort: Medium


P5: Documentation

These items improve knowledge sharing and onboarding.

P5-1: Document Webhook Secret Validation

Problem: Webhook validation patterns not documented.

Steps:

  1. Audit all webhook handlers
  2. Document signature verification per provider
  3. Create webhook testing guide
  4. Add validation helpers
  5. Document debugging workflow

Files: /docs/API_INTEGRATION_GUIDE.md (Webhook Integration section) Depends On: None Effort: Low


P5-2: Document Connection Health Monitoring

Problem: No guide for monitoring integration health.

Steps:

  1. Document health check patterns
  2. Create monitoring dashboard guide
  3. Document failure modes
  4. Add alerting recommendations
  5. Create troubleshooting guide

Files: /docs/INTEGRATION_MONITORING.md Depends On: None Effort: Low


P5-3: Document Voice AI Provider Configuration

Problem: Voice AI setup not documented.

Steps:

  1. Document Cartesia setup
  2. Document ElevenLabs fallback
  3. Document Retell integration
  4. Create troubleshooting guide
  5. Add voice testing procedures

Files: /docs/VOICE_AI_SETUP.md Depends On: None Effort: Low


P5-4: Document Call Recording Storage

Problem: Call recording architecture not documented.

Steps:

  1. Document storage location
  2. Document retention policy
  3. Add compliance considerations
  4. Document access controls
  5. Create cleanup procedures

Files: /docs/CALL_RECORDINGS.md Depends On: None Effort: Low


P5-5: Document RLS Policies

Problem: RLS policy logic not documented.

Steps:

  1. Extract RLS policies from migrations
  2. Document each policy's purpose
  3. Create testing guide
  4. Document bypass rules
  5. Add security considerations

Files: /docs/RLS_POLICIES.md Depends On: None Effort: Low


P5-6: Create Onboarding Developer Guide

Problem: No comprehensive developer onboarding.

Steps:

  1. Document local setup
  2. Document architecture overview
  3. Create coding standards guide
  4. Document testing workflow
  5. Create PR review checklist

Files: /docs/DEVELOPER_GUIDE.md Depends On: None Effort: Medium


P5-7: Document Background Job Patterns

Problem: Job patterns not documented.

Steps:

  1. Document BullMQ usage
  2. Document database job pattern
  3. Create job debugging guide
  4. Document retry strategies
  5. Add monitoring guide

Files: /docs/BACKGROUND_JOBS.md Depends On: None Effort: Low


P5-8: Create API Integration Guide

Problem: No guide for third-party API integration.

Steps:

  1. Document integration patterns
  2. Create example integration
  3. Document error handling
  4. Add rate limit handling
  5. Create testing guide

Files: /docs/API_INTEGRATION_GUIDE.md Depends On: None Effort: Medium


P5-9: Document Database Migration Procedures

Problem: Migration process not documented.

Steps:

  1. Document migration workflow
  2. Add rollback procedures
  3. Create testing guide
  4. Document production deployment
  5. Add emergency procedures

Files: /docs/DATABASE_MIGRATIONS.md Depends On: None Effort: Low


P5-10: Create Security Best Practices Guide

Problem: Security practices not consolidated.

Steps:

  1. Document authentication patterns
  2. Document authorization patterns
  3. Add encryption guide
  4. Document secret management
  5. Create security review checklist

Files: /docs/SECURITY_GUIDE.md Depends On: None Effort: Medium


Dependencies Graph

P0-2 (orphan data) ─┬─► P0-1 (Client FK) ─┬─► P0-8 (Portal.clientId) ─► P0-9 (chain)
                    │                      │
                    └─► P0-3 (cleanup)     └─► P1-2, P1-3 (Product/Service migration)
                                                            │
                                                            ▼
                                           P4-2 (knowledge companyId) ─► P4-3 (vector)

P0-4 (MFA encrypt) ─► P0-5 (OAuth encrypt) ─► P0-6 (integration encrypt)

P2-6 (Business rename) ─► P2-5 (Portal→PortalAIConfig)

P1-9 (response format) ─► P3-2 (pagination) ─► P2-11 (Zod validation)
                                               │
                                               ▼
                                         P1-15 (error codes)

P1-7 (demo data) ─► P1-8 (demo knowledge)

P3-9 (request ID) ─► P3-10 (log aggregation)

Migration Execution Notes

Before Starting

  1. Create backup: Ensure database backup exists
  2. Feature flag: Consider feature flags for reversible changes
  3. Monitor: Have monitoring dashboards open
  4. Communicate: Notify team of migration window

Execution Order

  1. P0 first: Data integrity issues must be fixed before other changes
  2. Dependencies: Follow dependency graph
  3. Test each step: Verify before proceeding
  4. Document: Update CHANGELOG after each batch

Rollback Plan

Each migration should have:

  • Rollback SQL or Prisma migration
  • Expected rollback time
  • Verification queries
  • Communication plan

Testing Requirements

  • Unit tests for all new utilities
  • Integration tests for data migrations
  • Load tests for performance-sensitive changes
  • Security review for encryption changes

This plan is the execution roadmap for ARCHITECTURE.md. Track progress in project management tools and update this document as items complete.