Renata protected schema planning

Role Set Template Schema Planning

Protected, read-only schema planning for possible future config-only role-set template storage.

This is a protected, read-only schema planning preview. It does not create tables, run migrations, write role sets, assign users, or store configuration.

Purpose

This page defines the planned D1 schema shape, field rules, relationships, sensitivity classifications, audit requirements, rollback model, and migration-readiness gates for future role-set template storage.

It is schema planning only. No active SQL migration, table, endpoint, write code, CRUD action, or storage exists.

Why Schema Planning Comes Before Migrations

Role-set templates may become the first config-only CRUD candidate, but schema work needs privacy boundaries before any SQL file exists. Planning keeps fields, relationships, audit events, rollback, and exclusions visible before implementation pressure appears.

Planned Role-Set Template Tables

Table Purpose Status Sensitivity
future_role_set_templates Stores config-only role-set template metadata. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring
future_role_definitions Stores role definitions inside a role-set template. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring
future_role_permission_mappings Stores permissions assigned to roles inside a template. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring
future_role_set_template_versions Stores version snapshots for rollback and audit. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring
future_role_set_template_audit_events Stores future config-only audit events for template changes. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring
future_role_set_template_status_history Stores planned lifecycle transitions such as draft, archived, pilot-ready, or rollback-requested. planned-only non-sensitive configuration metadata only; excludes identity, health, submissions, tracking, analytics, and scoring

Planned Field Map

future_role_set_templates

id: configuration identifiercommunity_id: configuration relationshiptemplate_slug: configuration metadatatemplate_name: configuration metadatatemplate_description: configuration metadatatemplate_status: lifecycle metadatatemplate_version: version metadatacreated_at: timestamp metadataupdated_at: timestamp metadataarchived_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain only as future configuration metadata after policy approval; no resident or submission retention applies.

future_role_definitions

id: configuration identifiertemplate_id: configuration relationshiprole_id: configuration identifierrole_key: configuration metadatarole_label: configuration metadatarole_description: configuration metadatacreated_at: timestamp metadataupdated_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain only as future configuration metadata after policy approval; no resident or submission retention applies.

future_role_permission_mappings

id: configuration identifiertemplate_id: configuration relationshiprole_id: configuration identifierpermission_key: configuration metadatapermission_status: configuration metadatapermission_group: configuration metadatablocked_permission_reason: configuration metadatacreated_at: timestamp metadataupdated_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain only as future configuration metadata after policy approval; no resident or submission retention applies.

future_role_set_template_versions

id: configuration identifiertemplate_id: configuration relationshipversion_number: version metadatatemplate_version: version metadatachange_reason: audit metadatarollback_source_version: version metadatacreated_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain future version snapshots only under an approved config rollback policy.

future_role_set_template_audit_events

id: configuration identifiertemplate_id: configuration relationshiprole_id: configuration identifierpermission_key: configuration metadatachange_reason: audit metadatacreated_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain future audit metadata only after audit policy approval; actor_ref remains conceptual until identity policy exists.

future_role_set_template_status_history

id: configuration identifiertemplate_id: configuration relationshiptemplate_status: lifecycle metadataversion_number: version metadatachange_reason: audit metadatarollback_source_version: version metadatacreated_at: timestamp metadataactor_ref: conceptual actor reference

Retention boundary: Retain only as future configuration metadata after policy approval; no resident or submission retention applies.

The `actor_ref` field is conceptual only. It is not real identity storage, email storage, Cloudflare header storage, or credential storage.

Relationships And Versioning

From To Status Rule
future_role_definitions.template_id future_role_set_templates.id planned-only Role definitions belong to a future template; no user assignment relationship exists.
future_role_permission_mappings.role_id future_role_definitions.role_id planned-only Permission mappings refer to role definitions only; they do not grant live permissions.
future_role_set_template_versions.template_id future_role_set_templates.id planned-only Versions snapshot configuration metadata for rollback planning.
future_role_set_template_audit_events.template_id future_role_set_templates.id planned-only Audit events are future-only and require audit policy before activation.
future_role_set_template_status_history.template_id future_role_set_templates.id planned-only Status history is lifecycle metadata only and triggers no behavior.
  • Version snapshots must exist before pilot publication.
  • Published pilot templates cannot be edited in place; a new version is required.
  • Archive and restore states are metadata only until a future approval process exists.
  • Rollback source version must reference a planned template version, not resident or submission data.
  • Versioning must not create user assignment, identity, submission, or staff-decision records.

Sensitivity Classification

Every planned table is classified as non-sensitive configuration metadata only. The schema plan does not include resident identity, health information, submission content, staff decisions, tracking, analytics, scoring, or compliance metrics.

Excluded Fields

real namesemailsphone numbersaddressesmedical detailsdiagnosis detailsmedication detailslegal detailstrauma detailsinsurance detailssubstance-use disclosuressubmission contentpull-up contentpush-up contentstaff review notesattendance dataparticipation dataparticipation analyticsscoring dataranking datadiscipline pointscompliance metricsresident performance metrics

Audit Event Planning

Future audit event Status Implementation Activation requirement
role_set_template_created future-only not implemented requires audit policy before activation
role_definition_added future-only not implemented requires audit policy before activation
role_definition_updated future-only not implemented requires audit policy before activation
role_definition_removed future-only not implemented requires audit policy before activation
role_permission_mapping_updated future-only not implemented requires audit policy before activation
role_set_template_archived future-only not implemented requires audit policy before activation
role_set_template_restored future-only not implemented requires audit policy before activation
role_set_template_pilot_ready future-only not implemented requires audit policy before activation
role_set_template_rollback_requested future-only not implemented requires audit policy before activation

Rollback And Archival Model

  • Previous version snapshots must be available before pilot publication.
  • Rollback must reference a template version, not a person, submission, or staff decision.
  • Archived templates cannot be used for new pilots without restore metadata.
  • Rollback request events must be future audit events only until audit policy is approved.
  • No resident/submission data may be tied to template version rollback in the first config-only phase.

Migration-Readiness Gates

D1 schema reviewednaming convention approvedmigration rollback plan writtenaudit model approvedseed strategy approvedno sensitive fields confirmedno identity linkage confirmedno submission linkage confirmedCloudflare Access policy checkedstaff/privacy approval recordedtest coverage plan written

What Remains Blocked

no active SQL migrationno D1 tableno write endpointno role-set storageno user assignmentno account creationno identity storageno live submissionsno staff decisionsno attendance trackingno participation trackingno participation analyticsno scoring/ranking/compliance metrics

Recommended Next Phase

Phase 7.12 - Role Set Template Migration Activation Review, Planning Only

Phase 7.11 adds a manual disposable local dry-run scaffold only. Next is a planning-only activation review with no production activation, preview D1 targeting, active migrations folder change unless explicitly approved, write endpoints, CRUD UI, user assignment, or submissions.

future-only

Schema plan summary: 6 planned tables, 23 planned fields, and no active storage.