# 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