SWAMPy

✎ CARVE FLESH

EDITING: 001_foundation.sql

-- JILMEK API foundation migration. Run after database/schema.sql.
-- All timestamps are stored in UTC by the application/database session.

CREATE TABLE IF NOT EXISTS pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(80) NOT NULL UNIQUE,
    page_type VARCHAR(40) NOT NULL,
    status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    hero_title VARCHAR(220) NULL,
    hero_summary TEXT NULL,
    hero_primary_cta VARCHAR(120) NULL,
    hero_secondary_cta VARCHAR(120) NULL,
    body_sections JSON NULL,
    featured_media_id BIGINT UNSIGNED NULL,
    seo_title VARCHAR(220) NULL,
    seo_description VARCHAR(320) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS service_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    service_id BIGINT UNSIGNED NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    name VARCHAR(180) NOT NULL,
    short_description TEXT NULL,
    description MEDIUMTEXT NULL,
    media_id BIGINT UNSIGNED NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX(service_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS enquiry_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    enquiry_id BIGINT UNSIGNED NOT NULL,
    admin_user_id BIGINT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    INDEX(enquiry_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    expires_at DATETIME NOT NULL,
    last_used_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    user_agent_hash CHAR(64) NULL,
    ip_hash CHAR(64) NULL,
    INDEX(admin_user_id,revoked_at),
    INDEX(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CANCEL

Name	Type	Size	Modified	Actions
↩ ..	DIR	—	—
📄 001_foundation.sql	SQL	2.1 KB	2026-11-06 10:12	EDIT
📄 002_media_seed.sql	SQL	63.1 KB	2026-11-06 11:41	EDIT
📄 media-manifest.json	JSON	41.7 KB	2026-11-06 11:41	EDIT
📄 schema.sql	SQL	3.5 KB	2026-11-06 10:05	EDIT