People (CRM)

The people module turns the person records that ingestion creates into a CRM: lifecycle stages, tags, notes, CRM fields, a merged timeline, and saved audiences (segments) that campaigns, the Management API and the console all share.

Module key people. Routes live under /o/<org>/apps/<app>/people; mutations post to /api/console/people?intent=….

The model#

person_id in this module's tables carries no foreign key: persons belongs to the analytics module and the registry has to migrate cleanly whatever order the modules land in. The tables are scoped by app_id, which cascades from applications, and every query filters on it.

spm_person_revenue_status(app_id, person_id, status) is a plpgsql wrapper that resolves revenue_subscriptions at execution time behind to_regclass, so a revenue condition compiles and returns false on an installation where the revenue module has never run.

Lifecycle stages#

visitor → lead → signup → activated → paying, plus churned. The column is plain text, so an application may use its own value; the automatic rules only act on the stages above.

crm.stage_source records where the current stage came from:

• manual — an operator chose it. Nothing automatic ever changes it again.
• auto — the hourly people.stages.auto job set it.

The rules, in order, and each only ever moves a person forward:

1. A person who is identified and sits at visitor or lead becomes signup.
2. A person with an active or trialing revenue subscription, sitting below paying, becomes paying. Skipped entirely until the revenue tables exist.

churned and any custom stage are never entered or left automatically.

Segment DSL#

A definition is JSON:

{
  "all": [ /* every condition must hold */ ],
  "any": [ /* at least one must hold */ ]
}

all and any are combined with AND: (all…) AND (any1 OR any2). An empty definition matches everyone, which is why saving a dynamic segment requires at least one condition.

Condition#

{
  field: string,           // see the table below
  op: "eq" | "neq" | "contains" | "in" | "gt" | "lt" | "gte" | "lte"
    | "exists" | "within_days" | "not_within_days",
  value?: string | number | boolean | string[],
  event?: { name: string, count?: number, withinDays?: number }
}

Fields#

exists takes an optional boolean: { "field": "trait.plan", "op": "exists", "value": false } asserts the absence.

contains is a case-insensitive substring match; %, _ and ! in the value are escaped, so a value can never widen its own pattern.

Examples#

Signed up in the last 30 days:

{ "all": [
  { "field": "stage", "op": "in", "value": ["signup", "activated", "paying"] },
  { "field": "first_seen_at", "op": "within_days", "value": 30 }
] }

High-intent anonymous visitors:

{ "all": [
  { "field": "stage", "op": "eq", "value": "visitor" },
  { "field": "sessions", "op": "gte", "value": 3 },
  { "field": "last_seen_at", "op": "within_days", "value": 14 }
] }

Paying customers on the pro plan who have not checked out this month:

{ "all": [
  { "field": "trait.plan", "op": "eq", "value": "pro" },
  { "field": "not_did_event", "op": "eq",
    "event": { "name": "checkout_completed", "withinDays": 30 } }
] }

Anyone from paid search or a named campaign:

{ "any": [
  { "field": "first_source", "op": "eq", "value": "google" },
  { "field": "first_source", "op": "eq", "value": "meta" }
] }

How it compiles#

segmentSql(definition, { appId }, { alias?, paramOffset? }) returns { where, params }: a boolean SQL expression over a persons row (aliased p by default) and the bind parameters it refers to.

const { where, params } = segmentSql(definition, { appId }, { paramOffset: 1 });
const rows = await query(
  `SELECT p.id FROM persons p WHERE p.app_id = $1 AND (${where})`,
  [appId, ...params],
);

Two invariants hold for every definition, however it was produced:

1. Nothing from the definition reaches the SQL text. Field names resolve through a whitelist to fixed fragments. Values and jsonb keys are bind parameters. trait.x'); DROP TABLE persons; -- is not escaped — it is rejected, because it is not a field.
2. An operator a field does not support is a validation error, not a silent no-op. validateSegmentDefinition (zod) is the same gate the API, the worker and the query layer all pass through.

Dynamic segments store only a definition and a cached member_count; the directory filter compiles the definition inline, so a dynamic segment is never stale on read. people.segments.refresh recomputes the cached counts every 15 minutes, and saving or pressing Refresh recomputes immediately.

Every compute also upserts today's row in segment_member_history (UTC day, the count, computed_at), in the same statement that updates the cached count, so the two never disagree. The segment list and the segment page draw the last 30 days as a trend line beside the count; a day nobody computed repeats the day before, and a segment computed on fewer than two days shows no line yet. History is counts only, so people.read is enough to see it.

The editor's live count also shows up to five matching people, most recently seen first, to an operator who holds people.pii.read. The route adds them to the preview response only for that operator, with names and avatars already resolved; for anyone else the response carries the count alone.

PII rules#

people.read is enough to see the directory, the timeline and the stages. Reading a person's actual identity needs people.pii.read:

• Names and emails are masked (A., a•••@•••.com) everywhere without it — the directory, the dossier header, note authors.
• Reserved traits (email, phone, name, firstName, lastName) are shown as ••• in the traits panel.
• The CSV export is refused outright.
• The segment editor's preview carries no sample people.
• The distinct id and the person id are not masked: they are the identifiers an operator needs to correlate a support conversation, and they are what the application chose to send.

Redaction is decided once, by the page, and passed to the components as a pii boolean, so a component cannot widen it by accident.

Writes need people.write; a viewer can read everything they are allowed to see and change nothing. Every write is audited: people.updated, people.bulk_updated, people.note_added, people.exported, segment.saved, segment.deleted, segment.refreshed.

Changing a person's stage, tags or a CRM field, and saving an existing segment, confirm with Undo. The write answers with its audit id; the Undo posts the same intent with the previous values and undoOf=<audit id>, which the route accepts only for the caller's own people.updated or segment.saved on the same person or segment within ten minutes, once. The reversal is audited like any write, with metadata.undoOf naming the original. Both reversals are blind (neither record has a revision), so a colleague's change inside the window is overwritten. Creating or deleting a segment and bulk edits have no Undo.

CSV export#

POST /api/console/people?intent=export-csv with the directory's filters streams text/csv, paged by person id so a large export never sits in memory.

Always present: id, identified, stage, tags, first_seen_at, last_seen_at, sessions, events, pageviews, first_source, last_source, country, device, company, plan, owner.

Only with people.pii.read: distinct_id, email, name.

A value that begins with =, +, - or @ is prefixed with an apostrophe, so a trait a tenant's own user typed cannot become a formula in a spreadsheet.

Jobs#

Both jobs return immediately when persons does not exist yet, and one bad segment definition never stops the sweep.

Query layer#

src/lib/people/queries.ts is the only place that reads a person, and it is what the Management API and campaigns import:

type PeopleScope = { orgId; appId; envId?; timezone? };

searchPeople(scope, { q, stage, tags, segmentId, identified, sort, direction, cursor, limit })
countPeople(scope, filters)
getPerson(scope, personId)
personTimeline(scope, personId, { before, limit, kinds })
personSessions(scope, personId, limit)
personSegments(scope, personId)
personRevenue(scope, personId)           // null until the revenue module exists
updatePerson(scope, personId, { stage?, tags?, crm? }, actorId)
bulkUpdatePeople(scope, personIds, { stage?, addTags?, removeTags? }, actorId)
addNote(scope, personId, body, authorId)
listSegments(scope) / loadSegment(scope, id) / saveSegment(scope, input, actorId)
deleteSegment(scope, id) / refreshSegment(segmentId)   // also upserts today's history row
sampleSegment(scope, definition, { pii, limit? })      // masked unless pii; the route asks only with pii
addSegmentMembers(scope, id, personIds, actorId) / removeSegmentMembers(scope, id, personIds)
exportPeopleCsv(scope, filters, { pii })  // ReadableStream<Uint8Array>
peopleOverview(scope, { from, to })
applyAutomaticStages({ orgId, appId })
listStages(scope) / listTags(scope, limit)

// src/lib/people/segment-history.ts
memberTrend({ appId }, segmentIds, days = 30)  // Map<segmentId, number[]>, oldest first
pruneSegmentHistory(appId, keepDays = 400)

The directory pages with an opaque cursor (base64 of the sort key plus the person id), so a page boundary stays correct while ingestion writes underneath it. envId narrows events, sessions and errors to one environment; people themselves are not environment-scoped.

What the assistant can do here#

Pilot searches people by text, stage, tag or segment, reads one person's dossier and timeline, and lists segments. Personal data stays gated: without people.pii.read emails and names come back masked, and the assistant treats a masked value as hidden rather than absent — it will not guess the characters or infer the person from surrounding traits.

Updating a person's stage, tags or CRM fields and creating a segment are proposals shown with the before and after; people.write and your approval are both required. A person's own traits are tenant data, so a trait containing something that reads like an instruction is reported as a value and nothing more.