Data Models and Storage

Relevant source files

• proto/gen/api/v1/common.pb.go
• proto/gen/store/idp.pb.go
• proto/gen/store/inbox.pb.go
• proto/gen/store/memo.pb.go
• proto/gen/store/user_setting.pb.go
• proto/store/inbox.proto
• proto/store/memo.proto
• proto/store/user_setting.proto
• server/router/api/v1/auth_service_sso.go
• server/router/api/v1/test/auth_service_sso_username_test.go
• store/db/db.go
• store/db/mysql/memo.go
• store/db/mysql/user.go
• store/db/postgres/memo.go
• store/db/postgres/user.go
• store/db/sqlite/memo.go
• store/db/sqlite/user.go
• store/driver.go
• store/idp.go
• store/memo.go
• store/migrator.go
• store/store.go
• store/user.go
• store/user_setting.go

This document describes the core data models, storage layer architecture, and database abstractions used in memos. It covers the entity structures, protocol buffer definitions, and the store abstraction pattern that enables multi-database support.

---

Overview of Storage Architecture

The memos application implements a three-layer storage architecture that separates API contracts, business logic, and database implementations.

Title: Storage Layer Abstraction

Sources: store/memo.go35-55 store/db/sqlite/memo.go16-52 store/db/mysql/memo.go16-60 store/db/postgres/memo.go16-49 store/store.go13-31

This separation enables:

• Type safety between frontend and backend via protocol buffers.
• Database portability through the Driver interface store/driver.go10-87
• Clean business logic that doesn't depend on specific SQL dialects.
• In-memory caching at the store level for frequently accessed entities like users and settings store/store.go27-31

---

Core Data Entities

Memo Entity

The Memo is the primary entity representing a note or post. It exists in three forms across the architecture layers.

Store Layer Definition

Field	Type	Description
ID	int32	System-generated unique identifier store/memo.go37
UID	string	User-defined unique identifier store/memo.go39
RowStatus	RowStatus	Lifecycle state: NORMAL or ARCHIVED store/memo.go42
CreatorID	int32	Foreign key to the user who created the memo store/memo.go43
CreatedTs	int64	Unix timestamp of creation store/memo.go44
UpdatedTs	int64	Unix timestamp of last update store/memo.go45
Content	string	Markdown-formatted content store/memo.go48
Visibility	Visibility	Access control: PUBLIC, PROTECTED, or PRIVATE store/memo.go49
Pinned	bool	Whether the memo is pinned to the top store/memo.go50
Payload	*storepb.MemoPayload	Protobuf-backed structured metadata store/memo.go51
ParentUID	*string	Composed field indicating parent memo for comments store/memo.go54

Sources: store/memo.go35-55 proto/store/memo.proto1-20

User Entity

The User entity represents a system user with authentication and profile information.

Store Layer Definition

Field	Type	Description
ID	int32	System-generated identifier store/user.go30
Username	string	Unique username for login store/user.go38
Role	Role	User role: ADMIN or USER store/user.go39
Nickname	string	Display name store/user.go41
PasswordHash	string	Hashed password store/user.go42
AvatarURL	string	Profile avatar URL store/user.go43
Description	string	User bio/description store/user.go44

Sources: store/user.go29-45

---

Migration and Schema Management

The Migrator handles database schema versioning and upgrades using embedded SQL scripts.

Title: Migration Flow Logic

• Location: store/migration/{driver}/{version}/NN__description.sql store/migrator.go39-40
• Latest Schema: LATEST.sql is used for fresh installations to bypass incremental steps store/migrator.go56-57
• Transactions: Migrations are applied within a single database transaction store/migrator.go148-153
• Seeding: In demo mode, the database is seeded with initial data store/migrator.go129-134

Sources: store/migrator.go21-172

---

Store Abstraction Pattern

The Store struct provides the entry point for all data operations, utilizing a Driver for persistence and Cache for performance.

Store Interface Structure

Title: Data Access Pattern

Sources: store/store.go13-31 store/driver.go10-87

CRUD Operations and Logic

Memo Operations

• Create: Validates UID using base.UIDMatcher before calling the driver store/memo.go109-114
• Delete: Performs a cascading delete, removing memo_relation and attachment records associated with the memo ID store/memo.go140-159

User Operations and Caching

The Store implements an in-memory cache for user data to reduce database load.

• Get: Checks userCache first; if missed, queries the driver and populates the cache store/user.go148-169
• Update/Delete: Synchronizes the cache by setting or deleting the entry store/user.go126-134 store/user.go171-181
• Concurrency: Uses userCreateMu to prevent concurrent first-user setup store/user.go105-124

User Settings

Settings are stored as key-value pairs where the value is a stringified JSON of a protobuf message store/user_setting.go14-18 The store provides helper methods for complex settings like RefreshTokens and PersonalAccessTokens store/user_setting.go149-237

Sources: store/memo.go109-159 store/user.go92-181 store/user_setting.go43-237

---

Database Driver Implementations

Each driver (SQLite, MySQL, PostgreSQL) handles SQL dialect differences, particularly for timestamps and JSON.

Dialect Differences

Feature	SQLite	MySQL	PostgreSQL
Timestamps	Unix Integer store/db/sqlite/memo.go44	FROM_UNIXTIME store/db/mysql/memo.go32	Unix BigInt store/db/postgres/memo.go41
JSON	protojson to String store/db/sqlite/memo.go21	protojson to String store/db/mysql/memo.go21	protojson to String store/db/postgres/memo.go20
Placeholders	? store/db/sqlite/memo.go18	? store/db/mysql/memo.go18	$1, $2... store/db/postgres/memo.go38
Identity	RETURNING id store/db/sqlite/memo.go41	LastInsertId() store/db/mysql/memo.go47	RETURNING id store/db/postgres/memo.go38

Implementation Logic

• SQLite: Uses QueryRowContext with RETURNING for efficient creation store/db/sqlite/memo.go41-49
• MySQL: Requires a secondary GetMemo call after ExecContext because it lacks a RETURNING clause for all fields store/db/mysql/memo.go41-59
• PostgreSQL: Uses positional placeholders ($1, $2) and QueryRowContext store/db/postgres/memo.go38-49

Sources: store/db/sqlite/memo.go store/db/mysql/memo.go store/db/postgres/memo.go