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.

Author: @pk675 PR: dealops#7605 State: draft open Stack: Dealops 2 Files: 55 Diff: +4534 / -1 Branch: codex/dea-8104-api-conversations

What it adds

Application-owned conversation history: ApiConversation plus ordered ApiConversationMessage rows with origin, authors, text, external keys, and timestamps.

What it changes

Introduces a shared ApiConversationStore contract and Prisma-backed functions for create/get/list/append, exposed through an internal staff-only tRPC router.

What it proves

Adds a focused Mocha E2E suite with 26 real PostgreSQL scenarios covering retries, races, pagination, tenant isolation, access gates, and rollback behavior.

What it avoids

No LangGraph tables, checkpoints, quote-job dispatch, SSE, adapters, attachments, or agent execution are included in this PR.

Shared types contract
Prisma schema + migration
Storage service
tRPC adapter
E2E fixtures
RFC + knowledge
Diff note: the supplied diff is truncated. This explainer is based on the PR description, file list, and visible diff hunks; it does not claim line-by-line coverage of omitted sections.

1. Why this exists

Current gap

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.
Boundary chosen

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.
Coverage count
26
conversation scenarios passed in GitHub CI
Default page
50
messages per page unless configured
Max batch
100
messages per append by default

2. What changes

Shared contract

packages/types/apiConversation.ts defines the transport-agnostic contract.

  • ApiConversation
  • ApiConversationMessage
  • ConversationScope
  • ApiConversationStore
  • Zod schemas and configurable limits
Database

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.
Service

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.
Internal API

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

Before
  • 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.
After
  • 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 interface
packages/prisma/schema/models/api-conversations.prismamodels
apps/server/src/dealops2/apiConversations/conversationService.tscore logic
apps/server/src/trpc/router/apiConversations/_router.tsstaff API
apps/server/e2e_tests/trpc/apiConversations.e2e.tsreal DB scenarios
apps/server/e2e_tests/fixtures/apiConversations/**/expected.jsonone response per fixture
rfcs/2026-10-08-api-conversations.mdcontract rationale
knowledge/concepts/api-conversations.mdknowledge base

3. How it works

Request path

1
Caller

Trusted in-process tRPC caller invokes apiConversations.*.

2
Staff gate

Router enforces active DEALOPS_INTERNAL_STAFF.

3
Tenant scope

Scope is always { organizationId: ctx.organization.id }.

4
Store

Service validates inputs and executes Prisma / SQL operations.

5
PostgreSQL

Unique constraints and transactions decide races and retries.

Create semantics

Identity lookup

The service validates the creator, then chooses one lookup key:

  • If externalThreadKey is present: org + external thread key.
  • Otherwise: org + proposed conversation UUID.
Race handling

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

Append invariant

A batch is all-or-nothing. If one changed retry conflicts, any new messages in the same request roll back too.

Retry invariant

Exact retries return existing rows, including stored timestamps, and do not consume sequence numbers.

Ordering invariant

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

Focused CI step

server_tests.yml adds a dedicated Mocha invocation for API conversation storage and excludes it from the broader tRPC suite using an inverted grep.

Real integration coverage

The suite calls the real tRPC middleware/service in process and uses PostgreSQL. HTTP is skipped, but persistence and authorization paths are not mocked.

Fixture style

Each expected.json is one API response directly, with runtime bindings for generated IDs, external keys, and stored timestamps.

Scenario coverage map
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.
Validation reported by the PR: GitHub CI on 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

Explicitly out of scope

6. Risks / rollback / open questions

Risk: migration rehearsal

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.

Rollback shape
  • The code path is new and behind a new internal tRPC router.
  • Operational rollback can remove or stop calling apiConversations without impacting existing quote flows.
  • Database rollback is more deliberate once rows exist because the migration creates new tables/enums and FKs.
Open follow-up scope
  • 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.
Reviewer focus: the highest-value review surface is the append transaction: conflict detection, external-key uniqueness with nullable values, timestamp equality, and whether tenant/author checks match future trusted adapter expectations.