#Database Schema Documentation

Rin uses SQLite database (via Drizzle ORM) with 12 tables.

#Table Overview

#users (User Table)

Stores user information, supporting multiple login methods.

Indexes:

• Unique index users_username_unique ON username

Notes:

• Same user can login via multiple methods (GitHub + QQ + Email), openid distinguishes different login methods
• email field is used for email verification login, username becomes full email address after login

Password hashing rules:

• users.password stores an unsalted SHA-256 hash as a 64-character lowercase hex string; at login the server hashes the submitted password the same way and compares the results
• When you set a password manually in the D1 database, write the hashed value into the column; a plaintext password makes the account unable to log in
• Compute the hash on Linux:

printf '%s' 'your-plaintext-password' | sha256sum | cut -d' ' -f1

• Compute the hash on macOS:

printf '%s' 'your-plaintext-password' | shasum -a 256 | cut -d' ' -f1

• Update the password in D1:

UPDATE users
SET password = 'hash-from-the-step-above'
WHERE username = 'target-username';

#feeds (Posts Table)

Stores blog post content.

Relations:

• uid → users.id (post belongs to one user)

#moments (Moments Table)

Stores user short posts/moments.

Relations:

• uid → users.id (moment belongs to one user)

#comments (Comments Table)

Stores post comments, supports guest comments.

Relations:

• feed_id → feeds.id ON DELETE CASCADE
• user_id → users.id ON DELETE CASCADE (nullable)

Notes:

• For guest comments, user_id is NULL, use guest_name/guest_email/guest_website for guest info

#visits (Visit Records Table)

Records each visit to a post.

Relations:

• feed_id → feeds.id ON DELETE CASCADE

Notes:

• This table stores raw visit data, statistics are aggregated via visit_stats

#visit_stats (Visit Statistics Table)

Stores post visit statistics using HyperLogLog for deduplication.

Relations:

• feed_id → feeds.id ON DELETE CASCADE

#friends (Friend Links Table)

Stores友情链接 (friend links).

Relations:

• uid → users.id ON DELETE CASCADE

#hashtags (Tags Table)

Stores post tags.

#feed_hashtags (Post-Tag Association Table)

Many-to-many relationship between posts and tags.

Relations:

• feed_id → feeds.id ON DELETE CASCADE
• hashtag_id → hashtags.id ON DELETE CASCADE

Notes:

• Composite primary key is (feed_id, hashtag_id), ensures no duplicate tag assignments per post

#info (System Configuration Table)

Stores system key-value configuration.

Indexes:

• Unique index info_key_unique ON key

Common key examples:

• site.name - Site name
• site.description - Site description
• site.avatar - Site avatar
• site.page_size - Pagination size
• rss - RSS enabled status
• Other runtime configuration

#cache (Cache Table)

Stores application cache data.

Constraints:

• Composite unique constraint cache_key_type_unique ON (key, type)

Indexes:

• idx_cache_type ON type
• idx_cache_key ON key

type values used by email friend-link applications:

• friend.email: keyed by the applicant's email; stores the in-flight application state (stage, operation, JSON payload, verification code, deadlines).
• friend.email.lock: fixed key of global, implements the "only one application at a time" concurrency lock.

type='client.config' keys used by friend groups:

• friend_groups: JSON array of admin-defined group names (excluding the default group).
• friend_group_order: JSON array controlling friend-link display order; an empty string element represents the default group ("Friends"), and new groups are appended after it by default.

#linked_accounts (Third-Party Login Bindings Table)

Stores third-party login accounts bound to users (GitHub, Gitee, QQ, WeChat, etc.).

Relations:

• user_id → users.id ON DELETE CASCADE

Notes:

• One user can bind multiple third-party accounts
• One third-party account cannot be bound to multiple users

#Table Relationship Diagram

users (1) ──< (N) feeds
                 ├──< (N) comments
                 ├──< (N) friends
                 └──< (N) linked_accounts

feeds (1) ──< (N) comments
feeds (1) ──< (N) visits
feeds (1) ──< (1) visit_stats
feeds (N) >──< (N) hashtags (via feed_hashtags)

moments (N) >── (1) users

#Notes for Direct Database Modification

1. Use Drizzle ORM migrations: When modifying schema, generate Drizzle migration files instead of executing raw SQL
2. No DROP/RENAME: Cloudflare D1 does not support DROP TABLE and RENAME TABLE, migrations must use ALTER TABLE ADD COLUMN
3. Timestamp format: All timestamps use Unix timestamp (integer, seconds)
4. Foreign key constraints: SQLite does not enforce foreign key constraints by default, application layer is responsible for data consistency
5. Guest comments: comments.user_id can be NULL, when user is deleted, all their comments are CASCADE deleted

#sqlite_sequence Table

sqlite_sequence is an internal SQLite system table. It is created and maintained automatically by SQLite.

Purpose:

• Records the current maximum ROWID for tables that use AUTOINCREMENT
• Ensures INTEGER PRIMARY KEY AUTOINCREMENT columns increment monotonically, preventing ROWID reuse
• SQLite automatically reads and updates the sequence value from this table each time a new row is inserted

Notes:

• This is an internal table maintained by SQLite and should not be modified directly
• If a table's sequence value becomes corrupted, you can fix it with UPDATE sqlite_sequence SET seq = N WHERE name = 'table_name', but this is rarely needed