Database — Known Issues & Gotchas

Issues encountered during development and E2E testing related to PostgreSQL, Prisma, MongoDB, and Redis.

1. Missing Seed Data Breaks Registration (Plans Table Empty)

Symptom: The registration plan-select step shows “No plans available for your region” and the signup button remains disabled. No plans appear regardless of country/region selection.

Root cause: The Plan table is empty. Plans are not created by migrations — they are seeded data. On a fresh database or after db:reset, the seed has not been run.

Fix:

cd /c/code/leadmetrics-v3
pnpm --filter @leadmetrics/db db:seed

This creates: Plans (Enterprise/Basic/Pro/Agency), AgentConfig records (25 agents), and test users.

Must be run: Once per fresh database, or after any prisma migrate reset.

2. Windows localhost Resolves to IPv6 (::1), Breaking Auth Cookies

Symptom: Login succeeds (API returns tokens) but the app immediately redirects back to /login. The JWT cookies are set but the middleware cannot read them.

Root cause: On Windows with Node 17+, localhost resolves to ::1 (IPv6). Fastify binds to 0.0.0.0 (IPv4 only by default). The JWT middleware sets cookies for the origin of the API request. If API_URL uses localhost but the browser visits 127.0.0.1, cookie domains can mismatch.

Fix: In apps/dashboard/.env.local, ensure ALL three vars use 127.0.0.1:

API_URL=http://127.0.0.1:3003
NEXT_PUBLIC_APP_URL=http://127.0.0.1:3000
NEXT_PUBLIC_API_URL=http://127.0.0.1:3003
NEXT_PUBLIC_API_SOCKET_URL=http://127.0.0.1:3003

Also affects: The DATABASE_URL in .env files — localhost:5434 works fine because PostgreSQL has no IPv4/IPv6 mismatch issue in this setup (it is accessed server-side, not from a browser).

3. Prisma Client Must Be Regenerated After Schema Changes

Symptom: TypeScript errors like Property 'activityLog' does not exist on type 'PrismaClient', or runtime errors when accessing new models/fields that exist in the schema but not in the generated client.

Root cause: Prisma generates a typed client from the schema. If schema.prisma is modified but prisma generate is not run, the client is stale.

Fix:

pnpm --filter @leadmetrics/db db:generate
# or
cd packages/db && npx prisma generate

When to run: After any change to packages/db/prisma/schema.prisma — new models, new fields, changed field types, new enums.

Note: In development with prisma dev / tsx --watch, this may not auto-run. Always run manually after schema edits before starting workers or the API server.

4. BullMQ Queue Worker Must Be Started Separately

Symptom: Tenant is registered, setup chain is enqueued, but no jobs ever process. Context file is never generated. No agent activity logs appear.

Root cause: The BullMQ workers (setup.worker, strategy.worker, strategy-writer.worker, activity.worker) run in a separate process from the API server. pnpm dev in apps/api starts the HTTP/Socket server only — not the workers.

Fix:

# In a separate terminal
cd /c/code/leadmetrics-v3/apps/api
pnpm worker

Worker behavior:

• Polls for new tenants every 30 seconds on startup
• Automatically starts per-tenant queues when a new tenant is detected
• Must be running at all times for agent processing to work

5. ActivityLog actor Field Was Generic — Broke Per-Agent Filtering

Symptom: The agent detail page (/settings/agents/[agentId]) showed no “Agent Milestones” even after agents had run. All activity log entries existed in the DB but none matched the filter actor = agent.role.

Root cause: All four BullMQ workers were logging with actor: "LM Agent" (a generic string) instead of the specific agent role. The detail page filters by actor = agent.role (e.g. "context-file-writer"), so nothing matched.

Fix applied: Changed each worker to log with its specific role as the actor:

6. Socket.IO Redis Adapter Requires Two Separate Redis Connections

Symptom: If you attempt to reuse a single IORedis instance for both the pub and sub sides of @socket.io/redis-adapter, the adapter throws or behaves incorrectly (messages not delivered, adapter silently fails).

Root cause: The Redis pub/sub protocol is stateful — a connection in subscriber mode cannot send regular commands. @socket.io/redis-adapter requires two completely separate connections.

Correct setup (already in apps/api/src/socket/index.ts):

const pubClient = new IORedis(redisUrl, { maxRetriesPerRequest: null, lazyConnect: true });
const subClient = pubClient.duplicate(); // separate connection
await pubClient.connect();
await subClient.connect();
io.adapter(createAdapter(pubClient, subClient));

7. Agent Event Publisher Uses a Separate Redis Connection in Workers

Symptom: Worker publishes agent_events:{tenantId} but the API socket server never receives it.

Root cause: The worker process and the API process are separate Node.js processes. The worker uses publishAgentEvent() from packages/queue/src/agent-events.ts which creates its own IORedis publisher connection. If Redis is unreachable from the worker process, events are silently swallowed (the function catches all errors).

Debugging steps:

1. Check Redis is running: redis-cli ping → should return PONG
2. Verify REDIS_URL env var is set correctly in both the worker and API processes
3. Test pub/sub directly: redis-cli subscribe agent_events:* in one terminal, then trigger an agent run

8. NEXT_PUBLIC_* Env Vars Require Dashboard Restart

Symptom: After editing apps/dashboard/.env.local (e.g. changing NEXT_PUBLIC_API_URL), the dashboard still uses the old value.

Root cause: NEXT_PUBLIC_* variables are inlined at build time by Next.js webpack. They are not read from the environment at runtime. The dev server must be fully restarted (kill + pnpm dev) to pick up changes.

Does NOT require restart: Server-side-only vars like DATABASE_URL, BETTER_AUTH_SECRET, BETTER_AUTH_URL — these are read at request time.