Backend Setup & MySQL Schema

Environment File

/home/usrquanainsights/htdocs/quanainsights.com/private/sandbox/hoomans/.env.production

-- HOOMANS - Pet Registration & Tracking System
-- MySQL 8.0+ / utf8mb4 / InnoDB

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(32) NOT NULL,
    country_code VARCHAR(8) NOT NULL DEFAULT '+91',
    avatar_path VARCHAR(500) NULL,
    fcm_token VARCHAR(500) NULL,
    status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
    phone_verified_at DATETIME NULL,
    last_login_at DATETIME NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_uuid (uuid),
    UNIQUE KEY uq_users_phone (phone),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_status_deleted (status, deleted_at),
    KEY idx_users_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    full_name VARCHAR(160) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('super_admin','admin','support') NOT NULL DEFAULT 'admin',
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_admins_uuid (uuid),
    UNIQUE KEY uq_admins_email (email),
    KEY idx_admins_role_status (role, status),
    KEY idx_admins_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    species ENUM('dog','cat','bird','rabbit','other') NOT NULL DEFAULT 'dog',
    breed VARCHAR(160) NULL,
    age_label VARCHAR(80) NULL,
    gender ENUM('male','female','unknown') NOT NULL DEFAULT 'unknown',
    color VARCHAR(120) NULL,
    temperament VARCHAR(500) NULL,
    medical_notes TEXT NULL,
    public_notes TEXT NULL,
    photo_path VARCHAR(500) NULL,
    status ENUM('active','missing','found','archived') NOT NULL DEFAULT 'active',
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pets_uuid (uuid),
    KEY idx_pets_user (user_id),
    KEY idx_pets_status_deleted (status, deleted_at),
    KEY idx_pets_species (species),
    CONSTRAINT fk_pets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qr_codes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    pet_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    qr_image_path VARCHAR(500) NULL,
    scan_url VARCHAR(700) NOT NULL,
    status ENUM('pending_payment','active','inactive','lost','replaced') NOT NULL DEFAULT 'pending_payment',
    activated_at DATETIME NULL,
    deactivated_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_qr_codes_uuid (uuid),
    UNIQUE KEY uq_qr_codes_code (code),
    KEY idx_qr_codes_pet (pet_id),
    KEY idx_qr_codes_status_deleted (status, deleted_at),
    CONSTRAINT fk_qr_codes_pet FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    qr_code_id BIGINT UNSIGNED NOT NULL,
    pet_id BIGINT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    latitude DECIMAL(10, 7) NULL,
    longitude DECIMAL(10, 7) NULL,
    accuracy_meters INT UNSIGNED NULL,
    city VARCHAR(120) NULL,
    region VARCHAR(120) NULL,
    country VARCHAR(120) NULL,
    finder_name VARCHAR(160) NULL,
    finder_phone VARCHAR(32) NULL,
    finder_message TEXT NULL,
    location_permission TINYINT(1) NOT NULL DEFAULT 0,
    owner_notified_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_scans_uuid (uuid),
    KEY idx_scans_qr_created (qr_code_id, created_at),
    KEY idx_scans_pet_created (pet_id, created_at),
    KEY idx_scans_location (latitude, longitude),
    CONSTRAINT fk_scans_qr_code FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE CASCADE,
    CONSTRAINT fk_scans_pet FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    pet_id BIGINT UNSIGNED NULL,
    qr_code_id BIGINT UNSIGNED NULL,
    razorpay_order_id VARCHAR(190) NOT NULL,
    razorpay_payment_id VARCHAR(190) NULL,
    razorpay_signature VARCHAR(255) NULL,
    amount_paise INT UNSIGNED NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'INR',
    status ENUM('created','paid','failed','refunded','cancelled') NOT NULL DEFAULT 'created',
    paid_at DATETIME NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_payments_uuid (uuid),
    UNIQUE KEY uq_payments_order (razorpay_order_id),
    KEY idx_payments_user (user_id),
    KEY idx_payments_pet (pet_id),
    KEY idx_payments_qr (qr_code_id),
    KEY idx_payments_status_created (status, created_at),
    CONSTRAINT fk_payments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_payments_pet FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE SET NULL,
    CONSTRAINT fk_payments_qr FOREIGN KEY (qr_code_id) REFERENCES qr_codes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    pet_id BIGINT UNSIGNED NULL,
    scan_id BIGINT UNSIGNED NULL,
    type ENUM('qr_scanned','payment_success','payment_failed','pet_missing','pet_found','system') NOT NULL,
    channel ENUM('in_app','push','email','sms','whatsapp') NOT NULL DEFAULT 'in_app',
    title VARCHAR(180) NOT NULL,
    body TEXT NOT NULL,
    payload JSON NULL,
    status ENUM('queued','sent','failed','read') NOT NULL DEFAULT 'queued',
    sent_at DATETIME NULL,
    read_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_notifications_uuid (uuid),
    KEY idx_notifications_user_created (user_id, created_at),
    KEY idx_notifications_pet (pet_id),
    KEY idx_notifications_scan (scan_id),
    KEY idx_notifications_type_status (type, status),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_notifications_pet FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE SET NULL,
    CONSTRAINT fk_notifications_scan FOREIGN KEY (scan_id) REFERENCES scans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_descriptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    pet_id BIGINT UNSIGNED NOT NULL,
    provider ENUM('openai','grok','manual') NOT NULL DEFAULT 'openai',
    model VARCHAR(80) NULL,
    prompt TEXT NOT NULL,
    description TEXT NOT NULL,
    safety_advice TEXT NULL,
    structured_output JSON NULL,
    status ENUM('draft','approved','rejected','archived') NOT NULL DEFAULT 'draft',
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ai_descriptions_uuid (uuid),
    KEY idx_ai_descriptions_pet_created (pet_id, created_at),
    KEY idx_ai_descriptions_status (status),
    KEY idx_ai_descriptions_approved_by (approved_by),
    CONSTRAINT fk_ai_descriptions_pet FOREIGN KEY (pet_id) REFERENCES pets(id) ON DELETE CASCADE,
    CONSTRAINT fk_ai_descriptions_admin FOREIGN KEY (approved_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;