# Kingdom Wife Blueprint — System Architecture

> *The platform stack. PHP + IONOS + MySQL · matches Sandi's locked pattern from membership-site-builder.*

---

## Stack lock (non-negotiable)

| Layer | Choice | Why |
|---|---|---|
| Backend | **PHP** (no Node, no Django, no Laravel) | Matches her existing `bootstrap.php` / `auth.php` template set |
| Database | **MySQL** via phpMyAdmin | `db809893484` already provisioned on IONOS |
| Hosting | **IONOS shared hosting** (LAMP) | Already paid · DFI Training Academy on same |
| Frontend | Server-rendered PHP + minimal JS | Matches her static-first approach |
| Payments | **Stripe** (test → live cutover) | Industry standard · low integration cost |
| Email | IONOS PHP mailer OR ConvertKit | Decide based on volume; PHP-mailer for <500 sends/day |
| Calendar | **Calendly** for cohort calls | Already in her tool stack |
| Video conf | Zoom (paid) for cohort calls; recordings to private S3 or Bunny CDN | Avoid Vimeo (PHP integration friction) |
| File storage | IONOS file storage for PDFs · S3 if scale demands | Most files <50MB · IONOS sufficient initially |

---

## Folder structure (under `public_html/kingdom-wife/`)

```
public_html/kingdom-wife/
├── admin/                   ← Sandi only (require_admin)
│   ├── dashboard.php
│   ├── cohort-tracking.php
│   ├── purchase-log.php
│   ├── email-queue.php
│   └── reports/
│       └── monthly.php
├── includes/
│   ├── bootstrap.php        ← session start · DB connect · helpers
│   ├── auth.php             ← login · logout · require_login · require_tier
│   ├── stripe-webhook.php
│   ├── email-sender.php
│   └── content-gate.php     ← tier-based content access checks
├── docs/                    ← downloadable PDFs (workbook · scripture cards)
├── database/
│   └── schema.sql           ← canonical schema · version-tracked
├── styles/
│   ├── kwb.css              ← Sandi Personal palette tokens
│   └── kwb-print.css
├── images/
├── js/
│   └── app.js               ← minimal · journal save · cohort RSVP
├── content/                 ← course modules · gated by tier
│   ├── module-1-inner-life/
│   │   ├── lesson-01.php
│   │   ├── lesson-02.php
│   │   └── ...
│   ├── module-2-relational/
│   ├── module-3-authority/
│   └── module-4-leadership/
├── cohort/                  ← Tier 3 only (require_tier 3)
│   ├── home.php
│   ├── recordings.php
│   └── accountability-pairs.php
├── signup.php
├── login.php
├── logout.php
├── dashboard.php            ← user-facing dashboard (their tier · their progress)
├── account.php
├── checkout.php             ← Stripe redirect
├── thank-you.php
├── index.php                ← public landing (Three Truths · sample chapter · signup CTA)
└── .htaccess                ← security · clean URLs · auth redirects
```

---

## MySQL schema (extends existing `db809893484`)

```sql
-- USERS table (extend existing if shared with DFI; otherwise new)
CREATE TABLE kwb_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP NULL,
  current_tier TINYINT DEFAULT 0,           -- 0=none, 1=book, 2=course, 3=cohort
  tier_expires_at TIMESTAMP NULL,           -- for monthly tier 3
  is_admin BOOLEAN DEFAULT FALSE,
  email_verified BOOLEAN DEFAULT FALSE,
  newsletter_opt_in BOOLEAN DEFAULT TRUE,
  INDEX (email), INDEX (current_tier)
);

-- PURCHASES table
CREATE TABLE kwb_purchases (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  stripe_payment_intent VARCHAR(255) UNIQUE,
  tier_purchased TINYINT NOT NULL,
  amount_cents INT NOT NULL,
  currency CHAR(3) DEFAULT 'USD',
  status ENUM('pending','succeeded','failed','refunded') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES kwb_users(id),
  INDEX (status), INDEX (created_at)
);

-- COHORT membership (Tier 3 only)
CREATE TABLE kwb_cohort_membership (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  cohort_number INT NOT NULL,
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  left_at TIMESTAMP NULL,
  accountability_partner_id INT NULL,
  attended_calls INT DEFAULT 0,
  total_calls_during_membership INT DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES kwb_users(id),
  FOREIGN KEY (accountability_partner_id) REFERENCES kwb_users(id),
  INDEX (cohort_number), INDEX (left_at)
);

-- JOURNAL entries (private to user · encrypted at rest if budget allows)
CREATE TABLE kwb_journal_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  prompt_id VARCHAR(100),                   -- which prompt this answers
  entry_text MEDIUMTEXT,
  is_shared_with_partner BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES kwb_users(id),
  INDEX (user_id, created_at)
);

-- EMAIL queue (drives Day 0/3/7/14/30 sequence)
CREATE TABLE kwb_email_queue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  email_template VARCHAR(50) NOT NULL,      -- e.g., 'day-0-welcome', 'day-3-intro'
  scheduled_for TIMESTAMP NOT NULL,
  sent_at TIMESTAMP NULL,
  status ENUM('pending','sent','failed','cancelled') DEFAULT 'pending',
  FOREIGN KEY (user_id) REFERENCES kwb_users(id),
  INDEX (status, scheduled_for)
);

-- EMAIL log (after-the-fact record)
CREATE TABLE kwb_email_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  email_template VARCHAR(50),
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  opened_at TIMESTAMP NULL,
  clicked_link VARCHAR(255) NULL,
  FOREIGN KEY (user_id) REFERENCES kwb_users(id)
);

-- CONTENT progress (which lessons a user has completed)
CREATE TABLE kwb_content_progress (
  user_id INT NOT NULL,
  content_id VARCHAR(100) NOT NULL,         -- e.g., 'module-1-lesson-3'
  completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, content_id),
  FOREIGN KEY (user_id) REFERENCES kwb_users(id)
);
```

---

## Auth flow

1. User signs up via `/signup.php` (email + password + tier choice).
2. Password hashed with PHP `password_hash()` (bcrypt cost 12).
3. Session cookie set via `bootstrap.php` (HttpOnly · Secure · SameSite=Lax).
4. `auth.php::require_login()` and `auth.php::require_tier($n)` gate every protected page.
5. Stripe checkout redirect on tier purchase. Webhook back to PHP confirms payment → `kwb_purchases` record → `kwb_users.current_tier` updated.

---

## Security checklist (per `membership-site-builder` SKILL)

- [x] HTTPS-only (IONOS auto-SSL)
- [x] HttpOnly + Secure + SameSite cookies
- [x] CSRF tokens on all forms (submit + journal)
- [x] Password hashed with bcrypt
- [x] SQL: prepared statements only (PDO with `?` placeholders)
- [x] `.htaccess` denies access to `/includes/`, `/database/`, `*.sql`, `*.bak`
- [x] Stripe webhook signature verification
- [x] Rate limiting on `/login.php` (5 attempts/15 min)
- [x] Audit log table for admin actions
- [x] Backup: nightly DB dump · weekly file backup · 30-day retention

---

## Cross-references

- `_skills/membership-site-builder/SKILL.md` — parent stack pattern
- `_skills/kingdom-wife-membership-platform/SKILL.md` — KWB-specific spec + Stack Lock
- `_knowledge/SECURITY_PRIVACY.md` — what stays out of git
- `02_action_plan.md` — Phase 2 build sequence
- `05_automation_plan.md` — what runs without Sandi
