这是 Relay Observatory 代理项目实现系列的第三篇。完整代码放在 GitHub。
前两篇完成了 HTTP/HTTPS 正向代理 和 SOCKS5 三种命令。当用户、密码和流量都只存在内存时，进程一重启，账号和统计就全部丢失。
这一篇解决数据层问题：如何用 SQLite 持久化用户、认证、分协议流量总量和 24 小时趋势。
为什么单机代理适合 SQLite
这个场景具有典型的 SQLite 特征：
- 单实例服务；
- 写入来自连接结束事件，频率有限；
- 后台查询以聚合为主；
- 希望只部署一个可执行文件和一个数据文件；
- 不需要多机共享写数据库。
如果为了几十个用户和每秒少量统计写入部署 PostgreSQL，运维复杂度会远大于收益。
SQLite 的边界也很明确：它适合单机单写，不适合把数据库文件放在 NFS 上让多台代理同时写。
数据模型：总量和趋势分开
用户表：
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE COLLATE NOCASE,
password_hash BLOB NOT NULL,
role TEXT NOT NULL DEFAULT 'user'
CHECK (role IN ('admin', 'user')),
enabled INTEGER NOT NULL DEFAULT 1
CHECK (enabled IN (0, 1)),
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);设计点：
- 用户名使用
COLLATE NOCASE，避免Alice和alice成为两个账号； - 密码只保存 bcrypt hash；
enabled用于立即阻止新连接；- 管理员和代理用户复用同一张表。
流量总量表：
CREATE TABLE traffic_totals (
user_id INTEGER NOT NULL,
protocol TEXT NOT NULL,
uploaded_bytes INTEGER NOT NULL DEFAULT 0,
downloaded_bytes INTEGER NOT NULL DEFAULT 0,
requests INTEGER NOT NULL DEFAULT 0,
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, protocol),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);小时趋势表：
CREATE TABLE traffic_hourly (
user_id INTEGER NOT NULL,
protocol TEXT NOT NULL,
bucket TEXT NOT NULL,
uploaded_bytes INTEGER NOT NULL DEFAULT 0,
downloaded_bytes INTEGER NOT NULL DEFAULT 0,
requests INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (user_id, protocol, bucket),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);为什么不只保留明细日志？
如果每个连接都插入明细，后台每次打开都要扫描大量历史记录做 GROUP BY。当前需求只关心累计值和小时曲线，直接维护聚合表更便宜。
如果未来需要审计单次连接，再增加 append-only 明细表，而不是让当前查询承担不需要的成本。
SQLite 连接参数
启动时执行：
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;作用：
| PRAGMA | 作用 |
|---|---|
journal_mode=WAL | 读查询不必等待写事务完全结束 |
foreign_keys=ON | 删除用户时级联删除流量，避免孤儿数据 |
busy_timeout=5000 | 遇到短暂写锁时等待，而不是立即报 database is locked |
项目使用 modernc.org/sqlite，它是纯 Go 驱动，不要求部署环境安装 CGO 工具链。
当前规模下设置单连接：
db.SetMaxOpenConns(1)SQLite 只有一个写者。单连接能让行为更容易推导，也避免 :memory: 测试因为连接池创建出多个独立数据库。
当读压力明显增加时，可以使用多个连接，但要确保每个连接都正确设置连接级 PRAGMA。
用 bcrypt 存储密码
创建用户：
hash, err := bcrypt.GenerateFromPassword(
[]byte(password),
bcrypt.DefaultCost,
)
_, err = db.Exec(`
INSERT INTO users(username, password_hash, role)
VALUES(?, ?, ?)
`, username, hash, role)认证时只查询启用用户：
err := db.QueryRow(`
SELECT id, username, role, password_hash
FROM users
WHERE username = ? COLLATE NOCASE
AND enabled = 1
`, username).Scan(&id, &name, &role, &hash)
if err != nil ||
bcrypt.CompareHashAndPassword(hash, []byte(password)) != nil {
return Identity{}, false
}这里故意把“用户不存在”和“密码错误”统一成认证失败，避免向外部泄露用户名是否存在。
统一 Authenticator，网络层不依赖 SQLite
HTTP 和 SOCKS5 不应该导入 database/sql。它们只依赖接口：
type Authenticator interface {
Authenticate(username, password string) (Identity, bool)
}
type Identity struct {
ID int64
Username string
Role string
}SQLite 数据库实现这个接口，测试中的内存凭据也实现同一个接口：
HTTP Proxy ----\
SOCKS5 Proxy ----> Authenticator <---- SQLite
Admin Login ----/这个边界带来两个直接收益：
- 网络协议测试不需要真实数据库；
- 以后换 PostgreSQL、LDAP 或远程认证服务，不需要修改代理协议代码。
每次流量写入同时维护两张表
流量写入必须保证总量和小时趋势一致，因此使用同一个事务：
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
// 更新 traffic_totals
// 更新 traffic_hourly
return tx.Commit()总量使用 UPSERT：
INSERT INTO traffic_totals(
user_id, protocol,
uploaded_bytes, downloaded_bytes,
requests, updated_at
)
VALUES(?, ?, ?, ?, 1, ?)
ON CONFLICT(user_id, protocol) DO UPDATE SET
uploaded_bytes =
uploaded_bytes + excluded.uploaded_bytes,
downloaded_bytes =
downloaded_bytes + excluded.downloaded_bytes,
requests = requests + 1,
updated_at = excluded.updated_at;这里的 excluded.uploaded_bytes 表示本次准备插入的值。
同一个用户第一次产生 HTTP 流量时创建行，后续请求只做原子累加，不需要先 SELECT 再 UPDATE，也避免读改写竞争。
小时 bucket 如何生成
将 UTC 时间截断到整点：
bucket := time.Now().
UTC().
Truncate(time.Hour).
Format(time.RFC3339)例如：
2026-08-24T16:37:42Z
=> 2026-08-24T16:00:00Z小时表主键为：
(user_id, protocol, bucket)同一用户、同一协议、同一小时的流量会累计到同一行。后台查询最近 24 小时只需要扫描有限行数。
为什么要区分协议
项目记录：
const (
ProtocolHTTP = "http"
ProtocolHTTPS = "https_connect"
ProtocolSOCKS = "socks_connect"
ProtocolSOCKSBind = "socks_bind"
ProtocolSOCKSUDP = "socks_udp"
)总量仍然可以跨协议求和，但保留 protocol 维度后，后台能够回答：
- HTTP 与 SOCKS5 谁占用更多流量；
- UDP 中继是否突然增长；
- BIND 是否真的有人使用；
- 某个协议的请求数与字节数是否异常。
如果一开始就只存用户总量，这些信息以后无法恢复。
后台聚合查询
总量：
SELECT
COALESCE(SUM(uploaded_bytes), 0),
COALESCE(SUM(downloaded_bytes), 0),
COALESCE(SUM(requests), 0)
FROM traffic_totals;协议占比：
SELECT
protocol,
SUM(uploaded_bytes),
SUM(downloaded_bytes),
SUM(requests)
FROM traffic_totals
GROUP BY protocol;最近 24 小时趋势：
SELECT
bucket,
SUM(uploaded_bytes),
SUM(downloaded_bytes),
SUM(requests)
FROM traffic_hourly
WHERE bucket >= ?
GROUP BY bucket
ORDER BY bucket;用户排行则把 users 与 traffic_totals 左连接。使用 LEFT JOIN 是为了让还没有流量的新用户也能出现在管理列表中。
旧 .env 用户如何迁移
原始版本使用：
PROXY_USERS=alice:strong-secret,bob:strong-password升级后启动程序会：
- 解析旧用户列表；
- 查询 SQLite 是否已有同名用户；
- 不存在则 bcrypt 后插入；
- 已存在则跳过，不覆盖后台修改过的密码。
这类迁移应该是幂等的。服务每次启动都执行也不会改变已有账户。
完成迁移后，PROXY_USERS 只承担首次导入，用户生命周期全部由后台管理。
同步写入的边界
当前实现在线程结束时同步写 SQLite，优点是简单、统计不易丢失。
但如果代理发展到每秒数千个短连接，单写者会成为瓶颈。届时可以改成：
代理连接结束
↓
写入有界 channel
↓
后台协程按 100 条或 1 秒批量事务需要同时处理：
- channel 满时是阻塞还是丢统计；
- 进程退出前如何 flush；
- 写失败如何重试；
- 避免重复入账的幂等 ID。
不要在当前负载还很低时提前引入这套复杂度。
小结
代理数据层的关键不是“把 map 换成 SQLite”，而是提前确定：
- 密码只存 bcrypt hash；
- 网络层依赖认证接口，不依赖数据库；
- 总量与趋势在同一事务中更新；
- 保留协议维度；
- 用 UPSERT 原子累加；
- 旧配置迁移必须幂等。
下一篇完成最后一层：把 Vue 3 管理后台编译后嵌入 Go 可执行文件。