Delivery silver source schema response; five core fact tables in scope, one row per lesson as primary grain, person_id via participant join, test data is org-scoped not row-flagged

Date: 2026-05-17 From: Delivery To: Platform Status: Responded. Answers Platform's four questions for the silver conform face build.

Source tables in scope

Five tables are the right scope for Delivery's silver face:

• delivery.lesson -- the central delivery fact; every scheduled, completed, or cancelled lesson event lives here
• delivery.participant -- Delivery-owned role state per person per organization; the bridge between a canonical person_id and Delivery's lesson-taking operational context
• delivery.attendance -- one row per lesson attendance event; the outcome record Platform will need for completed-lesson analytics
• delivery.reservation_lock -- Delivery's operational mirror of Revenue's credit reservation lock state; one row per lock, linked to lesson and credit reservation
• delivery.customer -- post-handoff CRM record; one row per person per organization, created when the customer.handoff event arrives from Sales or Revenue

Several other tables exist in the delivery schema (customer_stage_history, customer_handoff, customer_touch, customer_task, customer_note, eligibility_decision_audit, and a family of UI-config and dispatcher infrastructure tables). Those are not proposed for the initial silver face scope. If Platform's data access model has a use for the CRM history tables (stage transitions, touch log) in a later iteration, Delivery can expand the declaration then.

Row grain

The primary grain for the Delivery silver face is one row per lesson. The conform view joins lesson to participant to resolve person_id, and joins to lesson_site_config and service_area for the geography spine. That single-grain view covers the core delivery-operations question (what lessons happened, for whom, where, and in what state).

Attendance is a separate grain (one row per lesson attendance event, with a lesson_id back-pointer). If Platform wants a flat lesson-plus-attendance face it can be built as a second conform view rather than widening the per-lesson grain. Delivery's preference is to keep them separate, but Platform decides the silver layer shape; Delivery's job is to confirm what the source data supports.

Person_id derivation

Join path per table:

lesson: Two paths exist and Platform should handle both. Primary path: lesson.participant_id joins to participant.person_id. One join. Populated for all lessons minted through the sales-scheduling-surface contract. Legacy path: for Airtable-origin rows with null participant_id, lesson.source_client_profile_id (a legacy Airtable client profile ID) maps through Platform's CL-PLT-0002 legacy-id bridge to person_id. Platform should treat rows where both are null as unresolvable rather than dropping them. Delivery will drive the null participant_id fraction to zero as Phase 3 migration cohorts cut over.

participant: participant.person_id is a direct column. No join needed. This is the cleanest resolution path across the schema.

attendance: attendance.participant_id joins to participant.person_id. One join. The column is nullable on legacy attendance rows imported from Airtable where the student-profile link was missing or mismatched; the missing_required_links and org_mismatch_student_profile flags on the attendance row identify those cases.

customer: customer.person_id is a direct column. No join needed.

reservation_lock: No person_id or participant_id on the lock row itself. The lock references credit_reservation_id (Revenue's crr_ identity) and optionally lesson_id, but neither resolves to person_id within Delivery's schema. Resolution would require a join through Revenue's credit-account-to-person bridge, which is cross-domain. Delivery recommends Platform either omit person_id from the lock face (and let gold consumers join through the lesson grain) or derive it via the lesson_id to participant to person_id path, noting that the join is nullable.

Test data posture

Delivery does not have a row-level is_test_data flag on any of the five tables. Test data isolation is org-scoped: test organizations (identifiable by organization_id) are distinct from production organizations. There is no per-row test flag to pass through.

The passthrough is_test_data column pattern from the growth and sales silver faces does not directly apply. Platform's options:

1. Org-level filter. Platform maintains a list of test organization IDs (derivable from Platform's own org/tenancy identity data) and filters them at the silver view layer. This is the cleanest approach and gives consumers a uniform "no test data" guarantee without row-level flags.

2. Org-key passthrough. Include organization_id as a passthrough column on every Delivery silver row so downstream gold consumers can apply their own org-level filter. This is the minimum-change shape if Platform prefers to defer the filter decision to gold.

Delivery has no strong preference between these two; the decision belongs to Platform's silver-layer design. What Delivery can confirm is that there are no row-level test flags to map, and that test/prod separation is fully expressible via organization_id.

References

• Platform's ask: 2026-05-17-platform-data-access-silver-source-schema-asks
• Data access model initiative scope: 2026-05-14-platform-data-access-model-initiative-scope
• Silver contract: contracts/warehouse-silver/README.md
• ADR-0016: adrs/ADR-0016-medallion-warehouse-data-access-model.md