363 lines
14 KiB
Markdown
363 lines
14 KiB
Markdown
# Database Schema
|
|
|
|
LinkLog stores its persistent state in SQLite. The schema is defined by the ordered migrations in [`backend/app/database.py`](backend/app/database.py), and the current schema version is **17** (`PRAGMA user_version`). Application startup applies migrations that are newer than the database's current version; existing migration entries must not be changed.
|
|
|
|
The default database file is `backend/data/linklog.db`. Set `LINKLOG_DATABASE_PATH` to use another path. Foreign-key enforcement is enabled for every application connection.
|
|
|
|
## Entity-relationship diagram
|
|
|
|
```mermaid
|
|
erDiagram
|
|
USERS ||--o{ TOKENS : authenticates
|
|
USERS ||--o{ LINKS : owns
|
|
USERS ||--o{ USER_PLUGIN_CONFIG : configures
|
|
USERS ||--o{ EMAIL_VERIFICATION_TOKENS : verifies
|
|
USERS ||--o{ PASSWORD_RESET_TOKENS : resets
|
|
USERS ||--o{ MASTODON_OAUTH_STATES : authorizes
|
|
USERS ||--o{ USER_EMAIL_ADDRESSES : has
|
|
USERS ||--o{ OTP_RECOVERY_CODES : recovers
|
|
USERS ||--o{ SECURITY_AUDIT_EVENTS : acts
|
|
USERS o|--o{ TAGS : creates
|
|
LINKS ||--o{ LINK_TAGS : classified_by
|
|
TAGS ||--o{ LINK_TAGS : classifies
|
|
USER_EMAIL_ADDRESSES ||--o{ EMAIL_ADDRESS_VERIFICATION_TOKENS : verifies
|
|
|
|
USERS {
|
|
TEXT id PK
|
|
TEXT username UK
|
|
TEXT email UK
|
|
TEXT password_hash
|
|
TEXT avatar_url
|
|
TEXT bio
|
|
INTEGER is_admin
|
|
INTEGER email_verified
|
|
TEXT otp_secret
|
|
INTEGER otp_enabled
|
|
TEXT created_at
|
|
TEXT updated_at
|
|
}
|
|
TOKENS {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT token_hash UK
|
|
TEXT token_type
|
|
TEXT expires_at
|
|
TEXT device_id
|
|
TEXT token_family_id
|
|
TEXT created_at
|
|
INTEGER revoked
|
|
}
|
|
LINKS {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT title
|
|
TEXT url
|
|
TEXT comment
|
|
TEXT timestamp
|
|
TEXT created_at
|
|
TEXT updated_at
|
|
INTEGER is_public
|
|
INTEGER mastodon_posted
|
|
TEXT mastodon_post_id
|
|
TEXT mastodon_posted_at
|
|
TEXT mastodon_post_ids
|
|
}
|
|
TAGS {
|
|
TEXT id PK
|
|
TEXT name UK
|
|
TEXT created_by FK
|
|
TEXT created_at
|
|
}
|
|
LINK_TAGS {
|
|
TEXT link_id PK_FK
|
|
TEXT tag_id PK_FK
|
|
}
|
|
PLUGINS {
|
|
TEXT id PK
|
|
TEXT name UK
|
|
TEXT version
|
|
INTEGER enabled
|
|
TEXT config
|
|
TEXT created_at
|
|
TEXT updated_at
|
|
}
|
|
USER_PLUGIN_CONFIG {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT plugin_name
|
|
TEXT config
|
|
TEXT created_at
|
|
TEXT updated_at
|
|
}
|
|
EMAIL_VERIFICATION_TOKENS {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT token_hash UK
|
|
TEXT expires_at
|
|
TEXT created_at
|
|
}
|
|
PASSWORD_RESET_TOKENS {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT token_hash UK
|
|
TEXT expires_at
|
|
TEXT created_at
|
|
}
|
|
MASTODON_OAUTH_STATES {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT state_hash UK
|
|
TEXT instance
|
|
TEXT client_id
|
|
TEXT client_secret
|
|
TEXT redirect_uri
|
|
TEXT expires_at
|
|
TEXT created_at
|
|
}
|
|
USER_EMAIL_ADDRESSES {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT email UK
|
|
INTEGER verified
|
|
TEXT created_at
|
|
TEXT updated_at
|
|
}
|
|
EMAIL_ADDRESS_VERIFICATION_TOKENS {
|
|
TEXT id PK
|
|
TEXT email_address_id FK
|
|
TEXT token_hash UK
|
|
TEXT expires_at
|
|
TEXT created_at
|
|
}
|
|
OTP_RECOVERY_CODES {
|
|
TEXT id PK
|
|
TEXT user_id FK
|
|
TEXT code_hash UK
|
|
INTEGER used
|
|
TEXT created_at
|
|
TEXT used_at
|
|
}
|
|
SECURITY_AUDIT_EVENTS {
|
|
TEXT id PK
|
|
TEXT actor_id FK
|
|
TEXT action
|
|
TEXT target_type
|
|
TEXT target_id
|
|
TEXT outcome
|
|
TEXT details
|
|
TEXT created_at
|
|
}
|
|
APP_SETTINGS {
|
|
TEXT name PK
|
|
TEXT value
|
|
TEXT updated_at
|
|
}
|
|
```
|
|
|
|
`UK` means a unique constraint. `PK_FK` means the column participates in the composite primary key and is also a foreign key. SQLite stores timestamps as `TEXT` using its timestamp defaults. Boolean values are stored as `INTEGER` values (`0` or `1`). JSON configuration and Mastodon post ID lists are stored as `TEXT`.
|
|
|
|
## Tables
|
|
|
|
### `users`
|
|
|
|
The user account and profile table.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key, normally a UUID. |
|
|
| `username` | TEXT | no | | Unique public username. |
|
|
| `email` | TEXT | no | | Unique primary email address. |
|
|
| `password_hash` | TEXT | no | | Password hash; plaintext passwords are not stored. |
|
|
| `avatar_url` | TEXT | yes | | Stored avatar reference. |
|
|
| `bio` | TEXT | yes | | Profile biography. |
|
|
| `is_admin` | INTEGER | no | `0` | Administrator flag. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
| `email_verified` | INTEGER | no | `0` | Whether the primary email is verified. |
|
|
| `otp_secret` | TEXT | yes | | Encrypted TOTP secret when configured. |
|
|
| `otp_enabled` | INTEGER | no | `0` | Whether OTP is required at login. |
|
|
|
|
### `tokens`
|
|
|
|
Access and refresh token records. Only token hashes are persisted. `device_id` and `token_family_id` support device binding, rotation, reuse detection, and family revocation.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `user_id` | TEXT | no | | FK to `users.id`. |
|
|
| `token_hash` | TEXT | no | | Unique stored token hash. |
|
|
| `token_type` | TEXT | no | `access` | Token category. |
|
|
| `expires_at` | TEXT | no | | Expiration timestamp. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `revoked` | INTEGER | no | `0` | Revocation flag. |
|
|
| `device_id` | TEXT | yes | | Client/device identifier. |
|
|
| `token_family_id` | TEXT | yes | | Refresh-token family identifier. |
|
|
|
|
### `links`
|
|
|
|
Saved links and their publication state. `mastodon_post_ids` is a JSON array stored as text; the older `mastodon_post_id` column remains for migration compatibility.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `user_id` | TEXT | no | | FK to `users.id`. |
|
|
| `title` | TEXT | no | | Saved page title. |
|
|
| `url` | TEXT | no | | Tracking-cleaned URL. |
|
|
| `comment` | TEXT | yes | | User comment. |
|
|
| `timestamp` | TEXT | no | | User-supplied or captured link time. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
| `is_public` | INTEGER | no | `1` | Public-feed visibility flag. |
|
|
| `mastodon_posted` | INTEGER | no | `0` | Whether publication has occurred. |
|
|
| `mastodon_post_id` | TEXT | yes | | Legacy single Mastodon post ID. |
|
|
| `mastodon_posted_at` | TEXT | yes | | Publication timestamp. |
|
|
| `mastodon_post_ids` | TEXT | yes | | JSON array of publication IDs. |
|
|
|
|
### `tags` and `link_tags`
|
|
|
|
`tags` contains reusable labels. `link_tags` is the many-to-many join table between links and tags. Tag names are unique, and `tags.created_by` is nullable so a deleted creator does not remove the tag.
|
|
|
|
| Table | Columns | Constraints |
|
|
| --- | --- | --- |
|
|
| `tags` | `id` TEXT, `name` TEXT, `created_at` TEXT, `created_by` TEXT | Primary key `id`; unique `name`; `created_by` references `users.id` with `ON DELETE SET NULL`. |
|
|
| `link_tags` | `link_id` TEXT, `tag_id` TEXT | Composite primary key (`link_id`, `tag_id`); both FKs cascade on delete. |
|
|
|
|
Index: `idx_link_tags_tag_id` supports reverse tag lookups. `idx_tags_created_by` supports creator-based tag management.
|
|
|
|
### `plugins`
|
|
|
|
Installed plugin definitions and global plugin configuration. `config` is JSON text.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `name` | TEXT | no | | Unique plugin name. |
|
|
| `version` | TEXT | no | | Plugin version. |
|
|
| `enabled` | INTEGER | no | `1` | Enablement flag. |
|
|
| `config` | TEXT | yes | | Plugin JSON configuration. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
|
|
### `user_plugin_config`
|
|
|
|
Per-user plugin settings. The pair (`user_id`, `plugin_name`) is unique; `plugin_name` is a logical plugin identifier and is not a foreign key to `plugins`.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `user_id` | TEXT | no | | FK to `users.id`. |
|
|
| `plugin_name` | TEXT | no | | Plugin identifier. |
|
|
| `config` | TEXT | yes | | User-specific JSON configuration. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
|
|
### Verification, reset, and OAuth state tables
|
|
|
|
These tables store one-time or short-lived workflow state. Token and state values are persisted as hashes where applicable. All user-owned rows are deleted when their user is deleted.
|
|
|
|
| Table | Important columns | Foreign key / uniqueness |
|
|
| --- | --- | --- |
|
|
| `email_verification_tokens` | `id`, `user_id`, `token_hash`, `expires_at`, `created_at` | `user_id` -> `users.id` with `ON DELETE CASCADE`; unique `token_hash`. |
|
|
| `password_reset_tokens` | `id`, `user_id`, `token_hash`, `expires_at`, `created_at` | `user_id` -> `users.id` with `ON DELETE CASCADE`; unique `token_hash`. |
|
|
| `mastodon_oauth_states` | `id`, `user_id`, `state_hash`, `instance`, `client_id`, `client_secret`, `redirect_uri`, `expires_at`, `created_at` | `user_id` -> `users.id` with `ON DELETE CASCADE`; unique `state_hash`. OAuth client secrets are encrypted by the service layer. |
|
|
|
|
Indexes: `idx_email_verification_tokens_user_id`, `idx_password_reset_tokens_user_id`, and `idx_mastodon_oauth_states_state_hash`.
|
|
|
|
### `app_settings`
|
|
|
|
Global key/value settings, including setup and mail configuration. Sensitive values are encrypted by the service layer before storage where required.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `name` | TEXT | no | | Primary key setting name. |
|
|
| `value` | TEXT | no | | Setting value. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
|
|
### `user_email_addresses`
|
|
|
|
Verified and pending alternative email addresses. The primary address remains in `users.email`; this table holds additional addresses.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `user_id` | TEXT | no | | FK to `users.id` with `ON DELETE CASCADE`. |
|
|
| `email` | TEXT | no | | Globally unique alternative address. |
|
|
| `verified` | INTEGER | no | `0` | Verification flag. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `updated_at` | TEXT | no | `CURRENT_TIMESTAMP` | Last update timestamp. |
|
|
|
|
Index: `idx_user_email_addresses_user_id`.
|
|
|
|
### `email_address_verification_tokens`
|
|
|
|
Verification tokens for alternative addresses.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `email_address_id` | TEXT | no | | FK to `user_email_addresses.id` with `ON DELETE CASCADE`. |
|
|
| `token_hash` | TEXT | no | | Unique token hash. |
|
|
| `expires_at` | TEXT | no | | Expiration timestamp. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
|
|
Index: `idx_email_address_verification_tokens_address_id`.
|
|
|
|
### `otp_recovery_codes`
|
|
|
|
One-time recovery codes for users with OTP enabled. Only code hashes are stored.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `user_id` | TEXT | no | | FK to `users.id` with `ON DELETE CASCADE`. |
|
|
| `code_hash` | TEXT | no | | Unique recovery-code hash. |
|
|
| `used` | INTEGER | no | `0` | Consumption flag. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Creation timestamp. |
|
|
| `used_at` | TEXT | yes | | Consumption timestamp. |
|
|
|
|
Index: `idx_otp_recovery_codes_user_id`.
|
|
|
|
### `security_audit_events`
|
|
|
|
Security-relevant audit events. `actor_id` is nullable so an account deletion does not remove the audit record; it becomes `NULL` through `ON DELETE SET NULL`.
|
|
|
|
| Column | Type | Null | Default | Notes |
|
|
| --- | --- | --- | --- | --- |
|
|
| `id` | TEXT | no | | Primary key. |
|
|
| `actor_id` | TEXT | yes | | FK to `users.id`, `ON DELETE SET NULL`. |
|
|
| `action` | TEXT | no | | Action name. |
|
|
| `target_type` | TEXT | no | | Target entity type. |
|
|
| `target_id` | TEXT | yes | | Target identifier. |
|
|
| `outcome` | TEXT | no | `success` | Result classification. |
|
|
| `details` | TEXT | no | `{}` | JSON text, sanitized by the audit service. |
|
|
| `created_at` | TEXT | no | `CURRENT_TIMESTAMP` | Event timestamp. |
|
|
|
|
Indexes: `idx_security_audit_events_created_at` and `idx_security_audit_events_actor_id`.
|
|
|
|
## Migration history
|
|
|
|
| Version | Change |
|
|
| ---: | --- |
|
|
| 1 | Creates users, tokens, links, plugins, and per-user plugin configuration. |
|
|
| 2 | Adds tags and the link/tag join table. |
|
|
| 3 | Normalizes tag names with a leading `#`. |
|
|
| 4 | Adds tag ownership through `tags.created_by`. |
|
|
| 5 | Adds primary-email verification and its token table. |
|
|
| 6 | Adds global application settings. |
|
|
| 7 | Adds password-reset tokens. |
|
|
| 8 | Adds Mastodon OAuth state. |
|
|
| 9-10 | Adds Mastodon publication fields and migrates to a JSON list of post IDs. |
|
|
| 11 | Adds OTP secret and enablement fields to users. |
|
|
| 12 | Adds alternative email addresses and their verification tokens. |
|
|
| 13-14 | Temporarily adds then removes pending primary-email-change state. It is not part of the current schema. |
|
|
| 15 | Adds device and token-family fields to tokens. |
|
|
| 16 | Adds OTP recovery codes. |
|
|
| 17 | Adds security audit events. |
|
|
|
|
To inspect a live database directly:
|
|
|
|
```sh
|
|
sqlite3 backend/data/linklog.db '.schema'
|
|
sqlite3 backend/data/linklog.db 'PRAGMA user_version;'
|
|
```
|