Call Context Service schema foundation

Adds the Dealops 2 AI quoting read-layer contract and storage tables that will let the agent use org-scoped call/context data instead of querying Salesforce directly.

Author @pk675 PR #6632 Ticket DEA-7274 Area dealops2/aiQuoting State open Files 6 Diff +487 / -0 Base main

What it adds

Three additive Prisma models for a multi-tenant Call Context Service: call evidence, opportunity enrichment, and sync freshness.

What it exposes

A plain TypeScript CallContextService contract with five methods: evidence, listCalls, getCall, getContext, and sync.

What it preserves

No FK relations, no runtime readers/writers, and no Salesforce puller in this PR. The real Prisma-backed implementation is deferred to DEA-7275.

Review focus

Schema shape, tenant scoping, uniqueness constraints, soft-delete expectations, and whether the internal TS contract matches future caller needs.

CallRecord

OppContext

CallSyncState

CallContextService

Sandbox extract registry

1. Why this exists

Before

The AI quoting path has no dedicated, tenant-scoped read layer for call evidence and quote-driving opportunity context.

Agent logic would otherwise need direct CRM/Salesforce-specific access patterns.
Freshness and evidence availability are not represented as first-class data.
Org-specific fields have no stable contract for prompt construction.

After this PR

Dealops 2 gets a contract-first foundation for the Call Context Service.

Calls are the required evidence input.
Opportunity context is optional enrichment.
Freshness lives beside the data as explicit sync state.
Implementation remains intentionally unwired until DEA-7275.

Design intent: this is additive-only infrastructure. The service is meant to replace direct per-org Salesforce querying with a normalized, org-scoped read layer for the AI quoting agent.

2. What changes

CRM-owned inputs

Dealops_Call_Note

Dealops_Opp_Context

→

New storage

CallRecord, OppContext, CallSyncState

→

Future readers

CallContextService → AI quoting agent

Table

CallRecord

One row per CRM call/note. This is the evidence the agent needs before quoting.

org scoped source + crm id dedupe soft delete raw/clean body type

Table

OppContext

One optional enrichment row per opportunity with typed quote-driving fields plus a JSON attributes bag.

optional typed steering fields provenance JSON soft delete

Table

CallSyncState

One freshness row per opportunity. This is what evidence() will use to decide whether data is usable.

fresh/stale/never/broken last error soft delete

TypeScript contract

CallContextService

Internal interface only. No zod schema because this is not a request boundary.

plain TS Mocha shape tests implementation later

File	Change	Reviewer lens
packages/prisma/schema/models/v2/call-context.prisma	Adds models/enums for call records, opportunity context, and sync state.	Data shape, indexes, uniqueness, nullable fields, no-FK pattern.
packages/prisma/migrations/20260813191951_add_call_context_tables/migration.sql	Creates enums, tables, indexes, and org-scoped unique constraints.	Generated SQL matches Prisma model intent.
packages/prisma/migrations/20260813192622_call_context_soft_delete/migration.sql	Adds `deletedAt` to all three new tables.	Future reads must filter soft-deleted rows.
apps/server/src/dealops2/aiQuoting/callContext/types.ts	Defines `DealRef`, data DTOs, results, and the service interface.	Caller-facing contract and semantics.
apps/server/src/dealops2/aiQuoting/callContext/__tests__/types.test.ts	Adds a fake implementation to prove the interface is usable.	Shape coverage only; not persistence behavior.
packages/prisma/scripts/sandbox-extract/registry.ts	Registers new models as directly scoped by `organizationId`.	Sandbox extraction handles no-FK tables safely.

3. How it works

Storage model

CallRecord = evidence

Calls are keyed by organizationId, source, and crmRecordId so Salesforce, HubSpot, and manual IDs can coexist without collision.

bodyType defaults to raw.
clean is reserved for a future redaction/summarization pass.
contentHash gives future sync code a change-detection hook.

OppContext = enrichment

The typed columns are intentionally narrow: only fields that steer quote behavior or lookalike matching get first-class columns.

dealSize, industry, employeeCount
dealType, segment, competitors
Everything else lands in attributes.

CallSyncState = freshness

Freshness is explicit instead of inferred from row existence.

Status enum: fresh, stale, never, broken.
lastSyncAt and lastError explain current state.
Unique per org + opportunity.

Service contract

evidence(deal)

Returns count, newest call date, context presence, sync state, and an ok / no_evidence verdict.

listCalls(deal, opts)

Returns newest-first calls. limit defaults to 10; type: "clean" is accepted before the clean pipeline exists.

getCall({ orgId, callId })

Fetches one call only within the caller's org scope.

getContext(deal)

Returns optional opportunity enrichment. The agent must still operate when this returns null.

sync(deal, opts)

Defines future sync behavior. In v1, only already-fresh ifStale can skip cleanly; otherwise it should report an error rather than fake a pull.

Tenant scoping and no-FK pattern

The schema mirrors the existing AiQuotingAccount approach: store IDs by value, do not create Prisma relations.

3 new models

0 foreign keys

3 soft-delete columns

3 sandbox extract overrides

Writes are not blocked if an opportunity is missing or deleted.
Sandbox extraction can still scope rows directly through organizationId.
Future reads must consistently filter deletedAt IS NULL.

Key uniqueness/index rules

Model	Constraint / index	Why it exists
CallRecord	`@@unique([organizationId, source, crmRecordId])`	Dedupes by CRM-owned record ID while avoiding cross-source collisions.
CallRecord	`@@index([organizationId, opportunityV2Id, callDate])`	Supports newest-first call listing and evidence checks per deal.
OppContext	`@@unique([organizationId, opportunityV2Id])`	One enrichment row per org-scoped opportunity.
CallSyncState	`@@unique([organizationId, opportunityV2Id])`	One freshness state per org-scoped opportunity.

4. What it doesn't change

Explicit non-goals in this PR

No production code reads from CallRecord, OppContext, or CallSyncState yet.
No code writes or syncs CRM data into the new tables yet.
No tRPC route, REST route, UI, prompt change, or AI agent behavior change is included.
No zod validation is added because CallContextService is an internal TypeScript contract, not an API boundary.
No hard delete behavior is introduced; the schema expects soft deletion through deletedAt.
No legacy Dealops 1 tables are touched.

5. Risks / rollback / open questions

Migration state needs reviewer attention. The PR description says pnpm exec prisma migrate dev was not run and that migration generation needs go-ahead, but the diff includes two migration SQL files. Review should confirm whether these migrations are intended to land as-is or should be regenerated under repo convention.

Primary risk

Schema contract ossifies before the real read/write implementation lands in DEA-7275.

Review typed OppContext fields carefully.
Confirm fieldSources is sufficient for provenance and conflict resolution.
Confirm soft-delete filtering is acceptable as a future implementation requirement.

Rollback shape

Because nothing reads or writes these tables yet, app rollback is low-risk.

Code rollback removes the interface, tests, schema model file, and sandbox overrides.
Database rollback depends on whether the new migrations have been applied to an environment.

Validation done

pnpm run typecheck clean for touched code; unrelated main errors called out by author.
pnpm exec mocha passes the new shape test, 5/5.
Persistence behavior is not tested here because implementation is intentionally absent.