Data Architecture

Field	Value
Status	live (current-state)
Owner	INFRA / DIR
Applies to	All Partile persisted and ephemeral data: the Postgres system of record, Redis ephemera, the upstream app-event plane, and the designed downstream ML data plane.
Last updated	2026-06-14
Evidence	partile-infra migrations 0001_init.sql / 0002_auth.sql / 0003_safety_retention.sql / 0004_account_erasure.sql / 0005_consent_events.sql / 0006_ml_snapshots.sql / 0007_connection_requests.sql / 0008_profile_intents.sql / 0009_match_rationales.sql / 0010_seed_accounts.sql; infra PR-21 (52cd2c8) boarding-pass-gated /presence contract; infra PR-22 (2680a01) message send; infra PR-23 (75d8337) in-window directory; infra PR-24 (a6cf2af) connection requests; infra PR-25 (0934291) profile/intent tags; infra PR-26 (e40f0fd) internal-signal ranking; infra PR-27 (e43b60c) cached Claude rationale; infra PR-28 (d2db03f) stateless Claude profile interview; infra PR-29 (1fbcf6e) persistent connections list; infra PR-30 seed/purge tooling; mobile PR-15 (a078460) boarding-pass UI/state gate; mobile PR-16 (305b387) camera barcode scan/on-device BCBP parse/sanitized confirmation; mobile PR-17 (c478b26) Photos/Files image import/local barcode decode/cache cleanup; mobile PR-18 (075b035) physical iPhone scan/import/gate/unlock QA and camera-preview hard-gate fix; mobile PR-19 (56ee67f) synthetic BCBP parser fixtures/tests; mobile PR-20 (1527911) stable non-sensitive selector contract for gated MVP screen states; mobile PR-21 (245d5d6) hard-gated local/dev automation seams and npm run test:automation; mobile PR-22 (c49fc1a) Maestro E2E scaffold and npm run test:maestro; mobile PR-23 (ea3aaac) message composer; mobile PR-24 (c441157) directory/stars; mobile PR-25 (12615d7) connect requests plus restored pre-conversation safety; mobile PR-26 (88cf083) profile editing; ../registers/ropa.md, ../registers/retention-schedule.md; readiness/data-inventory-and-retention.md; readiness/ml-event-taxonomy.md; ../control-register.md (PRIV-, ML-).

Current State

Partile's system of record is PostgreSQL - 16 tables across nine forward-only migrations. Redis holds only short-lived security/operational state. The upstream consent/event plane exists (user_consents, app_events, strict emit_event, product-safe try_emit_event), infra PR-18 emits auth, presence, match-action, and safety hookpoint events, infra PR-19 emits worker match lifecycle events, infra PR-20 adds anonymous ML snapshot tables, infra PR-22 emits message.sent, and infra PR-24 emits connection request lifecycle events. There is still no analytics warehouse, model-training pipeline, or model store. Infra PR-21 now gates the authenticated product /presence write behind a decoded boarding-pass marker (verification_source=boarding_pass_barcode, verification_level=document_verified) and rejects manual/self-reported/ unverified product presence. Mobile PR-15 removes the manual presence product path and locks product surfaces behind the same active verified-presence check. Mobile PR-16 adds camera barcode scan, on-device BCBP parsing, sanitized confirmation, and document-verified presence creation while keeping raw barcode content transient. Mobile PR-17 adds Photos/Files image import through local barcode decode, shared confirmation/submission, and cache-copy cleanup; PDFs/ non-images are unsupported and fail closed. Mobile PR-18 physically verifies the iPhone scan/import/gate/unlock flow using sanitized evidence and fixes a camera preview hard gate without changing the no-raw-retention boundary. Mobile PR-19 adds automated synthetic BCBP parser tests for valid derived-only output, fail-closed invalids, default TTL, malformed optional fields, and sanitized test output with no raw payloads. Mobile PR-20 adds stable, non-sensitive UI selectors for later automation; selector values are public test metadata and do not encode dynamic IDs, user/account values, file names, MIME values, or boarding-pass content. Mobile PR-21 adds hard-gated local/dev QA seams for automation: in-memory sign-in, synthetic scan/import controls, and an in-memory presence/API store that is enabled only with __DEV__ plus EXPO_PUBLIC_PARTILE_QA_AUTOMATION=1. These seams do not introduce a retained data class and are absent from default/EAS/product builds. Mobile PR-22 adds Maestro E2E flows and a headless static validator over those hard-gated synthetic paths; it adds no retained data class, real pass artifact, account, token, device identifier, or production bypass. The backend is minimal by construction: no raw boarding-pass artifacts are stored, session tokens are stored only as hashes, and account erasure has a soft-delete clock plus worker hard-purge path. Infra PR-22 adds backend message send into the existing conversation tables with metadata-only message.sent events. Infra PR-23 adds the in-window directory without a new retained data class. Infra PR-24 adds connection_requests for directional request-with-note, accept/decline state, presence references, and the conversation bridge. Infra PR-25 adds self-authored professional profile summary and intent_tags to traveler_profiles; headline already existed. Infra PR-26 ranks directory entries using internal profile signals without adding a retained data class. Infra PR-27 adds match_rationales for cached pair rationale sentences keyed by minimized-input hash and expiry. Infra PR-28 adds a stateless /profile/interview route; it sends bounded role/content turns to the provider when keyed, returns 503 without a key, and does not write summaries, events, or logs. Infra PR-29 exposes accepted, non-blocked contacts through GET /connections over existing accepted connection_requests. Infra PR-30 adds users.is_seed plus explicit seed/purge tooling for pre-launch mock traveler graphs.

For the authoritative field-level inventory and provisional lawful bases, see the RoPA (../registers/ropa.md) and readiness/data-inventory-and-retention.md; this document is the narrative shape, not a duplicate of those tables.

Persisted data classes (where the sensitive data lives)

Domain	Tables	Class	Notes
Identity / profile	users, traveler_profiles, external_identities	PII / PII-link / UGC	LinkedIn sub, email, name, picture; self-authored headline, summary, and intent tags; users.is_seed marks mock seed accounts; display fields shown to cards where minimized
Sessions	app_sessions	security	Only a SHA-256 token hash + timestamps; never plaintext
Proximity / presence	flight_presences	derived personal	airport/terminal/gate/departure + window times + verification level
Matching / connection requests	match_candidates, connection_requests, match_rationales	derived personal / UGC	user pair, presence context, request note, accept/decline state, conversation bridge, cached rationale sentence and minimized-input hash
Messaging	conversations, conversation_messages	UGC	message body for accepted conversations
Trust & safety	user_blocks, user_reports	derived / UGC	block pairs; report reason (closed enum) + details + status
Consent	user_consents	consent record	granular analytics / ML-training / marketing grants with notice-version evidence
Events	app_events	pseudonymized event	upstream event store for auth/presence/match/safety/message/connection hookpoints; actor-linked rows cascade on user delete
ML snapshots	ml_training_snapshots, ml_training_snapshot_rows	anonymous aggregate	k-anonymous aggregate rows from whitelisted T2 events only; no actor/object ids or per-event timestamps; survives user erasure

erDiagram
  USERS ||--|| TRAVELER_PROFILES : has
  USERS ||--o{ FLIGHT_PRESENCES : creates
  USERS ||--o{ CONNECTION_REQUESTS : requests
  USERS ||--o{ CONNECTION_REQUESTS : receives
  USERS ||--o{ MATCH_CANDIDATES : side_a
  USERS ||--o{ MATCH_CANDIDATES : side_b
  USERS ||--o{ CONVERSATION_MESSAGES : sends
  USERS ||--o{ MATCH_RATIONALES : pair_member
  FLIGHT_PRESENCES ||--o{ CONNECTION_REQUESTS : presence_ref
  MATCH_CANDIDATES ||--o| CONVERSATIONS : opens
  CONVERSATIONS ||--o{ CONVERSATION_MESSAGES : contains

  USERS {
    uuid id PK
    text status
    boolean is_seed
    timestamp deleted_at
  }
  TRAVELER_PROFILES {
    uuid user_id FK
    text headline
    text summary
    json intent_tags
  }
  FLIGHT_PRESENCES {
    uuid id PK
    uuid user_id FK
    text airport_code
    timestamp starts_at
    timestamp expires_at
    text verification_level
  }
  MATCH_CANDIDATES {
    uuid id PK
    uuid user_a_id FK
    uuid user_b_id FK
    text status
    timestamp expires_at
  }
  CONNECTION_REQUESTS {
    uuid id PK
    uuid requester_user_id FK
    uuid target_user_id FK
    text note
    text status
  }
  CONVERSATIONS {
    uuid id PK
    uuid match_candidate_id FK
    text status
  }
  CONVERSATION_MESSAGES {
    uuid id PK
    uuid conversation_id FK
    uuid sender_user_id FK
    text body
  }
  MATCH_RATIONALES {
    uuid id PK
    uuid user_a_id FK
    uuid user_b_id FK
    text rationale
    timestamp expires_at
  }

Re-identification rule (load-bearing): airport_code + gate + departure_time + time-of-day is a small anonymity set. Presence and match data are treated as personal data even though no name is attached to a row. This is why proximity rows are classified derived/personal, not internal, and why the ML anonymization boundary is the gating control.

Redis (ephemeral security data)

Key	Purpose	Lifetime
OAuth state	OIDC CSRF protection	~10 min TTL, single-use (atomic GETDEL)
Mobile handoff code	One-time auth handoff (no token in URL)	120s TTL, single-use, SHA-256-keyed
ratelimit:<bucket>:<hash>	Fixed-window rate-limit counters	per-window TTL; identities SHA-256-hashed

Redis holds no durable personal data - only self-expiring security state and hashed counters. It is reconstructable and is not a backup target by default (../policies/backup-and-recovery-policy.md).

Matching and rationale pipeline

flowchart LR
  presence[Document-verified flight_presences] --> job[matching Cloud Run Job]
  job --> overlap[Find overlapping airport and time window]
  overlap --> blocks[Apply symmetric block separation]
  blocks --> rank[Rank with internal profile signals]
  rank --> candidates[(match_candidates)]
  candidates --> key{ANTHROPIC_API_KEY set?}
  key -->|yes| prompt[Minimized pair prompt]
  prompt --> claude[Anthropic Claude]
  claude --> rationales[(match_rationales)]
  key -->|no| noai[No rationale text]
  candidates --> directory[Ranked directory and connect flow]

What is deliberately NOT stored

• No raw boarding-pass artifacts. Product presence intake rejects 11 banned fields (barcode, PNR, record locator, ticket/seat number, passenger/legal name, ...) at the Pydantic layer. verification_source is required to gate the write but not persisted; flight_number/airline are accepted but not persisted or returned. Only derived, non-secret context is kept (PRIV-06, SEC-06). The backend half of the PD-012 boarding-pass gate is implemented in infra PR-21; mobile PR-15 implements the UI/state gate; mobile PR-16 implements camera scan/on-device parse/confirmation/create; mobile PR-17 implements Photos/Files image import through the same local decode/confirmation/create path; mobile PR-18 physically verifies the iPhone scan/import gate and records only sanitized/synthetic evidence; mobile PR-19 adds synthetic parser tests proving the parser output remains derived-only and invalid inputs fail closed; mobile PR-20 adds non-sensitive selectors for those UI states; mobile PR-21 adds hard-gated local/dev automation seams using static non-secret fixtures, with no retained raw pass material and no production/default bypass; mobile PR-22 adds Maestro E2E flow scaffolding and static validation for those same deterministic paths without adding raw artifacts or persisted automation data.
• No plaintext session tokens; no OAuth access/ID/refresh tokens persisted. The LinkedIn id_token is verified in memory via JWKS before identity is trusted.
• No special-category attributes - never inferred, derived, or stored (Art 9 hard constraint; ML-02).
• No device IDs, push tokens, analytics warehouse, model-training pipeline, or model store. PR-18 adds upstream auth/presence/match/safety product event emissions; PR-19 adds worker match.candidate_proposed / match.expired emissions as actor-scoped T1 rows; PR-20 adds only the anonymous aggregate snapshot boundary; PR-22 adds metadata-only message.sent; PR-24 adds note-free connection request lifecycle events; PR-27/PR-28 smokes verify rationale/interview text is not emitted to events.

Boarding-pass intake boundary (backend contract + scan/import implemented)

The product requires a verified boarding pass to create presence (PD-012). Infra PR-21 implements the backend product contract for that boundary, mobile PR-15 implements the gate/lock shell, mobile PR-16 implements the camera scan path, mobile PR-17 implements Photos/Files image import, mobile PR-18 physically verifies the iPhone scan/import/confirm/create/unlock path, mobile PR-19 adds automated synthetic parser coverage for that decode boundary, mobile PR-20 adds stable UI selectors so later automation can address those states, and mobile PR-21 adds hard-gated local/dev automation seams for deterministic QA. Mobile PR-22 layers Maestro flows and static validation on top of those seams. Barcode decode happens on-device, the traveler confirms only derived trip details, and the app submits the document-verified presence. PDF/document inputs are not decoded today and fail closed unless a later local, no-retention decoder is added:

• Raw material is transient. For PR-16 camera scan, the decoded barcode payload (IATA BCBP) is passed directly into the parser and discarded after deriving a normalized trip. For PR-17 image import, the picked URI is a local function argument, local decode uses scanFromURLAsync, and cache copies are deleted best-effort after decode. Raw material is not retained - not stored in Postgres, not logged, not put in navigation params, not put in an event, and not sent to the API. It exists only for the duration of decode/verify.
• Only minimal normalized trip/presence fields persist. From a decoded pass the backend keeps the same minimal, derived flight_presences context (airport, derived window, verification level) - never the barcode payload, PNR, record locator, ticket/seat number, or passenger/legal name. The PR-16/PR-17 parser path reads only origin airport, optional destination display, optional carrier/flight, and a conservative TTL. Mobile PR-19 turns those parser expectations into a repeatable synthetic test and asserts no raw/PII fields are exposed from the parser result.
• Barcode decoding, not OCR, is the verification source. OCR may only fill a label when barcode extraction fails; it does not establish trip verification.
• No new sensitive data class. Because raw material is transient and only the existing minimal presence fields are stored, the backend gate adds a verification step, not a new retained data class. Boarding-pass legal basis / minimization / retention is queued for counsel at C25.
• Scanner robustness hardening (scanner PR-1, c0aaa71) preserves the minimization boundary. The on-device BCBP parser was reworked from a fixed 60-char/one-leg reader to a Resolution 792-conformant walk (common + repeated mandatory legs, declared-size conditional section, optional security tail) with graceful degradation (a usable trip can still derive from a leg with a trustworthy origin; it fails closed when none is trustworthy). The image decode path now generates transient normalized variants (resize/crop/rotate via expo-image-manipulator) and retries scanFromURLAsync to localize a small barcode inside a large screenshot. Crucially this changes robustness only, not data exposure: the public parser still returns derived-only fields (origin, optional destination/carrier/flight, TTL) and never name/PNR/seat/sequence/ document/loyalty/baggage/raw-payload; variant image files are temporary and deleted; raw material remains non-retained.
• New data flow — sanitized scanner failure telemetry (PII-free by construction). On decode/parse failure the app now emits a Sentry event (scanner.failure) carrying ONLY a closed set of enumerated fields: failure class, attempted symbologies, file kind, rounded image dimensions, decode attempt/variant counts, and parser stage/reason codes (fixed string-literal unions). The event type is structurally incapable of carrying the raw payload, passenger name, PNR/record locator, seat, check-in sequence, document/loyalty number, file URI/path, or image content; it fires only when a Sentry DSN is configured. Director substance-verified at code level (telemetry.ts, bcbp.ts) and via the telemetry sanitization unit tests. Subprocessor: Sentry (already recorded). Adds a diagnostic signal, not a new retained sensitive class.
• Decode engine = on-device Apple Vision (scanner PR-2, 0176ce8); minimization unchanged. Static-image barcode reading moved from expo-camera scanFromURLAsync to a first-party Apple Vision (VNDetectBarcodesRequest) native module, because the former could not read PDF417 from real screenshots. The native bridge returns only barcode payload strings, transiently, straight into the existing PR-1 parser — it never logs/stores/returns the raw payload, the image, or the file path. No new data class; the engine swap is robustness-only.
• Share-to-import hand-off now uses the App Group shared container (scanner PR-2). The iOS Share Extension hands the boarding-pass file to the main app via an App Group sharedData path (the deep-link handler rejects raw extension-sandbox URIs), opens the host app via extensionContext.open, and the copied file is transient/cleaned after import. Same minimization boundary as camera/import: only derived non-secret trip fields persist; raw image/barcode never leaves the device or is retained. Director substance-verified at code level.

Retention & Erasure Posture

flowchart TB
  seed[app.seed one-shot] --> seed_users[(users where is_seed = true)]
  seed_users --> seed_graph[Mock profiles, presences, requests, conversations, messages, rationales]
  purge[app.purge_seed before GA] --> event_refs[Delete seed-related app_events object refs]
  event_refs --> delete_seed[Delete is_seed users]
  delete_seed --> cascades[FK cascades remove seed-owned graph]
  smoke[app.seed_smoke] --> verify[Verify purge and real marker survives]

  retention[retention Cloud Run Job every 5 min] --> sessions[Expired sessions]
  retention --> presences[Expired presences]
  retention --> candidates[Terminal candidates]
  retention --> conversations[Stale conversations and messages]
  retention --> deleted[Soft-deleted users after grace]

• Retention is real and enforced by the worker sweeps delivered in infra PR-12 (PRIV-03, SEC-12 - implemented): sessions 30d after expiry/revoke, presences 7d, non-matched candidates 30d, matched conversations+messages 90d (deleted via FK cascade by removing the owning matched candidate). Gated by RETENTION_ENABLED; all durations are RETENTION_* env values so counsel's numbers need no code change. Durations are placeholders pending counsel (C4). Authoritative table: ../registers/retention-schedule.md.
• Account erasure is implemented for the backend account graph (infra PR-14, PRIV-04): authenticated DELETE /account soft-deletes the current user with users.deleted_at, revokes all sessions immediately, rejects future auth for non-active users, prevents LinkedIn no-revival during the grace window, and worker-purges soft-deleted users after RETENTION_ACCOUNT_ERASURE_DAYS (default 7). The hard delete relies on existing ON DELETE CASCADE child relationships. Edge cases (tombstoning a deleted user's sent messages C6; retaining reports filed against a deleted user C7) are counsel calls; current code uses the schema's existing cascade behavior.
• Blocks and LinkedIn-derived PII persist for the life of the account (safety
• identity); reports archive after resolution (180d target, counsel C4).

Event / ML Data Boundary (upstream events implemented, downstream ML future)

The event/ML plane is specified in readiness/ml-event-taxonomy.md and governed by ../policies/ai-ml-data-governance-policy.md. Infra PR-17 implements the backend foundation; infra PR-18 emits the first product hookpoints (auth/presence/match/safety), infra PR-19 emits worker match lifecycle hookpoints through the same consent gate, infra PR-20 implements the k-anonymous T2 snapshot boundary, infra PR-22 emits metadata-only message events, and infra PR-24 emits connection request lifecycle events. Key designed and implemented properties:

• Consent tiers gate flow: T0 operational (never ML without re-consent), T1 analytics (needs analytics consent), T2 ML-training (needs explicit, revocable ml_training consent). PR-17 enforces this in code via the tiered emit_event helper, PR-18 product hookpoints and PR-19 worker hookpoints use try_emit_event so missing consent skips capture without failing the user or worker action. PR-22 verifies message.sent is T1 and analytics-gated; PR-24 verifies connection.requested is T1 while connection.accepted and connection.declined are T2. Mobile PR-13 gives users in-app controls for the three grants (ML-05, PRIV-10).
• No content/secrets/identity in events: the helper rejects banned keys and obvious email/JWT-like string values before insert; message.sent records length, never body; connection events omit request notes and note is a banned event field; internal UUIDs, never sub/email (ML-04).
• Safety events are never a training signal (T0-only) (ML-03).
• Erasure propagates to the pseudonymized event store, not to models. Models train only on anonymized snapshots. Infra PR-20 implements the first technical boundary: T2-only, whitelisted aggregate rows, k-anonymity suppression, non-whitelisted field stripping, no actor/object ids, and rows that survive source-user erasure. A model trained on a ratified anonymized snapshot is treated as out of erasure scope - not deleted, not retrained (ML-07; operator decision 2026-06-07). Counsel C8/C9 still ratify the final standard before real model training.

Links

• Record of Processing Activities: ../registers/ropa.md
• Retention schedule (mechanics + evidence): ../registers/retention-schedule.md
• Control register (PRIV-, ML-, SEC-12): ../control-register.md
• Privacy/retention + classification policies: ../policies/privacy-and-retention-policy.md, ../policies/data-classification-and-handling-policy.md, ../policies/ai-ml-data-governance-policy.md
• Root data inventory (field-level): readiness/data-inventory-and-retention.md
• Event taxonomy: readiness/ml-event-taxonomy.md

Known Gaps / Next Work

• DSAR/rights process not built (PRIV-05); registers are pointer-only.
• Report/message tombstoning policy not decided (C6/C7); current account erasure uses FK cascade.
• Ranking/AI product endpoints are built, but ranking/AI event hookpoints are not. Anthropic/Claude processing is integrated but key-gated; no real provider call occurs unless ANTHROPIC_API_KEY is set (GOV-06, C27).
• Counsel anonymization ratification remains open (ML-07, C8/C9), even though the technical boundary is implemented in infra PR-20.
• Retention durations remain counsel placeholders (C4); backups must not silently defeat erasure once production backups exist (see backup policy).
• No data residency / transfer mechanism decided (PRIV-11, C15).