# VulaPrize + VulaPulse
## Production PHP/cPanel Master Build Specification
**Document status:** Code-ready technical blueprint
**Target platform:** cPanel shared hosting or managed cPanel VPS
**Application type:** Server-rendered PHP web application with progressive JavaScript enhancement
**Primary database:** MySQL 8.0+ or MariaDB 10.6+
**Recommended PHP:** PHP 8.4; PHP 8.3 minimum
**Primary timezone:** `Africa/Johannesburg`
**Currency:** South African Rand (`ZAR`)
**Prepared for:** Full implementation by a PHP developer or an AI coding agent
**Last specification review:** 4 July 2026
---
# 0. How to Use This Document
This file is the single source of truth for building the complete VulaPrize + VulaPulse platform on cPanel.
A developer must:
1. Read the entire document before generating code.
2. Preserve all non-negotiable business rules.
3. Build the system in the implementation order defined later in this file.
4. Never invent a shortcut that weakens payment verification, role separation, ledger integrity, contest fairness, or auditability.
5. Use database transactions for every operation that changes money, KW, contest-entry rights, winners, or payment status.
6. Treat external payment callbacks as untrusted, duplicated, and unordered input.
7. Never expose private filesystem paths, secrets, SQL errors, stack traces, payment payloads, or admin-only fields to clients.
8. Complete every acceptance test before production launch.
This blueprint expands and hardens the earlier VulaPrize + VulaPulse concept. Important corrections include:
- Entry rights are tracked through **entry entitlements**, not only an order count.
- Payment callbacks are **idempotent** and stored as immutable events.
- KW uses a proper **double-entry-style ledger discipline** with an atomic cached balance.
- Contest answers are **committed and locked before activation**, then revealed after closure.
- Winner selection is deterministic, transactional, repeatable, and auditable.
- Support tickets use separate messages and internal admin notes.
- Secrets and writable storage are placed outside `public_html` wherever the host permits.
- cPanel cron scripts use locks so overlapping executions cannot corrupt data.
---
# 1. Product Definition
## 1.1 VulaPrize
VulaPrize is a contest platform where registered clients enter number-based, objectively verifiable challenges connected to advertised prizes. A contest has a fixed question, reference media, opening time, closing time, answer, entry price, entry cap, and deterministic winner-selection rule.
The default winner rule is:
```text
smallest absolute distance between submitted guess and correct answer
then earliest valid entry timestamp
then lowest contest entry ID as a final deterministic tie-breaker
```
Equivalent ordering:
```sql
ORDER BY
ABS(guess_value - :correct_answer) ASC,
submitted_at ASC,
id ASC
LIMIT 1
```
## 1.2 VulaPulse
VulaPulse is the engagement and loyalty subsystem. Clients earn KW, short for Kilo-Wins, through permitted non-purchase actions such as daily streak claims and configured bonuses.
KW rules:
- KW is not legal tender.
- KW cannot be withdrawn as cash.
- KW cannot be transferred between clients.
- KW cannot be purchased directly with ZAR.
- KW cannot be manually edited without an immutable admin audit record and ledger transaction.
- KW can be redeemed only for rewards enabled by the platform, such as contest-entry entitlements or manually fulfilled vouchers.
- The client-facing KW transaction page must not expose ZAR payment amounts.
## 1.3 Request Hub
Request Hub is the support-ticket subsystem.
Non-negotiable rule:
```text
A client may not create a new ticket while that client has any ticket whose status is pending or in_progress.
```
The restriction must be enforced in both the user interface and server-side service layer. A disabled button alone is not sufficient.
## 1.4 Administration Console
The administration console provides:
- dashboard metrics;
- contest lifecycle management;
- secure answer commitment and reveal;
- user management;
- KW adjustment with reason and audit trail;
- payment and webhook reconciliation;
- entry-entitlement inspection;
- winner selection and publication;
- support-ticket management;
- redemption fulfilment;
- email queue and system-log monitoring;
- configuration management;
- audit-log review.
---
# 2. Mandatory Compliance and Launch Gate
The software must not describe the product as legally exempt from gambling, consumer-protection, promotional-competition, tax, advertising, or licensing rules merely because the winner is selected by skill.
Before accepting real money, the operator must obtain South African legal advice covering at least:
- classification of every contest mechanic;
- whether an entry fee is permitted for the specific format;
- age and identity restrictions;
- prize terms, delivery, substitutions, and refunds;
- Consumer Protection Act requirements;
- POPIA privacy obligations;
- record-retention requirements;
- tax treatment;
- payment-provider acceptable-use rules;
- marketing claims and winner publication consent;
- dispute and complaint procedures.
The production application must contain a configuration flag:
```env
REAL_MONEY_ENABLED=false
```
Real-money checkout routes must refuse payment creation until all of the following are true:
```text
APP_ENV=production
APP_DEBUG=false
REAL_MONEY_ENABLED=true
PAYMENT_GATEWAY_MODE=live
TERMS_VERSION is configured
PRIVACY_VERSION is configured
```
This launch gate prevents accidental live trading during development.
---
# 3. Non-Negotiable Business Rules
## 3.1 Role isolation
Roles:
- `admin`
- `client`
Rules:
- Every admin route requires an authenticated user with role `admin`.
- Every client route requires an authenticated user with role `client`, unless explicitly shared.
- A client must never receive admin navigation, admin metrics, payment raw payloads, other clients’ details, internal notes, answer secrets, or audit logs.
- An admin who opens a client-only route may be redirected to `/admin` rather than silently rendered as a client.
- Role checks belong in middleware and service authorization, not only templates.
## 3.2 Contest answer integrity
A contest answer must be configured and cryptographically committed before the contest becomes active.
Once the contest status becomes `active`:
- question content that could materially change the answer is locked;
- reference media is locked;
- answer commitment is locked;
- answer changes are prohibited;
- closing time may only be extended through a privileged action with a required reason and audit event;
- closing time may never be moved backward after entries exist, except through a documented cancellation workflow.
The answer may remain hidden from clients until the contest is completed.
## 3.3 Entry entitlement integrity
A client can submit a guess only when all of the following are true:
- the contest is active;
- current server time is before the contest closing time;
- the user is active and authenticated;
- the user has an unused valid entry entitlement for the contest;
- the user has not exceeded the contest’s maximum entries per user;
- the guess is within the configured numeric range;
- the same user has not submitted the same guess for the same contest when duplicate guesses are disabled;
- CSRF, rate-limit, and anti-automation checks pass.
Each accepted guess consumes exactly one entitlement in the same database transaction as the entry insert.
## 3.4 Payment integrity
- Browser redirects do not prove payment.
- A success page does not grant entries.
- Only a verified server-to-server webhook/ITN or an explicitly authorized admin reconciliation can mark an order paid.
- The paid amount, merchant account, currency, order reference, and gateway transaction identifier must match expected values.
- Duplicate payment events must not grant duplicate entitlements.
- Payment event processing must be idempotent.
- Raw callback data is admin-only and should be retained according to the approved retention policy.
## 3.5 KW privacy rule
The client’s transaction page displays only KW ledger entries:
```text
Date | Description | +KW or -KW | Balance after transaction
```
It must not query or render:
- order amount;
- payment gateway fee;
- prize value;
- gateway transaction ID;
- ZAR revenue;
- other users’ data.
Entry fees may still be displayed on public contest and checkout pages because a client must know the purchase price. The restriction applies to the KW transaction ledger and client account activity representation.
## 3.6 Ticket lock
Before creating a ticket, execute the lock check within the same database transaction used to insert the new ticket:
```sql
SELECT id
FROM support_tickets
WHERE user_id = :user_id
AND status IN ('pending', 'in_progress')
LIMIT 1
FOR UPDATE;
```
If a row exists, creation fails with a user-safe validation message.
## 3.7 Winner finality
- Winner selection may run only after a contest is closed.
- The answer must be available and match its pre-committed hash.
- At least one valid entry must exist.
- Selection runs under a database transaction with a contest row lock.
- Re-running selection returns the already selected winner unless an authorized void/reselection process is used.
- Reselection requires a reason, a second privileged confirmation, and complete audit records.
---
# 4. Recommended Technical Stack
## 4.1 Required server capabilities
- Apache or LiteSpeed under cPanel
- PHP 8.3 minimum; PHP 8.4 recommended
- MySQL 8.0+ or MariaDB 10.6+
- `pdo_mysql`
- `openssl`
- `mbstring`
- `intl`
- `json`
- `fileinfo`
- `curl`
- `filter`
- `session`
- `sodium` when available
- Composer 2
- HTTPS certificate
- cPanel Cron Jobs
- SMTP account or transactional email provider
Do not begin production implementation on PHP 8.1. PHP 8.1 is end-of-life. PHP 8.2 is security-fixes-only until the end of 2026. Prefer a supported version that the host can patch.
## 4.2 Composer packages
Use the minimum necessary dependencies:
```json
{
"require": {
"php": "^8.3 || ^8.4 || ^8.5",
"ext-pdo": "*",
"ext-json": "*",
"ext-openssl": "*",
"ext-mbstring": "*",
"phpmailer/phpmailer": "^6.9",
"vlucas/phpdotenv": "^5.6",
"ramsey/uuid": "^4.7"
},
"autoload": {
"psr-4": {
"App\\": "app/"
}
},
"config": {
"optimize-autoloader": true,
"sort-packages": true,
"platform-check": true
}
}
```
Optional packages must be justified. Do not install a full framework unless the hosting plan and deployment process support it reliably.
## 4.3 Application style
Use a lightweight MVC/service architecture:
- front controller;
- explicit router;
- middleware pipeline;
- controllers for HTTP handling;
- services for business logic;
- repositories for data access;
- views for escaped HTML rendering;
- DTO/value objects for validated inputs;
- database transactions in service methods;
- gateway adapters for PayFast and Yoco;
- cron commands using the same services as web routes.
Do not place business logic directly in templates.
---
# 5. Secure cPanel Filesystem Layout
The preferred layout keeps application code, secrets, logs, and uploads outside the public document root.
```text
/home/CPANEL_USER/
├── vulaprize-app/
│ ├── app/
│ │ ├── Controllers/
│ │ ├── Core/
│ │ ├── DTO/
│ │ ├── Exceptions/
│ │ ├── Middleware/
│ │ ├── Repositories/
│ │ ├── Services/
│ │ ├── Support/
│ │ └── Validation/
│ ├── bootstrap/
│ │ └── app.php
│ ├── config/
│ │ ├── app.php
│ │ ├── auth.php
│ │ ├── database.php
│ │ ├── mail.php
│ │ ├── payments.php
│ │ └── security.php
│ ├── database/
│ │ ├── migrations/
│ │ ├── seeds/
│ │ └── schema.sql
│ ├── resources/
│ │ ├── views/
│ │ └── email/
│ ├── routes/
│ │ ├── web.php
│ │ ├── admin.php
│ │ ├── client.php
│ │ └── webhook.php
│ ├── storage/
│ │ ├── cache/
│ │ ├── logs/
│ │ ├── private/
│ │ ├── sessions/
│ │ └── uploads/
│ │ └── contests/
│ ├── cron/
│ │ ├── close-contests.php
│ │ ├── process-email-queue.php
│ │ ├── expire-entitlements.php
│ │ ├── cleanup-expired-tokens.php
│ │ ├── reconcile-pending-orders.php
│ │ └── maintenance.php
│ ├── tests/
│ │ ├── Unit/
│ │ ├── Integration/
│ │ └── Fixtures/
│ ├── vendor/
│ ├── .env
│ ├── .env.example
│ ├── composer.json
│ └── composer.lock
│
└── public_html/
├── index.php
├── .htaccess
├── robots.txt
├── favicon.ico
├── assets/
│ ├── css/
│ ├── js/
│ ├── images/
│ └── fonts/
└── media/
└── contest-public/
```
## 5.1 If code must live inside `public_html`
When the host does not permit the preferred layout, place non-public files in protected directories and deny direct HTTP access:
```apache
# In sensitive directories
Require all denied
```
At minimum, deny access to:
- `.env`
- `config/`
- `app/`
- `database/`
- `storage/`
- `vendor/`
- `cron/`
- log files
- SQL files
- backups
Never store `.env`, SQL backups, private identity documents, or logs in a directly browsable directory.
## 5.2 Writable permissions
Typical permissions:
```text
directories: 0755
files: 0644
.env: 0600 where supported, otherwise 0640
storage: 0750 or the minimum required by the hosting PHP user
```
Do not use `0777`.
---
# 6. Environment Configuration
Create `.env.example` without real secrets:
```dotenv
APP_NAME="VulaPrize"
APP_ENV=local
APP_DEBUG=true
APP_URL=https://example.co.za
APP_TIMEZONE=Africa/Johannesburg
APP_KEY=
APP_VERSION=1.0.0
REAL_MONEY_ENABLED=false
TERMS_VERSION=2026-07-01
PRIVACY_VERSION=2026-07-01
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=cpuser_vulaprize
DB_USERNAME=cpuser_vulauser
DB_PASSWORD=
DB_CHARSET=utf8mb4
SESSION_NAME=vulaprize_session
SESSION_LIFETIME_MINUTES=120
SESSION_SECURE_COOKIE=true
SESSION_SAME_SITE=Lax
TRUSTED_PROXIES=
MAIL_TRANSPORT=smtp
MAIL_HOST=
MAIL_PORT=587
MAIL_ENCRYPTION=tls
MAIL_USERNAME=
MAIL_PASSWORD=
MAIL_FROM_ADDRESS=no-reply@example.co.za
MAIL_FROM_NAME="VulaPrize"
SUPPORT_EMAIL=support@example.co.za
PAYMENT_DEFAULT_GATEWAY=payfast
PAYMENT_GATEWAY_MODE=sandbox
PAYFAST_MERCHANT_ID=
PAYFAST_MERCHANT_KEY=
PAYFAST_PASSPHRASE=
PAYFAST_PROCESS_URL=https://sandbox.payfast.co.za/eng/process
PAYFAST_VALIDATE_URL=https://sandbox.payfast.co.za/eng/query/validate
YOCO_SECRET_KEY=
YOCO_WEBHOOK_SECRET=
YOCO_API_BASE=https://payments.yoco.com/api
RECAPTCHA_ENABLED=false
RECAPTCHA_SITE_KEY=
RECAPTCHA_SECRET_KEY=
UPLOAD_MAX_BYTES=5242880
ALLOWED_IMAGE_MIME=image/jpeg,image/png,image/webp
LOG_LEVEL=debug
LOG_RETENTION_DAYS=90
PAYMENT_EVENT_RETENTION_DAYS=365
AUDIT_RETENTION_DAYS=2555
```
## 6.1 Configuration rules
- Fail closed when required production variables are missing.
- Never supply insecure defaults for live payment keys.
- Validate URL schemes and prevent non-HTTPS callback URLs in production.
- Keep gateway keys in `.env`, not editable plaintext in the database.
- Database settings may contain non-secret display configuration, but secrets remain environment variables.
- Generate `APP_KEY` from at least 32 cryptographically random bytes.
Example command:
```bash
php -r "echo base64_encode(random_bytes(32)), PHP_EOL;"
```
---
# 7. HTTP Request Lifecycle
Every browser request follows this sequence:
```text
Apache/LiteSpeed
-> public_html/.htaccess
-> public_html/index.php
-> bootstrap/app.php
-> error handler
-> secure session initialization
-> router match
-> global middleware
-> route middleware
-> controller
-> service
-> repository/PDO
-> response/view
```
Global middleware order:
1. trusted host validation;
2. HTTPS enforcement in production;
3. request ID generation;
4. security headers;
5. session start;
6. session fingerprint validation;
7. maintenance-mode check;
8. request-size limit;
9. route dispatch;
10. exception-to-response handling;
11. structured access log.
POST/PUT/PATCH/DELETE browser forms additionally require:
- CSRF validation;
- method validation;
- content-type validation;
- route-specific authorization;
- rate limiting;
- input validation.
Webhook routes do not use session CSRF. They use gateway signature/origin verification, event idempotency, amount validation, and strict payload schemas.
---
# 8. Route Catalogue
## 8.1 Public routes
| Method | Path | Controller action | Notes |
|---|---|---|---|
| GET | `/` | `HomeController@index` | Active contests and platform introduction |
| GET | `/contests` | `PublicContestController@index` | Public contest listing |
| GET | `/contests/{slug}` | `PublicContestController@show` | Never expose hidden answer |
| GET | `/winners` | `WinnerController@index` | Only consented/public winner data |
| GET | `/winners/{publicId}` | `WinnerController@show` | Completed contest result |
| GET | `/terms` | `LegalController@terms` | Versioned terms |
| GET | `/privacy` | `LegalController@privacy` | Versioned privacy notice |
| GET | `/contact` | `ContactController@show` | General information |
| GET | `/login` | `AuthController@showLogin` | Guest only |
| POST | `/login` | `AuthController@login` | Rate limited |
| GET | `/register` | `AuthController@showRegister` | Guest only |
| POST | `/register` | `AuthController@register` | Rate limited, consent captured |
| GET | `/verify-email` | `EmailVerificationController@notice` | Authenticated |
| GET | `/verify-email/{token}` | `EmailVerificationController@verify` | Signed opaque token |
| POST | `/verify-email/resend` | `EmailVerificationController@resend` | Rate limited |
| GET | `/forgot-password` | `PasswordController@showForgot` | Guest only |
| POST | `/forgot-password` | `PasswordController@sendReset` | Enumeration-safe |
| GET | `/reset-password/{token}` | `PasswordController@showReset` | Token validated |
| POST | `/reset-password` | `PasswordController@reset` | Revokes sessions |
| POST | `/logout` | `AuthController@logout` | CSRF protected |
## 8.2 Client routes
Prefix: `/account`
| Method | Path | Action |
|---|---|---|
| GET | `/account` | Dashboard |
| POST | `/account/streak/claim` | Claim daily KW |
| GET | `/account/contests` | Eligible contests |
| GET | `/account/contests/{slug}` | Contest account view |
| POST | `/account/contests/{slug}/checkout` | Create order and gateway redirect |
| GET | `/account/orders/{publicId}` | Order status; no gateway secrets |
| GET | `/account/payments/return` | Browser return page; never grants entries |
| GET | `/account/payments/cancel` | Cancel page |
| POST | `/account/contests/{slug}/entries` | Submit guess and consume entitlement |
| GET | `/account/entries` | User’s own entries |
| GET | `/account/kw` | KW ledger only |
| GET | `/account/redeem` | Rewards catalogue |
| POST | `/account/redeem/entry` | Redeem KW for entry entitlement |
| POST | `/account/redeem/voucher` | Redeem KW for voucher |
| GET | `/account/redemptions` | User’s redemption history |
| GET | `/account/support` | User’s ticket list |
| GET | `/account/support/new` | New ticket form, blocked when open ticket exists |
| POST | `/account/support` | Create ticket with server-side lock |
| GET | `/account/support/{publicId}` | Ticket and public messages |
| POST | `/account/support/{publicId}/messages` | Add client message if allowed |
| GET | `/account/profile` | Profile form |
| POST | `/account/profile` | Update profile |
| POST | `/account/profile/password` | Change password and revoke other sessions |
| POST | `/account/profile/consent` | Update optional marketing consent |
## 8.3 Admin routes
Prefix: `/admin`
| Method | Path | Action |
|---|---|---|
| GET | `/admin` | Dashboard metrics |
| GET | `/admin/contests` | Contest list/filter |
| GET | `/admin/contests/create` | Create form |
| POST | `/admin/contests` | Create draft |
| GET | `/admin/contests/{id}/edit` | Edit allowed fields |
| POST | `/admin/contests/{id}` | Update draft/upcoming contest |
| POST | `/admin/contests/{id}/answer` | Set encrypted answer and commitment |
| POST | `/admin/contests/{id}/activate` | Validate and activate |
| POST | `/admin/contests/{id}/close` | Manual close |
| POST | `/admin/contests/{id}/select-winner` | Deterministic selection |
| POST | `/admin/contests/{id}/publish-result` | Publish answer/result |
| POST | `/admin/contests/{id}/cancel` | Cancel and trigger refund review |
| GET | `/admin/users` | User search |
| GET | `/admin/users/{id}` | Full admin profile |
| POST | `/admin/users/{id}/status` | Activate/suspend |
| POST | `/admin/users/{id}/kw-adjustment` | Ledger adjustment with reason |
| POST | `/admin/users/{id}/password-reset` | Send reset; never set plaintext password |
| GET | `/admin/orders` | Orders and reconciliation |
| GET | `/admin/orders/{id}` | Order, events, entitlements |
| POST | `/admin/orders/{id}/reconcile` | Privileged/manual review |
| GET | `/admin/payment-events` | Event diagnostics |
| GET | `/admin/entries` | Search entries |
| GET | `/admin/redemptions` | Reward fulfilment queue |
| POST | `/admin/redemptions/{id}/complete` | Complete atomically |
| POST | `/admin/redemptions/{id}/fail` | Fail/refund KW where policy permits |
| GET | `/admin/support` | Ticket queue |
| GET | `/admin/support/{id}` | Ticket conversation and internal notes |
| POST | `/admin/support/{id}/status` | Update status |
| POST | `/admin/support/{id}/messages` | Add public response |
| POST | `/admin/support/{id}/internal-note` | Add admin-only note |
| GET | `/admin/settings` | Non-secret settings |
| POST | `/admin/settings` | Update validated settings |
| GET | `/admin/audit` | Audit-log search |
| GET | `/admin/email-queue` | Queue status |
| POST | `/admin/email-queue/{id}/retry` | Retry failed message |
| GET | `/admin/system` | Health, PHP, DB, cron heartbeat |
## 8.4 Webhook routes
| Method | Path | Action |
|---|---|---|
| POST | `/webhooks/payfast/itn` | Verify and process PayFast ITN |
| POST | `/webhooks/yoco` | Verify and process Yoco event |
Webhook routes must:
- be HTTPS-only in production;
- accept POST only;
- read the raw body before parsing where gateway verification requires it;
- reject oversized payloads;
- never render HTML errors;
- log a request ID;
- return a simple gateway-compatible 2xx/4xx/5xx response;
- avoid sending email synchronously.
---
# 9. Role and Permission Matrix
| Capability | Guest | Client | Admin |
|---|---:|---:|---:|
| View public contests | Yes | Yes | Yes |
| Register/login | Yes | No | No |
| View own dashboard | No | Yes | No |
| Purchase entry rights | No | Yes | No |
| Submit own guesses | No | Yes | No |
| View own KW ledger | No | Yes | No |
| View ZAR payment total in checkout/order receipt | No | Own only | All |
| View ZAR in KW ledger | Never | Never | N/A |
| View another client’s entries | No | No | Yes |
| Create support ticket | No | Yes, lock applies | No |
| Manage support tickets | No | Own public thread | Yes |
| Create/edit contest | No | No | Yes |
| Set/reveal answer | No | No | Yes |
| Select winner | No | No | Yes |
| Adjust KW | No | No | Yes, reason required |
| View raw payment event | No | No | Yes, privileged |
| View internal support note | No | No | Yes |
| View audit log | No | No | Yes |
---
# 10. Domain State Machines
## 10.1 Contest state
```text
draft -> upcoming -> active -> closed -> judging -> completed
\ \ \ \ \
\ \ \ \ -> cancelled
\ \ \ -> cancelled
\ \ -> cancelled
\ -> cancelled
-> cancelled
```
Allowed meanings:
- `draft`: incomplete, admin-editable, not visible.
- `upcoming`: validated and public, not accepting entries.
- `active`: accepting checkout and entries.
- `closed`: entry acceptance stopped.
- `judging`: answer verified and winner-selection operation in progress/review.
- `completed`: winner selected and result final; publication may be separate.
- `cancelled`: no new entries; refund/reversal workflow required where applicable.
Transitions must be implemented through a service, never by arbitrary form-submitted status values.
## 10.2 Order state
```text
pending -> processing -> paid
| | |
| | -> refunded / partially_refunded
| -> failed
-> cancelled / expired / failed
```
A paid order is immutable except for refund-related fields and reconciliation metadata.
## 10.3 Entitlement state
```text
available -> consumed
available -> expired
available -> revoked
```
Each unit represents one right to submit one guess. For efficient storage, a row may represent multiple units with `quantity_total` and `quantity_used`, but consumption must be atomic.
## 10.4 Redemption state
```text
pending -> processing -> completed
| |
| -> failed -> reversed
-> cancelled -> reversed
```
Entry-entitlement redemptions can normally complete immediately in one transaction. Voucher redemptions are normally pending until admin fulfilment.
## 10.5 Ticket state
```text
pending -> in_progress -> resolved -> closed
| | |
| | -> in_progress (reopen by admin policy)
| -> resolved
-> resolved
```
The ticket lock applies to `pending` and `in_progress`.
---
# 11. Full Database Schema
## 11.1 Database conventions
- Engine: `InnoDB`
- Character set: `utf8mb4`
- Collation: `utf8mb4_unicode_ci` or the server’s modern equivalent
- Store timestamps in UTC in the database.
- Convert to `Africa/Johannesburg` for display.
- Store ZAR amounts as integer cents, not floating-point values.
- Use unsigned integer IDs internally.
- Use opaque `public_id` values in URLs.
- Use foreign keys where supported.
- Use application-level validation in addition to database constraints.
## 11.2 Complete SQL DDL
```sql
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE users (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
email VARCHAR(190) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(80) NULL,
last_name VARCHAR(80) NULL,
phone_e164 VARCHAR(20) NULL,
id_number_encrypted TEXT NULL,
address_encrypted TEXT NULL,
role ENUM('admin','client') NOT NULL DEFAULT 'client',
status ENUM('pending_verification','active','suspended','disabled') NOT NULL DEFAULT 'pending_verification',
email_verified_at DATETIME NULL,
kw_balance BIGINT UNSIGNED NOT NULL DEFAULT 0,
streak_count INT UNSIGNED NOT NULL DEFAULT 0,
last_streak_local_date DATE NULL,
failed_login_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
locked_until DATETIME NULL,
last_login_at DATETIME NULL,
last_login_ip_hash CHAR(64) NULL,
terms_version VARCHAR(40) NOT NULL,
terms_accepted_at DATETIME NOT NULL,
privacy_version VARCHAR(40) NOT NULL,
privacy_accepted_at DATETIME NOT NULL,
marketing_consent TINYINT(1) NOT NULL DEFAULT 0,
marketing_consent_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_users_public_id (public_id),
UNIQUE KEY uq_users_email (email),
KEY idx_users_role_status (role, status),
KEY idx_users_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE user_sessions (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
session_hash CHAR(64) NOT NULL,
ip_hash CHAR(64) NULL,
user_agent_hash CHAR(64) NULL,
last_activity_at DATETIME NOT NULL,
expires_at DATETIME NOT NULL,
revoked_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_user_sessions_hash (session_hash),
KEY idx_user_sessions_user_active (user_id, revoked_at, expires_at),
CONSTRAINT fk_user_sessions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE login_attempts (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email_hash CHAR(64) NOT NULL,
ip_hash CHAR(64) NOT NULL,
was_successful TINYINT(1) NOT NULL DEFAULT 0,
attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_login_attempts_ip_time (ip_hash, attempted_at),
KEY idx_login_attempts_email_time (email_hash, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE email_verification_tokens (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
token_hash CHAR(64) NOT NULL,
expires_at DATETIME NOT NULL,
used_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_email_verification_token_hash (token_hash),
KEY idx_email_verification_user (user_id, expires_at),
CONSTRAINT fk_email_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE password_reset_tokens (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
token_hash CHAR(64) NOT NULL,
expires_at DATETIME NOT NULL,
used_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_password_reset_token_hash (token_hash),
KEY idx_password_reset_user (user_id, expires_at),
CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contests (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
title VARCHAR(190) NOT NULL,
slug VARCHAR(190) NOT NULL,
short_description VARCHAR(300) NULL,
description MEDIUMTEXT NOT NULL,
question_text TEXT NOT NULL,
rules_text MEDIUMTEXT NOT NULL,
prize_name VARCHAR(190) NOT NULL,
prize_description TEXT NULL,
prize_value_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
entry_fee_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
currency CHAR(3) NOT NULL DEFAULT 'ZAR',
minimum_guess BIGINT NOT NULL DEFAULT 0,
maximum_guess BIGINT NOT NULL,
max_entries_per_user INT UNSIGNED NOT NULL DEFAULT 20,
duplicate_guess_policy ENUM('allow','disallow_per_user') NOT NULL DEFAULT 'disallow_per_user',
status ENUM('draft','upcoming','active','closed','judging','completed','cancelled') NOT NULL DEFAULT 'draft',
opens_at DATETIME NOT NULL,
closes_at DATETIME NOT NULL,
answer_locked_at DATETIME NULL,
closed_at DATETIME NULL,
completed_at DATETIME NULL,
cancelled_at DATETIME NULL,
cancellation_reason TEXT NULL,
result_published_at DATETIME NULL,
winner_entry_id BIGINT UNSIGNED NULL,
created_by BIGINT UNSIGNED NOT NULL,
updated_by BIGINT UNSIGNED NOT NULL,
row_version INT UNSIGNED NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_contests_public_id (public_id),
UNIQUE KEY uq_contests_slug (slug),
KEY idx_contests_status_times (status, opens_at, closes_at),
KEY idx_contests_created_by (created_by),
CONSTRAINT fk_contests_created_by FOREIGN KEY (created_by) REFERENCES users(id),
CONSTRAINT fk_contests_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contest_secrets (
contest_id BIGINT UNSIGNED NOT NULL,
encrypted_answer MEDIUMTEXT NOT NULL,
answer_commit_hash CHAR(64) NOT NULL,
answer_salt_encrypted TEXT NOT NULL,
encryption_version VARCHAR(30) NOT NULL DEFAULT 'v1',
committed_by BIGINT UNSIGNED NOT NULL,
committed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
revealed_at DATETIME NULL,
PRIMARY KEY (contest_id),
UNIQUE KEY uq_contest_answer_commit (answer_commit_hash),
CONSTRAINT fk_contest_secrets_contest FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE,
CONSTRAINT fk_contest_secrets_admin FOREIGN KEY (committed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contest_media (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
contest_id BIGINT UNSIGNED NOT NULL,
media_type ENUM('image','video','document') NOT NULL DEFAULT 'image',
storage_path VARCHAR(500) NOT NULL,
public_path VARCHAR(500) NULL,
original_name VARCHAR(255) NOT NULL,
mime_type VARCHAR(100) NOT NULL,
size_bytes BIGINT UNSIGNED NOT NULL,
sha256_hash CHAR(64) NOT NULL,
alt_text VARCHAR(255) NULL,
sort_order INT NOT NULL DEFAULT 0,
is_primary TINYINT(1) NOT NULL DEFAULT 0,
locked_at DATETIME NULL,
uploaded_by BIGINT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_contest_media_contest (contest_id, sort_order),
UNIQUE KEY uq_contest_media_hash (contest_id, sha256_hash),
CONSTRAINT fk_contest_media_contest FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE CASCADE,
CONSTRAINT fk_contest_media_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
contest_id BIGINT UNSIGNED NOT NULL,
gateway ENUM('payfast','yoco') NOT NULL,
gateway_mode ENUM('sandbox','live') NOT NULL,
gateway_transaction_id VARCHAR(190) NULL,
merchant_reference VARCHAR(190) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'ZAR',
unit_price_cents BIGINT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL,
expected_amount_cents BIGINT UNSIGNED NOT NULL,
paid_amount_cents BIGINT UNSIGNED NULL,
status ENUM('pending','processing','paid','failed','cancelled','expired','refunded','partially_refunded') NOT NULL DEFAULT 'pending',
paid_at DATETIME NULL,
failed_at DATETIME NULL,
expires_at DATETIME NOT NULL,
refunded_amount_cents BIGINT UNSIGNED NOT NULL DEFAULT 0,
failure_code VARCHAR(100) NULL,
failure_message VARCHAR(500) NULL,
idempotency_key CHAR(36) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_orders_public_id (public_id),
UNIQUE KEY uq_orders_merchant_reference (merchant_reference),
UNIQUE KEY uq_orders_idempotency_key (idempotency_key),
UNIQUE KEY uq_orders_gateway_transaction (gateway, gateway_transaction_id),
KEY idx_orders_user_created (user_id, created_at),
KEY idx_orders_contest_status (contest_id, status),
KEY idx_orders_status_expiry (status, expires_at),
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_orders_contest FOREIGN KEY (contest_id) REFERENCES contests(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE payment_events (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
gateway ENUM('payfast','yoco') NOT NULL,
gateway_event_id VARCHAR(190) NOT NULL,
order_id BIGINT UNSIGNED NULL,
event_type VARCHAR(100) NOT NULL,
signature_valid TINYINT(1) NOT NULL DEFAULT 0,
origin_valid TINYINT(1) NOT NULL DEFAULT 0,
amount_valid TINYINT(1) NOT NULL DEFAULT 0,
payload_hash CHAR(64) NOT NULL,
headers_json MEDIUMTEXT NULL,
payload_json LONGTEXT NOT NULL,
processing_status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
processing_attempts INT UNSIGNED NOT NULL DEFAULT 0,
processing_error TEXT NULL,
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
processed_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_payment_events_gateway_event (gateway, gateway_event_id),
UNIQUE KEY uq_payment_events_payload_hash (gateway, payload_hash),
KEY idx_payment_events_order (order_id),
KEY idx_payment_events_status_time (processing_status, received_at),
CONSTRAINT fk_payment_events_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE entry_entitlements (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
contest_id BIGINT UNSIGNED NOT NULL,
source_type ENUM('paid_order','kw_redemption','admin_grant','promotion','reversal') NOT NULL,
source_id BIGINT UNSIGNED NULL,
quantity_total INT UNSIGNED NOT NULL,
quantity_used INT UNSIGNED NOT NULL DEFAULT 0,
status ENUM('available','consumed','expired','revoked') NOT NULL DEFAULT 'available',
expires_at DATETIME NULL,
granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
revoked_at DATETIME NULL,
revoke_reason VARCHAR(500) NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_entitlements_public_id (public_id),
KEY idx_entitlements_user_contest_status (user_id, contest_id, status),
KEY idx_entitlements_source (source_type, source_id),
CONSTRAINT fk_entitlements_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_entitlements_contest FOREIGN KEY (contest_id) REFERENCES contests(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contest_entries (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
contest_id BIGINT UNSIGNED NOT NULL,
entitlement_id BIGINT UNSIGNED NOT NULL,
guess_value BIGINT NOT NULL,
distance_from_answer BIGINT UNSIGNED NULL,
status ENUM('valid','void','winner','non_winner') NOT NULL DEFAULT 'valid',
void_reason VARCHAR(500) NULL,
request_id CHAR(36) NULL,
submitted_ip_hash CHAR(64) NULL,
submitted_user_agent_hash CHAR(64) NULL,
submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
judged_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_entries_public_id (public_id),
KEY idx_entries_user_contest (user_id, contest_id, submitted_at),
KEY idx_entries_contest_judging (contest_id, status, submitted_at, id),
KEY idx_entries_guess (contest_id, guess_value),
CONSTRAINT fk_entries_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_entries_contest FOREIGN KEY (contest_id) REFERENCES contests(id),
CONSTRAINT fk_entries_entitlement FOREIGN KEY (entitlement_id) REFERENCES entry_entitlements(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE contest_winners (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
contest_id BIGINT UNSIGNED NOT NULL,
entry_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
correct_answer BIGINT NOT NULL,
winning_guess BIGINT NOT NULL,
distance BIGINT UNSIGNED NOT NULL,
selection_algorithm VARCHAR(100) NOT NULL DEFAULT 'absolute_distance_v1',
selected_by BIGINT UNSIGNED NOT NULL,
selected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
status ENUM('selected','verified','notified','accepted','fulfilled','void') NOT NULL DEFAULT 'selected',
verification_notes TEXT NULL,
notification_sent_at DATETIME NULL,
accepted_at DATETIME NULL,
fulfilled_at DATETIME NULL,
voided_at DATETIME NULL,
void_reason TEXT NULL,
public_consent TINYINT(1) NOT NULL DEFAULT 0,
public_display_name VARCHAR(120) NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_contest_winner_contest (contest_id),
UNIQUE KEY uq_contest_winner_entry (entry_id),
KEY idx_winners_user (user_id),
CONSTRAINT fk_winners_contest FOREIGN KEY (contest_id) REFERENCES contests(id),
CONSTRAINT fk_winners_entry FOREIGN KEY (entry_id) REFERENCES contest_entries(id),
CONSTRAINT fk_winners_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_winners_selected_by FOREIGN KEY (selected_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE contests
ADD CONSTRAINT fk_contests_winner_entry
FOREIGN KEY (winner_entry_id) REFERENCES contest_entries(id) ON DELETE SET NULL;
CREATE TABLE kw_transactions (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
direction ENUM('credit','debit') NOT NULL,
amount BIGINT UNSIGNED NOT NULL,
balance_before BIGINT UNSIGNED NOT NULL,
balance_after BIGINT UNSIGNED NOT NULL,
source_type ENUM('daily_streak','streak_bonus','redemption','redemption_reversal','admin_adjustment','contest_bonus','promotion','correction') NOT NULL,
source_id BIGINT UNSIGNED NULL,
description VARCHAR(255) NOT NULL,
idempotency_key VARCHAR(190) NOT NULL,
created_by BIGINT UNSIGNED NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_kw_transactions_public_id (public_id),
UNIQUE KEY uq_kw_transactions_idempotency (idempotency_key),
KEY idx_kw_transactions_user_time (user_id, created_at, id),
KEY idx_kw_transactions_source (source_type, source_id),
CONSTRAINT fk_kw_transactions_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_kw_transactions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE reward_catalogue (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
name VARCHAR(190) NOT NULL,
reward_type ENUM('contest_entry','voucher') NOT NULL,
kw_cost BIGINT UNSIGNED NOT NULL,
entry_quantity INT UNSIGNED NULL,
voucher_value_cents BIGINT UNSIGNED NULL,
currency CHAR(3) NOT NULL DEFAULT 'ZAR',
is_active TINYINT(1) NOT NULL DEFAULT 1,
starts_at DATETIME NULL,
ends_at DATETIME NULL,
per_user_limit INT UNSIGNED NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_reward_catalogue_public_id (public_id),
KEY idx_reward_catalogue_active (is_active, starts_at, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE redemptions (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
reward_id BIGINT UNSIGNED NOT NULL,
contest_id BIGINT UNSIGNED NULL,
kw_spent BIGINT UNSIGNED NOT NULL,
status ENUM('pending','processing','completed','failed','cancelled','reversed') NOT NULL DEFAULT 'pending',
entitlement_id BIGINT UNSIGNED NULL,
fulfilment_reference VARCHAR(190) NULL,
client_note VARCHAR(500) NULL,
admin_note TEXT NULL,
requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
processing_at DATETIME NULL,
completed_at DATETIME NULL,
failed_at DATETIME NULL,
reversed_at DATETIME NULL,
processed_by BIGINT UNSIGNED NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_redemptions_public_id (public_id),
KEY idx_redemptions_user_time (user_id, requested_at),
KEY idx_redemptions_status (status, requested_at),
CONSTRAINT fk_redemptions_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_redemptions_reward FOREIGN KEY (reward_id) REFERENCES reward_catalogue(id),
CONSTRAINT fk_redemptions_contest FOREIGN KEY (contest_id) REFERENCES contests(id) ON DELETE SET NULL,
CONSTRAINT fk_redemptions_entitlement FOREIGN KEY (entitlement_id) REFERENCES entry_entitlements(id) ON DELETE SET NULL,
CONSTRAINT fk_redemptions_processed_by FOREIGN KEY (processed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE support_tickets (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
public_id CHAR(36) NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
subject VARCHAR(190) NOT NULL,
category ENUM('account','payment','contest','entry','redemption','technical','privacy','other') NOT NULL DEFAULT 'other',
priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
status ENUM('pending','in_progress','resolved','closed') NOT NULL DEFAULT 'pending',
assigned_admin_id BIGINT UNSIGNED NULL,
last_client_message_at DATETIME NULL,
last_admin_message_at DATETIME NULL,
resolved_at DATETIME NULL,
closed_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_support_tickets_public_id (public_id),
KEY idx_support_tickets_user_status (user_id, status),
KEY idx_support_tickets_queue (status, priority, updated_at),
CONSTRAINT fk_support_tickets_user FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT fk_support_tickets_assignee FOREIGN KEY (assigned_admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE support_ticket_messages (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ticket_id BIGINT UNSIGNED NOT NULL,
author_user_id BIGINT UNSIGNED NOT NULL,
visibility ENUM('public','internal') NOT NULL DEFAULT 'public',
message TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_ticket_messages_ticket_time (ticket_id, created_at, id),
CONSTRAINT fk_ticket_messages_ticket FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
CONSTRAINT fk_ticket_messages_author FOREIGN KEY (author_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE notifications (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
type VARCHAR(100) NOT NULL,
title VARCHAR(190) NOT NULL,
body VARCHAR(1000) NOT NULL,
action_url VARCHAR(500) NULL,
read_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_notifications_user_read (user_id, read_at, created_at),
CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE email_queue (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
template_key VARCHAR(100) NOT NULL,
recipient_email VARCHAR(190) NOT NULL,
recipient_name VARCHAR(190) NULL,
subject VARCHAR(255) NOT NULL,
payload_json MEDIUMTEXT NOT NULL,
status ENUM('pending','sending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
priority SMALLINT NOT NULL DEFAULT 100,
attempts INT UNSIGNED NOT NULL DEFAULT 0,
max_attempts INT UNSIGNED NOT NULL DEFAULT 5,
available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
locked_at DATETIME NULL,
locked_by VARCHAR(100) NULL,
last_error TEXT NULL,
sent_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_email_queue_work (status, available_at, priority, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE app_settings (
setting_key VARCHAR(100) NOT NULL,
setting_value MEDIUMTEXT NULL,
value_type ENUM('string','integer','boolean','json') NOT NULL DEFAULT 'string',
is_public TINYINT(1) NOT NULL DEFAULT 0,
updated_by BIGINT UNSIGNED NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (setting_key),
CONSTRAINT fk_app_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE audit_logs (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
request_id CHAR(36) NULL,
actor_user_id BIGINT UNSIGNED NULL,
actor_role VARCHAR(30) NULL,
action VARCHAR(120) NOT NULL,
entity_type VARCHAR(100) NOT NULL,
entity_id VARCHAR(100) NULL,
before_json LONGTEXT NULL,
after_json LONGTEXT NULL,
reason VARCHAR(1000) NULL,
ip_hash CHAR(64) NULL,
user_agent_hash CHAR(64) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_audit_actor_time (actor_user_id, created_at),
KEY idx_audit_entity (entity_type, entity_id, created_at),
KEY idx_audit_action_time (action, created_at),
CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cron_locks (
lock_name VARCHAR(100) NOT NULL,
locked_by VARCHAR(100) NOT NULL,
locked_at DATETIME NOT NULL,
expires_at DATETIME NOT NULL,
PRIMARY KEY (lock_name),
KEY idx_cron_locks_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE cron_heartbeats (
job_name VARCHAR(100) NOT NULL,
last_started_at DATETIME NULL,
last_finished_at DATETIME NULL,
last_status ENUM('success','failed','running') NULL,
last_message VARCHAR(1000) NULL,
duration_ms BIGINT UNSIGNED NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (job_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET FOREIGN_KEY_CHECKS = 1;
```
## 11.3 Duplicate-guess constraint
MySQL cannot conditionally apply a unique key based on a contest policy. Enforce the policy in the entry service under a contest/user lock. Optionally add this unique index when every contest disallows a client’s duplicate guess:
```sql
ALTER TABLE contest_entries
ADD UNIQUE KEY uq_entry_user_contest_guess (user_id, contest_id, guess_value);
```
Do not add it when any contest allows duplicates.
## 11.4 Initial seed data
Seed only safe non-secret values:
```sql
INSERT INTO reward_catalogue
(public_id, name, reward_type, kw_cost, entry_quantity, voucher_value_cents, is_active)
VALUES
(UUID(), '1 Extra Contest Entry', 'contest_entry', 10, 1, NULL, 1),
(UUID(), '3 Extra Contest Entries', 'contest_entry', 30, 3, NULL, 1),
(UUID(), 'R50 Voucher', 'voucher', 500, NULL, 5000, 1);
INSERT INTO app_settings (setting_key, setting_value, value_type, is_public)
VALUES
('site_name', 'VulaPrize', 'string', 1),
('base_streak_reward_kw', '5', 'integer', 0),
('streak_bonus_schedule', '{"7":10,"14":15,"21":20,"30":30}', 'json', 0),
('support_open_ticket_limit', '1', 'integer', 0),
('maintenance_mode', 'false', 'boolean', 0);
```
Create the first admin through a one-time CLI installer, not a public web installer. The installer must:
- require CLI execution;
- reject weak passwords;
- hash with `PASSWORD_ARGON2ID` where available, otherwise `PASSWORD_DEFAULT`;
- set email as verified;
- output no password;
- disable or delete itself after successful execution.
---
# 12. Core PHP Components
## 12.1 `Database`
Responsibilities:
- create one PDO connection per request;
- set exception mode;
- disable emulated prepares;
- use UTF-8;
- provide transaction helpers;
- support retry only for known deadlock/serialization failures;
- never log credentials.
Required PDO options:
```php
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_STRINGIFY_FETCHES => false,
]
```
Transaction helper contract:
```php
public function transaction(callable $callback, int $maxDeadlockRetries = 2): mixed;
```
## 12.2 `Router`
Must support:
- GET, POST, PUT, PATCH, DELETE;
- route parameters;
- named routes;
- middleware arrays;
- method-not-allowed responses;
- 404 responses;
- URL generation;
- route grouping by prefix and middleware.
## 12.3 `Request`
Must provide:
- normalized method;
- route parameters;
- query input;
- post input;
- JSON body;
- raw body;
- selected headers;
- uploaded files;
- client IP after trusted-proxy handling;
- request ID;
- content-type checks.
Never trust `X-Forwarded-For` unless the direct proxy is configured as trusted.
## 12.4 `Response`
Must support:
- HTML view;
- redirect;
- JSON;
- status codes;
- headers;
- no-store responses;
- file download for authorized private documents only.
## 12.5 View renderer
- All variables escaped by default through helper `e()`.
- Rich HTML is not allowed unless sanitized using an explicit allow-list.
- Templates cannot execute SQL.
- Templates receive prepared view models, not repository objects.
- Layouts include CSRF token meta data only for authenticated browser pages.
## 12.6 Validation
Each form has a dedicated validator or DTO. Validation errors are keyed by field and stored in flash session data.
Do not silently coerce invalid numbers. Use strict integer validation:
```php
filter_var($value, FILTER_VALIDATE_INT)
```
For money received from forms, clients submit quantities, not authoritative totals. The server derives:
```text
expected amount = contest entry fee x validated quantity
```
---
# 13. Authentication Specification
## 13.1 Registration
Required inputs:
- first name;
- last name;
- email;
- password;
- password confirmation;
- acceptance of current terms;
- acceptance of current privacy notice;
- optional marketing consent.
Rules:
- normalize email using trim and lowercase;
- do not reveal whether a similar disabled account exists beyond safe workflow messages;
- password minimum 12 characters;
- permit password managers and paste;
- no arbitrary maximum below 128 characters;
- hash using Argon2id when available;
- create `pending_verification` user;
- issue one-time email verification token;
- store only token hash;
- expire token, for example, after 24 hours;
- queue verification email;
- do not create a privileged role from public input.
## 13.2 Login
Algorithm:
1. Validate CSRF.
2. Normalize email.
3. Check IP/email rate limits.
4. Find user by email.
5. Run password verification. Use timing-safe dummy hash verification when the user does not exist.
6. Reject suspended/disabled users with a generic safe message.
7. If successful:
- reset failed count;
- regenerate PHP session ID;
- create `user_sessions` record;
- set minimal session values;
- record last login;
- redirect based on role.
8. Record every attempt using hashed email and IP.
Session values:
```php
$_SESSION = [
'user_id' => 123,
'role' => 'client',
'session_token' => 'random opaque value',
'csrf_token' => 'random opaque value',
'last_activity' => time(),
'ua_hash' => hash('sha256', normalized user agent),
];
```
Do not store password hashes, ID numbers, payment keys, or full profiles in the session.
## 13.3 Session security
Set before `session_start()`:
```php
ini_set('session.use_strict_mode', '1');
ini_set('session.use_only_cookies', '1');
ini_set('session.cookie_httponly', '1');
ini_set('session.cookie_secure', '1'); // production HTTPS
ini_set('session.cookie_samesite', 'Lax');
```
Use idle timeout and absolute timeout. Revoke sessions on:
- password reset;
- password change, except optionally current session;
- account suspension;
- security-sensitive email change;
- explicit “log out all devices”.
Avoid hard-binding sessions to exact IP because mobile networks change addresses. A salted IP-prefix hash may be used as a risk signal, not an unconditional lock.
## 13.4 Password reset
- Always show the same response whether email exists or not.
- Generate at least 32 random bytes.
- Store `hash('sha256', token)`, never the raw token.
- Token expires in 30–60 minutes.
- Mark token used in the same transaction as password update.
- Revoke all active sessions.
- Queue security notification email.
## 13.5 Email change
Email changes should use a pending email and verification flow. Do not replace the current login email until the new address is verified.
---
# 14. Contest Management Specification
## 14.1 Contest creation validations
Required:
- unique title/slug;
- question;
- full rules;
- prize name;
- entry price in cents;
- valid guess range;
- opening and closing time;
- maximum entries per user;
- primary reference media;
- answer commitment before activation.
Validation examples:
```text
minimum_guess <= maximum_guess
opens_at < closes_at
max_entries_per_user between 1 and configured platform maximum
entry_fee_cents >= 0
prize_value_cents >= 0
```
## 14.2 Answer commitment
When an authorized admin enters the answer before activation:
1. Validate integer answer inside contest range.
2. Generate random salt.
3. Compute commitment:
```text
HMAC-SHA256(contest_id | answer | salt | question/media hash, APP_KEY-derived commitment key)
```
4. Encrypt the answer and salt with an authenticated encryption scheme.
5. Store encrypted values and commitment hash.
6. Mark `answer_locked_at` when activating.
7. Write audit event without plaintext answer in logs.
At judging:
1. Decrypt answer and salt.
2. Recompute commitment.
3. Compare with `hash_equals()`.
4. Abort winner selection if mismatch.
Do not send the plaintext answer to the browser before publication.
## 14.3 Activation checklist
Activation fails unless:
- status is `draft` or `upcoming` as allowed;
- answer commitment exists;
- primary media exists;
- media hashes are stored;
- open/close dates are valid;
- rules exist;
- terms linkage exists;
- entry fee is valid;
- no unresolved validation error exists;
- real-money gateway is configured when entry fee is greater than zero.
## 14.4 Automatic opening and closure
Cron service updates:
```text
upcoming + opens_at <= now -> active
active + closes_at <= now -> closed
```
Web entry and checkout services must independently check dates. Cron is a convenience, not the only enforcement mechanism.
## 14.5 Contest cancellation
Cancellation workflow:
- require reason;
- lock contest row;
- set cancelled state;
- reject new checkout and entry;
- identify paid orders and unused/used entitlements;
- create refund-review records or admin work items;
- prevent automatic deletion;
- notify affected clients;
- retain audit trail.
Never simply delete a contest that has orders or entries.
---
# 15. Entry Entitlement and Guess Submission
## 15.1 Why entitlements are required
An order can grant multiple entry rights, and KW redemption can grant additional rights. The system must distinguish:
- number purchased;
- number granted;
- number already consumed;
- source of each right;
- expiry/revocation state.
A single `entry_count_granted` field on an order cannot safely represent all these states.
## 15.2 Remaining entries query
```sql
SELECT COALESCE(SUM(quantity_total - quantity_used), 0) AS remaining
FROM entry_entitlements
WHERE user_id = :user_id
AND contest_id = :contest_id
AND status = 'available'
AND (expires_at IS NULL OR expires_at > UTC_TIMESTAMP());
```
## 15.3 Atomic guess submission algorithm
Inside one database transaction:
1. Lock the contest row with `FOR UPDATE` or obtain a consistent protected read.
2. Confirm active state and server time window.
3. Lock the user row; confirm active status.
4. Count existing valid entries for cap enforcement.
5. Validate guess range.
6. If duplicate guesses are disabled, check duplicate under the same transaction.
7. Select the oldest available entitlement with remaining quantity:
```sql
SELECT *
FROM entry_entitlements
WHERE user_id = :user_id
AND contest_id = :contest_id
AND status = 'available'
AND quantity_used < quantity_total
AND (expires_at IS NULL OR expires_at > UTC_TIMESTAMP())
ORDER BY granted_at ASC, id ASC
LIMIT 1
FOR UPDATE;
```
8. Insert contest entry.
9. Increment `quantity_used` by one.
10. If `quantity_used == quantity_total`, set entitlement `consumed`.
11. Commit.
12. Return entry receipt.
The service must reject double-click duplicates through an idempotency/request token when practical.
## 15.4 Guess privacy
Clients may see only their own guesses before result publication. Public pages must not show a live guess distribution because it can influence later entrants unless the approved contest rules intentionally allow it.
## 15.5 Entry receipt
Display:
- contest title;
- opaque entry reference;
- submitted guess;
- submitted date/time in SAST;
- entry status;
- number of remaining entitlements.
Do not expose entitlement database IDs or other users’ entries.
---
# 16. Payment Architecture
## 16.1 Gateway abstraction
Define:
```php
interface PaymentGatewayInterface
{
public function createCheckout(Order $order, User $user, Contest $contest): CheckoutRedirect;
public function verifyWebhook(WebhookRequest $request): VerifiedGatewayEvent;
public function queryTransaction(string $gatewayTransactionId): GatewayTransactionStatus;
}
```
Implement:
- `PayFastGateway`
- `YocoGateway`
The order service must not contain gateway-specific signature code.
## 16.2 Order creation algorithm
Inside a transaction:
1. Validate authenticated client and active contest.
2. Validate quantity against allowed purchase options and per-user cap.
3. Count existing entries plus available entitlements plus requested quantity.
4. Derive unit price from database.
5. Calculate expected amount in integer cents using checked arithmetic.
6. Create pending order with:
- UUID public ID;
- unique merchant reference;
- idempotency key;
- expiry time;
- sandbox/live mode.
7. Commit.
8. Call gateway checkout creation.
9. Redirect client.
If gateway checkout creation fails, mark order failed or leave pending for safe retry according to the adapter contract. Do not grant entitlements.
## 16.3 Webhook/ITN idempotency
On callback:
1. Read exact raw body and relevant headers.
2. Enforce payload size limit.
3. Parse according to gateway content type.
4. Derive a stable gateway event ID. When the gateway provides none, create a deterministic hash from trusted event fields and payload.
5. Insert payment event with unique gateway/event key.
6. If duplicate key, return the appropriate 2xx response without re-granting.
7. Verify signature/origin using the current official gateway documentation.
8. Resolve order using merchant reference and lock it `FOR UPDATE`.
9. Validate:
- gateway matches order;
- mode matches;
- merchant account matches;
- currency is ZAR;
- paid amount exactly matches expected amount, unless approved partial-payment logic exists;
- transaction ID is not attached to another order;
- event status represents successful payment.
10. If order already paid, mark event processed/ignored and return 2xx.
11. Update order to paid.
12. Insert one entitlement whose quantity equals paid order quantity.
13. Queue receipt email.
14. Insert in-app notification.
15. Mark event processed.
16. Commit.
17. Return gateway-compatible success quickly.
Do not send SMTP mail while holding the payment transaction.
## 16.4 PayFast requirements
At implementation time, use the current official PayFast developer documentation. Do not copy an oversimplified signature snippet from a blog.
The adapter must implement all checks required by PayFast, including the current equivalents of:
- parameter normalization and signature verification;
- merchant validation;
- amount validation;
- server-side validation/confirmation where required;
- trusted-origin validation as documented;
- passphrase handling;
- sandbox/live endpoint separation;
- replay/idempotency protection.
Store `m_payment_id` or its current equivalent as the merchant order reference. The browser return URL remains informational only.
Official developer portal: `https://developers.payfast.co.za/`
## 16.5 Yoco requirements
The Yoco webhook endpoint must:
- accept HTTPS POST;
- use the raw request body for verification when required;
- verify event origin with the webhook secret and current Yoco algorithm;
- store the webhook event identifier;
- filter the event to the expected checkout/order reference;
- return a 2xx response promptly after durable processing;
- tolerate retries.
Yoco’s documentation states that webhook events may be retried when a 2xx response is not received, which makes idempotent event storage mandatory.
Official developer portal: `https://developer.yoco.com/`
## 16.6 Reconciliation
Admin reconciliation page must show:
- internal order reference;
- gateway;
- gateway transaction ID;
- expected amount;
- received amount;
- order state;
- event verification flags;
- entitlement granted;
- mismatch reason;
- timestamps;
- manual actions with audit log.
Manual “mark paid” must be disabled by default. If enabled, it must require:
- super-admin permission or equivalent;
- entered gateway reference;
- reason;
- second confirmation;
- audit record;
- unique idempotency key;
- entitlement creation through the same paid-order service.
## 16.7 Refunds
Refund processing must not delete entries or orders. Record refund amounts and state. Business/legal policy must define whether consumed entries remain valid, are voided, or trigger reselection. The software must not guess this policy.
---
# 17. KW Ledger and Daily Streaks
## 17.1 Ledger invariant
For every KW transaction:
```text
credit: balance_after = balance_before + amount
debit: balance_after = balance_before - amount
```
`users.kw_balance` is a cached current balance. `kw_transactions` is the immutable source of history.
Every balance change must:
1. lock user row;
2. calculate new balance;
3. reject negative balance;
4. update cached balance;
5. insert ledger row with before and after balances;
6. use a unique idempotency key;
7. commit atomically.
Never update `users.kw_balance` directly from a controller.
## 17.2 Daily streak date handling
The streak day is based on `Africa/Johannesburg`, not database UTC date.
```php
$zone = new DateTimeZone('Africa/Johannesburg');
$today = new DateTimeImmutable('today', $zone);
```
Store `last_streak_local_date` as the Johannesburg calendar date.
## 17.3 Claim algorithm
In a transaction:
1. Lock user row.
2. Determine current Johannesburg date.
3. If last claim date equals today, return already claimed without ledger insert.
4. If last claim date equals yesterday, increment streak.
5. Otherwise reset streak to 1.
6. Calculate base reward from settings.
7. Calculate milestone bonus from configured schedule.
8. Insert one or two ledger entries with deterministic idempotency keys such as:
```text
streak:{user_id}:{YYYY-MM-DD}:base
streak:{user_id}:{YYYY-MM-DD}:bonus
```
9. Update user balance, streak count, and last claim date.
10. Commit.
Default schedule:
| Day | Base | Bonus | Total |
|---:|---:|---:|---:|
| 1–6 | 5 | 0 | 5 |
| 7 | 5 | 10 | 15 |
| 14 | 5 | 15 | 20 |
| 21 | 5 | 20 | 25 |
| 30 | 5 | 30 | 35 |
After day 30, choose and document one policy:
- continue count and repeat milestones every 30 days;
- cap at 30;
- reset to 1 after the day-30 claim.
Recommended default: continue the streak count, with a 30-day cycle calculated separately. Do not silently overflow or reset.
## 17.4 Client KW page
Queries only `kw_transactions` for the authenticated user.
Columns:
- date/time;
- description;
- type;
- signed amount;
- balance after.
Example:
```text
4 Jul 2026, 08:14 | Daily streak — Day 7 | +5 KW | 140 KW
4 Jul 2026, 08:14 | Day 7 milestone bonus | +10 KW | 150 KW
```
No ZAR amounts appear in this table.
## 17.5 Admin KW adjustment
Required fields:
- credit or debit;
- positive amount;
- reason, minimum meaningful length;
- optional reference.
Rules:
- cannot debit below zero;
- requires recent password confirmation for high-risk adjustments;
- writes audit log;
- writes ledger entry with admin as `created_by`;
- sends client notification where policy requires.
---
# 18. Redemption Specification
## 18.1 Entry redemption
Inside one transaction:
1. Lock active reward row.
2. Validate reward is active and is `contest_entry`.
3. Lock contest and confirm it can accept redeemed entries.
4. Lock user.
5. Check per-user reward limits.
6. Debit KW through ledger service.
7. Create redemption.
8. Create entitlement with source `kw_redemption`.
9. Mark redemption completed and link entitlement.
10. Commit.
No placeholder contest entry is inserted. The entitlement is consumed only when the client submits a guess.
## 18.2 Voucher redemption
Inside one transaction:
1. Validate reward availability and limits.
2. Lock user.
3. Debit KW.
4. Create pending redemption.
5. Queue admin notification.
6. Commit.
Admin fulfilment requires:
- voucher/reference code;
- optional expiry date in notes or structured field;
- status change to completed;
- client email and in-app notification;
- audit event.
## 18.3 Failed redemption reversal
A failed/cancelled redemption that qualifies for reversal must create a new credit ledger transaction. Never delete the original debit.
Idempotency key example:
```text
redemption-reversal:{redemption_id}
```
---
# 19. Support Ticket Specification
## 19.1 Ticket creation
In one transaction:
1. Lock user row or use a suitable user-scoped advisory strategy.
2. Search for existing `pending` or `in_progress` ticket using `FOR UPDATE`.
3. If found, reject.
4. Create ticket.
5. Create first public message.
6. Set `last_client_message_at`.
7. Queue acknowledgement email.
8. Commit.
The public form must show:
```text
You already have an open request. Continue the existing conversation before creating another request.
```
It may link directly to the active ticket.
## 19.2 Ticket messages
- Client sees only `visibility='public'`.
- Admin sees public and internal messages with clear visual distinction.
- Client cannot set visibility.
- Client cannot post to a closed ticket unless reopening is explicitly allowed.
- HTML is escaped; plain text line breaks may be rendered safely.
- Attachments are excluded from MVP unless a secure private file-download design is implemented.
## 19.3 Status updates
Admin status update must:
- validate transition;
- update timestamps;
- optionally add a public reply;
- optionally add an internal note;
- notify client for public-impacting changes;
- write audit event.
When status changes to `resolved` or `closed`, the client may create a new ticket.
---
# 20. Winner Selection and Result Publication
## 20.1 Selection algorithm
Service method contract:
```php
public function selectWinner(int $contestId, int $adminId): ContestWinner;
```
Algorithm in one transaction:
1. Lock contest row.
2. Confirm status is `closed` or authorized `judging`.
3. If winner already exists, return it.
4. Load and decrypt answer.
5. Recompute answer commitment and fail on mismatch.
6. Count valid entries.
7. If no entries, move to a documented no-winner state or leave closed with audit note.
8. Set contest to `judging`.
9. Find winner using deterministic ordering.
10. Calculate and persist distance for all entries, preferably in a bounded update or batch if volume is high.
11. Mark winner entry `winner` and other valid entries `non_winner`.
12. Insert `contest_winners` row.
13. Set contest winner entry and completed timestamp.
14. Set contest status `completed`.
15. Queue winner notification.
16. Write audit event containing algorithm version and selected IDs, not sensitive identity data.
17. Commit.
Winner query:
```sql
SELECT id, user_id, guess_value, submitted_at
FROM contest_entries
WHERE contest_id = :contest_id
AND status = 'valid'
ORDER BY
ABS(guess_value - :correct_answer) ASC,
submitted_at ASC,
id ASC
LIMIT 1
FOR UPDATE;
```
## 20.2 Large contest optimization
For large entry volumes, calculate distance in SQL and index the judging columns. Because `ABS(guess-answer)` cannot use a normal index effectively, contest-scoped row counts must be capacity-tested. Shared hosting is unsuitable for very high-volume simultaneous contests without architectural changes.
## 20.3 Result publication
Completion and public publication are separate.
Before publication:
- verify winner eligibility;
- obtain required identity/delivery details securely;
- obtain consent for public name/image;
- confirm no unresolved fraud review;
- reveal correct answer;
- show winning guess and distance;
- publish only consented display name, never ID number, address, phone, or email.
---
# 21. File Upload Security
Contest media upload pipeline:
1. Require admin authentication and CSRF.
2. Check PHP upload error.
3. Enforce configured byte limit.
4. Detect MIME using `finfo`, not filename extension.
5. Decode and re-encode images when practical.
6. Reject SVG for MVP unless sanitized with a proven library.
7. Generate random server filename.
8. Store outside public root.
9. Calculate SHA-256 hash.
10. Generate public derivative/copy with safe permissions.
11. Store metadata.
12. Never execute files in upload directories.
Apache rule for public media directory:
```apache
Options -Indexes
RemoveHandler .php .phtml .php3 .php4 .php5 .php7 .php8
RemoveType .php .phtml .php3 .php4 .php5 .php7 .php8
Require all denied
```
---
# 22. Security Requirements
## 22.1 SQL injection
- PDO prepared statements only.
- Dynamic sort columns must be selected from a hard-coded allow-list.
- Never bind a table or column identifier supplied by the user.
## 22.2 Cross-site scripting
- Escape all output with `htmlspecialchars(..., ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8')`.
- Avoid storing arbitrary HTML.
- Apply a strict Content Security Policy.
- Never place untrusted input into inline scripts.
## 22.3 CSRF
- One random session token or rotating token strategy.
- Validate with `hash_equals()`.
- Apply to every state-changing browser request, including logout.
- Do not apply session CSRF to payment webhooks.
## 22.4 Security headers
Recommended production headers:
```text
Content-Security-Policy: default-src 'self'; img-src 'self' data: https:; style-src 'self'; script-src 'self'; connect-src 'self'; frame-ancestors 'none'; base-uri 'self'; form-action 'self' approved-payment-domains
X-Content-Type-Options: nosniff
Referrer-Policy: strict-origin-when-cross-origin
Permissions-Policy: camera=(), microphone=(), geolocation=()
Cross-Origin-Opener-Policy: same-origin
X-Frame-Options: DENY
Strict-Transport-Security: max-age=31536000; includeSubDomains
```
Adjust `form-action` and redirect behavior to support the selected gateway safely.
## 22.5 Rate limits
At minimum:
| Action | Suggested limit |
|---|---:|
| Login | 5 failed attempts per account/IP window, progressive delay |
| Registration | 5 per IP per hour |
| Password reset | 3 per email/IP per hour |
| Verification resend | 3 per hour |
| Streak claim | 10 requests per minute, idempotent |
| Guess submit | 10 per minute per user plus contest cap |
| Ticket create | 3 attempts per hour plus open-ticket lock |
| Ticket message | 20 per hour |
| Checkout create | 10 per hour per user/contest |
These values are starting points and must be tuned.
## 22.6 Sensitive data encryption
Encrypt ID number and address at application level using a key derived from `APP_KEY` or a dedicated encryption key. Use authenticated encryption and key versioning.
Never display decrypted identity data in list pages. Require privileged detail view and log access where appropriate.
## 22.7 Logging privacy
Never log:
- passwords;
- reset/verification raw tokens;
- full ID numbers;
- full addresses;
- card/payment credentials;
- gateway secret keys;
- session cookies;
- CSRF tokens;
- SMTP passwords.
Hash IP addresses with a rotating or secret salt when exact IP retention is not required.
## 22.8 Error handling
Production:
```ini
display_errors=Off
log_errors=On
expose_php=Off
```
Client response:
```text
Something went wrong. Reference: REQUEST_ID
```
Full exception details go to protected logs only.
## 22.9 Admin protection
Recommended:
- mandatory MFA for admins in phase 2;
- recent-password confirmation for sensitive actions;
- shorter admin session timeout;
- alert on new admin login;
- IP allow-list optional, not assumed;
- no shared admin accounts;
- audit every privileged mutation.
## 22.10 Business-logic abuse protection
Protect against:
- replayed payment callback;
- duplicate event delivery;
- double-click KW debit;
- concurrent streak claims;
- concurrent redemption requests;
- concurrent guess submission using one entitlement;
- cap bypass through multiple pending checkouts;
- changing contest data after activation;
- winner reselection without trace;
- forged browser success return.
Database locks, unique keys, idempotency keys, and service-level invariants are mandatory.
---
# 23. `.htaccess` Specification
Example public root configuration; adjust to host capabilities:
```apache
Options -Indexes
DirectoryIndex index.php
RewriteEngine On
# Preserve ACME challenge if used by certificate tooling
RewriteRule ^\.well-known/acme-challenge/ - [L]
# Force HTTPS in production, accounting for trusted proxy only when configured
RewriteCond %{HTTPS} !=on
RewriteCond %{HTTP:X-Forwarded-Proto} !https
RewriteRule ^ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
# Block hidden and sensitive files
RewriteRule (^|/)\.(?!well-known/) - [F,L]
RewriteRule ^(?:composer\.(?:json|lock)|phpunit\.xml|README|CHANGELOG|database|storage|vendor|app|config|routes|tests)(?:/|$) - [F,L,NC]
RewriteRule \.(?:sql|log|env|ini|bak|dist|sh)$ - [F,L,NC]
# Existing files/directories pass through
RewriteCond %{REQUEST_FILENAME} -f [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^ - [L]
# Front controller
RewriteRule ^ index.php [QSA,L]
Header always set X-Content-Type-Options "nosniff"
Header always set X-Frame-Options "DENY"
Header always set Referrer-Policy "strict-origin-when-cross-origin"
Require all denied
```
Do not blindly trust `X-Forwarded-Proto`; configure it only when the host’s reverse proxy is known.
---
# 24. Email and Notification System
## 24.1 Queue design
Web requests enqueue mail. A cron worker sends it.
Benefits:
- payment webhook responds quickly;
- SMTP failure does not roll back payment;
- retries are controlled;
- failures are visible to admin.
## 24.2 Templates
Required templates:
- email verification;
- password reset;
- password changed;
- new login/security alert;
- payment received;
- payment failed where appropriate;
- entry submitted;
- streak reminder, optional;
- support ticket received;
- support admin response;
- redemption received;
- redemption completed;
- winner selected;
- winner verification instructions;
- contest cancellation;
- refund status.
Template variables must be allow-listed. Do not use `eval()` or arbitrary template execution.
## 24.3 Queue worker
Worker steps:
1. Acquire cron lock.
2. Reset stale `sending` rows after a safe timeout.
3. Select batch with `FOR UPDATE SKIP LOCKED` when supported; otherwise use a lock-token pattern.
4. Mark rows sending.
5. Commit selection.
6. Send each email outside long DB transaction.
7. Update sent or failed state.
8. Apply exponential backoff.
9. Update heartbeat.
10. Release lock.
Shared hosting batch size should start small, for example 20–50 emails per run.
---
# 25. Cron Jobs
cPanel cron commands must use absolute paths and the host’s PHP CLI path.
Example:
```cron
*/5 * * * * /usr/local/bin/php -d detect_unicode=0 /home/CPANEL_USER/vulaprize-app/cron/close-contests.php >> /home/CPANEL_USER/vulaprize-app/storage/logs/cron-close.log 2>&1
* * * * * /usr/local/bin/php /home/CPANEL_USER/vulaprize-app/cron/process-email-queue.php >> /home/CPANEL_USER/vulaprize-app/storage/logs/cron-email.log 2>&1
15 * * * * /usr/local/bin/php /home/CPANEL_USER/vulaprize-app/cron/cleanup-expired-tokens.php >> /home/CPANEL_USER/vulaprize-app/storage/logs/cron-cleanup.log 2>&1
0 */6 * * * /usr/local/bin/php /home/CPANEL_USER/vulaprize-app/cron/reconcile-pending-orders.php >> /home/CPANEL_USER/vulaprize-app/storage/logs/cron-reconcile.log 2>&1
30 2 * * * /usr/local/bin/php /home/CPANEL_USER/vulaprize-app/cron/maintenance.php >> /home/CPANEL_USER/vulaprize-app/storage/logs/cron-maintenance.log 2>&1
```
Use the actual CLI path shown by the hosting provider, often one of:
```text
/usr/local/bin/php
/usr/bin/php
/opt/cpanel/ea-php84/root/usr/bin/php
```
## 25.1 Cron overlap protection
Every job acquires a named lock with expiry. If lock exists and has not expired, exit successfully without running another copy.
Do not schedule jobs so frequently that one execution routinely overlaps the next.
## 25.2 Cron responsibilities
| Job | Frequency | Responsibility |
|---|---:|---|
| Contest lifecycle | Every 5 minutes | Open/close contests based on UTC timestamps |
| Email queue | Every minute | Send queued messages in bounded batches |
| Token cleanup | Hourly | Delete expired used tokens based on retention policy |
| Order reconciliation | Every 6 hours | Recheck selected pending/processing orders through supported gateway API |
| Entitlement expiry | Hourly | Expire rights tied to closed/cancelled contests |
| Log/data maintenance | Daily | Rotate logs, prune rate-limit data, update health metrics |
| Streak reminder | Daily | Queue reminder only for opted-in users |
| Backup verification | Daily/weekly | Confirm expected backup marker and alert admin |
---
# 26. Front-End and UX Specification
## 26.1 Principles
- Mobile-first responsive design.
- Server-rendered HTML remains functional without JavaScript for core flows.
- JavaScript enhances loading states, confirmations, and asynchronous polling.
- Accessible labels and errors.
- Keyboard navigation.
- Sufficient contrast.
- No deceptive urgency or misleading winner claims.
- Clear payment totals before redirect.
## 26.2 Client dashboard
Cards:
- KW balance;
- current streak;
- claim status;
- available entry entitlements;
- active contests;
- recent own entries;
- open support request;
- recent notifications.
Daily claim button states:
```text
Claim daily KW
Claimed today
Unavailable while account is inactive
```
## 26.3 Contest page
Display:
- title;
- primary image;
- challenge question;
- contest rules;
- prize information approved for public display;
- entry fee;
- open/close date in SAST;
- countdown driven by server-provided timestamp, not trusted for enforcement;
- user’s existing entry count;
- available entitlements;
- checkout options;
- guess form when eligible;
- result after publication.
Never include the hidden answer, answer salt, commit secret, other users’ guesses, or unverified winner data in HTML/JSON.
## 26.4 Checkout UX
Before gateway redirect:
```text
Contest
Quantity
Price per entry
Total in ZAR
Terms acknowledgement
Payment gateway
```
After return:
```text
Payment confirmation is being verified.
```
Poll internal order status with bounded backoff or provide a refresh link. Do not say “paid” solely because the browser returned from the gateway.
## 26.5 Admin dashboard
Metrics:
- active clients;
- pending verification users;
- active/upcoming contests;
- paid revenue for selected period;
- pending/failed payments;
- available/consumed entitlements;
- open tickets;
- pending redemptions;
- email failures;
- last cron heartbeats.
Admin lists require pagination, filters, date ranges, safe sort allow-lists, and CSV export only where authorized and privacy-compliant.
---
# 27. JSON/AJAX Response Contract
For enhanced UI endpoints:
Success:
```json
{
"ok": true,
"message": "Daily KW claimed.",
"data": {
"kw_balance": 150,
"streak_count": 7,
"claimed_local_date": "2026-07-04"
},
"request_id": "uuid"
}
```
Validation failure:
```json
{
"ok": false,
"message": "Please correct the highlighted fields.",
"errors": {
"guess_value": ["The guess must be between 0 and 10000."]
},
"request_id": "uuid"
}
```
Conflict/idempotent response:
```json
{
"ok": false,
"code": "ALREADY_CLAIMED",
"message": "You have already claimed today’s KW.",
"request_id": "uuid"
}
```
Never return stack traces or SQL messages.
---
# 28. Service and Repository Contracts
## 28.1 Services
Required services:
```text
AuthService
EmailVerificationService
PasswordResetService
SessionService
ContestService
ContestAnswerService
ContestLifecycleService
EntryService
EntitlementService
OrderService
PaymentEventService
PaymentReconciliationService
KwLedgerService
StreakService
RedemptionService
SupportService
WinnerSelectionService
NotificationService
EmailQueueService
UploadService
AuditService
SettingsService
CronLockService
```
## 28.2 Repositories
Repositories contain SQL and mapping only. They must not decide business policy.
```text
UserRepository
SessionRepository
ContestRepository
ContestSecretRepository
ContestMediaRepository
OrderRepository
PaymentEventRepository
EntitlementRepository
EntryRepository
WinnerRepository
KwTransactionRepository
RewardRepository
RedemptionRepository
TicketRepository
TicketMessageRepository
NotificationRepository
EmailQueueRepository
SettingsRepository
AuditRepository
CronRepository
```
## 28.3 Exceptions
Use domain-specific exceptions:
```text
AuthenticationException
AuthorizationException
ValidationException
ConflictException
ContestNotActiveException
ContestClosedException
NoEntitlementException
EntryLimitReachedException
DuplicateGuessException
InsufficientKwException
OpenTicketExistsException
PaymentVerificationException
PaymentAmountMismatchException
AnswerCommitmentMismatchException
WinnerAlreadySelectedException
```
Controllers map these to safe HTTP responses.
---
# 29. File-by-File Build Contract
## 29.1 Bootstrap/core
### `public_html/index.php`
- define start time;
- require Composer autoload using absolute path;
- bootstrap application;
- create request;
- dispatch router;
- send response;
- catch only final throwable boundary.
### `bootstrap/app.php`
- load environment;
- validate configuration;
- set timezone behavior;
- register error handler;
- create PDO;
- create service container;
- load routes;
- return application instance.
### `app/Core/Container.php`
- explicit factories or bindings;
- singleton PDO/config/logger per request;
- no hidden global service locator inside domain logic.
### `app/Core/Logger.php`
- JSON-lines structured logs;
- request ID;
- log levels;
- secret redaction;
- daily file rotation or date-specific file.
### `app/Core/Crypto.php`
- random tokens;
- hashing;
- encryption/decryption with versioned payload;
- key derivation;
- no custom cryptographic algorithms.
## 29.2 Middleware
### `AuthMiddleware`
- require login;
- load active user;
- validate session record.
### `RoleMiddleware`
- compare role against allowed list;
- return 403 or role-appropriate redirect.
### `CsrfMiddleware`
- validate only state-changing browser requests;
- use timing-safe comparison.
### `RateLimitMiddleware`
- route-specific keys;
- DB-backed or filesystem cache with atomicity;
- fail safe under storage errors for high-risk endpoints.
### `VerifiedEmailMiddleware`
- prevent payment/entry when email is not verified.
### `MaintenanceMiddleware`
- allow admins and webhook processing where configured;
- block normal clients with 503 and retry information.
## 29.3 Controllers
Controllers must be thin:
```text
request -> validation DTO -> service call -> view/redirect/JSON
```
No SQL, no manual balance update, no gateway signature logic, no winner algorithm in controllers.
## 29.4 Views
Shared partials:
```text
layouts/public.php
layouts/client.php
layouts/admin.php
partials/flash.php
partials/errors.php
partials/pagination.php
partials/status-badge.php
partials/csrf.php
```
Every form includes explicit action and method. Every destructive action has confirmation and cannot be a GET request.
---
# 30. Logging, Audit, and Monitoring
## 30.1 Application logs
Log fields:
```json
{
"timestamp": "UTC ISO-8601",
"level": "error",
"request_id": "uuid",
"route": "webhooks.yoco",
"user_id": null,
"event": "payment_webhook_failed",
"context": {
"gateway_event_id": "safe reference",
"exception_class": "..."
}
}
```
## 30.2 Audit events
Audit at minimum:
- admin login;
- user suspension/reactivation;
- contest creation/update;
- answer commitment;
- contest activation/extension/cancellation;
- payment manual reconciliation;
- KW adjustment;
- redemption completion/failure;
- ticket internal notes and status changes;
- winner selection/verification/void;
- settings changes;
- data export;
- sensitive profile access where required.
Audit rows are append-only. No normal admin interface may edit or delete them.
## 30.3 Health checks
Admin system page displays:
- PHP version;
- required extensions;
- DB connectivity;
- migration version;
- storage writability;
- HTTPS status;
- mail test status;
- gateway mode;
- cron heartbeats;
- failed email count;
- unprocessed payment event count;
- disk-space warning if available.
Do not expose this page publicly.
---
# 31. Data Retention, Privacy, and Account Lifecycle
The exact retention schedule must be approved legally. The system must support configurable retention and anonymization rather than indiscriminate deletion.
Capabilities:
- export a client’s account data;
- correct profile details;
- record consent versions;
- anonymize eligible inactive accounts;
- preserve financial/audit records when legally required;
- remove marketing consent independently of account status;
- restrict access to identity/delivery details;
- record winner-publication consent.
Soft deletion is preferred for users linked to financial or contest records. Replace personal display data only when policy permits, while retaining referential integrity.
---
# 32. Backup and Restore
## 32.1 Backup scope
Back up:
- database;
- `.env` through a secure secret-backup process;
- application code and `composer.lock`;
- contest media;
- private delivery/winner documents if used;
- email templates;
- configuration exports.
Do not rely only on the hosting provider’s unspecified backup.
## 32.2 Recommended schedule
- database: daily minimum; more frequent for active payment periods;
- files/media: daily incremental plus weekly full;
- off-host encrypted copy: weekly minimum;
- retention: daily, weekly, monthly tiers based on policy.
## 32.3 Restore test
At least quarterly:
1. restore into isolated non-production database;
2. verify row counts and foreign keys;
3. verify media hashes;
4. run smoke tests;
5. document recovery time and gaps;
6. destroy test copy securely.
A backup that has never been restored is not proven.
---
# 33. Performance and Shared-Hosting Limits
## 33.1 Pagination
All unbounded lists must paginate. Defaults:
```text
client lists: 20 rows
admin lists: 50 rows
maximum requested page size: 100 rows
```
## 33.2 Index usage
Use `EXPLAIN` for:
- active contest list;
- user KW ledger;
- admin order filters;
- payment-event lookup;
- available entitlement query;
- ticket lock query;
- winner selection query.
## 33.3 Caching
Cache only non-sensitive, public data such as active contest summaries and settings. Invalidate on admin updates. Do not cache hidden answers, client balances, or authorization decisions in public files.
## 33.4 Capacity warning
Traditional shared cPanel hosting is appropriate for an MVP and moderate traffic. It may not be appropriate for:
- thousands of simultaneous checkout callbacks;
- large real-time leaderboards;
- very high-volume entry submission near closing time;
- heavy media processing;
- long-running queue workers.
Load-test expected peak traffic before launch. Define migration triggers to VPS/cloud infrastructure.
---
# 34. Test Strategy
## 34.1 Unit tests
Test pure logic:
- streak date transitions;
- streak bonus schedule;
- KW credit/debit calculations;
- order amount calculation;
- contest status transition rules;
- answer commitment verification;
- winner tie-breaking;
- reward eligibility;
- validation rules;
- encryption round trip.
## 34.2 Integration tests
Use a test database and transactions/fixtures:
- registration and verification;
- login lockout;
- session revocation;
- contest activation validation;
- concurrent streak claims;
- concurrent KW redemptions;
- payment duplicate callback;
- amount mismatch;
- entitlement grant only once;
- guess consumes exactly one entitlement;
- duplicate guess policy;
- entry cap enforcement;
- ticket open-lock race;
- winner selection idempotency;
- client cannot query admin data;
- client KW page has no ZAR values.
## 34.3 Security tests
- SQL injection payloads;
- stored/reflected XSS;
- CSRF on every mutation;
- session fixation;
- role-forced browsing;
- IDOR using another user’s public ID;
- upload polyglot and fake MIME files;
- path traversal;
- webhook forged signature;
- webhook replay;
- host-header injection;
- password reset token reuse;
- timing-safe generic login/reset responses.
## 34.4 Payment test matrix
| Scenario | Expected result |
|---|---|
| Valid paid event | Order paid, one entitlement grant, receipt queued |
| Same event repeated | 2xx, no second grant |
| Different event ID for same transaction | No second grant due gateway transaction uniqueness/order state |
| Wrong amount | Event failed/review, no grant |
| Wrong merchant | Reject, no grant |
| Invalid signature | Reject/log, no grant |
| Browser return without webhook | Order remains pending |
| Webhook before browser return | Order paid normally |
| Webhook after client closes browser | Order paid normally |
| Out-of-order failed then paid | State machine applies only valid transition |
| Sandbox event in live order | Reject/review |
## 34.5 Contest test matrix
| Scenario | Expected result |
|---|---|
| Exact answer exists | Earliest exact valid entry wins |
| Equal distance above/below | Earliest valid timestamp wins |
| Same timestamp precision | Lowest entry ID wins |
| No entries | No winner; documented state |
| Answer commitment mismatch | Abort and alert; no winner |
| Selection called twice | Same winner returned |
| Entry submitted at closing boundary | Server-side UTC rule determines acceptance consistently |
| Suspended user | Entry rejected or reviewed according to policy |
## 34.6 Acceptance test: no ZAR in client KW ledger
Automated test must:
1. create a paid order with a recognizable amount;
2. create KW transactions;
3. authenticate as client;
4. request `/account/kw`;
5. assert response contains KW descriptions and signed KW values;
6. assert it does not contain order amount, `R`, `ZAR`, gateway transaction ID, or revenue labels, excluding unrelated static legal/footer text if necessary;
7. inspect controller query to ensure it does not join orders.
---
# 35. cPanel Installation Procedure
## 35.1 Prepare cPanel
1. Create domain/subdomain and enable AutoSSL.
2. Select PHP 8.4 in MultiPHP Manager where available.
3. Enable required extensions in Select PHP Version or ask host.
4. Create database and database user through MySQL Database Wizard.
5. Grant only required privileges to the application user.
6. Create SMTP mailbox/provider credentials.
7. Confirm PHP CLI path.
8. Confirm cron feature is enabled.
## 35.2 Upload application
Preferred:
- deploy Git repository into `/home/CPANEL_USER/vulaprize-app`;
- set domain document root to `/home/CPANEL_USER/public_html` or a dedicated public folder;
- install Composer dependencies with production flags.
Command:
```bash
cd /home/CPANEL_USER/vulaprize-app
composer install --no-dev --optimize-autoloader --classmap-authoritative
```
If cPanel Terminal is unavailable, run Composer locally with a compatible PHP version and upload `vendor/` with `composer.lock`.
## 35.3 Configure environment
```bash
cp .env.example .env
```
Fill secrets, set production mode, and ensure `.env` is not public.
Production baseline:
```dotenv
APP_ENV=production
APP_DEBUG=false
SESSION_SECURE_COOKIE=true
PAYMENT_GATEWAY_MODE=sandbox
REAL_MONEY_ENABLED=false
```
Do not enable live money until the launch gate is complete.
## 35.4 Import schema
Use phpMyAdmin or CLI:
```bash
mysql -u DB_USER -p DB_NAME < database/schema.sql
```
Run migrations in order when migration tooling is implemented. Record migration versions in a `schema_migrations` table.
## 35.5 Create admin
Run CLI installer:
```bash
php bin/create-admin.php --email="admin@example.co.za"
```
Prompt for password without echo where the shell permits. Delete/disable installer after creation.
## 35.6 Configure public entry point
`public_html/index.php` must require the application using a verified absolute path. Do not build the path from query input.
## 35.7 Configure cron
Add jobs through cPanel → Advanced → Cron Jobs using absolute paths. Start with sandbox and inspect cron logs/heartbeats.
## 35.8 Configure payment sandbox
- create sandbox credentials;
- configure return, cancel, and notify/webhook URLs;
- ensure callback URLs use HTTPS;
- test every payment matrix case;
- verify duplicate callback safety.
## 35.9 Production cutover
1. Back up staging.
2. Set `APP_DEBUG=false`.
3. Confirm supported PHP patch version.
4. Confirm SSL and HSTS readiness.
5. Confirm legal terms and privacy versions.
6. Confirm gateway live account and acceptable-use approval.
7. Set live gateway endpoints/secrets.
8. Perform a low-value controlled live transaction.
9. Reconcile gateway, order, event, entitlement, email.
10. Only then set `REAL_MONEY_ENABLED=true`.
---
# 36. Deployment Checklist
## Server
- [ ] PHP 8.3+; PHP 8.4 preferred
- [ ] Current security patch installed
- [ ] Required extensions enabled
- [ ] HTTPS valid
- [ ] HTTP redirected safely
- [ ] Directory listing disabled
- [ ] Sensitive files outside public root or denied
- [ ] Production errors hidden
- [ ] Storage writable but not world-writable
- [ ] Timezone strategy verified
## Database
- [ ] InnoDB and utf8mb4
- [ ] Dedicated least-privilege user
- [ ] Schema imported
- [ ] Foreign keys enabled
- [ ] Indexes reviewed with EXPLAIN
- [ ] Initial admin created securely
- [ ] Backups configured
- [ ] Restore test scheduled
## Security
- [ ] CSRF on browser mutations
- [ ] Output escaping
- [ ] Prepared statements
- [ ] Session regeneration
- [ ] Secure cookies
- [ ] Rate limits
- [ ] Password reset tokens hashed
- [ ] Admin actions audited
- [ ] Upload MIME validation
- [ ] CSP/security headers
- [ ] IDOR tests passed
- [ ] Secret-redaction tests passed
## Payments
- [ ] Sandbox tested
- [ ] Signature/origin verification uses current official docs
- [ ] Amount/currency/merchant verified
- [ ] Callback events idempotent
- [ ] Browser return never grants entries
- [ ] Duplicate event tests passed
- [ ] Entitlements granted exactly once
- [ ] Reconciliation page works
- [ ] Live payment feature flag remains off until approved
## Contest fairness
- [ ] Answer committed before activation
- [ ] Reference media hash locked
- [ ] Activation validation complete
- [ ] Entry cutoff enforced server-side
- [ ] Entitlement consumed atomically
- [ ] Winner algorithm deterministic
- [ ] Winner selection idempotent
- [ ] Reselection protected and audited
## Privacy
- [ ] Client KW page contains no ZAR data
- [ ] Identity/address encrypted
- [ ] Internal notes hidden from clients
- [ ] Raw payment payload hidden from clients
- [ ] Consent versions captured
- [ ] Winner publication requires consent
- [ ] Retention schedule configured
## Operations
- [ ] Cron locks work
- [ ] Cron heartbeats visible
- [ ] Email queue retries work
- [ ] Failed email alert works
- [ ] Payment event failures alert admin
- [ ] Logs rotate
- [ ] Maintenance mode tested
- [ ] Backup and restore tested
---
# 37. Implementation Roadmap
## Phase 0 — Compliance and product freeze
Deliverables:
- approved contest rules;
- legal classification review;
- privacy/terms drafts;
- payment-provider approval;
- final reward rules;
- refund/cancellation policy;
- age and identity policy.
Exit criterion: no unresolved policy decision that changes database or payment behavior.
## Phase 1 — Foundation
Build:
- repository structure;
- Composer/autoload;
- environment validation;
- PDO and migrations;
- router/request/response;
- logging/error handling;
- views/layouts;
- CSRF and security headers.
Exit criterion: health page works in staging with no secrets exposed.
## Phase 2 — Authentication and roles
Build:
- registration;
- email verification;
- login/logout;
- password reset;
- session records/revocation;
- admin/client middleware;
- rate limiting;
- initial admin CLI.
Exit criterion: forced-browsing and session tests pass.
## Phase 3 — Contest administration
Build:
- contest CRUD;
- media uploads;
- answer commitment;
- lifecycle transitions;
- public contest views;
- cron opening/closure.
Exit criterion: active contest cannot be materially altered and answer remains hidden.
## Phase 4 — KW and streaks
Build:
- ledger service;
- client balance/history;
- daily claim;
- bonus schedule;
- admin adjustment;
- concurrency tests.
Exit criterion: ledger reconciles exactly to cached balance.
## Phase 5 — Payments and entitlements
Build:
- order service;
- PayFast adapter first;
- event store/idempotency;
- entitlement grant;
- return/cancel screens;
- reconciliation admin pages;
- email receipt queue.
Exit criterion: complete sandbox matrix and duplicate-event tests pass.
## Phase 6 — Entry submission
Build:
- entitlement display;
- guess validation;
- atomic consumption;
- own-entry history;
- entry caps;
- anti-automation/rate limits.
Exit criterion: concurrent submissions cannot consume one right twice.
## Phase 7 — Winner selection
Build:
- commitment verification;
- deterministic algorithm;
- result records;
- winner verification workflow;
- publication consent;
- notifications.
Exit criterion: fixed test fixtures always select expected winner.
## Phase 8 — Redemptions
Build:
- reward catalogue;
- entry redemption;
- voucher queue;
- completion/failure/reversal;
- admin pages.
Exit criterion: KW and entitlement states remain consistent under retries.
## Phase 9 — Request Hub
Build:
- ticket lock;
- conversation messages;
- internal notes;
- statuses;
- admin assignment;
- email notifications.
Exit criterion: UI and server both block a second open ticket, including concurrent requests.
## Phase 10 — Operations and hardening
Build:
- email worker;
- cron locks/heartbeats;
- backup procedures;
- audit search;
- monitoring;
- security test suite;
- performance tests;
- production runbook.
Exit criterion: go-live checklist signed off.
---
# 38. Definition of Done
A feature is not complete until:
- code follows architecture;
- validation exists;
- authorization exists;
- CSRF/rate limit applies where relevant;
- transaction boundaries are correct;
- idempotency is implemented where relevant;
- audit event exists for privileged changes;
- user-safe errors exist;
- automated tests pass;
- logs contain request ID and no secrets;
- mobile and keyboard UX is tested;
- database indexes are reviewed;
- documentation is updated;
- rollback behavior is understood.
The complete system is not production-ready until all deployment, payment, fairness, privacy, and operational checklists are complete.
---
# 39. Master Prompt for an AI Coding Agent
Copy the section below together with this entire file:
```text
You are implementing the VulaPrize + VulaPulse PHP application from the attached master specification.
Treat the specification as binding. Build production-quality PHP for cPanel shared hosting using PHP 8.3+ and MySQL/MariaDB. Use PSR-4 autoloading, PDO prepared statements, a front controller, explicit routing, middleware, thin controllers, service-layer business logic, repositories, transactions, CSRF protection, secure sessions, output escaping, structured logs, and immutable audit records.
Do not generate the entire project as one unreviewable response. Work phase by phase and preserve a file manifest. For each phase:
1. list the files to create or change;
2. state the invariants implemented;
3. provide complete file contents, not fragments;
4. provide the migration/schema changes;
5. provide tests;
6. provide cPanel installation or verification steps;
7. run a consistency review against the specification before moving on.
Critical rules:
- Client and admin authorization must be enforced in middleware and services.
- A client cannot create another ticket while one is pending or in_progress.
- The client KW ledger must never expose ZAR amounts or order data.
- KW cannot go negative and every change must create an immutable transaction row.
- Daily streak claims must be concurrency-safe and based on Africa/Johannesburg calendar dates.
- Browser payment returns never grant entries.
- Only verified, idempotently processed gateway callbacks grant entry entitlements.
- Paid and KW-granted entry rights must use entry_entitlements.
- Guess submission consumes exactly one entitlement atomically.
- Contest answers are committed and locked before activation.
- Winner selection is deterministic: smallest absolute distance, earliest submitted_at, lowest entry ID.
- Winner selection must be idempotent and audited.
- Payment, KW, entry, winner, and ticket-lock operations require database transactions and row locking where specified.
- Never hard-code real credentials.
- Never expose raw exceptions in production.
- Do not create public installers or default admin credentials.
Begin with Phase 1 Foundation. Before writing code, output the final directory tree and a dependency/configuration checklist. Then generate complete files for that phase only.
```
## 39.1 Phase-specific continuation prompt
```text
Continue with the next implementation phase from the VulaPrize + VulaPulse master specification. First inspect the existing file manifest and do not overwrite working code with incompatible interfaces. Provide complete changed files, migration steps, tests, and a phase acceptance checklist. Resolve any inconsistency against the master specification in favor of security, transaction integrity, role isolation, and idempotency.
```
## 39.2 Code-review prompt
```text
Audit the supplied VulaPrize + VulaPulse PHP project against the master specification. Do not rewrite immediately. Produce a severity-ranked defect report covering authorization, payment verification, idempotency, race conditions, database transactions, KW ledger integrity, entry entitlement consumption, answer commitment, winner selection, ticket lock, client ZAR leakage, CSRF, XSS, SQL injection, session security, uploads, logging, cron overlap, and cPanel deployment. For each defect, cite the exact file/function and provide a concrete remediation plan. Then patch critical and high-severity defects with complete changed files and tests.
```
---
# 40. Source and Verification Notes
This specification is based on the supplied VulaPrize + VulaPulse blueprint and expands it into a production-oriented PHP/cPanel architecture.
Technical references to verify during implementation:
- PHP supported versions: `https://www.php.net/supported-versions.php`
- PHP security documentation: `https://www.php.net/manual/en/security.php`
- cPanel Cron Jobs: `https://docs.cpanel.net/cpanel/advanced/cron-jobs/`
- PayFast developer documentation: `https://developers.payfast.co.za/`
- Yoco developer documentation: `https://developer.yoco.com/`
- PHPMailer: `https://github.com/PHPMailer/PHPMailer`
- OWASP ASVS: `https://owasp.org/www-project-application-security-verification-standard/`
- OWASP Cheat Sheet Series: `https://cheatsheetseries.owasp.org/`
Gateway signature, webhook-header, endpoint, and retry details can change. The implementation must be checked against the current official gateway documentation immediately before sandbox testing and again before live activation.
---
# 41. Final Instruction
Build the platform as a transactional system, not a collection of loosely connected PHP pages.
The core integrity chain is:
```text
verified user
-> valid order or KW redemption
-> immutable source record
-> entry entitlement
-> atomic guess submission
-> locked contest closure
-> verified committed answer
-> deterministic winner selection
-> auditable notification and fulfilment
```
Any implementation that bypasses this chain is incomplete and must not be deployed with real payments.