4. Data model

4.1 Spine

Firm ─┬─ Practice ─── MethodologyPack ─── PackVersion ─┬─ Dimension ─── Practice(mgmt) ─── Item
      │                                                ├─ BandScheme ─── Interpretation
      │                                                ├─ Recommendation ─── ServiceOffering
      │                                                └─ Form (census | short | frontline | 360 | interview guide)
      ├─ Client ─── Engagement ─── ClientWorkspace ─┬─ Population ─── Person ─── HierarchyNode
      │                                             ├─ Wave ─── Instrument ─── Invitation ─── Response
      │                                             │                                     └── ResponseItem | TextAnswer | VideoAnswer
      │                                             ├─ Interview · Document · StructureSnapshot
      │                                             ├─ Scorecard ─── ScoreCell
      │                                             ├─ Theme · Quote · Clip
      │                                             ├─ InsightObject ─── EvidenceRef
      │                                             ├─ Deliverable
      │                                             └─ ActionPortfolio ─── Initiative ─── ItemLink
      ├─ NormSet ─── NormCell          (firm book; external references)
      └─ User ─── RoleAssignment · AuditEvent

Note on naming: Practice is overloaded. In code, the firm's business unit is FirmPractice; the management practice being scored is PracticeConstruct. This document uses "Practice area" and "Practice" respectively.

4.2 Tenancy and encryption boundaries

Rules:

• No query path joins across client workspaces. Cross-client reads happen only through the Book pool.
• Every row in a workspace schema carries workspace_id; row-level security enforces it even inside the schema (defence in depth).
• Contractors hold RoleAssignments scoped to named engagement_ids with a mandatory expires_at.
• Workspace purge = destroy DEK + delete objects + write tombstone AuditEvent. Crypto-shredding makes backups unreadable.

4.3 Entities and critical fields

Types: id = UUIDv7. ts = UTC timestamp. enum(...) = closed set. → = foreign key.

Firm layer

Firm

FirmPractice — id, firm_id, name (e.g., "Org & Transformation"), lead_user_id.

MethodologyPack — id, firm_id, firm_practice_id, name, pack_type enum(health, change_readiness, operating_model, strategy_cascade, psych_safety, manager_360, ways_of_working, capability, customer_centricity, risk_conduct, ai_readiness, pmi, frontline_pulse, custom), forked_from_pack_version_id, owner_user_id.

PackVersion

Dimension — id, pack_version_id, code (e.g., DIR), name, definition, weight, required_for_index (bool), order.

PracticeConstruct — id, dimension_id, code (DIR-P2), name, definition, weight, min_items_answered (int, default 2 or 60%), order.

Item

BandScheme — id, pack_version_id, kind enum(absolute, norm_relative), bands[] {code, label, lower, upper}, norm_set_version_id (required when norm_relative).

Interpretation — id, band_scheme_id, target (dimension_id | practice_id | index), band_code, text, author_type enum(human, ai_draft), author_id, approved_by, approved_at. Unapproved interpretations cannot ship in a locked version.

Recommendation — id, pack_version_id, code (MGR-B2), title, move (short text), rationale, triggers[] {practice_id, band_codes[]}, service_offering_ids[], typical_duration, evidence_base (text).

ServiceOffering — id, firm_id, name, description, price_band, owner_practice_id.

Form — id, pack_version_id, kind enum(census, exec_short, frontline, pulse, rater_360, interview_exec, interview_middle, interview_frontline), item_ids[], block_layout, est_minutes_desktop, est_minutes_mobile, reportable_practices[] (computed).

NormSet — id, firm_id, layer enum(book, external), name, version, source (for external: publisher, licence ref, expiry), filters {industry, size_band, region, growth_stage, unionized, deskless_share_band}, eligibility {min_clients, min_respondents}, built_at.

NormCell — norm_set_id, target (item lineage | practice code | dimension code | index), stats {n_clients, n_resp, mean, sd, p25, p50, p75, p90}.

Engagement layer

Client — id, firm_id, name, industry (NAICS), size_band, region, contract_flags {book_opt_in, ai_improvement_opt_in, residency, retention_overrides, works_council}.

Engagement — id, client_id, firm_practice_id, package enum(rapid, full_census, transformation_pulse, portfolio, custom), stage enum(proposed, contracted, setup, fielding, analysis, readout, retainer, closed), partner_id, em_id, team[], fee_band, steering_dates[], crm_ref.

ClientWorkspace — id, engagement_id (1:1; a retainer re-uses the workspace across waves), residency, dek_ref, brand_mode enum(firm, co_brand), subdomain, anonymity_policy_id.

AnonymityPolicy

Population — id, workspace_id, snapshot_at, source enum(csv, workday, successfactors, bamboohr, rippling, personio, adp), hygiene_report {duplicates, leavers, missing_manager, cycles}.

Person — id, population_id, external_ref_hash (salted), contact (encrypted, purged at wave close + N days), hierarchy_node_id, attributes {level, function, location, tenure_band, union_flag, deskless_flag, language, employment_type, custom{}}. No name stored unless 360 or interviews require it.

HierarchyNode — id, population_id, parent_id, name, node_type, headcount, span_of_control, layer, cost_band (optional), maps_to_prior[] {prior_node_id, relation enum(same, merged_into, split_from, renamed), weight}.

Wave — id, workspace_id, seq, kind enum(baseline, pulse, post_intervention, annual, legacy_import), pack_version_id, open_at, close_at, status, comparable_to_wave_id, language_set[], target_response_rate.

Instrument — id, wave_id, form_id, audience_filter, channels[] enum(email, sms, slack, teams, whatsapp, qr, kiosk, sso, magic_link), additive_modules[].

Invitation — id, instrument_id, person_id, token_hash, channel, sent_at, opened_at, completed_at. Stored separately from responses; the link table is dropped at close unless the engagement is longitudinal at person level (off by default).

Response — id, instrument_id, respondent_key (opaque; not person_id), segment_snapshot {hierarchy_node_id, attributes at time of response}, started_at, submitted_at, duration_s, device, language, completeness.

ResponseItem — response_id, item_id, item_version, value_raw, value_scored (0–100 after reverse/normalize), is_na.

TextAnswer — id, response_id, item_id, text_original, text_redacted, language, translation, pii_flags[], construct_codes[], emergent_theme_ids[], quote_permission enum(internal, client_aggregate, client_shareable).

VideoAnswer — id, response_id, item_id, storage_ref, duration_s, transcript_redacted, speaker_segments, consent_tier enum(transcript_only, internal_video, client_shareable_video), retention_until, purged_at.

Interview — id, workspace_id, guide_form_id, interviewee_role_band, layer, date, notes, transcript_ref, consent_tier, coded_segments[].

Document — id, workspace_id, kind enum(strategy_deck, org_chart, prior_survey, policy, other), storage_ref, extracted_text_ref, coded_segments[].

StructureSnapshot — id, workspace_id, as_of, spans/layers stats per node, cost bands (optional), source.

Analysis layer

Scorecard

ScoreCell — scorecard_id, target (index | dimension | practice | item), value, pct_favorable, sd, ci_low, ci_high, n, band_code, band_borderline (bool), delta_prev, delta_sig, effect_size, norm_percentile_book, norm_percentile_external.

Theme — id, wave_id, kind enum(construct, emergent), construct_code, label, definition, prevalence, intensity, segment_skew[], confidence enum(high, medium, low), model_provenance.

Quote — id, source_type enum(text, video, interview, document), source_id, span, construct_codes[], theme_ids[], segment_label (k-safe), permission_tier.

Clip — id, video_answer_id | interview_id, t_start, t_end, transcript_span, construct_codes[], consent_tier.

InsightObject — see §6.

Deliverable — id, engagement_id, kind enum(deck, pdf, excel_appendix, clip_reel, manager_pack, portal_publish), template_id, insight_ids[], generated_at, generated_by, k_check_passed, file_ref.

ActionPortfolio / Initiative — id, workspace_id, title, owner (client user), due_date, status, leading_indicator {item_lineage_id | practice_code, target_delta}, linked_insight_ids[], service_offering_id (optional).

Governance layer

User — id, firm_id | client_id, email, sso_subject, status.

RoleAssignment — user_id, role enum(firm_admin, partner, practice_lead, em, analyst, contractor, client_sponsor, client_admin, client_viewer, client_manager), scope {firm | engagement_id | workspace_id | hierarchy_node_id}, expires_at.

AuditEvent — id, ts, actor_id, action enum(view_aggregate, view_identifiable, export, publish, approve, permission_change, purge, reopen_wave, ai_run), object_ref, purpose_text (required for view_identifiable and export), ip, result. Append-only; hash-chained per workspace.

4.4 Invariants the engine enforces

1. A Wave's pack_version_id must be locked.
2. ResponseItems store item_version; scores are never recomputed against a newer item version.
3. Any ScoreCell with n < k_scores is persisted as suppressed and never serialized to a client-facing surface.
4. An InsightObject cannot be published unless every EvidenceRef resolves and passes k at publish time and render time.
5. Book NormCells are built only from workspaces whose client has book_opt_in = true and only once min_clients and min_respondents are met.
6. Video past retention_until is purged by job; purged_at is set and clips referencing it degrade to transcript-only.