08 Data Structure

Kingdom Wife Blueprint — Data Structure

The MySQL schema · entity relationships · index strategy. Source of truth for the platform’s data model.

Entity relationship diagram

erDiagram
    KWB_USERS ||--o{ KWB_PURCHASES : "makes"
    KWB_USERS ||--o{ KWB_COHORT_MEMBERSHIP : "joins"
    KWB_USERS ||--o{ KWB_JOURNAL_ENTRIES : "writes"
    KWB_USERS ||--o{ KWB_EMAIL_QUEUE : "receives"
    KWB_USERS ||--o{ KWB_EMAIL_LOG : "logged-for"
    KWB_USERS ||--o{ KWB_CONTENT_PROGRESS : "completes"
    KWB_USERS ||--o| KWB_USERS : "accountability-partner"

    KWB_USERS {
        int id PK
        varchar email UK
        varchar password_hash
        varchar first_name
        timestamp created_at
        timestamp last_login
        tinyint current_tier
        timestamp tier_expires_at
        bool is_admin
        bool email_verified
        bool newsletter_opt_in
    }

    KWB_PURCHASES {
        int id PK
        int user_id FK
        varchar stripe_payment_intent UK
        tinyint tier_purchased
        int amount_cents
        char currency
        enum status
        timestamp created_at
    }

    KWB_COHORT_MEMBERSHIP {
        int id PK
        int user_id FK
        int cohort_number
        timestamp joined_at
        timestamp left_at
        int accountability_partner_id FK
        int attended_calls
        int total_calls_during_membership
    }

    KWB_JOURNAL_ENTRIES {
        int id PK
        int user_id FK
        varchar prompt_id
        mediumtext entry_text
        bool is_shared_with_partner
        timestamp created_at
    }

    KWB_EMAIL_QUEUE {
        int id PK
        int user_id FK
        varchar email_template
        timestamp scheduled_for
        timestamp sent_at
        enum status
    }

    KWB_EMAIL_LOG {
        int id PK
        int user_id FK
        varchar email_template
        timestamp sent_at
        timestamp opened_at
        varchar clicked_link
    }

    KWB_CONTENT_PROGRESS {
        int user_id PK
        varchar content_id PK
        timestamp completed_at
    }

Tier semantics (the central data concept)

The kwb_users.current_tier column drives nearly every gating decision. The values:

Value	Tier	What unlocks
0	None (signed up but not paid)	Public landing pages only · email queue starts at Tier 1 only after first purchase
1	Book Reader	Day 0/3/7/14/30 email sequence · sample chapter · intro module
2	Course Member	Tier 1 + full Four Realms course content · journal prompts · scripture library
3	Cohort Member	Tier 2 + monthly cohort call · accountability partner · cohort-only resources

Upgrade path: 0 → 1 → 2 → 3 (always forward; downgrades expire by setting tier_expires_at).

Data dictionaries

`kwb_users.current_tier`

0 = None
1 = Book Reader
2 = Course Member
3 = Cohort Member

`kwb_purchases.status`

pending — Stripe PaymentIntent created, not yet confirmed
succeeded — payment cleared, user upgraded
failed — payment failed, user not upgraded
refunded — payment refunded post-fact, may trigger downgrade

`kwb_email_queue.status`

pending — scheduled, not yet attempted
sent — sent successfully
failed — send failed (will retry up to 3x then mark cancelled)
cancelled — manually cancelled by Sandi via /admin

`kwb_email_template` (controlled vocabulary)

day-0-welcome
day-3-introduction-tier1, day-3-introduction-tier2, day-3-introduction-tier3
day-7-reflection
day-14-deeper-realm
day-30-celebrate-tier1, day-30-upgrade-tier2, day-30-cohort-confirm-tier3
quarterly-newsletter
cohort-call-reminder
cohort-call-followup

Index strategy

Table	Index	Purpose
`kwb_users`	`email` (unique)	Login lookup
`kwb_users`	`current_tier`	Tier-segmented queries
`kwb_purchases`	`stripe_payment_intent` (unique)	Webhook idempotency
`kwb_purchases`	`status, created_at`	Recent purchase reports
`kwb_cohort_membership`	`cohort_number, left_at`	Active members per cohort
`kwb_journal_entries`	`user_id, created_at`	User’s journal history
`kwb_email_queue`	`status, scheduled_for`	Cron picks pending sends

Privacy & encryption

Passwords: bcrypt hashed (cost 12). Never stored plain.
Journal entries: plain text in DB initially. Plan: encrypt at rest with per-user key once budget allows (column-level encryption via PHP openssl + key in .env).
Email + first_name: plain text (needed for outbound email rendering).
Stripe data: never stored beyond payment_intent_id. No card data touches the platform.
Cohort accountability content: lives in journal_entries with is_shared_with_partner=TRUE. Partner can read; no other user can.

Backup strategy

What	Frequency	Retention
Full DB dump	Nightly	30 days
Full DB dump	Weekly	1 year
Code (php files)	On every git commit	indefinite via GitHub
User uploads (none currently)	n/a	n/a
Stripe data	Stripe-side (canonical)	indefinite

Recovery procedure documented in _knowledge/BACKUP_RECOVERY_PLAN.md.

Schema versioning

database/schema.sql              ← canonical schema (latest)
database/migrations/
  2026-04-29_initial.sql         ← v1.0 baseline
  2026-MM-DD_<change>.sql        ← incremental migrations as schema evolves

Every schema change goes through: 1. Write migration in database/migrations/ 2. Test on local copy (or staging if available) 3. Run on production · capture timestamp + git SHA 4. Update schema.sql to reflect new state

Never edit production schema directly via phpMyAdmin without a migration file.

Cross-references

[[04_system_architecture]] — folder structure + auth flow
[[05_automation_plan]] — what queries this data
[[../../../../sandi-system-engine/_knowledge/SECURITY_PRIVACY|SECURITY_PRIVACY]] — what stays out of git