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

Priority	Total	✅ Done	🔄 Partial	❌ Pending	Progress
P0	12	12	0	0	100%
P1	15	12	1	2	87%
P2	12	9	1	2	79%
P3	12	8	1	3	71%
P4	14	3	2	9	29%
P5	10	10	0	0	100%
Total	75	54	5	16	79%

Quick Status Reference

P0 - Critical (12/12 Complete) ✅

ID	Item	Status	Notes
P0-1	Client.companyId FK	✅ Done	FK relation added to schema
P0-2	Fix Orphaned CompanyIds	✅ Done	cron/orphan-cleanup/route.ts runs hourly
P0-3	Delete Orphaned Records	✅ Done	Handled by orphan-cleanup cron job
P0-4	Encrypt MFA Secret	✅ Done	mfa-secret-encryption.ts + tests + migration
P0-5	Encrypt OAuth Tokens	✅ Done	oauth-token-encryption.ts + middleware
P0-6	Encrypt Integration Tokens	✅ Done	integration-token-encryption.ts + middleware
P0-7	Fix Billing Race Condition	✅ Done	credit-wallet-service.ts uses $transaction
P0-8	Portal.clientId Required FK	✅ Done	Commit cca045791
P0-9	Portal→Client→Company Chain	✅ Done	Chain validated via P0-1 + P0-8
P0-10	Atomic Webhook Processing	✅ Done	idempotencyKey with @unique in schema
P0-11	Request Signing	✅ Done	internal-api-auth.ts + request-signing.ts with HMAC-SHA256
P0-12	Session Token Security	✅ Done	sessionRotation.ts with privilege change detection

P1 - High (12/15 Complete)

ID	Item	Status	Notes
P1-1	Redis Rate Limiting	✅ Done	rate-limiter.ts with Upstash Redis, sliding window
P1-2	Product.businessId → companyId	❌ Pending	Requires schema migration
P1-3	Service.businessId → companyId	❌ Pending	Requires schema migration
P1-4	Lead Merge Transaction	✅ Done	leadService.prisma.ts:929 uses $transaction
P1-5	Missing FK Indexes	✅ Done	50+ @@index directives in schema
P1-6	PortalAnalyticsSummary clientId	🔄 Partial	NOT NULL constraint pending
P1-7	Demo Portal Data Chain	✅ Done	Demo data structure exists
P1-8	Seed KnowledgeBaseEntry	✅ Done	Knowledge base seeding in place
P1-9	Standardize API Response	✅ Done	api-response.ts with requestId and pagination
P1-10	VACUUM ANALYZE	✅ Done	Automated via database maintenance
P1-11	Connection Pool Monitoring	✅ Done	prometheusMetrics.ts tracks pool stats
P1-12	Graceful Shutdown	✅ Done	graceful-shutdown.ts with SIGTERM handler
P1-13	Database Query Timeout	✅ Done	database.ts has statement_timeout configured
P1-14	Circuit Breaker	✅ Done	api-circuit-breaker.ts + circuit-breaker.ts
P1-15	Structured Error Codes	✅ Done	error-codes.ts with domain-specific codes

P2 - Medium (9/12 Complete)

ID	Item	Status	Notes
P2-1	Remove Type Safety Bypasses	❌ Pending	Ongoing improvement
P2-2	Deduplication Algorithm	✅ Done	deduplication-service.ts with confidence scoring
P2-3	Admin Status Cache TTL	✅ Done	withAuth.ts has 60-second cache
P2-4	Device Fingerprinting	✅ Done	device-fingerprint.ts with trust levels
P2-5	Portal→PortalAIConfig 1:1	✅ Done	Commit 818039065
P2-6	Rename Business Model	🔄 Assessment	Decision needed on naming
P2-7	CommissionTransaction Cleanup	❌ Pending	Polymorphic design intentional
P2-8	Multi-Tenant Middleware	✅ Done	multi-tenant-middleware.ts
P2-9	TypeScript Strict Mode	✅ Done	Strict mode enabled in tsconfig
P2-10	Date/Time Handling	✅ Done	dates.ts with comprehensive timezone support
P2-11	Zod Validation	✅ Done	102+ routes have Zod validation
P2-12	Proper Enum Types	✅ Done	Prisma enums in schema

P3 - Standard (8/12 Complete)

ID	Item	Status	Notes
P3-1	Clean Unused Indexes	✅ Done	Index audit completed, unused indexes removed
P3-2	Standardize Pagination	✅ Done	Standard pagination pattern across APIs
P3-3	Audit Log Retention	❌ Pending	Retention policy definition needed
P3-4	API Versioning Doc	✅ Done	API_VERSIONING.md exists
P3-5	Distributed Tracing	🔄 Partial	Sentry tracing configured
P3-6	SLO/SLA Definitions	✅ Done	apiSlo.ts defines critical SLOs
P3-7	Test Coverage Report	❌ Pending	Coverage reporting setup needed
P3-8	Dead Code Audit	❌ Pending	Needs tooling setup
P3-9	Request ID in Logs	✅ Done	requestContext.server.ts
P3-10	Log Aggregation	✅ Done	Centralized logging via Sentry + Vercel
P3-11	Health Check Dependencies	✅ Done	health-checker.ts with comprehensive checks
P3-12	Runbooks	✅ Done	Various runbooks created

P4 - Lower (3/14 Complete)

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

P5 - Documentation (10/10 Complete)

ID	Item	Status	Notes
P5-1	Webhook Documentation	✅ Done	Merged into API_INTEGRATION_GUIDE.md
P5-2	Integration Monitoring	✅ Done	INTEGRATION_MONITORING.md
P5-3	Voice AI Setup	✅ Done	VOICE_AI_SETUP.md
P5-4	Call Recording Storage	✅ Done	CALL_RECORDINGS.md
P5-5	RLS Policies	✅ Done	RLS_POLICIES.md
P5-6	Developer Guide	✅ Done	DEVELOPER_GUIDE.md
P5-7	Background Jobs	✅ Done	BACKGROUND_JOBS.md
P5-8	API Integration Guide	✅ Done	API_INTEGRATION_GUIDE.md
P5-9	Database Migrations	✅ Done	DATABASE_MIGRATIONS.md
P5-10	Security Guide	✅ Done	SECURITY_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

Priority	Items	Focus Area	Risk Level	Status
P0	12	FK constraints, encryption, race conditions	HIGH - Data loss/security	✅ 100%
P1	15	Rate limiting, model migration, indexes	MEDIUM - Performance/stability	✅ 87%
P2	12	Type safety, model renaming, access control	MEDIUM - Code quality	✅ 79%
P3	12	Cleanup, standardization, observability	LOW - Technical debt	✅ 71%
P4	14	Schema refinement, validation, optimization	LOW - Enhancement	🔄 29%
P5	10	Documentation, runbooks, guides	LOW - 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.