Coaching silver source schema reply — table scope, row grain, person_id derivation, and test data posture confirmed

Replying to Platform's 2026-05-17 ask on the silver conform face for Coaching. All four questions answered below. Coaching's source is the coaching schema in the sguild-domains Supabase project per the two-project consolidation (2026-05-02-platform-supabase-consolidation).

Source tables in scope

Coaching proposes two grains for the silver face, which Platform may model as one wide face or two views — Coaching defers to Platform on the dbt structure:

Coach grain (coaching.coach). The primary table. One row per Coach role record, keyed by coa_-prefixed ID. Carries coaching-owned facts: status, skill levels, bio, rate references, assignment locations, home service area, service area coverage, travel constraint fields, and identity link columns (person_id, organization_id). This is the stable analytical anchor; every other coaching table hangs off it.

Booking projection grain (coaching.coach_lesson_booking). One row per (coach, lesson) pair, driven by Revenue's credit event stream. Columns: organization_id, coach_id, lesson_id, lesson_start, lesson_end, lesson_service_area_id, lesson_address, lesson_zip, state (one of: reserved, locked, released, consumed), reservation_id, lock_id. This table is the lock-aware supply projection and is the analytically interesting availability record. State values track the credit lifecycle per ADR-0008 and the coach-availability contract.

Supporting tables worth including in silver (or as platform-internal join sources):

• coaching.availability_template: recurring weekly supply pattern, one row per (coach, day-of-week, time window). Useful for a supply-hours analytic. Fields: organization_id, coach_id, day_of_week, local_start_time, local_end_time, timezone, effective_start_date, effective_end_date, status.
• coaching.availability_exception: one-off supply overrides. type is either additional or unavailable. Fields: organization_id, coach_id, window_start, window_end, type.
• coaching.coach_certification: coach certification membership, one row per (coach, certification type). Fields: organization_id, coach_id, certification_type_id, status, issued_at, expires_at.

The dispatcher plumbing tables (coaching.dispatcher_event, coaching.dispatcher_cursor, coaching.dispatcher_dedup, coaching.dispatcher_dead_letter, coaching.dispatcher_inbox_dedup) are operational infrastructure and have no analytical value. Leave them out of the silver face. The caches (coaching.zip_service_area_cache, coaching.zip_distance_cache) are operational lookup tables, not facts; leave them out as well. coaching.eligibility_consistency_sample is observability-only and has no business-analytics value; leave it out. coaching.coaching_audit_log is an append-only ops audit log; Coaching does not request it in the face today but Platform may include it if the Finance/Portfolio sections want an ops audit grain.

Row grain

Coach face: one row per coaching.coach.id. One Coach per Person per Organization; the (person_id, organization_id) pair is unique. This is the clean grain: one Coach role record.

Booking projection face: one row per (coach_id, lesson_id) pair. The compound primary key is (coach_id, lesson_id) on the table. A given lesson_id may appear on multiple coaches if eligibility produced multiple candidates, but in practice one coach is assigned per lesson; the state column distinguishes active from released projections.

Person_id derivation

coaching.coach.person_id is a direct column on the Coach row, already populated as a Platform per_-prefixed Person ID per ADR-0003. The join is straightforward: coaching.coach.person_id → platform.person.id.

Important caveat: person_id is nullable on coaching.coach. Legacy Airtable-migrated rows may have a null person_id until the rekey script (scripts/rekey-legacy-coach-ids.ts) has run against them and the result confirmed clean by scripts/check-legacy-scope-ids.ts. Platform should treat person_id IS NULL rows as valid coach records with an unresolved identity link rather than as junk. Coaching will clear this null population as the migration completes; the silver face may carry a person_id_resolved boolean or leave null as a passthrough — Platform's call.

For the booking projection grain, there is no person_id column on coaching.coach_lesson_booking directly. The derivation is: coach_lesson_booking.coach_id → coaching.coach.id → coaching.coach.person_id. Platform will need to join through coaching.coach to resolve person identity on that face.

Test data posture

There is no is_test_data column in the Coaching schema. Coaching scopes all rows by organization_id; the production tenant is a single organization under org_sguild. Test or seed data is distinguishable only through the organization_id dimension — test-harness orgs are separate organizations, not flagged rows within the production org.

Coaching is comfortable with the same passthrough pattern used for the Growth and Sales faces: include organization_id as a passthrough column, leave filtering to the gold section or the geography spine's org dimension. No hard filter at the silver layer is needed from Coaching's seat.

References

• Parent ask: 2026-05-17-platform-data-access-silver-source-schema-asks
• Initiative scope: 2026-05-14-platform-data-access-model-initiative-scope
• Silver contract: contracts/warehouse-silver/README.md
• ADR-0016 (medallion model), ADR-0003 (person ID shape), ADR-0008 (Coaching domain scope), ADR-0018 (continuous availability model)
• Coaching Prisma schema: prisma/schema.prisma in the coaching repo