The Sixth Sense at 6th Street

Live Data Schema Design Preview

Protected, read-only schema planning preview for possible future pull-up and push-up storage.

This is a protected, read-only schema planning preview. It does not create database tables, write data, collect submissions, store identity, or enable live workflows.

Purpose

This page describes a possible future D1 data model before any live storage exists. It is meant to make privacy, retention, audit, and access decisions visible before implementation work begins.

This page is not an admin tool, migration runner, account system, or storage surface.

Why Schema Design Comes Before Live Storage

Pull-up and push-up workflows could contain sensitive community content. Schema planning has to define what is allowed, what is excluded, who can see it, how long it can remain, and what audit trail exists before any table, endpoint, or live workflow is created.

Planned Data Domains

account rolesresident display aliasessubmissionspolicy evaluationsstaff review eventsmorning sheet placementsaudit eventsretention/deletion records

Planned Table Map

Planned table Status Purpose Classification Retention Launch blocker
future_accounts planned-only Planning placeholder for a future minimal account record if app-level accounts are ever approved. restricted identity-adjacent planning Do not retain until privacy approval defines whether app accounts may exist. Requires account model, identity storage decision, privacy approval, audit logging, and rollback plan.
future_role_assignments planned-only Planning placeholder for assigning resident, peer-leader, staff-reviewer, or program-admin roles after approval. restricted role metadata planning Retain only while role is active plus an approved audit window. Requires role mapping approval, staff permission policy, audit events, and privacy approval.
future_submission_drafts planned-only Planning placeholder for future pull-up and push-up drafts after account, privacy, and retention approval. sensitive community content planning Short retention window; unsubmitted drafts should expire quickly. Requires submission schema review, staff review policy, retention policy, deletion policy, and privacy approval.
future_policy_evaluations planned-only Planning placeholder for deterministic policy preview results tied to future drafts. sensitive review metadata planning Retain only as long as the associated draft is retained unless audit policy requires less detail. Requires policy versioning, retention limits, and audit model.
future_staff_reviews planned-only Planning placeholder for future staff review events and final public-display decisions. restricted staff decision planning Retain only within the approved staff-review audit window. Requires staff review workflow, staff permissions, audit logging, rejected-item handling, and privacy approval.
future_morning_sheet_items planned-only Planning placeholder for reviewed display text eligible for a future Morning Sheet. reviewed public-display planning Retain only display-safe text for the approved Morning Sheet window. Requires public-display policy, staff approval workflow, retention policy, and rollback plan.
future_audit_events planned-only Planning placeholder for future audit events around draft, review, placement, retention, and export actions. restricted operational audit planning Retain according to approved audit window and data-minimization rules. Requires audit event schema, export policy, retention policy, and privacy approval.
future_retention_events planned-only Planning placeholder for future expiry, archive, and deletion records. restricted retention metadata planning Retain only enough metadata to prove policy compliance without preserving sensitive draft content. Requires retention policy, deletion policy, archive rules, and audit logging.

Planned Relationships

  1. Alias and role boundary

    planned-only

    `future_accounts` would connect to `future_role_assignments` only after account and role policy approval.

  2. Draft review path

    planned-only

    `future_submission_drafts` would connect to `future_policy_evaluations` and `future_staff_reviews` after review rules are approved.

  3. Display boundary

    planned-only

    `future_morning_sheet_items` would contain reviewed display text only, never unreviewed sensitive draft content.

  4. Audit and retention boundary

    planned-only

    `future_audit_events` and `future_retention_events` would record minimal operational summaries after policy approval.

Planned Retention Rules

Category Retained? Window Deletion / expiry rule Staff visibility Resident visibility Export policy
Draft not submitted no expire quickly Remove automatically after the approved draft window. none unless submitted author only if future accounts exist not exportable
Submitted pending review planned if approved short review window Expire if not reviewed within approved window. approved reviewers only author status only if approved not exportable except privacy-approved audit summary
Needs revision planned if approved short revision window Expire if not revised within approved window. approved reviewers only author guidance only if approved not exportable
Blocked/not appropriate planned if approved minimal audit window Delete draft content and retain only minimal audit summary if policy requires it. staff reviewers and program admin only status only if approved by policy audit summary only if approved
Approved for Morning Sheet planned if approved meeting packet window Retain reviewed display text only for approved packet/archive policy. approved reviewers and operators public display text only display-safe text only if approved
Archived planned if approved approved archive window Purge according to retention policy. program admin and privacy-approved staff none unless policy allows restricted
Audit event planned if approved approved audit window Purge or summarize after audit window. program admin and privacy-approved staff none restricted audit export only if approved

Planned Audit Events

These are future-only event names. No audit logging code exists in this phase.

submission createdpolicy evaluation generatedstaff review viewedstaff review decision recordedrevision requestedapproved for morning sheetblockedarchiveddeleted/expiredexport generated

Privacy Boundaries

The planned table metadata excludes legal names, medical or diagnostic details, legal history, trauma details, unreviewed sensitive community content, attendance, participation tracking, discipline scores, ranking/compliance metrics, raw access identity headers, session credentials, and unapproved contact addresses.

What Must Never Be Stored

  • real resident legal names
  • medical details
  • diagnoses
  • legal history
  • trauma details
  • unreviewed sensitive community content
  • attendance data
  • participation tracking
  • discipline scores
  • ranking or compliance metrics
  • raw access identity headers
  • session credentials
  • contact addresses unless approved by future policy

Identity Strategy Options

Option A: Cloudflare Access only, no app account table

Use Cloudflare Access as the outer gate and keep the app from storing account records.

Risk: Role-specific app behavior would remain limited without a separate approved mapping.

planning-only

Option B: Staff-maintained role mapping with minimal pseudonymous display labels

Maintain minimal aliases and role mapping outside resident legal identity where approved.

Risk: Requires careful staff procedure, audit logging, and deletion rules.

planning-only

Option C: App-level accounts with approved retention/audit policy

Introduce app account records only after privacy, retention, audit, and role policies are approved.

Risk: Highest implementation and privacy risk; not recommended for first pilot.

planning-only

Recommended Safest First Pilot

Safest first live pilot strategy

  • staff-maintained role mapping
  • minimal aliases
  • no resident history view
  • staff review required
  • short retention window
  • audit logging
planning-only

What Remains Blocked Before Live Launch

  • no active migrations
  • no database writes
  • no real accounts
  • no identity storage
  • no resident profiles
  • no live submissions
  • no staff decisions
  • no attendance or participation tracking
  • no scoring/ranking/compliance metrics

Recommended Next Phase

Phase 5.7 — Staff Privacy Review Checklist, Planning Only

Review the planned schema with staff and privacy owners before any migration, write endpoint, account table, resident identity storage, or live workflow is implemented.

planning-only