Skip to content

Database & Storage Architecture Guide

Comprehensive technical guide covering the SQLite database schema, PRAGMA configurations, repository patterns, self-healing mechanisms, and transaction safety in TidyFactor-Go.

1. Overview & Connection Architecture

TidyFactor-Go uses a single, zero-dependency SQLite 3 database engine (backend/cms-api/storage/database.sqlite), initialized via the SqliteConnection singleton (App\Repository\SqliteConnection).

┌─────────────────────────────────────────────────────────┐
│              SqliteConnection Singleton                 │
└───────────────────────────┬─────────────────────────────┘
                            │ PDO Connection Initialization
       ┌────────────────────┼────────────────────┐
       ▼                    ▼                    ▼
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│ PRAGMA       │     │ Self-Healing │     │ Repository   │
│ WAL Mode     │     │ Baseline     │     │ Interfaces   │
│ & 5s Timeout │     │ Copy         │     │ (PSR-4)      │
└──────────────┘     └──────────────┘     └──────────────┘

Connection Settings & PRAGMA Rules

  • Journal Mode: Defaults to WAL (Write-Ahead Logging) via PRAGMA journal_mode = WAL. Falls back to TRUNCATE or DELETE if server environment blocks WAL.
  • Synchronous Mode: PRAGMA synchronous = NORMAL in WAL mode for optimal performance without risk of corruption; FULL in traditional journal modes.
  • Busy Timeout: PRAGMA busy_timeout = 5000 (5-second timeout waiting for write locks).
  • Foreign Keys: PRAGMA foreign_keys = ON.

2. Comprehensive Table Schema Reference

The database contains 11 relational tables managed through SQL DDL statements in SqliteConnection::bootstrap().

A. Core Site Content (content Table)

Single-row table containing the unified 11-section JSON site content document.

CREATE TABLE IF NOT EXISTS content (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    data TEXT NOT NULL,
    updated_at TEXT NOT NULL
);

B. Site Backups (content_backups Table)

Stores complete JSON snapshots created manually or automatically on content save.

CREATE TABLE IF NOT EXISTS content_backups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    data TEXT NOT NULL,
    size INTEGER NOT NULL DEFAULT 0,
    created_at INTEGER NOT NULL
);

C. Contact Inquiries (inquiries Table)

Stores contact lead submissions (capped at 200 records FIFO).

CREATE TABLE IF NOT EXISTS inquiries (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT NOT NULL DEFAULT '',
    company TEXT NOT NULL DEFAULT '',
    subject TEXT NOT NULL,
    message TEXT NOT NULL,
    read INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL
);

D. Admin Audit Trail (changelog Table)

Tracks edit history (last 100 entries).

CREATE TABLE IF NOT EXISTS changelog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    editor TEXT NOT NULL,
    changed_sections TEXT NOT NULL,
    timestamp TEXT NOT NULL
);

E. Global Settings (config Table)

Key-value configuration store (SMTP, brand settings, API keys).

CREATE TABLE IF NOT EXISTS config (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

F. User Credentials (users Table)

Stores admin username and bcrypt password hash.

CREATE TABLE IF NOT EXISTS users (
    username TEXT PRIMARY KEY,
    password_hash TEXT NOT NULL
);

G. Rate Limiter (login_attempts Table)

Tracks IP login failures (max 5 per 15 mins).

CREATE TABLE IF NOT EXISTS login_attempts (
    ip TEXT PRIMARY KEY,
    attempts INTEGER DEFAULT 0,
    last_attempt INTEGER NOT NULL
);

H. Content Engine Tables

  • content_items: Articles and publications (id, slug, type, title, summary, body, cover_image, video_url, gallery_images, takeaways, category, author_name, author_avatar, author_role, author_bio, is_featured, is_sticky, status, reading_time_min, published_at, created_at, updated_at).
  • content_categories: Taxonomies (slug, name_en, name_ar, description).
  • content_types: Format types (slug, name_en, name_ar, description).
  • content_tags: Tag mappings (item_id, tag).

3. Self-Healing & Integrity Routines

Baseline Database Self-Healing

If database.sqlite is missing or uninitialized, both SqliteConnection.php and install/index.php automatically copy default seed data from database-baseline.sqlite to guarantee an out-of-the-box functioning schema.

Automatic Disk Vacuuming

The Admin Diagnostics panel includes a SQLite PRAGMA integrity check and vacuum command (PRAGMA integrity_check, VACUUM), which calculates disk storage delta savings before and after execution.