Database Setup

The login module works with three framework-level tables plus one custom table for each user level. The framework tables are created when you first install Trongate v2. The member table is specific to your application.

Framework Tables

These three tables are shared across all user levels:

trongate_user_levels

CREATE TABLE trongate_user_levels (
    id int(11) NOT NULL AUTO_INCREMENT,
    level_title varchar(125) DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO trongate_user_levels (id, level_title) VALUES
(1, 'Administrators'),
(2, 'Members');

Each row represents a distinct user level. The id values must match the keys in your config/login.php.

trongate_users

CREATE TABLE trongate_users (
    id int(11) NOT NULL AUTO_INCREMENT,
    code varchar(32) DEFAULT NULL,
    user_level_id int(11) DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

This is the central user registry. Every person with an account - whether administrator or member - has exactly one row in this table. The per-level tables (like members) reference this row via a foreign key column.

trongate_tokens

CREATE TABLE trongate_tokens (
    id int(11) NOT NULL AUTO_INCREMENT,
    token varchar(125) DEFAULT NULL,
    user_id int(11) DEFAULT 0,
    expiry_date int(11) DEFAULT NULL,
    code varchar(3) DEFAULT '0',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Tokens represent authenticated sessions. When a user logs in, the login module creates a token record. On each request, the module checks for a valid, unexpired token. When the user logs out or the token expires, the session ends.

The Members Table

This is the per-level table for member accounts. It stores profile data alongside the authentication fields.

CREATE TABLE members (
    id int(11) NOT NULL AUTO_INCREMENT,
    username varchar(55) DEFAULT NULL UNIQUE,
    email_address varchar(255) DEFAULT NULL,
    password varchar(255) NOT NULL,
    first_name text DEFAULT NULL,
    last_name text DEFAULT NULL,
    date_created int(11) NOT NULL,
    num_logins int(11) NOT NULL DEFAULT 0,
    last_login int(11) NOT NULL DEFAULT 0,
    trongate_user_id int(11) NOT NULL,
    code varchar(16) NOT NULL,
    confirmed tinyint(1) DEFAULT NULL,
    ip_address varchar(65) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Key columns explained:

The username column has a UNIQUE constraint to prevent duplicate display names. The email_address column is also unique in practice (enforced at the application level). You may add a unique index on email_address if your application requires it.

Feel free to add additional columns to the members table for your application's specific needs - a bio field, avatar path, preferences JSON, or anything else.

Creating the Tables

You may run the SQL above directly, or use the Db module's query methods within a migration or setup script. The login.sql file included in the companion GitHub repository contains all tables plus the default user level inserts, ready to import.