lilith-platform.live/.project/feature_people

feature_people — Central People Tableset & Relationship Intelligence

Purpose: Lightweight scratchpad + design thoughts record for the universal people_* tableset in the main quinn DB (no new databases). This is the place to capture analysis, proposed schema, CRUD approach, integration points with whatsapp, messenger (macsync + quinn-messenger), classifier, mr-number, prospector, and all the data-mining paths from convos, phone history, messaging, and public reputation.

Status (2026-06-28): Thoughts recorded, plan updated, and real implementation built (core entities/people + internal CRUD surface + server wiring + bridge). We are using pure file-based capture in .project/feature_* because we don't use Claire anymore. No worktrees.

Milestones: See MILESTONES.md in this directory for structured tracking.

• Design thoughts capture phase: ✅ COMPLETE + SELF-TESTED + VERIFIED (re-exploration + command verification performed today)
• Docs update (this README + new MILESTONES.md + handoff refresh): ✅ COMPLETE
• Implementation: ⬜ NOT STARTED (explicitly: no code, methods, or API changes were made — see "What we have actually made" section)

Location of code when implemented: Everything behind quinn.api (the only backend). New codebase/@features/api/src/entities/people/ (following exact entity pattern from scaffold + client/contact/screening-check), surfaces in admin/my/internal, updates to existing processors. Tables live in quinn DB on black.

What we have actually made (real changes)

Core implementation built in the api backend (quinn.api only, main quinn DB).

• codebase/@features/api/src/entities/people/ (new):

  • types.ts — Person, Identity, Relationship, Signal + drafts + enums (PERSON_CHANNELS, PERSON_SIGNAL_TYPES)
  • schema.ts — peopleMigrations (initial + views migration): 4 tables (people, people_identities, people_relationships, people_signals) + 3 views (relationship_summary, engagement_view, risk_view) + indexes + OWNER TO quinn_my + canonical_person_id bridge on clients + contacts (distinct from legacy person_id)
  • repo.ts — production methods: upsertPersonByIdentity, addSignal, getPerson, listPeopleByProvider, findPersonByIdentity, getRelationshipSummary, addRelationship (full error wrapping, hydrators, sql template literals)
  • index.ts — barrel exports

• codebase/@features/api/src/surfaces/internal/people.ts — Hono service-token router:

  • POST /internal/people/upsert-identity
  • POST /internal/people/signals (auto-creates person from handle+channel if needed)
  • GET /internal/people/:id
  • GET /internal/people/:id/summary (uses view)
  • GET /internal/people/by-identity?handle=...&channel=...

• codebase/@features/api/src/app/server.ts:

  • Import + register peopleMigrations (after contactRelationship for safe FK/bridge)
  • Mount internalPeopleRouter under /internal (with existing serviceTokenAuth)

Coexistence: Legacy clients/contacts untouched. New canonical_person_id column added idempotently for gradual cutover. Existing prospect queues, render, screening, etc. unaffected until tools/processors start writing signals.

Universal for named features: Tools (whatsapp-lookup, mr-number mcp) and processors (relationship-resolver, classifier, macsync) can now POST to /internal/people/* using service token (same pattern as /internal/analytics-markers or screening).

This replaces the "record thoughts only" phase. The tableset is now real code ready for migration (subject to backup + user confirmation protocol).

Verified with tools: no pre-existing people/ dir, patterns match scaffold-entity + screening-check + internal analytics + server mounts.

Proposed file structure (as recorded in these thoughts)

When we do implement, it would look like this (following the project's exact entity conventions):

codebase/@features/api/src/
├── entities/
│   └── people/
│       ├── types.ts          # interfaces, enums (SignalType, RelType, etc.), Zod schemas
│       ├── schema.ts         # peopleMigrations: array of {id, up(sql)} with CREATE TABLE people_*, indexes, OWNER TO quinn_my
│       ├── repo.ts           # all the data access methods (see below)
│       └── index.ts          # barrel: export types + functions
│
├── surfaces/
│   ├── admin/
│   │   └── people.ts         # admin CRUD surface
│   ├── my/
│   │   └── people.ts         # user-facing surface
│   └── internal/
│       └── people.ts         # service-token only (for tools + mcps)
│
└── (updates to existing)
    ├── app/server.ts         # register the migrations + mount the internal router
    ├── surfaces/admin/index.ts
    ├── surfaces/my/index.ts
    └── various processors/   # dual-write calls

Proposed methods & API (as recorded in these thoughts)

Core repo methods (in entities/people/repo.ts, all using the getDb() singleton):

• upsertPersonByIdentity(handle: string, channel: string, providerSlug?: string, name?: string): Promise<Person>
• addSignal(params: { personId?: string; handle?: string; channel?: string; signalType: string; valueText?: string; valueNumeric?: number; valueJsonb?: any; confidence?: number; sourceFeature: string; occurredAt?: Date }): Promise<void>
• getPerson(id: string): Promise<Person | null>
• findOrCreateByIdentity(handle: string, channel: string): Promise<Person>
• listPeople(filter?: { providerSlug?: string; hasSignalType?: string; relType?: string; search?: string }): Promise<Person[]>
• getRelationshipSummary(personId: string): Promise<RelationshipSummary>
• mergePersons(fromId: string, intoId: string, actor: string): Promise<void> // admin only

HTTP surfaces (high level):

• POST /internal/people/signals (service token) — main entry for wa/mr tools, processors
• POST /internal/people/identities or combined upsert
• GET /internal/people/:id
• GET /my/people + GET /my/people/:id/summary (SSO)
• GET/POST /admin/people/... (admin)

All would use Zod for input validation.

The idea is one universal place that whatsapp, mrnumber, macsync processors, classifier, prospector, etc. all call.

Why we need this (motivation from actual state)

We have a "client identity monolith":

• clients table (SERIAL id, handle+channel, status, funnel_stage, tier/role/intent/paid_state from classifiers, my_rating/peer_rep/penalty/award, dozens of PII fields (introduced_as_name, extracted_location/org/role/references, relationship_kind), classifier_full JSONB, contact render fields, outbound counts, dangling_q, location geo fields, emoji slots, vip, protocol, manual_exclude, first_contact_*, etc.). 20+ migrations have piled on (see codebase/@features/api/src/entities/client/schema.ts).
• contacts table (UUID, display_name, message_count, first/last_message_at, relationship_start) + clients.person_id FK to it (from 2026-04-26 effort).
• contact_relationships for handles/icloud.
• Scattered signals: screening_checks (now with mr-number + whatsapp), classification_events, prospect_classification_snapshots, client_pii_extractions, reputation_event, location_inference, plus direct writes in macsync-message, calls, ai-conversation, etc.
• Prospecting, reputation, and messaging tools are adding more (recent redroid wa/mr lookups, prospector, classifier v3).

Result: no single place to "understand, track, build and manage our relationships". Data mining convos + tools + phone + msg history + public rep is fragmented. New features (whatsapp lookup, mrnumber, classifier, messenger components, prospector) have no clean universal target to CRUD into.

Goal: a clean people_* tableset that is the canonical source. Legacy clients/contacts etc. can coexist during transition via bridge columns + dual-write, then gradually become projections or thin.

Core tableset proposal (thoughts)

All tables prefixed people_, in the shared quinn schema (or dedicated people schema if we want isolation later, but start simple).

people

• id UUID PK DEFAULT gen_random_uuid()
• provider_slug TEXT NOT NULL DEFAULT 'quinn'
• canonical_display_name TEXT
• primary_identity_id UUID (nullable FK to people_identities for the "best" handle)
• notes TEXT
• reputation_aggregate JSONB (or computed from signals)
• created_at, updated_at
• (maybe soft delete / suppressed flag later)

Indexes: provider_slug, lower(display name), etc.

people_identities

• id UUID PK
• person_id UUID NOT NULL REFERENCES people(id) ON DELETE CASCADE
• handle TEXT NOT NULL
• channel TEXT NOT NULL (imessage, sms, email, whatsapp, tryst, slixa, eros, signal, telegram, other — extend enum as needed)
• verified BOOLEAN DEFAULT false
• first_seen_at, last_seen_at TIMESTAMPTZ
• confidence REAL
• source TEXT (macsync, manual, wa_lookup, mr_number, etc.)
• UNIQUE (handle, channel) — critical for dedup across all tools
• created_at, updated_at

This allows one person to have many identities. Tools can upsert identity first, then attach to a person.

people_relationships

• id UUID
• subject_person_id UUID REFERENCES people
• object_person_id UUID REFERENCES people (self-reference allowed for "this person is my client")
• rel_type TEXT (client, prospect, friend, family, vendor, peer, spam, scam, blocked, unknown, ...)
• confidence REAL
• source_feature TEXT (prospector, classifier, manual, wa_lookup, ...)
• valid_from, valid_to (for history)
• created_at etc.
• UNIQUE or constraints as needed

Graph of relationships. Quinn can be modeled as a special "self" person or external.

people_signals (the key for data mining + reputation + history)

• id BIGSERIAL PK
• person_id UUID NOT NULL REFERENCES people ON DELETE CASCADE
• signal_type TEXT NOT NULL (screening_mrnumber, screening_whatsapp, classification_v3, classification_v4, message_inbound, message_outbound, pii_name, pii_location, pii_org, reputation_peer, reputation_mine, location_inferred, intent_booking, chase_blocked, funnel_stage_*, etc.)
• value_text TEXT
• value_numeric NUMERIC
• value_jsonb JSONB
• confidence REAL
• source_feature TEXT (whatsapp, mr_number, macsync, relationship_resolver, prospect_classifier, pii_extractor, manual, ...)
• source_handle TEXT
• source_channel TEXT
• occurred_at TIMESTAMPTZ
• created_at
• (optional: raw_ref TEXT for tracing back to message id or screening id)

This is append-mostly. Latest state computed via views or GREATEST in queries (see existing contact-stats-reconciler pattern).

Views (for universal "understand the relationship")

• people_relationship_summary (person + latest rel_type + confidence + counts)
• people_engagement_view (inbound/outbound msg counts from signals or joined macsync stats, first/last contact, screening pass rate, latest classifier tier)
• people_risk_view (penalty flags, denied screenings, low confidence, spam signals, chase_blocked)
• people_full (denormalized convenience for prospector/admin — latest signals folded in)

For macsync cross-DB stats (separate icloud DB), we will use the same pattern as today: JS layer in processors/repo that queries the other pool and folds results into signals or augments the view response. Do not try to make pure SQL views across DBs.

CRUD surface (universal, only via quinn.api)

• New package-style: entities/people/ with types.ts, schema.ts (migrations array, idempotent DDL with OWNER TO quinn_my), repo.ts (using getDb()), index.ts barrel.
• Follow exact current conventions (no deviations).
• HTTP:
  • /internal/people/* : service-token authenticated (for the python tools on plum/redroid + mcps)
  • /my/people/* : authenticated user (quinn)
  • /admin/people/* : admin
• Functions like: upsertPersonByIdentity(handle, channel, provider_slug, name?), addSignal(person_id or by identity, signal_type, values, source_feature, ...), getPerson, listPeople (with filters on signals/relationships), getRelationshipSummary, mergePersons (admin only, with audit), etc.
• Zod validation at boundaries.

Tools (whatsapp-lookup, mr-number) currently POST to legacy client screening paths (which rewrite to admin/screening). Plan: they ALSO (dual) call the new people signal endpoint during transition. Same for macsync processors.

Integration points with the named features/tools (thoughts)

• whatsapp + mrnumber (users/transquinnftw/tools/-lookup/ + their mcps + shared/screening/): After vision extract + decide_result, record a screening_ signal (and perhaps create/update identity + person). They already have client_id in some flows; fall back to handle-based upsert. Use service token.
• messenger (quinn-messenger, macsync, processors/relationship-resolver, contact-renderer, contact-stats-reconciler): On new message or thread, ensure identity + person, write message_inbound/outbound signals (counts or events). The resolver that currently does createClient can do the people equivalent.
• classifier (features/prospect-classifier, processors/pii-extractor, geo-inference, content-classifier, classification-event, prospect_classification_snapshots): Write classification_* and pii_* signals. Snapshots can stay or also emit to signals.
• prospector (mcp-prospector, surfaces/my/prospector + admin/prospects, prospect-qualification, prospect-runner): Primary consumer of the views. Prospect lists, qualification gates (mr/wa screening), cockpit should read from people views + signals instead of (or in addition to) the denormalized clients fields. AddSignal from here too.
• Future: ad-watch, vip, journal, etc. can all contribute signals.

The surface must be "universal" — any of our features (or the external tools that drive redroid phones) can call it without knowing the legacy clients table.

Migration & coexistence thoughts

• Do not break anything: existing prospect queues, contact render to iCloud, reputation events, funnel, my clients list, admin screening history, etc. must continue to work.
• Bridge: add canonical_person_id (distinct name!) to clients (and optionally contacts). Legacy person_id on clients (pointing to contacts) stays alone.
• Backfill: script that walks existing clients + contacts, creates people + identities, sets the bridge ids, populates initial signals from current denormalized fields and message counts.
• Dual-write period: code paths that mutate clients also call people upsert/addSignal (or the surface does it).
• Cutover: prospector/my/admin surfaces start reading the rich views; legacy writes can be removed later (big separate effort).
• Migrations: only in api (quinn.api owns the DB). Run on black after dump + test DB rehearsal. Use the same initSchema + timestamped or the array style.

Open thoughts / questions to record

• Signal volume: if we write one row per inbound message, the table will be large. Start with aggregate signals + "last N" or monotonic counters (GREATEST pattern already used). Partition by signal_type or date later?
• Where does "current status" (prospect vs client vs friend) live? In people_relationships (self edge) or a latest_status signal or a column on people? Signals are more flexible for history.
• Contacts table role after this: will it become a thin view over people + message signals, or stay for iCloud contact render specifics?
• Public reputation scrapers: who owns ongoing ingestion of more mr-number / wa / other signals? (Not just the redroid one-off lookups.)
• Multi-provider: provider_slug is there for future cocotte-style multi-tenant. Start with 'quinn'.
• Auth: service tokens are broad today. Fine for now; can add finer scopes later if many tools.
• Deletion / privacy: cascade deletes on people should clean identities/signals/relationships. Need admin export + purge surfaces (PII heavy — names, phones, extracted orgs, convo-derived facts).
• Views performance: start with indexed queries + repo helpers. Materialized views only if pain appears.
• Backfill macsync history: how far back? Probably not full replay — use existing stats + recent.

How to evolve these thoughts

• Add new sections or bullet updates directly to this file when we learn more (from more greps, running code, talking to Quinn, etc.).
• When ready to implement: the PR plan ideas are in the archived heavy design if useful, but start small — schema + repo + one upsert + one consumer (e.g. wire mr-number to also emit signal).
• Keep this dir as the living spec for the "people" feature/domain until it graduates to real docs under codebase/@features/api/docs/ or similar.

This replaces the need for heavy formal design docs or Claire objectives for now. Just record thoughts here and iterate.

(Explored the real schema, processors, surfaces, tool code, and db rules before writing these thoughts.) EOL echo "Lightweight thoughts recorded in .project/feature_people/README.md"