L1:# @talkpilot/core-db

@talkpilot/core-db is the shared TypeScript database package that wires TalkPilot APIs, municipal CRM integrations, and internal tools to a single, type-safe MongoDB surface. Every repo (MIS, CIS, TalkPilot Server, etc.) imports this package to avoid re-implementing connections, collections, or validation helpers.

Purpose

• Provide a reliable, multi-domain MongoDB layer for TalkPilot and municipal data.
• Export typed getters, vector search helpers, and document factories so services can focus on behavior instead of schema wiring.
• Manage connection lifecycles, environment configuration, and test helpers from one place so every repo reuses the same plumbing.

Main Concepts

• Multi-domain clients – src/connection.ts exposes mongodbClient (TalkPilot) and municipalDataMongodbClient, each of which resolves MONGO_URI, DB overrides, and default names.
• Domain-specific getters – src/talkpilot/ and src/municipal/ host typed getters (agents, calls, streets, tickets, etc.), vector-search helpers, and service-friendly adapters that keep caller code DRY.
• Product-specific clients (future) – While the package currently exposes the shared TalkPilot + municipal clients, we expect each product (CIS, MIS, TalkPilot Server) to eventually get its own domain-specific client helpers or wrappers so the shared core can remain stable while new consumers add targeted extensions.
• Test helpers – src/test-utils/ plus src/__tests__/ reuse MongoMemoryServer and shared factories so tests start with clean data regardless of the consuming repo.
• Utility layers – src/utils/ contains shared validation, pagination, and environment helpers that complement the getters.
• Environment awareness – Defaults, fallbacks, and process.env lookups ensure local, CI, and Cloud Run clients all connect using the right URI/DB names.

Key Components

• src/connection.ts – Central connection logic that resolves URIs/DB names from env vars (MONGO_URI, MONGODB_URI, TALKPILOT_DB_NAME, MUNICIPAL_DB_NAME) and reuses a single MongoClient.
• src/talkpilot/ – Call history, agents, flows, sessions, leads, subscriptions, and support helpers exposed as getters plus helper enums/types for each collection.
• src/municipal/ – Municipal-specific collections (cities, streets, departmentsSubjects, tickets, etc.) plus vector search helpers and Ash Bina helpers used by MIS.
• src/utils/ – Shared helpers such as resolveConnection, pagination utilities, and schema validation helper functions.
• src/test-utils/ and src/__tests__/ – Utilities that bootstrap MongoMemoryServer, expose factories, and make sure Jest environments can stub database calls predictably.
• dist/ – Compiled output consumed by downstream repos (CJS + ESM + type defs).

Domain APIs

• TalkPilot domain – Imports like findAgents, getFlows, findCalls, and vectorSearchCalls live in src/talkpilot. These functions are the canonical access pattern for call history, session metadata, and provider configs.
• Municipal domain – Helpers such as findStreets, getMunicipalCities, findDepartmentSubjects, and createTicket live under src/municipal and feed MIS workflows (street hints, subject matching, Ash Bina tickets).

Environment variables

| Variable | Purpose | Required | |---------------------------|--------------------------------------------------------------------------------------|----------| | MONGO_URI | Primary MongoDB connection string for every domain (overridden by MONGODB_URI). | ✅ | | MONGODB_URI | Alternate connection string used when Mongo needs a second URI parameter. | ✅ | | TALKPILOT_DB_NAME | Optional override for the TalkPilot database name (defaults from URI path). | ❌ | | MUNICIPAL_DB_NAME | Optional override for the municipal database name (defaults to municipal-data). | ❌ | | ENV | Free-form label used in logs/validators (defaults to unknown). | ❌ |

If you pass a uri directly to mongodbClient.connect() or municipalDataMongodbClient.connect(), the client will prefer that value over the env vars.

Getting Started

Prerequisites

• Node.js 22.x+ (aligns with downstream services).
• npm 11+ or Yarn.
• MongoDB accessible from your environment or a MongoMemoryServer for tests.

Setup

1. Clone the repo and install dependencies:

   git clone https://github.com/talkpilot/core-db.git
   cd core-db
   npm install

2. Build the package before using it locally:

   npm run build

3. Import @talkpilot/core-db from another project by pointing package.json at the local path during development or installing the published release.

Sample .env

MONGO_URI=mongodb://localhost:27017
TALKPILOT_DB_NAME=talkpilot-dev
MUNICIPAL_DB_NAME=municipal-dev
ENV=development

Adjust MONGO_URI to match the running Mongo instance and configure talkpilot/municipal DB names if you want to keep them separate.

Local development

1. Run npm install.
2. Build the compiled output: npm run build.
3. Execute tests: npm run test.
4. Use npm link or npm pack to consume the freshly built package from other repos (CIS, MIS, TalkPilot Server).

Development guide

DEVELOPMENT.md contains the tactical steps for contributors. At a glance:

• Node 18+/TypeScript is required (aligns with downstream services).
• Run npm install → npm run build after cloning.
• Use npm link/npm link @talkpilot/core-db to test the package locally before publishing.
• When adding getters, define types, implement the function, export it through the domain index.ts, and add a corresponding test under the domain’s __tests__ folder.
• Always rely on the provided test factories (src/test-utils/factories) to seed data so tests remain consistent.
• Jest with mongodb-memory-server is the only execution path we have to verify this core utility—unit tests are the safety net for every change.

Refer to DEVELOPMENT.md for the full walkthrough, token instructions, and factory samples.

🧪 Testing

• npm run test – Jest suite (factories, utils, integration mocks) powered by mongodb-memory-server.
• Tests rely on src/__tests__/setup.ts to bootstrap the in-memory Mongo instances and wire shared factories/helpers before each run.
• When adding getters, helpers, or domain logic, create focused coverage inside the consuming domain’s __tests__/ folder and use the provided factories to keep fixtures consistent.

@talkpilot/core-db does not run in a product UI or feature branch—unit tests are the only reliable execution path to ensure your changes work. Every change must ship with a unit test so downstream repos can upgrade without surprises; treat the test suite as the canonical safety net for this core utility package.

🧹 Lint & build verification

• npm run lint – Run ESLint over src/**/*.{ts,tsx}.
• npm run format – Format the source files with Prettier.
• npm run build – Compile TypeScript and emit dist/ (used by downstream consumers).

✅ Pre-push checklist

1. npm run build.
2. npm run test.
3. npm run format.

Publishing & release notes

• Releases are handled by npm version <patch|minor|major> followed by npm publish. The package is a private @talkpilot dependency, so every contributor must install the shared npm automation token into their global ~/.npmrc before running publish or npm install.

• The shared token is rotated periodically—if you see authentication failures, request the refreshed token, update your ~/.npmrc, and retry. Never commit credentials to source control.

• After publishing, downstream repos (CIS, MIS, TalkPilot Server, etc.) should run npm update @talkpilot/core-db so they receive the latest helpers/bug fixes.

• Cloud Build & Cloud Run jobs that depend on this package pick up the new version the next time they rebuild their container; the services pull the compiled dist/ output and type definitions when installing the dependency.

• Release process, when to publish, branch hygiene, and the function-signature versioning policy: see DEVELOPMENT.md.

Version history

| Version | Note | | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1.1.9 | Maintenance release (build, test, format). | | 1.3.4 | Added new statistics getters for call & ticket dashboards (CP-149): summary, trend, hourly, and routing for calls, plus open/draft/subject ticket stats. Removed the legacy getters getTicketsCountByCityAndDateRange and getTicketsSubjectStats. | | 1.3.5 | Added the muniIssues collection & module (CP-1115): the MuniIssue document type, createMuniIssue (accepts a caller-supplied _id for a two-way Jira link), generateMuniIssueId, getMuniIssueById / getMuniIssueByJiraKey, a $jsonSchema validator with indexes (unique jira.key), and ensureMuniIssuesCollection to provision it at boot. | | 1.3.6 | Added contextNotes and products modules under src/talkpilot/. | | 1.3.7 | Added disableGenericPrompt optional boolean field to the Flow type and schema (CP-704). | | 1.3.8 | Ticket-count Map getters (CP-1200): deprecated string[] ticket getters and CallsStatsFilter fields, added Map<string, number> replacements; fixed ticket counting in aggregateCallsSummary. | | 1.3.9 | Fixed wrap-around hour filter boundary guards (CP-1166): excluded first-day morning calls and last-day evening calls from wrap-around windows. | | 1.3.10 | Added optional isDraft flag to Ticket (CP-1390): explicit draft marking with legacy fallback for existing tickets. | | 1.3.11 | Fixed pre-existing broken type declaration import in clientsConfig.types.d.ts that caused consumer tsc builds to fail. |

| 1.3.12 | Added callsWithTickets to CallsSummaryAggregation (CP-1200): distinct call count with at least one open ticket, enabling accurate ticketOpenRate and not_opened calculations in consumers. |

1.1.9

1. npm run build.
2. npm run test.
3. npm run format.

1.3.4 — Call & ticket statistics (CP-149)

New call statistics getters for dashboards (summary, trend, hourly, routing) and ticket statistics scoped to the same date range (open tickets, draft tickets, subject breakdowns).

Removed

| Removed | Replacement | | --- | --- | | getTicketsCountByCityAndDateRange | aggregateCallsSummary + findCallSidTicketCountsByCity — note: returns Map<string, number>, not string[] | | getTicketsSubjectStats | findSubjectsByCityAndDateRange | | SubjectStatsItem | SubjectItem |

Do I need to update my app?

Yes — if you used any of the removed functions above.

1.3.5 — Muni issues (CP-1115)

New muniIssues module under src/municipal/ for issues reported from the call-log screen. The document is written to the municipal DB after its Jira ticket is created.

Added

| Export | Purpose | | --- | --- | | MuniIssue / CreateMuniIssueInput | Document type and insert-input type (issueContent + jira domains). | | createMuniIssue(input, id) | Insert an issue; requires a caller-supplied _id from MIS (via generateMuniIssueId) so it matches the id embedded in the Jira ticket (two-way link). | | generateMuniIssueId() | Pre-generate the _id to pass to createMuniIssue. | | getMuniIssueById / getMuniIssueByJiraKey | Lookups by our id or by the Jira key. | | ensureMuniIssuesCollection() | Provision the $jsonSchema validator and indexes (unique jira.key); run once at MIS boot. |

Do I need to update my app?

No — purely additive. New consumers (MIS) import these from @talkpilot/core-db.

1.3.6 — Context Notes & Products

Context Notes — new contextNotes collection under src/talkpilot/ for per-client, per-product notes injected into AI calls at runtime. Each document holds a systemPrompt and a list of time-bounded entries (activeFrom, expiresAt). Two setters with split ownership: setContextNoteEntries for the notes list and setContextNoteConfig for systemPrompt and product.

Products — new products collection under src/talkpilot/ cataloguing TalkPilot products. Each document has a stable name and an optional displayName locale map (Record<string, string>) for UI display.

Added

| Export | Purpose | | --- | --- | | getContextNotes(clientId, product) | Fetch context note documents for a client/product pair. | | createContextNote(input) | Insert a new context note document. | | setContextNoteEntries(id, clientId, notes) | Replace the notes list on an existing document. | | setContextNoteConfig(id, clientId, config) | Update systemPrompt and/or product on an existing document. | | getAllProducts() | Fetch the full product catalogue. |

Do I need to update my app?

No — purely additive.

1.3.7 — Generic prompt control (CP-704)

Added an optional boolean field to the Flow type and MongoDB schema.

Added

| Export | Purpose | | --- | --- | | Flow.disableGenericPrompt | Optional boolean. When true, disables the generic prompt for the flow. Existing flows without this field continue to work unchanged. |

Do I need to update my app?

No — the field is optional and fully backwards-compatible.

1.3.8 — Ticket-count Map getters (CP-1200)

Adds Map<string, number> ticket getters that return per-call ticket counts. The old string[] getters and filter fields are kept and marked @deprecated.

Deprecated → Replacement

| Deprecated | Replacement | | --- | --- | | findCallSidsWithTicketsByCity | findCallSidTicketCountsByCity → Map<string, number> | | findCallSidsWithDraftTicketsByCity | findCallSidDraftTicketCountsByCity → Map<string, number> | | CallsStatsFilter.callSidsWithTickets | CallsStatsFilter.callSidTicketCounts | | CallsStatsFilter.callSidsWithDraftTickets | CallsStatsFilter.callSidDraftTicketCounts |

Do I need to update my app?

No — the old fields still compile. Migrate when convenient.

1.3.9 — Wrap-around hour filter boundary guards (CP-1166)

When hourFrom > hourTo (e.g. 23:00–13:00), two silent miscounting bugs existed:

• First-day morning: calls before hourTo on the first day (e.g. 08:00 on Jun 8) were counted even though the window starts at hourFrom that day. Fixed by adding dateLocal > startStr to the after-midnight leg.
• Last-day evening: calls at or after hourFrom on the last day (e.g. 23:30 on Jun 9) were counted even though the window ends at hourTo that day. Fixed by adding dateLocal < endStr to the before-midnight leg.

Affects: aggregateCallsTrend, aggregateCallsSummary, aggregateCallsHourlyByRange, aggregateCallsRouting, findFilteredCallSids, findSubjectsByCityAndDateRange.

Do I need to update my app?

No API changes — both fixes correct silent miscounting. Upgrading is recommended if you use a wrap-around hour window.

1.3.10 — Ticket draft flag (CP-1390)

Added an optional isDraft boolean field to the Ticket type.

Added

| Export | Purpose | | --- | --- | | Ticket.isDraft | Optional boolean. When true, the ticket is a draft. When false, it is not. When absent (legacy tickets), draft detection falls back to the previous event_subject_id heuristic. |

Affects: findCallSidDraftTicketCountsByCity, findCallSidsWithDraftTicketsByCity (draft filtering in ticket statistics).

Do I need to update my app?

No — purely additive. Existing tickets without isDraft continue to work unchanged. New consumers (MIS) can pass isDraft: true when creating a draft ticket.

1.3.11 — Fix clientsConfig type declaration import

Fixed a pre-existing broken import path in the published clientsConfig.types.d.ts (introduced in an earlier release, unrelated to 1.3.10). Consumer TypeScript builds could fail with:

Cannot find module 'src/utils/shared.types' or its corresponding type declarations.

The import now uses a relative path (../../utils/shared.types) so tsc resolves it correctly from node_modules/@talkpilot/core-db/dist/.

Do I need to update my app?

Yes — upgrade to 1.3.11 if your build failed with the error above (affects any version that shipped the broken declaration). No API or behavior changes otherwise.

1.3.12 — Calls-with-tickets count (CP-1200)

Added callsWithTickets to CallsSummaryAggregation — the number of distinct calls that opened at least one ticket. Use this field (instead of openTickets) to compute call-level rates and "calls without a ticket" counts.

Do I need to update my app?

Yes — replace openTickets with callsWithTickets wherever you compute ticketOpenRate or not_opened.

🛠 CI/CD & deployment

• This package is consumed by Cloud Build-based services (TalkPilot Server, MIS, CIS) as a dependency when Docker images are built. Keep dist/ in sync with your builds because the compiled artifact is what downstream services install.
• Releases require the shared npm token documented in DEVELOPMENT.md; consult that guide for contribution, linking, and token rotation procedures.

Resources

• DEVELOPMENT.md (setup, tooling, publishing, token management)
• src/test-utils and the __tests__ folder for examples of MongoMemoryServer wiring.