Database

Niuton uses PostgreSQL with separate databases for each environment.

Databases

Database	Environment	User
niuton_db	E2E (Development)	niuton_user
niuton_prod	PROD (Production)	niuton_prod_user

Core Tables

Users & Auth

niuton_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    email VARCHAR(255),
    is_admin BOOLEAN DEFAULT false,
    avatar_color VARCHAR(7),
    totp_secret VARCHAR(32),
    totp_enabled BOOLEAN DEFAULT false,
    backup_codes TEXT,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

niuton_login_attempts (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    ip_address VARCHAR(45),
    success BOOLEAN,
    attempted_at TIMESTAMP DEFAULT NOW()
);

niuton_api_tokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES niuton_users(id),
    token_hash VARCHAR(64) NOT NULL,
    device_name VARCHAR(100),
    device_id VARCHAR(100),
    last_used TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

App Data

niuton_notes (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255),
    content TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

niuton_finance (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    type VARCHAR(20),     -- 'income' or 'expense'
    amount DECIMAL(12,2),
    category VARCHAR(100),
    description TEXT,
    date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

niuton_docs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    content TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

niuton_places (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    name VARCHAR(255),
    lat DECIMAL(10,7),
    lng DECIMAL(10,7),
    description TEXT,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

Team Chat

niuton_chat_rooms (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) DEFAULT 'channel',
    created_by INTEGER REFERENCES niuton_users(id),
    quota_mb INTEGER DEFAULT 200,
    created_at TIMESTAMP DEFAULT NOW()
);

niuton_chat_participants (
    room_id INTEGER REFERENCES niuton_chat_rooms(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES niuton_users(id),
    last_read_at TIMESTAMP,
    last_active TIMESTAMP,
    PRIMARY KEY (room_id, user_id)
);

niuton_chat_messages (
    id SERIAL PRIMARY KEY,
    room_id INTEGER REFERENCES niuton_chat_rooms(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES niuton_users(id),
    content TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP
);

niuton_chat_reactions (
    id SERIAL PRIMARY KEY,
    message_id INTEGER REFERENCES niuton_chat_messages(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES niuton_users(id),
    emoji VARCHAR(10),
    created_at TIMESTAMP DEFAULT NOW()
);

niuton_chat_mentions (
    id SERIAL PRIMARY KEY,
    message_id INTEGER REFERENCES niuton_chat_messages(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES niuton_users(id)
);

niuton_chat_presence (
    user_id INTEGER PRIMARY KEY REFERENCES niuton_users(id),
    last_seen TIMESTAMP DEFAULT NOW()
);

Apps & System

niuton_apps (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    icon_url VARCHAR(255),
    app_path VARCHAR(255),
    category VARCHAR(50),
    is_system BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    supports_context BOOLEAN DEFAULT false,
    created_by_user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

niuton_system_config (
    key VARCHAR(100) PRIMARY KEY,
    value TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

niuton_trash (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    item_type VARCHAR(20),
    item_name VARCHAR(255),
    original_path TEXT,
    app_id INTEGER,
    metadata JSONB,
    trashed_at TIMESTAMP DEFAULT NOW()
);

niuton_sync_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100),
    record_id INTEGER,
    operation VARCHAR(10),
    user_id INTEGER,
    changed_at TIMESTAMP DEFAULT NOW()
);

niuton_audit_log (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    action VARCHAR(100),
    details JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

Chess

niuton_chess_games (
    id SERIAL PRIMARY KEY,
    white_user_id INTEGER,  -- nullable (AI plays white)
    black_user_id INTEGER,
    pgn TEXT,
    fen VARCHAR(100),
    status VARCHAR(20),
    result VARCHAR(10),
    mode VARCHAR(20),
    engine_depth INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

niuton_chess_challenges (
    id SERIAL PRIMARY KEY,
    from_user_id INTEGER NOT NULL,
    to_user_id INTEGER NOT NULL,
    color VARCHAR(10),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT NOW()
);

Encoding

UTF-8 Required

PostgreSQL must use UTF-8 encoding. The connection sets client_encoding = 'UTF8' explicitly:

$pdo->exec("SET client_encoding = 'UTF8'");

If your database was created with SQL_ASCII encoding, you need to migrate:

# Dump, recreate with UTF8, restore
pg_dump -U postgres niuton_db > /tmp/dump.sql
dropdb -U postgres niuton_db
createdb -U postgres -E UTF8 -O niuton_user niuton_db
psql -U postgres niuton_db < /tmp/dump.sql

Accessing the Database

# From the Niuton container (114)
sudo -u postgres psql -d niuton_db

# Common queries
\dt niuton_*          -- List all Niuton tables
\d niuton_users       -- Show table structure
SELECT count(*) FROM niuton_users;