Developer
Database
SQLite engine configuration, WAL mode, schema design, and table definitions.
Database
GO Shortener uses SQLite in Write-Ahead Logging (WAL) mode via the CGO-free modernc.org/sqlite driver. This provides the performance of an embedded database with zero external database server administration.
Performance Pragmas & Concurrency
At startup, the database connection (InitDB in internal/database/db.go) executes essential pragmas:
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-20000; -- 20MB in-memory page cache
- Write-Ahead Logging (WAL): Writes are appended to a
-walfile while reads continue concurrently against the main database file without blocking. - Single-Writer Pool:
db.SetMaxOpenConns(1)restricts write operations to a single worker, completely avoidingSQLITE_BUSYlock contention. - Synchronous Normal: Balances durability and high IOPS without risking database corruption.
Core Schema Definitions
1. links
Stores all generated short links:
CREATE TABLE IF NOT EXISTS links (
id TEXT PRIMARY KEY,
short_code TEXT UNIQUE NOT NULL,
destination_url TEXT NOT NULL,
owner_id TEXT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
expires_at DATETIME NOT NULL,
deleted_at DATETIME,
status TEXT NOT NULL DEFAULT 'ACTIVE',
auto_renew INTEGER NOT NULL DEFAULT 0,
click_count INTEGER NOT NULL DEFAULT 0
);
2. users
Stores registered user accounts:
CREATE TABLE IF NOT EXISTS users (
id TEXT PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password_hash TEXT,
auth_provider TEXT NOT NULL DEFAULT 'email',
firebase_uid TEXT,
role TEXT NOT NULL DEFAULT 'user',
status TEXT NOT NULL DEFAULT 'active',
timeout_until DATETIME,
timeout_reason TEXT,
ban_reason TEXT,
quota_limit INTEGER NOT NULL DEFAULT 100,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_login_at DATETIME
);
3. link_clicks
Stores privacy-preserving redirect events:
CREATE TABLE IF NOT EXISTS link_clicks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
country TEXT DEFAULT 'Unknown',
referrer TEXT,
device_type TEXT,
browser TEXT,
os TEXT
);
4. quota_usage
Tracks sliding quota windows without storing raw IP addresses:
CREATE TABLE IF NOT EXISTS quota_usage (
id INTEGER PRIMARY KEY AUTOINCREMENT,
identity_key TEXT NOT NULL,
is_anonymous INTEGER NOT NULL,
window_start TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1,
UNIQUE(identity_key, window_start)
);
5. reports & permanent_link_requests
Queue tables for moderation and administrator approval workflows.
6. login_records & admin_audit_logs
Tamper-resistant audit trails capturing timestamps, account identifiers, SHA-256 IP hashes, User-Agents, and action resolutions.