API conversation storage foundation
PR #7605 adds Dealops 2 conversation-history tables, a shared storage interface, a staff-only tRPC adapter, and real PostgreSQL E2E coverage as the first mergeable slice of DEA-8104.
Application-owned conversation history: ApiConversation plus ordered ApiConversationMessage rows with origin, authors, text, external keys, and timestamps.
Introduces a shared ApiConversationStore contract and Prisma-backed functions for create/get/list/append, exposed through an internal staff-only tRPC router.
Adds a focused Mocha E2E suite with 26 real PostgreSQL scenarios covering retries, races, pagination, tenant isolation, access gates, and rollback behavior.
No LangGraph tables, checkpoints, quote-job dispatch, SSE, adapters, attachments, or agent execution are included in this PR.
1. Why this exists
DEA-8104 needs a durable transcript for conversational quote workflows, but that transcript should not be owned by LangGraph or any single agent framework.
- Threads can have multiple human authors.
- Integrations may import several messages before the agent responds.
- Retries and concurrent appends must not duplicate or reorder history.
This PR makes storage independently mergeable: database tables, shared application types, Prisma operations, and a narrow internal API.
- Conversation UUID becomes the future LangGraph
thread_id. - Permanent chat transcript stays separate from execution checkpoints.
- Storing a message does not invoke a model, quote run, or queue job.
2. What changes
packages/types/apiConversation.ts defines the transport-agnostic contract.
ApiConversationApiConversationMessageConversationScopeApiConversationStore- Zod schemas and configurable limits
packages/prisma/migrations/20261008223000_api_conversations/migration.sql creates the new storage layer.
- Two enums: entrypoint and message role.
- Two tables with FKs to orgs and users.
- Indexes for org activity, sequence reads, and retry identities.
apps/server/src/dealops2/apiConversations/conversationService.ts implements the store with Prisma and raw SQL where needed.
- Create/get/list operations.
- Atomic bulk append.
- Exact retry detection.
- Tenant and author checks.
apps/server/src/trpc/router/apiConversations/_router.ts adds a thin tRPC adapter under appRouter.apiConversations.
- Requires active internal staff.
- Uses ctx organization as tenant scope.
- Maps storage errors to tRPC errors.
Schema shape
| Entity | Important fields | Identity / ordering rule | Reviewer note |
|---|---|---|---|
| ApiConversation | id, organizationId, createdByUserId, entrypoint, client, externalThreadKey, timestamps |
Get-or-create by org + externalThreadKey when present, otherwise org + UUID. |
Retry returns the original row unchanged; creator/origin are not overwritten. |
| ApiConversationMessage | id, conversationId, userId, role, content, sequence, externalMessageKey, timestamps |
Append order is stored as positive integer sequence, unique per conversation. |
sentAt preserves source time; pagination still uses ingestion sequence. |
Before / after
- No framework-independent transcript for API conversations.
- No shared interface for future MCP, Slack, or agent adapters.
- No stable application table for conversation UUIDs that can become LangGraph thread IDs.
- Retry, pagination, and append-race semantics were not encoded as a storage contract.
- Conversation and message rows live in Prisma-owned PostgreSQL schema.
- All callers target
ApiConversationStore. - Messages append atomically and return records in request order.
- History reads page by stable sequence cursor.
Meaningful files
packages/types/apiConversation.tsshared interfacepackages/prisma/schema/models/api-conversations.prismamodelsapps/server/src/dealops2/apiConversations/conversationService.tscore logicapps/server/src/trpc/router/apiConversations/_router.tsstaff APIapps/server/e2e_tests/trpc/apiConversations.e2e.tsreal DB scenariosapps/server/e2e_tests/fixtures/apiConversations/**/expected.jsonone response per fixturerfcs/2026-10-08-api-conversations.mdcontract rationaleknowledge/concepts/api-conversations.mdknowledge base3. How it works
Request path
Trusted in-process tRPC caller invokes apiConversations.*.
Router enforces active DEALOPS_INTERNAL_STAFF.
Scope is always { organizationId: ctx.organization.id }.
Service validates inputs and executes Prisma / SQL operations.
Unique constraints and transactions decide races and retries.
Create semantics
The service validates the creator, then chooses one lookup key:
- If
externalThreadKeyis present: org + external thread key. - Otherwise: org + proposed conversation UUID.
Creation first reads, then inserts. If PostgreSQL raises a uniqueness race, the service re-reads the winning row and returns it.
Identity conflicts that cannot be reconciled reject with CONFLICT.
Append transaction
A batch is all-or-nothing. If one changed retry conflicts, any new messages in the same request roll back too.
Exact retries return existing rows, including stored timestamps, and do not consume sequence numbers.
Returned append results follow request order. History reads follow sequence order.
Critical SQL behavior
The append path uses raw SQL for the part Prisma cannot express compactly: convert the input batch to rows, exclude existing identities, assign sequences with ROW_NUMBER(), then read back exact matches.
SELECT * FROM "ApiConversation"
WHERE "id" = ${conversationId}
AND "organizationId" = ${organizationId}
FOR UPDATE
INSERT INTO "ApiConversationMessage" (...)
SELECT ..., max(sequence) + ROW_NUMBER() OVER (ORDER BY position)
FROM fresh
ON CONFLICT DO NOTHING
If the final read cannot find one exact row per submitted message, the service throws ConversationError('CONFLICT') and the transaction rolls back.
Error mapping
| Storage error | tRPC response | Typical cause |
|---|---|---|
NOT_FOUND |
NOT_FOUND |
Missing conversation or foreign-tenant conversation; these are intentionally indistinguishable. |
CONFLICT |
CONFLICT |
Changed retry payload, conflicting IDs/external keys, or unavailable conversation identity. |
INVALID_AUTHOR |
BAD_REQUEST |
User message author is not valid for this organization/conversation context. |
ZodError |
BAD_REQUEST |
Malformed input, invalid limits, duplicate identities in a batch, or blank content. |
4. Tests and validation
server_tests.yml adds a dedicated Mocha invocation for API conversation storage and excludes it from the broader tRPC suite using an inverted grep.
The suite calls the real tRPC middleware/service in process and uses PostgreSQL. HTTP is skipped, but persistence and authorization paths are not mocked.
Each expected.json is one API response directly, with runtime bindings for generated IDs, external keys, and stored timestamps.
| Area | Covered scenarios |
|---|---|
| Lifecycle | Empty history, multiple turns, user + assistant roles, multiple human authors, reopening an external thread. |
| Idempotency | Conversation retries, exact message retries, mixed new + retried batches, timestamp equality for retried records. |
| Conflict handling | Changed content, author, role, external key, removed external key, explicit sentAt, conflicting message identities. |
| Concurrency | Simultaneous conversation creation, concurrent appends, concurrent exact retries. |
| Pagination / limits | Cursor pages during appends, default page size, max page size, max batch size, configurable service limits. |
| Isolation / access | Tenant isolation, foreign messages, invalid authors, staff-only gate, anonymous/non-staff rejection, revoked staff access. |
f3d3a5fa8 passed all 26 conversation scenarios plus full server E2E, migrations, server unit tests, client E2E, server/client typechecks, lint, and formatting.
5. What it doesn't change
- No LangGraph checkpoint tables are added.
- No agent/model call is triggered by message storage.
- No quote-run queueing, cancellation, replacement, or one-active-run enforcement is implemented here.
- No SSE streaming path is introduced.
- No public REST/OpenAPI endpoint is added; this is a Dealops 2 tRPC route.
- No Slack/MCP adapter behavior beyond storage-friendly identity fields.
- No attachments, multimodal message parts, file ingestion, or context tools.
- No client UI changes are indicated in the file list.
- The JavaScript append planner is removed; its relevant behavior is now asserted through SQL/API E2E scenarios.
6. Risks / rollback / open questions
The PR states the Prisma migration is additive and schema validation/client generation passed earlier, but Neon migration rehearsal is still pending access to a disposable branch.
- The code path is new and behind a new internal tRPC router.
- Operational rollback can remove or stop calling
apiConversationswithout impacting existing quote flows. - Database rollback is more deliberate once rows exist because the migration creates new tables/enums and FKs.
- LangGraph integration using conversation UUID as
thread_id. - Quote-run slot enforcement: queued/running/cancelling per conversation.
- User-confirmed replacement and worker-stop coordination.
- Attachments plus MCP/Slack adapters.