Authelia 数据库 Schema 设计指南：表、列与键的命名规范及源码实践

Authelia 数据库 Schema 设计指南表、列与键的命名规范及源码实践【免费下载链接】autheliaThe Single Sign-On Multi-Factor portal for web apps. OpenID Certified™ and Post-Quantum Cryptography Ready.项目地址: https://gitcode.com/GitHub_Trending/au/authelia本篇指南围绕 Authelia 官方开发文档中的数据库 SchemaDatabase Schema规范展开系统讲解表名Table Names、列名Column Names与各类键名Key Names外键、唯一键、主键的命名约定并结合仓库内 internal/storage/migrations 下真实迁移脚本MySQL / PostgreSQL / SQLite逐一印证这些规范在 Authelia 多数据库实现中的落地方式。读完本文你将掌握 Authelia 团队在数据库建模时遵循的完整命名体系能够在为 Authelia 贡献代码、编写迁移脚本或审查 Schema 变更时写出风格一致、可跨数据库移植的 SQL。规范背景为什么 Authelia 需要一套数据库命名规范Authelia 是一个支持多种后端存储的单点登录与多因素认证门户其存储层在 internal/storage 中实现了对 MySQL、PostgreSQL、SQLite 三种数据库的统一抽象。官方文档在 database-schema.md 中明确指出表名和列名应当在每一种数据库实现中保持一致Should match in every database implementation。这一要求源于仓库的迁移体系设计所有数据库共享同一套逻辑 Schema迁移脚本按V版本号.名称.up.sql/.down.sql的形式存放在 internal/storage/migrations 目录下分别针对mysql、postgres、sqlite三个子目录。例如 V0004.OpenIDConnect.up.sql 在三个数据库中同时存在且表结构一致。从 migrations.go 的loadMigrations逻辑可以看出Authelia 启动时会按版本号顺序加载 up/down 迁移脚本在这种一份逻辑 Schema、三份方言脚本的架构下如果各数据库命名不统一将直接导致跨库兼容、迁移校验与后续维护的混乱。因此这套规范的核心目标有三个跨数据库一致性同一张表、同一列、同一个约束在 MySQL、PostgreSQL、SQLite 中拥有完全相同的名字可读性所有标识符全部小写、单词间用下划线分隔符合 SQL 社区通用习惯也避免大小写敏感带来的坑可预测性键名约束名遵循固定格式开发者无需查表即可推断某个外键或唯一键叫什么。表名Table Names规范文档为表名定义了 5 条硬性规则规则说明1. 跨实现一致表名在所有数据库实现中必须一致2. 全小写表名全部使用小写字母3. 使用单数形式表名使用单数不是复数4. 单词间用下划线单词之间使用_分隔5. 字符白名单只允许字母数字和下划线关于第 5 条还有两条细分约束下划线的使用单词之间必须始终使用下划线下划线只能用于两种情况单词之间以及临时表的前缀。首尾字符表名只能以字母开头、以字母结尾文档中提到的前缀/后缀例外情况除外例如临时表前缀。在仓库的真实迁移脚本中这些规则得到了严格贯彻。以 V0001.Initial_Schema.up.sql 为例初始 Schema 中的表名全部为小写、单数、下划线分隔authentication_logs认证日志identity_verification身份验证记录totp_configurationsTOTP 配置u2f_devices/webauthn_devicesWebAuthn 设备duo_devicesDuo 设备user_preferences用户偏好migrations迁移记录encryption加密元数据注意这些名称刻意不使用复数如totp_configurations而非totp_configurations_tableuser_preferences而非preferences也不包含数据库方言特有的前缀。即便在某次迁移中需要清理历史遗留表V0007.ConsistencyFixes.up.sql 中删除的也是一批历史遗留的混合大小写命名如AuthenticationLogs、TOTPSecrets、Preferences、U2FDeviceHandles等这恰好从反面印证了全小写规范的价值——旧的命名风格最终被统一修正。临时表的前缀规范关于下划线作为临时表前缀的例外仓库中有一个非常直观的实例。V0002.WebAuthn.up.sql 在升级 WebAuthn 表结构时先把旧表重命名为带下划线前缀的备份表ALTER TABLE totp_configurations RENAME _bkp_UP_V0002_totp_configurations; ALTER TABLE u2f_devices RENAME _bkp_UP_V0002_u2f_devices;这里_bkp_UP_V0002_totp_configurations遵循了临时表以下划线_作为前缀的约定_bkp表示备份backupUP_V0002标识它是 V0002 号迁移的 up 方向产生的中间表。数据迁移完成后V0007.ConsistencyFixes.up.sql 中再将其删除DROP TABLE IF EXISTS _bkp_UP_V0002_totp_configurations;。这种命名让临时表与业务表一眼可辨且不会与正式表冲突。列名Column Names规范列名的规则与表名高度一致但更严格——下划线只能用于单词之间不允许作为前缀使用规则说明1. 跨实现一致列名在所有数据库实现中必须一致2. 全小写列名全部使用小写字母3. 字符白名单只允许字母数字和下划线4. 下划线仅用于分词下划线只能出现在单词之间不可作为前缀5. 首尾为字母列名只能以字母开头、以字母结尾在 V0004.OpenIDConnect.up.sql 中OAuth2/OIDC 相关的列名完美体现了这套规则CREATE TABLE IF NOT EXISTS oauth2_consent_session ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, challenge_id CHAR(36) NOT NULL, client_id VARCHAR(255) NOT NULL, subject CHAR(36) NOT NULL, authorized BOOLEAN NOT NULL DEFAULT FALSE, granted BOOLEAN NOT NULL DEFAULT FALSE, requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, responded_at TIMESTAMP NULL DEFAULT NULL, expires_at TIMESTAMP NULL DEFAULT NULL, form_data TEXT NOT NULL, requested_scopes TEXT NOT NULL, granted_scopes TEXT NOT NULL, requested_audience TEXT NULL, granted_audience TEXT NULL ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_520_ci;可以观察到几个值得注意的实践细节多词列名一律下划线分词challenge_id、client_id、requested_at、responded_at、expires_at、form_data、requested_scopes、granted_audience等没有任何驼峰或混合大小写避免保留字granted、authorized、active这类词本身不是保留字可直接使用需要表达授权范围时用scopes而非scope注意requested_scopes/granted_scopes是复数名词作列名这与表名单数规则并不冲突因为表名规则针对的是表而非列跨库列类型有方言差异但列名一致对比 SQLite 版本的同一迁移AUTO_INCREMENT变为AUTOINCREMENT、TEXT NULL补充了DEFAULT 但列名完全相同这正是列名在每种数据库实现中一致的直接证据。此外从 V0002.WebAuthn.up.sql 中可以看到webauthn_devices表的列设计同样遵守规范created_at、last_used_at、rpid、kidkey id 缩写、aaguid、sign_count、clone_warning、attestation_type等全部小写、下划线分词、以字母开头结尾。键名Key Names规范键名约束名是这套规范中最具工程可预测性的部分。文档给出了三种键的固定命名格式外键Foreign Keys格式为table_name_column_name_fkey其中table_name是外键所在表的表名column_name是该外键对应列的列名。在 V0004.OpenIDConnect.up.sql 中可以看到标准用法ALTER TABLE oauth2_consent_session ADD CONSTRAINT oauth2_consent_subject_fkey FOREIGN KEY (subject) REFERENCES user_opaque_identifier (identifier) ON UPDATE RESTRICT ON DELETE RESTRICT;拆解oauth2_consent_subject_fkey表名oauth2_consent外键所在的oauth2_consent_session表列名subject外键列后缀_fkey标识这是外键。同文件中的另外四个外键同理oauth2_authorization_code_session_challenge_id_fkey、oauth2_authorization_code_session_subject_fkey、oauth2_access_token_session_challenge_id_fkey、oauth2_pkce_request_session_challenge_id_fkey等全部严格遵循表名_列名_fkey格式命名长度虽然较长但完全可预测——看到约束名即可知道它挂在哪张表的哪一列上。值得注意SQLite 版本 V0004.OpenIDConnect.up.sql 采用CONSTRAINT ... FOREIGN KEY内联声明PostgreSQL 版本 V0004.OpenIDConnect.up.sql 采用ADD CONSTRAINT三种方言写法不同但约束名完全一致再次印证跨库一致的约束命名原则。唯一键Unique Keys格式为table_name_key_name_key其中table_name是唯一键所在表的表名key_name是描述该键的名称也可以是它所在的列名。V0004.OpenIDConnect.up.sql 中的示例CREATE UNIQUE INDEX user_opaque_identifier_service_sector_id_username_key ON user_opaque_identifier (service, sector_id, username); CREATE UNIQUE INDEX user_opaque_identifier_identifier_key ON user_opaque_identifier (identifier);第一行是复合唯一键user_opaque_identifier表上由service sector_id username三列组成的唯一约束key name 部分直接拼接了三列名service_sector_id_username再接_key后缀。第二行是单列唯一键key name 直接取列名identifier。V0007.ConsistencyFixes.up.sql 展示了统一唯一键命名的过程——把早期迁移中裸写UNIQUE KEY (username)的隐式命名统一改成了显式、可预测的命名CREATE UNIQUE INDEX duo_devices_username_key ON duo_devices (username); CREATE UNIQUE INDEX encryption_name_key ON encryption (name); CREATE UNIQUE INDEX identity_verification_jti_key ON identity_verification (jti); CREATE UNIQUE INDEX totp_configurations_username_key ON totp_configurations (username); CREATE UNIQUE INDEX user_opaque_identifier_identifier_key ON user_opaque_identifier (identifier); CREATE UNIQUE INDEX user_opaque_identifier_lookup_key ON user_opaque_identifier (service, sector_id, username); CREATE UNIQUE INDEX user_preferences_username_key ON user_preferences (username); CREATE UNIQUE INDEX webauthn_devices_kid_key ON webauthn_devices (kid); CREATE UNIQUE INDEX webauthn_devices_lookup_key ON webauthn_devices (username, description);注意这里有两个细节lookup语义当复合唯一键的列名组合过长或语义上是查询定位键时Authelia 使用_lookup_key作为 key name如user_opaque_identifier_lookup_key、webauthn_devices_lookup_key这体现了文档中key name 是描述该键的名称的灵活性与旧命名的对比这段迁移先通过存储过程PROC_DROP_INDEX删除了旧索引见 V0007.ConsistencyFixes.up.sql如username、name、jti这类无前缀裸名索引再按规范重建是理解唯一键必须表名_key_name_key的最佳反面教材。主键Primary Keys文档对主键的约定最为宽松也最符合数据库引擎的现实约束大多数数据库引擎不允许自定义主键名称因此不应显式设置主键名除非是为了改回默认格式。主键默认名由引擎生成MySQL 为PRIMARYPostgreSQL 为表名_pkeySQLite 通常为sqlite_autoindex_*或行内主键。因此规范刻意不强行统一主键名——这与外键、唯一键形成鲜明对比可自定义命名的约束严格格式化不可自定义命名的约束交给引擎默认处理。从仓库代码看所有建表语句的主键都采用引擎默认形式。例如 V0001.Initial_Schema.up.sql 中CREATE TABLE IF NOT EXISTS authentication_logs ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, ...只写了PRIMARY KEY从不指定主键约束名。SQLite 版本 V0004.OpenIDConnect.up.sql 同样只是id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT。这一约定也降低了跨数据库迁移的复杂度——因为主键命名本就不在迁移脚本可稳定控制的范围内。补充观察普通索引的命名虽然官方文档的键名规范只覆盖外键、唯一键、主键三类但从迁移脚本中可以观察到普通索引非唯一索引的命名惯例可作为工程实践补充单列索引表名_列名_idx如 V0004.OpenIDConnect.up.sql 中的oauth2_authorization_code_session_request_id_idx、oauth2_authorization_code_session_client_id_idx复合索引表名_列1_列2_idx如oauth2_authorization_code_session_client_id_subject_idxV0004.OpenIDConnect.up.sql。普通索引采用_idx后缀与唯一键的_key后缀、外键的_fkey后缀形成完整的三级命名体系阅读迁移脚本时可以通过后缀立即判断约束/索引类型。命名规范的源码实践验证以上规范并非停留在文档层面仓库中有一套完整的迁移体系在持续执行它迁移目录结构internal/storage/migrations 下mysql/、postgres/、sqlite/三个子目录各自维护同版本迁移脚本表、列、键命名三库一致迁移加载机制migrations.go 通过//go:embed migrations/*将迁移文件嵌入二进制loadMigrationsmigrations.go按 up/down 方向与版本号排序执行支持升/降级命名纠偏历史V0007.ConsistencyFixes.up.sql 专门清理历史遗留的混合大小写表名、无前缀索引名并将外键约束修正为表名_列名_fkey格式见 V0007.ConsistencyFixes.up.sql说明这套规范是强制实施的而非纸面建议方言差异的隔离类型层面各数据库有差异MySQL 的AUTO_INCREMENT 引擎子句、SQLite 的AUTOINCREMENT 内联约束、PostgreSQL 的ADD CONSTRAINT但标识符层面完全统一——这正是文档第一条规则表名与列名应在每种数据库实现中一致的直接体现。常见问题与自查清单在贡献数据库相关代码时可以用以下清单快速自查是否符合规范表名检查在 MySQL / PostgreSQL / SQLite 三份迁移脚本中名称完全一致全部小写单数形式单词间用_分隔仅含字母、数字、下划线以字母开头和结尾临时表使用_前缀如_bkp_UP_V0002_totp_configurations列名检查三库列名一致全部小写仅含字母数字与下划线下划线仅用于单词之间不以_开头或结尾键名检查外键表名_列名_fkey唯一键表名_key_name_keykey name 可为列名或语义名如lookup主键不显式命名交给引擎默认小结Authelia 的数据库 Schema 规范用一套简洁而严格的规则解决了多数据库后端下标识符管理的核心痛点表名与列名做到跨库一致、全小写下划线分词键名通过表名_列名_fkey/表名_key_name_key的固定格式保证可预测性主键则尊重引擎默认行为。这些规范在 internal/storage/migrations 的每一份迁移脚本中都得到了真实执行并辅以 migrations.go 的版本化加载机制。无论是为 Authelia 新增存储表、编写升级迁移还是审查他人的 Schema 变更遵循本文所述约定就能让数据库结构在三种后端间保持一致、清晰且易于长期维护。【免费下载链接】autheliaThe Single Sign-On Multi-Factor portal for web apps. OpenID Certified™ and Post-Quantum Cryptography Ready.项目地址: https://gitcode.com/GitHub_Trending/au/authelia创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考