04 System Architecture

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)

-- 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)

Cross-references