Open-source software for Joomla, Gitea, and web platforms. Home of MokoSuite, MokoGitea, and MokoCLI.

Tennessee

architecture/joomla-database-conventions.-

Joomla Database Conventions

Database schema standards for MokoSuite extensions.

Table Naming

#__mokosuitebackup_profiles          # Backup profiles
#__mokosuitebackup_records           # Backup history records
#__mokosuitecross_posts              # Cross-post queue
#__mokosuitecross_services           # Configured services
#__mokosuitecross_templates          # Message templates
#__mokosuitecommunity_profiles       # User profiles
#__mokosuitecommunity_connections    # Friend connections
#__mokosuitecommunity_activities     # Activity stream
#__mokoog_tags                       # OG meta tag records

Pattern: #__{extensionname}_{entity} — the extension name has no separators, entity is plural.

Standard Columns

Every table should include these Joomla-standard columns where applicable:

Column	Type	Purpose
`id`	`INT UNSIGNED AUTO_INCREMENT`	Primary key
`published`	`TINYINT(1) DEFAULT 1`	Publication state (1=published, 0=unpublished, -2=trashed)
`ordering`	`INT DEFAULT 0`	Manual sort order
`checked_out`	`INT UNSIGNED DEFAULT NULL`	Record locking (user ID)
`checked_out_time`	`DATETIME DEFAULT NULL`	Record lock timestamp
`created_by`	`INT DEFAULT 0`	Creator user ID
`created_at`	`DATETIME DEFAULT CURRENT_TIMESTAMP`	Creation timestamp
`modified_by`	`INT DEFAULT 0`	Last modifier user ID
`modified_at`	`DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`	Last modified
`params`	`TEXT`	JSON configuration blob

SQL Install Script

sql/install.mysql.sql — creates all tables:

CREATE TABLE IF NOT EXISTS `#__mokosuitebackup_profiles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `description` TEXT,
    `backup_type` VARCHAR(50) NOT NULL DEFAULT 'full',
    `config` TEXT COMMENT 'JSON profile configuration',
    `filters` TEXT COMMENT 'JSON exclude filters',
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    `ordering` INT NOT NULL DEFAULT 0,
    `checked_out` INT UNSIGNED DEFAULT NULL,
    `checked_out_time` DATETIME DEFAULT NULL,
    `created_by` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modified_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuitebackup_profiles_published` (`published`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuitebackup_records` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `profile_id` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
    `backup_type` VARCHAR(50) NOT NULL DEFAULT 'full',
    `archive_path` VARCHAR(1024) DEFAULT NULL,
    `archive_size` BIGINT UNSIGNED DEFAULT 0,
    `db_size` BIGINT UNSIGNED DEFAULT 0,
    `files_count` INT UNSIGNED DEFAULT 0,
    `checksum` VARCHAR(64) DEFAULT NULL COMMENT 'SHA-256',
    `duration_seconds` INT UNSIGNED DEFAULT 0,
    `error_message` TEXT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `completed_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuitebackup_records_profile` (`profile_id`),
    KEY `idx_mokosuitebackup_records_status` (`status`),
    KEY `idx_mokosuitebackup_records_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

Update Scripts

Versioned migrations in sql/updates/mysql/{version}.sql:

-- sql/updates/mysql/01.01.00.sql
-- Add encryption support

ALTER TABLE `#__mokosuitebackup_profiles`
    ADD COLUMN `encryption_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `filters`,
    ADD COLUMN `encryption_key_hash` VARCHAR(128) DEFAULT NULL AFTER `encryption_enabled`;

ALTER TABLE `#__mokosuitebackup_records`
    ADD COLUMN `encrypted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `checksum`;

Rules for update scripts:

One file per version, named exactly matching the manifest version
Only contains the delta from the previous version
Use ALTER TABLE for schema changes
Use INSERT / UPDATE for data migrations
Never drop columns in minor versions (only major)
Always test upgrade path: fresh install AND upgrade from previous version

Composite Keys

For content-type lookups (like OG tags), use composite keys:

CREATE TABLE `#__mokoog_tags` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `content_type` VARCHAR(100) NOT NULL,
    `content_id` INT UNSIGNED NOT NULL,
    `og_title` VARCHAR(255) DEFAULT NULL,
    `og_description` TEXT,
    `og_image` VARCHAR(1024) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unq_mokoog_tags_content` (`content_type`, `content_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

Uninstall Script

sql/uninstall.mysql.sql — drops tables in reverse dependency order:

DROP TABLE IF EXISTS `#__mokosuitebackup_records`;
DROP TABLE IF EXISTS `#__mokosuitebackup_profiles`;

Pages