🗄️ PHPStrap Database Structure Guide

This guide explains the core tables, relationships, and best practices for working with the PhPstrap database.

🔍 Overview

PhPstrap uses a lightweight MySQL schema centered on users, settings, and optional feature tables (modules, invites, tokens, purchases, logs, affiliates). The schema is designed to be modular: you can run a minimal core or enable related features as needed.

🧩 Relationships (Text ERD)

users (id) ─┬─< affiliate_clicks.user_id
            ├─< affiliate_signups.user_id
            ├─< affiliate_signups.referred_user_id
            ├─< invites.generated_by
            ├─  invites.used_by (nullable)
            ├─< password_resets.user_id
            ├─< token_purchases.user_id
            ├─< user_tokens.user_id
            └─< withdrawals.user_id
withdrawals.processed_by (nullable) ──> users.id

settings.updated_by (nullable) ──> users.id
modules (no FKs)
admin_logs (optional; often includes actor_id -> users.id)

🏛️ Core Tables

users

The primary identity table. Tracks authentication, verification, membership, balances, and API access.

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
name	VARCHAR(100)	Display name
Email	VARCHAR(191)	Unique; indexed
password	VARCHAR(255)	BCrypt/Password_hash()
is_admin	TINYINT(1)	Admin flag
verified	TINYINT(1)	Email verified
verified_at	DATETIME NULL	Verification timestamp
membership_status	ENUM/VAR	e.g., free/premium
api_token	VARCHAR(64)	For API access
created_at / updated_at	DATETIME	Timestamps

settings

Key-value configuration store. Also used for app_version as the canonical version flag.

Column	Type	Notes
key (PK)	VARCHAR(191)	Unique setting key
value	TEXT	Serialized/JSON/string
updated_by	INT NULL	FK → users.id (nullable)
updated_at	DATETIME	Last change

modules

Registry for optional features (hcaptcha/smtp/analytics). Stores versions, enablement, and module-specific JSON.

Column	Type	Notes
name (PK)	VARCHAR(100)	Machine name (e.g., "smtp")
title	VARCHAR(150)	Display title
description	TEXT	Module purpose
version	VARCHAR(20)	Module version
enabled	TINYINT(1)	Feature toggle
settings	JSON/TEXT	Module config
hooks	JSON/TEXT	Event hooks

💳 Accounts, Tokens & Access

user_tokens

Tracks user token balances/credits (for quotas or billing).

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id
balance	INT	Current token count
updated_at	DATETIME	Last update

token_purchases

Token purchase ledger (auditing/accounting).

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id
amount	INT	Tokens purchased
reference	VARCHAR(255)	Payment ref/txn id
created_at	DATETIME	Purchase time

withdrawals

Tracks payout/withdrawal requests and processing.

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id
amount	DECIMAL(10,2)	Payout amount
status	ENUM/VAR	pending/approved/paid/rejected
processed_by	INT NULL	FK → users.id (admin, nullable)
created_at / updated_at	DATETIME	Timestamps

🔐 Access & Security

password_resets

One-time reset tokens with expirations.

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id
token	VARCHAR(64)	Unique; indexed
expires_at	DATETIME	Expiry time
created_at	DATETIME	Issued time

invites

Invitation codes and their usage (referral/onboarding).

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
code	VARCHAR(32)	Unique; indexed
generated_by	INT	FK → users.id
used_by	INT NULL	FK → users.id (nullable)
used_at	DATETIME NULL	Redemption time
created_at	DATETIME	Issued time

🤝 Affiliate (Optional)

affiliate_clicks

Click events attributed to an affiliate user.

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id (affiliate)
ip	VARCHAR(45)	IPv4/IPv6
ua	VARCHAR(255)	User agent
created_at	DATETIME	Event time

affiliate_signups

New signups attributed to an affiliate, including referred user.

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
user_id	INT	FK → users.id (affiliate)
referred_user_id	INT	FK → users.id (new user)
created_at	DATETIME	Event time

📝 Admin & Observability (Optional)

admin_logs

Administrative activity trail (actions performed in the admin UI or background jobs).

Column	Type	Notes
id (PK)	INT AUTO_INCREMENT	Primary key
action	VARCHAR(100)	Event name/key
actor_id	INT NULL	FK → users.id (nullable)
context	JSON/TEXT	Extra metadata
created_at	DATETIME	When it happened

⚙️ Indexing & Performance

users.email should be UNIQUE INDEX.
invites.code, password_resets.token should be indexed/unique.
Foreign keys (where enabled) should have indexes on the child columns: user_tokens.user_id, withdrawals.user_id, etc.
Consider pruning logs and old resets—see retention below.

🏷️ Versioning (Source of Truth)

The canonical application version is stored in settings.app_version. A code-side fallback exists in /config/version.php for cold-starts and installer flows.

📦 Migrations & Retention

Installer: The installer creates tables and seeds defaults. Re-running safely detects existing tables.
Updater: The admin updater writes the new app_version to settings after a successful file update.
Backups: Always back up DB before structural changes or major upgrades.
Retention: Periodically delete expired password_resets, old admin_logs, and stale affiliate_clicks as policy allows.

For questions or contributions, open an issue on GitHub.