Database Schema

Database Schema

Codex uses SQLite3 for data persistence with a normalized relational schema.

Core Tables

Users

Stores user account information and authentication data.

CREATE TABLE users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    image_path TEXT
);

Posts

Forum posts with content and metadata.

CREATE TABLE posts (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    channel_id TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    image_path TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (channel_id) REFERENCES channels(id)
);

Comments

Threaded comments on posts.

CREATE TABLE comments (
    id TEXT PRIMARY KEY,
    post_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Channels

Topic-based organization of posts.

CREATE TABLE channels (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    description TEXT,
    owner_id TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    image_path TEXT,
    is_private BOOLEAN DEFAULT 0,
    FOREIGN KEY (owner_id) REFERENCES users(id)
);

Reactions

Like/dislike functionality for posts and comments.

CREATE TABLE reactions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    target_id TEXT NOT NULL,
    target_type TEXT NOT NULL, -- 'post' or 'comment'
    reaction_type TEXT NOT NULL, -- 'like' or 'dislike'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE(user_id, target_id, target_type)
);

Relationship Tables

Memberships

User membership in channels.

CREATE TABLE memberships (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    channel_id TEXT NOT NULL,
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (channel_id) REFERENCES channels(id),
    UNIQUE(user_id, channel_id)
);

Moderators

Channel moderation assignments.

CREATE TABLE mods (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    channel_id TEXT NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (channel_id) REFERENCES channels(id),
    UNIQUE(user_id, channel_id)
);

Feature Tables

Flags

Content flagging for moderation.

CREATE TABLE flags (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    target_id TEXT NOT NULL,
    target_type TEXT NOT NULL,
    reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Saved Posts

User bookmarks.

CREATE TABLE saved (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    post_id TEXT NOT NULL,
    saved_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    UNIQUE(user_id, post_id)
);

Loyalty Points

User engagement tracking.

CREATE TABLE loyalty (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    points INTEGER DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Muted Channels

Hidden channels per user.

CREATE TABLE muted_channels (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    channel_id TEXT NOT NULL,
    muted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (channel_id) REFERENCES channels(id),
    UNIQUE(user_id, channel_id)
);

Channel Rules

Guidelines per channel.

CREATE TABLE rules (
    id TEXT PRIMARY KEY,
    channel_id TEXT NOT NULL,
    rule_text TEXT NOT NULL,
    order_index INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id)
);

Migrations

Schema migrations are located in migrations/:

1. 001_schema.sql - Main database schema
2. 002_triggers.sql - Database triggers
3. 003_indexes.sql - Performance indexes

Running Migrations

bin/codex migrate

Seeding Data

bin/codex seed

Image Storage

Images are stored in the filesystem:

• User images: db/userdata/images/user-images/
• Channel images: db/userdata/images/channel-images/
• Post images: db/userdata/images/post-images/

Indexes

Performance indexes on:

• User lookups (username, email)
• Post queries (channel_id, user_id, created_at)
• Comment queries (post_id, created_at)
• Reaction queries (target_id, target_type)
• Membership queries (user_id, channel_id)