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.

---

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

---

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)
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)

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

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