The Prospect Activity Model

Design brief · 2026-07 · grounded in a 9-report code + prod audit (read-only aws dynamodb query against propflow-prod and an authed 229-row pull of GET /api/leasing/prospects), adversarially re-verified against working-copy HEAD 014bb34ba

TL;DR

This was never a display bug. PropFlow has no record that anything ever happened to a prospect — only a handful of mutable scalars on one row, three of which are untrustworthy (appliedAt is the poller's clock, approvedAt never persists, signedAt is date-only). Six surfaces each invented their own answer to "when was this lead last active" from whatever scalar sat nearest, and they disagree about the same prospect within an hour of it being created.

The fix is two layers: an append-only per-prospect event log that reuses the renewal lane's existing entity-activity machinery (zero new infrastructure), and two monotone pointer fields on the inquiry row so the list stays cheap. Every surface then reads one shared accessor. A 15-kind PMS-agnostic vocabulary and five strict time-basis rules keep the log honest — rule 4 alone ("never now() labelled as an event time") is the entire appliedAt fix.

Slice 1 has shipped: the shared accessor with a first-touch fallback to inquiryDate/createdAt — the only event-time-accurate PMS stamps we have — which clears the visible dashes with no log, no new storage, and no backfill. Everything after it is proposed and held for review.

Headline findings (all prod-verified)

1. One prospect, two surfaces, opposite answers — one hour old

prospect_appfolio_app_30_1784987585948 (Camellia, source Website, applied the same day). GET /api/leasing/prospects/<id> returns conversations: 0, tours: 0, signals: 0, conversationId: null, so the list column renders "—" (no activity, ever). The same payload carries createdAt 2026-07-25T13:49:00.000Z and inquiry.appliedAt 2026-07-25T13:53:05.948Z, which the detail page renders as three active rows — "Received in AppFolio", "Synced to PropFlow", "Application Submitted".

Worse: those last two print the same instant as two different events, because appliedAt is the sync clock. (The three-row render is not universal — app_29's receipt and sync minute are identical, so it renders two.)

2. appliedAt is the sync-run clock, confirmed three independent ways

• Code — the writer assigns now.toISOString(), sixty lines from a sibling call that deliberately passes app.receivedAt with the in-code comment "never now()".
• Fingerprint — all 11 appliedAt values in prod: 10 of 11 land in a 158 ms window, at second :05.869–:06.027 of their minute. Humans do not submit applications at the same sub-second offset ten times. That is a cron tick.
• Id epoch — the prospect id is minted from now.getTime(), and its decoded epoch is byte-identical to the stored appliedAt on every id-stamped row (app_30 → 2026-07-25T13:53:05.948Z, app_29 → 12:53:05.893Z, app_28 → 01:08:05.898Z, app_25, app_21).

The drift is unbounded, not poll-bounded — a backfilled or delayed ingest can be arbitrarily late:

On this row-set, createdAt is a more truthful "when did they apply" than the field named appliedAt.

3. approvedAt is written in memory and dropped before DynamoDB

The PMS writer stamps merged.approvedAt, but the field appears nowhere in the DynamoDB writer allow-list — not in INQUIRY_C_FIELDS, not in InquiryFieldPatch, not in any setAlways/setIfAbsent in upsertInquiryProjection. grep -c approvedAt src/lib/data/dynamo/inquiries.ts = 0.

Prod: 1 of 229 rows carries it (a pre-collapse seed), and 0 of the 5 live approved-stage rows do. The detail page's "Application Approved" milestone therefore renders lit, with a blank date, on every approved prospect in production. "An approval is an activity" currently has no timestamp to read anywhere.

4. That dropped field also churns prod every 5 minutes

The sync's no-op guard compares existing.approvedAt === merged.approvedAt. Since the left side can never persist, the comparison can never match → write → value dropped → repeat every 300 s, forever. Measured live across two ticks by bounded DDB reads: exactly 8 Camellia rows re-write on every tick, and they are precisely the approved / rejected / lease-signed AppFolio cohort. Version counters: app_7 19,606 → 19,608 in five minutes; controls minted the same day sit at v=1.

This is the receipt for why a max(createdAt, updatedAt, appliedAt, …) patch was the wrong move — and the reason is sharper than "it's hacky". updatedAt is a write clock pinned to now for exactly that cohort, so a max would float the eight stalest rows permanently to the top of a recency sort while the honest 92% sorted correctly. It produces a worse ranking than the dashes. (Contrary to an earlier framing, updatedAt is not globally polluted: 158 of 229 rows are quiet >30 days. The churn is cohort-specific.)

A second bug rides along: one stamp site tests the raw mapped stage instead of the sticky computed nextStage, so a lease_signed resident whose AppFolio row still reads "Approved" is re-approved every tick. And adversarial verification found a third stamp site — in the co-applicant group-fold path added by PR #4553 — that none of the design drafts cited. A patch touching only the two documented sites leaves grouped applications churning forever.

5. Where the blanks actually live — the scoping correction

229 rows pulled; 133 render "—" (58.1%), 96 render a date. But 87% of the blanks are test data:

On the only production property with blanks, all 17 have no conversation at all — zero dangling FKs, zero personId mismatches. So on real customer data, "PMS-sourced row with no Clara conversation" is 100% of the problem, not a third of it. Scoping the work off 133 would size the identity-remediation track roughly 5× too large.

Precise cohort: Camellia has 9 PMS-born rows and 7 render "—" (app_30, app_29, app_28, app_25, app_21, app_7, gc_6) — all 3 applications filed in the last 24 h among them. The other 10 Camellia blanks are Clara-lane rows with no conversation. A fix that covers only the 7 still leaves 10 dashes on the page.

6. The column is not merely blank — it is affirmatively wrong where it does populate

Of the 5 rows carrying both appliedAt and a displayed activity date, 4 show a date older than a real event on the same row:

"—" reads as "no activity". A stale date reads as authoritative — the more dangerous failure.

The six contradictory derivations

Nothing is shared between them — not a constant, not a helper, not a type. A max-of-timestamps patch would have fixed one cell and made it seven definitions.

Funnel and period metrics add a further axis, keying off createdAt and inquiry.appliedAt with no conversation dependency at all.

The design — two layers, one accessor, one vocabulary

Layer 1 — an append-only per-prospect event log (the truth)

EntityActivityEvent rows at PK=LOG#prospect#<inquiryId>, SK=EVT#<iso>#<eventId>. Zero new infrastructure — the seam is already cut and already accepts prospects:

• entityType already includes 'prospect' (types.ts:6123), and the type's own doc comment names prospects as the intended future member.
• entityLogPK already accepts 'prospect' (helpers.ts:406-409). Per-entity partitions: naturally sharded, bounded reads, no cross-org hot key.
• channel already includes 'pms'; payload is loose by contract.
• 14 renewal writer sites prove the shape in production.
• Verified greenfield: bounded prod query on LOG#prospect#prospect_appfolio_app_30_… → Count: 0. No migration hazard.

Two structural fixes before the second entity type joins: appendActivityEvent/getActivityEvents currently live on the renewal-namespaced SagaStorage port, which is why nobody else has used them. Extract a standalone src/lib/data/entity-activity/ module and have DynamoSagaStorage delegate — not a lift onto IDataRepository, which would incur an unpriced JsonRepository implementation for the local-dev backend (local DATA_BACKEND=json gets an explicit no-op writer, acceptable because the writer is fire-and-forget by contract). And the reader currently queries with no Limit; the extracted one takes a bounded limit + ScanIndexForward: false.

Event ids are deterministic — sha1(inquiryId|type|timestamp) — because the append is an unconditional putItem and the PMS poller is at-least-once. A replayed tick must overwrite, not duplicate. (The renewal lane's random id is safe only because Temporal gives it exactly-once.)

Layer 2 — two monotone pointers on the INQUIRY# row (the speed)

lastActivityAt?: string        // max(event time) over kinds where advancesRecency
lastActivityType?: ProspectActivityType

Plus the first writer for the already-designed, zero-writer lastContactedAt, advanced only by message and completed-call events. Three deliberate calls:

• Two questions, two fields. "Anything happened" ≠ "we last talked to them." The stale-lead digest asking "who haven't we called in 7 days" reads lastContactedAt; the list column reads lastActivityAt. The disease was six accidental definitions, not two deliberate ones.
• No stored summary string. A summary is copy; storing it means a copy change requires a backfill. Render the PM-facing label from lastActivityType at the UI boundary.
• Fields go in InquiryFieldPatch but NOT INQUIRY_C_FIELDS. C-fields are seeded through setIfAbsent, so a C-field pointer would freeze at its first value forever. This is the exact posture the file already documents for the cadence stamps.

The pointer write is a sibling of updateInquiryFields, not upsertInquiryProjection (whose optimistic-lock version guard would manufacture ConflictErrors on a busy conversation). Structural precedent already in the file:

attribute_exists(PK) AND organizationId = :cOrg
AND (attribute_not_exists(lastActivityAt) OR lastActivityAt < :at)

Version-free, org-guarded, monotone, advance-only, and it never upserts a shell row. A losing concurrent write is a no-op, not an error.

One accessor, every surface

src/lib/domain/leasing/prospect-activity.ts
prospectLastActivity(p) -> { at: string|null; label: string; basis: 'event'|'observed'|'first_touch' }

Identical fallback chain everywhere: max(lastActivityAt, conversationLastMessageAt) → inquiryDate → createdAt. Never updatedAt, never appliedAt, never approvedAt, never signedAt.

Conversations are deliberately not copied into the log

Conversations and tours are already durable, timestamped entities with their own rows. Copying every message into a prospect event log is double-bookkeeping that will drift within a quarter and multiplies write volume by message count. One storage is the wrong unification target; one composer is the right one. A pure, unit-tested buildProspectTimeline({ events, conversations, tours, inquiry }) returns entries carrying a real ISO instant, its precision and its basis — formatting happens in the component. The detail page renders it; the list reads the pointer.

The event vocabulary — 15 kinds, PMS-agnostic by construction

Lives in src/lib/data/statuses/prospect-activity.ts, following the existing statuses/ convention. No vendor word crosses into src/lib/data/types.ts — pmsType and external ids ride in payload, so Yardi / RealPage adapters fill what they have and leave the rest null.

advancesRecency is a static property of the kind in this table, not a per-event boolean — a per-event flag is forgeable by a careless writer; a table lookup is not. Pure re-observations (a sync tick that learns nothing new) emit no event, so the pointer never moves. That is what makes the design categorically immune to the churn that would have poisoned a max-of-scalars.

Two notable gaps the log closes without a schema migration: there is no deniedAt field anywhere on the inquiry, and closedAt/closedReason are writable with zero writers. The log carries application_denied — that is the whole point of having a log.

Time-basis rules

Every event carries its own instant (the sort key) plus payload.timeBasis: 'event' | 'observed' and payload.precision: 'instant' | 'day'.

Unmapped PMS truth we already fetch and throw away

AppFolio's rental-application and guest-card rows are declared in our own types with ScreenedOn, ApprovedAt, DeniedAt, CanceledAt, DecisionMadeAt, LastActivityDate and LastActivityType. A repo-wide grep across src, lambda and scripts returns only those eight declaration lines. Neither adapter reads them; the vendor-neutral domain types have no slot to carry them. AppFolio ships a per-guest-card last-activity feed with a typed reason, and PropFlow drops it at the adapter boundary.

Slice plan

The one improvement over every draft design

"Persist approvedAt and the churn stops" is true — but seed-once semantics mean the five currently-approved rows would be seeded with the deploy tick's now, so a PM reads "approved today" for an approval weeks old. That is the exact class of lie being removed. Gate on a genuine transition instead:

if (nextStage === APPROVED && existingProspect.stage !== APPROVED && !merged.approvedAt)
    merged.approvedAt = nowIso;

Then for the five existing rows both sides stay undefined, the no-op guard matches, the write loop stops, and nothing is fabricated. Genuine future transitions get a stamp truthful to within one 5-minute tick. Same guard at all three sites.

Regression tests that must fail on current code: (a) two consecutive sync runs over an approved application produce updated === 0 on the second — covering the per-application path and the group-fold path; (b) a lease_signed prospect whose PMS row still reads "Approved" is not re-stamped; (c) approvedAt round-trips through the real-DDB reader harness. A unit test would not have caught (c) — that is why the field was a silent no-op for months.

Backfill posture

Deterministic replay, idempotent by construction: stable event id + timestamped sort key means a re-run overwrites, and a monotone pointer cannot regress.

Do not synthesize approvals. Zero of five live approved rows carry a timestamp and there is nothing honest to reconstruct from. The forward writer stamps a truthful first-observed time within one tick of deploy. Say that in the PR body so the gap reads as a decision, not a miss.

Risk is low and bounded because the backfill is not load-bearing — slice 1's first_touch fallback already covers all 17 production blanks, so if the backfill slips or is held, nothing regresses. Rehearse on the 116 sandbox rows first: free, and it exercises the path at ~7× production volume before touching real data.

Open questions for Fede

1. "Last Activity" vs "Last Contact" — one column or two? DECIDED (Fede, 2026-07-26): keep the UI as it is today — one "Last Activity" column, no second column. The data model still tracks the two pointers separately underneath (a submit is an activity, not a contact, and lastContactedAt already has a consumer), but no new UI column ships; the stale-lead digest reads the contact pointer without changing the page.
2. When the PMS tells us what changed but not when, what does the row say and where does it sort? Options: (a) "Approved — seen Jul 21", sorts fresh; (b) "Approved — date not recorded", does not move recency; (c) show the date with no qualifier. Lean (a) and yes-sort-fresh, because we did learn something that day. This is both customer-facing copy and a ranking rule.
3. Repair the 11 drifted appliedAt values in prod? Fixing forward is free; fixing the existing 11 is a production data write.
4. Sandbox backfill — skip or clean? 116 of 133 blanks are Willows / yale-sandbox debris. Skipping is cheaper and safer; cleaning matters only if those properties get demoed.

Flag, not a question: today the column is cross-property on "All properties" by accident. A per-inquiry pointer is more correct — a person's activity at another property is not this lead's activity — but it is a visible behavior change and belongs in the PR body.

Explicitly not being built

• max(createdAt, updatedAt, appliedAt, approvedAt, signedAt) — refused on the receipt, not on principle: it produces a worse ranking than the dashes.
• A decisionAt/decisionOutcome field pair in this arc. Right model, wrong time — the log closes the same gap without a field migration and three reader changes.
• Tour.confirmedAt — a third home for one instant (confirmation already lives in the tour history and in a partial notification surrogate). Tour completion is a genuinely missing lifecycle state and deserves its own ADR.
• A stageHistory[] array on the row the list reads in bulk — unbounded growth on the hot read path.
• Extending ActivityLogEntry: single global PK=PROP#GLOBAL partition, no prospect foreign key at all, closed maintenance/voice/renewal type union. A bounded reverse query of its 500 newest rows returns zero leasing events, and it could not join one back to a prospect if it had one.

What this makes cheap later

PM response time on applications is currently unmeasurable: there are zero application_review timing rows in prod on any property (Camellia 0, Yale 25 Station 0, The Willows 15 — all renewal countersigns, on a test property). Once decisions are events with a real occurrence time, PM response time is a projection over the log — submitted-at to decided-at — rather than a parallel writer nobody wired. Don't build it in this arc; don't add a second timing writer either. The log subsumes it.

Sources: 9-report prospect-activity audit (surface map, prod data reality, lifecycle inventory, AppFolio sync trace, adversarial verification, three competing designs, synthesis verdict). All counts from a live authed 229-row prospect pull and bounded read-only propflow-prod queries; no writes were made. Numbers reflect the state of prod at audit time and line citations were re-anchored to working-copy HEAD 014bb34ba — earlier drafts' line numbers were stale after PR #4553.