Database Schema

FORGE generates a complete PostgreSQL database schema with UUID primary keys, proper indexing, soft deletes, and JSONB-based translations. This page serves as the full reference for every table, column, and relationship in a generated application.

Schema Overview

Every FORGE application ships with 19 tables organized into five categories:

+-------------------------------------------------------------------------+
|                       DATABASE ARCHITECTURE                             |
+-------------------------------------------------------------------------+
|                                                                         |
|  CORE (Authentication & Authorization)                                  |
|  users, roles, permissions, role_user, permission_role,                 |
|  password_resets, otp_codes                                             |
|                                                                         |
|  SYSTEM (Configuration & Auditing)                                      |
|  settings, languages, translations, api_keys, audit_logs, sessions      |
|                                                                         |
|  CONTENT (CMS & Navigation)                                             |
|  contents, menus                                                        |
|                                                                         |
|  STORAGE & REFERENCE                                                    |
|  media (polymorphic), lookups (hierarchical)                            |
|                                                                         |
|  PAYMENTS (when payment provider enabled)                               |
|  invoices, invoice_items                                                |
|                                                                         |
+-------------------------------------------------------------------------+

All tables follow these conventions

• UUID primary keys via gen_random_uuid()
• Timestamps created_at and updated_at on every table
• Soft deletes via a nullable deleted_at column where applicable
• JSONB columns for translations and flexible metadata

Entity Relationship Diagram

                           +-------------+
                           |    users    |
                           +------+------+
                                  |
           +----------------------+---------------------+
           |                      |                     |
           v                      v                     v
   +---------------+      +-------------+      +-----------------+
   |   role_user   |      | audit_logs  |      | password_resets |
   +-------+-------+      +-------------+      +-----------------+
           |
           v
   +---------------+      +--------------------+
   |    roles      |<-----| permission_role    |
   +---------------+      +----------+---------+
                                     |
                                     v
                           +-----------------+
                           |  permissions    |
                           +-----------------+

   +----------+  +----------+  +--------------+  +----------+
   | settings |  | languages|  | translations |  | api_keys |
   +----------+  +----------+  +--------------+  +----------+

   +----------+   +----------+   +----------+   +----------+
   | contents |   |  menus   |   |  media   |   | lookups  |
   +----------+   +----------+   +----------+   +----------+

Core Tables

users

Main user accounts table. Supports both email-based and mobile-based authentication, with soft deletes for safe account deactivation.

Column	Type	Constraints	Description
id	UUID	PK, default gen_random_uuid()	Unique identifier
email	VARCHAR(255)	UNIQUE, nullable	Email address
mobile	VARCHAR(20)	UNIQUE, nullable	Phone number
password	VARCHAR(255)	nullable	Argon2id hash
remember_token	VARCHAR(100)	nullable	Session persistence token
name	VARCHAR(255)	NOT NULL	Display name
avatar	VARCHAR(500)	nullable	Avatar URL or path
is_active	BOOLEAN	default true	Account enabled flag
email_verified_at	TIMESTAMP	nullable	Email verification timestamp
mobile_verified_at	TIMESTAMP	nullable	Mobile verification timestamp
last_login_at	TIMESTAMP	nullable	Last login timestamp
last_login_ip	VARCHAR(45)	nullable	IPv6-compatible IP
deleted_at	TIMESTAMP	nullable	Soft delete marker
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

-- At least one identifier is required
CONSTRAINT users_email_or_mobile CHECK (
    email IS NOT NULL OR mobile IS NOT NULL
);

-- Partial indexes (exclude soft-deleted rows)
CREATE INDEX idx_users_email ON users(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_mobile ON users(mobile) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_is_active ON users(is_active) WHERE deleted_at IS NULL;

WARNING

The email and mobile columns are both nullable individually, but the CHECK constraint ensures at least one is always present. Which field is required depends on the auth_method chosen during forge new.

roles

Defines user roles for the RBAC system. System roles (admin, user) cannot be deleted.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
name	VARCHAR(100)	NOT NULL, UNIQUE	Machine name (admin)
display_name	VARCHAR(255)	NOT NULL	Human label (Administrator)
description	TEXT	nullable	Role description
is_system	BOOLEAN	default false	Prevents deletion
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

CREATE INDEX idx_roles_name ON roles(name);
CREATE INDEX idx_roles_is_system ON roles(is_system);

permissions

Granular permission records grouped by resource.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
name	VARCHAR(100)	NOT NULL, UNIQUE	Dot-notation key (users.create)
display_name	VARCHAR(255)	NOT NULL	Human label (Create Users)
description	TEXT	nullable	Permission description
group_name	VARCHAR(100)	NOT NULL	Grouping key (users, roles)
is_system	BOOLEAN	default true	Prevents deletion
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

CREATE INDEX idx_permissions_name ON permissions(name);
CREATE INDEX idx_permissions_group ON permissions(group_name);

role_user (Pivot)

Many-to-many relationship between users and roles.

CREATE TABLE role_user (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id     UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT NOW(),
    assigned_by UUID REFERENCES users(id),

    PRIMARY KEY (user_id, role_id)
);

permission_role (Pivot)

Many-to-many relationship between permissions and roles.

CREATE TABLE permission_role (
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    role_id       UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,

    PRIMARY KEY (permission_id, role_id)
);

Auth Tables

password_resets

Stores hashed password-reset tokens with expiration.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
email	VARCHAR(255)	NOT NULL	Target email
token	VARCHAR(255)	NOT NULL	SHA-256 hashed token
expires_at	TIMESTAMP	NOT NULL	Expiration time
used_at	TIMESTAMP	nullable	When the token was consumed
created_at	TIMESTAMP	default NOW()	Created timestamp

otp_codes

One-time password codes for mobile authentication and verification flows.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
identifier	VARCHAR(255)	NOT NULL	Email or mobile number
code	VARCHAR(10)	NOT NULL	6-digit OTP code
type	VARCHAR(50)	NOT NULL	login, verify, or reset
attempts	INT	default 0	Failed verification count
max_attempts	INT	default 3	Maximum allowed attempts
expires_at	TIMESTAMP	NOT NULL	Expiration time
verified_at	TIMESTAMP	nullable	Successful verification time
created_at	TIMESTAMP	default NOW()	Created timestamp

CREATE INDEX idx_otp_codes_identifier ON otp_codes(identifier);
CREATE INDEX idx_otp_codes_expires ON otp_codes(expires_at);
CREATE INDEX idx_otp_codes_cleanup ON otp_codes(expires_at)
    WHERE verified_at IS NULL;

DANGER

OTP codes have a strict max_attempts limit. After 3 failed verifications the code is invalidated. Always create a new OTP after exhausting attempts -- never reset the counter.

System Tables

settings

Key-value application settings with type awareness and optional encryption.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
group_name	VARCHAR(100)	NOT NULL	Logical group (general, email, sms)
key	VARCHAR(255)	NOT NULL	Setting key (app_name)
value	TEXT	nullable	Plain text or encrypted value
type	VARCHAR(50)	default string	string, boolean, number, json, encrypted
is_public	BOOLEAN	default false	Exposed to frontend API
is_sensitive	BOOLEAN	default false	Masked in admin UI
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

UNIQUE (group_name, key)

Encrypted settings

When type = 'encrypted', values are stored as enc:v1:nonce:ciphertext:tag using AES-256-GCM. The encryption key is read from the FORGE_ENCRYPTION_KEY environment variable and is never stored in the database.

languages

Supported languages with RTL direction support.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
code	VARCHAR(10)	NOT NULL, UNIQUE	ISO code (en, ar)
name	VARCHAR(100)	NOT NULL	English name (Arabic)
native_name	VARCHAR(100)	nullable	Native name (العربية)
direction	VARCHAR(3)	default ltr	ltr or rtl
is_default	BOOLEAN	default false	Default language flag
is_active	BOOLEAN	default true	Availability flag
sort_order	INT	default 0	Display order
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

-- Only one default language allowed
CREATE UNIQUE INDEX idx_languages_default ON languages(is_default)
    WHERE is_default = true;

translations

UI translation strings stored as JSONB with all language values in a single row.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
group_name	VARCHAR(100)	NOT NULL	Logical group (common, auth, admin)
key	VARCHAR(255)	NOT NULL	Translation key (login_title)
translations	JSONB	NOT NULL	{"en": "Sign In", "ar": "تسجيل الدخول"}
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

UNIQUE (group_name, key);
CREATE INDEX idx_translations_values ON translations USING GIN (translations);

Default translation groups:

Group	Purpose	Example Keys
common	Shared UI strings	Save, Cancel, Delete, Confirm
auth	Authentication pages	login_title, register_title
admin	Admin dashboard	dashboard_title, sidebar labels
validation	Form messages	required, min_length
web.home	Web homepage	Welcome_to, App_Description
web.header	Site header	Sign_In, Get_Started
web.footer	Site footer	All_rights_reserved, Privacy

api_keys

Third-party API key management with rate limiting and revocation.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
name	VARCHAR(255)	NOT NULL	Descriptive name
key_hash	VARCHAR(255)	NOT NULL	SHA-256 hash of the key
key_prefix	VARCHAR(12)	NOT NULL	First 8 chars for identification
permissions	JSONB	default []	Allowed permission names
rate_limit	INT	default 1000	Max requests per hour
last_used_at	TIMESTAMP	nullable	Last usage timestamp
last_used_ip	VARCHAR(45)	nullable	Last usage IP
request_count	BIGINT	default 0	Total request count
expires_at	TIMESTAMP	nullable	Expiration (NULL = never)
revoked_at	TIMESTAMP	nullable	Revocation timestamp
revoked_by	UUID	FK to users	Who revoked the key
revoked_reason	TEXT	nullable	Revocation reason
created_by	UUID	NOT NULL, FK to users	Key creator
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

audit_logs

Immutable activity audit trail. Audit logs have no updated_at column -- they are append-only.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
user_id	UUID	FK to users, nullable	Acting user (NULL for system)
user_email	VARCHAR(255)	nullable	Denormalized email for history
api_key_id	UUID	FK to api_keys, nullable	If action was via API key
action	VARCHAR(50)	NOT NULL	created, updated, deleted, login
resource_type	VARCHAR(100)	NOT NULL	user, role, setting
resource_id	UUID	nullable	Affected resource ID
old_values	JSONB	nullable	Previous state
new_values	JSONB	nullable	New state
ip_address	VARCHAR(45)	nullable	Client IP
user_agent	TEXT	nullable	Client user agent
url	VARCHAR(500)	nullable	Request URL
created_at	TIMESTAMP	default NOW()	Event timestamp

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id);
CREATE INDEX idx_audit_logs_action ON audit_logs(action);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);
CREATE INDEX idx_audit_logs_filter ON audit_logs(resource_type, action, created_at DESC);

sessions (Optional)

User session tracking for session-based authentication scenarios.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
user_id	UUID	NOT NULL, FK to users	Session owner
token_hash	VARCHAR(255)	NOT NULL, UNIQUE	Hashed session token
ip_address	VARCHAR(45)	nullable	Client IP
user_agent	TEXT	nullable	Browser/client string
device_name	VARCHAR(255)	nullable	Friendly device name
last_activity	TIMESTAMP	default NOW()	Last request time
expires_at	TIMESTAMP	NOT NULL	Session expiration
revoked_at	TIMESTAMP	nullable	Manual revocation
created_at	TIMESTAMP	default NOW()	Created timestamp

Content Tables

contents

CMS content pages with full translation support, SEO metadata, and scheduled publishing.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
title	VARCHAR(255)	NOT NULL	Default language title
slug	VARCHAR(255)	NOT NULL, UNIQUE	URL-friendly identifier
summary	TEXT	nullable	Short description
details	TEXT	nullable	Rich HTML content
button_text	VARCHAR(100)	nullable	Optional CTA button
seo	JSONB	default {}	{title, description, keywords, robots, canonical}
translations	JSONB	default {}	Per-language overrides for all text fields
is_active	BOOLEAN	default true	Published flag
publish_at	TIMESTAMPTZ	nullable	Scheduled publish date
expire_at	TIMESTAMPTZ	nullable	Auto-unpublish date
sort_order	INT	default 0	Display ordering
created_by	UUID	FK to users	Author
updated_by	UUID	FK to users	Last editor
created_at	TIMESTAMPTZ	default NOW()	Created timestamp
updated_at	TIMESTAMPTZ	default NOW()	Updated timestamp
deleted_at	TIMESTAMPTZ	nullable	Soft delete marker

Translation JSONB structure:

{
  "ar": {
    "title": "من نحن",
    "summary": "نبذة عن الشركة",
    "details": "<p>محتوى الصفحة...</p>",
    "button_text": "اقرأ المزيد",
    "seo": {
      "title": "من نحن | الموقع",
      "description": "وصف الصفحة"
    }
  }
}

Featured images

Content images are not stored as a column on the contents table. Instead, they use the polymorphic media table with model_type='Content' and collection='featured_image'. Use MediaService.attach() and MediaService.get_first_for_model() to manage them.

menus

Navigation menu items with hierarchical structure (parent/child), visibility controls, and translations.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
title	VARCHAR(255)	NOT NULL	Default language title
url	VARCHAR(500)	nullable	Link URL
icon	VARCHAR(100)	nullable	Icon identifier
target	VARCHAR(20)	default _self	_self or _blank
visibility	VARCHAR(20)	default public	public, auth, or guest
translations	JSONB	default {}	{"ar": {"title": "الرئيسية"}}
parent_id	UUID	FK to menus(id), nullable	Parent menu item
is_active	BOOLEAN	default true	Active flag
sort_order	INT	default 0	Display ordering
created_at	TIMESTAMPTZ	default NOW()	Created timestamp
updated_at	TIMESTAMPTZ	default NOW()	Updated timestamp
deleted_at	TIMESTAMPTZ	nullable	Soft delete marker

Visibility options:

Value	Who can see it
public	Everyone (default)
auth	Logged-in users only
guest	Non-logged-in users only

Storage Table

media

Polymorphic file uploads supporting any model type. Follows a Spatie-style pattern where model_type + model_id + collection identify the owner.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
disk	VARCHAR(50)	default local	local or s3
path	VARCHAR(500)	NOT NULL	Relative file path
filename	VARCHAR(255)	NOT NULL	Original file name
mime_type	VARCHAR(100)	nullable	MIME type
size	BIGINT	nullable	File size in bytes
width	INT	nullable	Image width (pixels)
height	INT	nullable	Image height (pixels)
variants	JSONB	default {}	{"thumb": "path/thumb.jpg"}
alt_text	VARCHAR(500)	nullable	Accessibility text
caption	TEXT	nullable	Display caption
metadata	JSONB	default {}	EXIF data, custom metadata
model_type	VARCHAR(100)	nullable	Owner model (Content, User)
model_id	UUID	nullable	Owner record ID
collection	VARCHAR(100)	default default	featured_image, avatars
uploaded_by	UUID	FK to users	Uploader
created_at	TIMESTAMP	default NOW()	Created timestamp
updated_at	TIMESTAMP	default NOW()	Updated timestamp

-- Polymorphic lookup index
CREATE INDEX idx_media_polymorphic ON media(model_type, model_id, collection)
    WHERE model_type IS NOT NULL;

Example associations:

Content (id: abc-123)  <-- media WHERE model_type='Content',
                            model_id='abc-123', collection='featured_image'

User (id: def-456)     <-- media WHERE model_type='User',
                            model_id='def-456', collection='avatars'

Lookup Table

lookups

Configurable reference data for dropdowns, categories, and hierarchical classifications.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
type	VARCHAR(100)	NOT NULL	Lookup category (country, status)
parent_id	UUID	FK to lookups(id), nullable	Parent for hierarchy
name	VARCHAR(255)	NOT NULL	Default language name
value	VARCHAR(255)	nullable	Machine-readable value
translations	JSONB	default {}	{"ar": {"name": "الكويت"}}
is_active	BOOLEAN	default true	Active flag
sort_order	INT	default 0	Display ordering
metadata	JSONB	default {}	Extra data (e.g., country codes)
created_at	TIMESTAMPTZ	default NOW()	Created timestamp
updated_at	TIMESTAMPTZ	default NOW()	Updated timestamp

CREATE INDEX idx_lookups_type ON lookups(type) WHERE is_active = true;
CREATE INDEX idx_lookups_parent ON lookups(parent_id);

Use lookups for reusable reference data

Instead of creating a new table for every dropdown (countries, statuses, categories), store them all in lookups with different type values. The parent_id column enables hierarchical data like region > country > city.

Payment Tables

These tables are generated when a payment provider is enabled via forge provider:add payment stripe.

invoices

Invoice records for payment tracking with auto-generated invoice numbers.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
invoice_number	VARCHAR(50)	NOT NULL, UNIQUE	Auto-generated: INV-YYYYMM-XXXX
customer_id	UUID	FK to users, nullable	Customer account
customer_name	VARCHAR(255)	nullable	Customer name (denormalized)
customer_email	VARCHAR(255)	nullable	Customer email (denormalized)
subtotal	DECIMAL(12,2)	default 0	Sum of line items
tax_amount	DECIMAL(12,2)	default 0	Tax amount
discount_amount	DECIMAL(12,2)	default 0	Discount applied
total_amount	DECIMAL(12,2)	NOT NULL	Final amount
currency	VARCHAR(3)	default USD	ISO currency code
status	VARCHAR(20)	default pending	Payment status
payment_provider	VARCHAR(50)	nullable	stripe, hyperpay, etc.
payment_intent_id	VARCHAR(255)	nullable	Provider's payment ID
payment_method	VARCHAR(50)	nullable	card, apple_pay, etc.
card_last4	VARCHAR(4)	nullable	Last 4 digits
card_brand	VARCHAR(20)	nullable	visa, mastercard, etc.
description	TEXT	nullable	Invoice description
notes	TEXT	nullable	Internal notes
metadata	JSONB	default {}	Custom metadata
due_date	TIMESTAMPTZ	nullable	Payment due date
paid_at	TIMESTAMPTZ	nullable	Payment timestamp
refunded_at	TIMESTAMPTZ	nullable	Refund timestamp
created_at	TIMESTAMPTZ	default NOW()	Created timestamp
updated_at	TIMESTAMPTZ	default NOW()	Updated timestamp

-- Indexes
CREATE INDEX idx_invoices_customer ON invoices(customer_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_payment_intent ON invoices(payment_intent_id);
CREATE INDEX idx_invoices_number ON invoices(invoice_number);
CREATE INDEX idx_invoices_created ON invoices(created_at DESC);

-- Auto-generate invoice number trigger
CREATE OR REPLACE FUNCTION generate_invoice_number()
RETURNS TRIGGER AS $$
DECLARE
    year_month TEXT;
    seq_num INT;
BEGIN
    year_month := TO_CHAR(NOW(), 'YYYYMM');

    SELECT COALESCE(MAX(
        CAST(SUBSTRING(invoice_number FROM 'INV-' || year_month || '-(\d+)') AS INT)
    ), 0) + 1
    INTO seq_num
    FROM invoices
    WHERE invoice_number LIKE 'INV-' || year_month || '-%';

    NEW.invoice_number := 'INV-' || year_month || '-' || LPAD(seq_num::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_invoice_number
    BEFORE INSERT ON invoices
    FOR EACH ROW
    WHEN (NEW.invoice_number IS NULL)
    EXECUTE FUNCTION generate_invoice_number();

Invoice Status Flow:

pending → processing → paid → refunded
    ↓         ↓          ↓
 cancelled  failed   partially_refunded

Status	Description
pending	Invoice created, awaiting payment
processing	Payment in progress
paid	Payment successful
failed	Payment failed
cancelled	Invoice cancelled before payment
refunded	Full refund processed
partially_refunded	Partial refund processed

invoice_items

Line items for invoices with automatic total calculation.

Column	Type	Constraints	Description
id	UUID	PK	Unique identifier
invoice_id	UUID	NOT NULL, FK to invoices	Parent invoice
description	VARCHAR(500)	NOT NULL	Item description
quantity	INTEGER	default 1	Item quantity
unit_price	DECIMAL(12,2)	NOT NULL	Price per unit
amount	DECIMAL(12,2)	NOT NULL	quantity * unit_price
product_id	UUID	nullable	Optional product reference
product_type	VARCHAR(100)	nullable	Product model type
created_at	TIMESTAMPTZ	default NOW()	Created timestamp
updated_at	TIMESTAMPTZ	default NOW()	Updated timestamp

CREATE INDEX idx_invoice_items_invoice ON invoice_items(invoice_id);

-- Auto-calculate amount and update invoice totals
CREATE OR REPLACE FUNCTION update_invoice_totals()
RETURNS TRIGGER AS $$
BEGIN
    -- Calculate item amount
    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
        NEW.amount := NEW.quantity * NEW.unit_price;
    END IF;

    -- Update invoice subtotal and total
    UPDATE invoices
    SET subtotal = (
            SELECT COALESCE(SUM(amount), 0)
            FROM invoice_items
            WHERE invoice_id = COALESCE(NEW.invoice_id, OLD.invoice_id)
        ),
        total_amount = subtotal + tax_amount - discount_amount,
        updated_at = NOW()
    WHERE id = COALESCE(NEW.invoice_id, OLD.invoice_id);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate_invoice_item_amount
    BEFORE INSERT OR UPDATE ON invoice_items
    FOR EACH ROW
    EXECUTE FUNCTION update_invoice_totals();

CREATE TRIGGER recalculate_invoice_on_item_delete
    AFTER DELETE ON invoice_items
    FOR EACH ROW
    EXECUTE FUNCTION update_invoice_totals();

Cascading totals

When invoice items are added, updated, or deleted, triggers automatically recalculate the invoice's subtotal and total_amount. You don't need to manually update these values.

Index Strategy

FORGE applies several indexing patterns to optimize query performance:

Pattern	Example	Purpose
Partial indexes	WHERE deleted_at IS NULL	Skip soft-deleted rows
Composite indexes	(resource_type, action, created_at)	Multi-column filters
GIN indexes	USING GIN (translations)	JSONB containment queries
Unique partial	WHERE is_default = true	Enforce single default
Descending	created_at DESC	Newest-first ordering

JSONB Patterns

FORGE uses JSONB columns in three distinct patterns:

1. Inline Translations

Used by contents, menus, and lookups to store per-language text inline:

-- Get Arabic title
SELECT translations->'ar'->>'title' FROM contents WHERE slug = 'about';

2. Translation Bundles

Used by the translations table to store UI strings:

-- Get all Arabic auth translations
SELECT key, translations->>'ar' AS value
FROM translations
WHERE group_name = 'auth';

3. Structured Metadata

Used by contents.seo, media.metadata, and api_keys.permissions:

-- Find content with specific SEO robots setting
SELECT * FROM contents
WHERE seo->>'robots' = 'noindex';

Migration Order

Migrations run in dependency order. FORGE generates numbered migration files:

00001  create_languages_table
00002  create_users_table
00003  create_roles_table
00004  create_permissions_table
00005  create_role_user_table
00006  create_permission_role_table
00007  create_password_resets_table
00008  create_otp_codes_table
00009  create_settings_table
00010  create_translations_table
00011  create_api_keys_table
00012  create_audit_logs_table
00013  create_media_table
00014  create_contents_table
00015  create_menus_table
00016  migrate_media_polymorphic
00017  create_lookups_table
00018  create_invoices_table      (when payments enabled)
00019  create_invoice_items_table (when payments enabled)

After migrations, seeders populate default data in this order:

01  seed_permissions (all permission groups)
02  seed_roles (admin, user)
03  seed_users (admin@{app}.test, user@{app}.test)
04  seed_languages (en, ar)
05  seed_settings (general, auth, email, storage)
06  seed_translations (common, auth, admin, web.*)

Useful Queries

Get user with roles and permissions

SELECT
    u.id, u.email, u.name,
    array_agg(DISTINCT r.name) AS roles,
    array_agg(DISTINCT p.name) AS permissions
FROM users u
LEFT JOIN role_user ru ON u.id = ru.user_id
LEFT JOIN roles r ON ru.role_id = r.id
LEFT JOIN permission_role pr ON r.id = pr.role_id
LEFT JOIN permissions p ON pr.permission_id = p.id
WHERE u.id = $1
GROUP BY u.id, u.email, u.name;

Check if user has a specific permission

SELECT EXISTS (
    SELECT 1
    FROM users u
    JOIN role_user ru ON u.id = ru.user_id
    JOIN permission_role pr ON ru.role_id = pr.role_id
    JOIN permissions p ON pr.permission_id = p.id
    WHERE u.id = $1 AND p.name = $2
) AS has_permission;

Get all translations for a language

SELECT group_name, key, translations->>$1 AS value
FROM translations
WHERE translations ? $1;
-- $1 = language code, e.g. 'ar'

Audit log summary (last 7 days)

SELECT
    u.email, al.action, al.resource_type,
    COUNT(*) AS event_count
FROM audit_logs al
JOIN users u ON al.user_id = u.id
WHERE al.created_at > NOW() - INTERVAL '7 days'
GROUP BY u.email, al.action, al.resource_type
ORDER BY event_count DESC;

See Also

• Database -- Backend database layer and connection management
• Models -- Rust model definitions mapped to these tables
• Content Management -- How the CMS uses the contents table
• Multi-Language -- JSONB translation patterns in depth