Data Models and Storage

Relevant source files

• proto/api/v1/memo_service.proto
• proto/gen/api/v1/memo_service.pb.go
• proto/gen/api/v1/memo_service.pb.gw.go
• proto/gen/api/v1/memo_service_grpc.pb.go
• server/router/api/v1/memo_service.go
• store/db/mysql/memo.go
• store/db/postgres/memo.go
• store/db/sqlite/memo.go
• store/memo.go
• store/user.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.

For information about the API layer that exposes these data models, see API and Service Architecture. For details on database configuration and deployment, see Database Configuration.

---

Overview of Storage Architecture

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

Sources: store/memo.go1-160 server/router/api/v1/memo_service.go24-144 store/db/sqlite/memo.go1-248

This separation enables:

• Type safety between frontend and backend via protocol buffers
• Database portability through the store abstraction
• Clean business logic that doesn't depend on specific SQL dialects

---

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 auto-increment identifier
UID	string	User-defined unique identifier (validated by UIDMatcher)
RowStatus	RowStatus	Lifecycle state: Normal or Archived
CreatorID	int32	Foreign key to the user who created the memo
CreatedTs	int64	Unix timestamp of creation
UpdatedTs	int64	Unix timestamp of last update
Content	string	Markdown-formatted content
Visibility	Visibility	Access control: PUBLIC, PROTECTED, or PRIVATE
Pinned	bool	Whether the memo is pinned to the top
Payload	*storepb.MemoPayload	Structured metadata extracted from content
ParentUID	*string	Composed field indicating parent memo for comments

Sources: store/memo.go35-55

Protocol Buffer Definition

The API-layer memo includes additional computed fields for client consumption:

Field	Type	Description
name	string	Resource name in format memos/{memo}
state	State	Protobuf enum mapping to RowStatus
creator	string	Resource reference: users/{user}
create_time	Timestamp	Protobuf timestamp (optional on creation)
update_time	Timestamp	Protobuf timestamp (optional on creation)
display_time	Timestamp	Display timestamp (configurable per instance)
content	string	Required markdown content
visibility	Visibility	Access control enum
tags	[]string	Output only - extracted from content
pinned	bool	Pin status
attachments	[]*Attachment	Optional file attachments
relations	[]*MemoRelation	Optional memo relations (references, comments)
reactions	[]*Reaction	Output only - emoji reactions
property	*Property	Output only - computed content properties
parent	*string	Output only - parent memo reference for comments
snippet	string	Output only - plain text snippet of content
location	*Location	Optional geographic location

Sources: proto/api/v1/memo_service.proto153-231 proto/gen/api/v1/memo_service.pb.go215-258

Memo Payload Structure

The MemoPayload contains structured data extracted during content processing:

Sources: store/memo.go51 server/runner/memopayload/memopayload.go

---

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
RowStatus	RowStatus	Account status: Normal or Archived
CreatedTs	int64	Unix timestamp of registration
UpdatedTs	int64	Unix timestamp of last update
Username	string	Unique username for login
Role	Role	User role: ADMIN or USER
Email	string	Email address
Nickname	string	Display name
PasswordHash	string	Bcrypt-hashed password
AvatarURL	string	Profile avatar URL
Description	string	User bio/description

Sources: store/user.go40-56

Special User: SystemBot

The system includes a predefined bot user for automated operations:

Sources: store/user.go26-38

---

Supporting Entities

Visibility Enum

The visibility system controls memo access:

Value	Description
PUBLIC	Visible to all users, including unauthenticated
PROTECTED	Visible to authenticated users only
PRIVATE	Visible only to creator and admins

Sources: store/memo.go12-33

RowStatus Enum

Entity lifecycle states:

Value	Description
Normal	Active entity
Archived	Soft-deleted entity

Role Enum

User permission levels:

Value	Description
ADMIN	Full system access, can manage all resources
USER	Standard user access, can manage own resources

Sources: store/user.go7-24

---

Entity Relationships

Sources: store/memo.go store/user.go server/router/api/v1/memo_service.go586-626

---

Store Abstraction Pattern

The store abstraction provides a database-agnostic interface for data operations.

Store Interface Structure

Sources: store/memo.go109-159 store/store.go

CRUD Operations

Create Operation

The CreateMemo method validates the UID format and delegates to the driver:

1. Validate UID format using base.UIDMatcher
2. Call driver.CreateMemo(ctx, create)
3. Driver executes database-specific INSERT
4. Return created memo with generated ID and timestamps

UID Validation: Must be 1-32 characters, alphanumeric and hyphens only, cannot start or end with hyphen.

Sources: store/memo.go109-114 server/router/api/v1/memo_service.go34-40

List Operation with Filtering

The ListMemos method supports complex filtering via the FindMemo struct:

FindMemo Field	Purpose
ID, UID	Exact match filters
IDList, UIDList	IN clause filters
CreatorID	Filter by creator
RowStatus	Filter by lifecycle state
VisibilityList	Filter by visibility levels
ExcludeContent	Performance optimization: skip content field
ExcludeComments	Filter out comment memos
Filters	CEL expression filters
Limit, Offset	Pagination
OrderByPinned	Sort pinned memos first
OrderByUpdatedTs	Sort by update time instead of create time
OrderByTimeAsc	Ascending time order

Sources: store/memo.go57-82

Update Operation

Updates use partial update semantics - only provided fields are modified:

Sources: store/memo.go93-103 server/router/api/v1/memo_service.go366-440

Delete Operation

Delete triggers cascading cleanup:

1. Delete memo_relation records (source and target)
2. Delete attachments linked to memo
3. Delete the memo itself

Sources: store/memo.go140-159

---

Database Driver Implementations

Each database driver implements the same interface but with database-specific SQL syntax.

SQLite Driver Characteristics

Feature	Implementation
Insert Return	Uses RETURNING clause to get generated values in single query
Placeholder	Uses ? for parameters
Timestamps	Stored as INTEGER (Unix timestamps)
JSON Payload	Stored as TEXT, marshaled via protojson
Comment Filtering	Uses WHERE parent_uid IS NULL

Example CreateMemo:

Sources: store/db/sqlite/memo.go16-52

MySQL Driver Characteristics

Feature	Implementation
Insert Return	Uses LastInsertId(), then queries for full record
Placeholder	Uses ? for parameters
Timestamps	Stored as DATETIME, converted via UNIX_TIMESTAMP() and FROM_UNIXTIME()
JSON Payload	Stored as TEXT/JSON column
Comment Filtering	Uses HAVING parent_uid IS NULL due to GROUP BY requirements

Example CreateMemo:

Sources: store/db/mysql/memo.go16-60

PostgreSQL Driver Characteristics

Feature	Implementation
Insert Return	Uses RETURNING clause like SQLite
Placeholder	Uses $1, $2, $3... numbered placeholders
Timestamps	Stored as BIGINT (Unix timestamps)
JSON Payload	Stored as TEXT, marshaled via protojson
Helper Function	placeholder(n) generates $n syntax

Example CreateMemo:

Sources: store/db/postgres/memo.go16-49

---

Query Construction and Ordering

Complex Ordering Logic

The parseMemoOrderBy method implements AIP-132 compliant ordering:

Supported fields:
- pinned: Always sorts DESC (pinned first)
- display_time: Maps to created_ts or updated_ts based on instance setting
- create_time: Explicitly sorts by created_ts
- update_time: Explicitly sorts by updated_ts  
- name: Alias for display_time

Default: "display_time desc"

Example: "pinned desc, display_time desc" results in:

1. Pinned memos first
2. Within each group, newest first by display time
3. ID as final tie-breaker

Sources: server/router/api/v1/memo_service.go791-844

Filter System

Memos support CEL (Common Expression Language) filters for complex queries:

Examples:
- creator_id == 1 || visibility in ["PUBLIC", "PROTECTED"]
- has_link == true && has_incomplete_tasks == true
- tag_search in ["work", "project"]

Sources: store/db/sqlite/memo.go57-63 server/router/api/v1/memo_service.go170-175

---

User Caching Layer

The store implements an in-memory cache for user data to reduce database queries:

Cache Key: User ID as string

Cache Updates: Automatic on CreateUser, UpdateUser, ListUsers, and GetUser operations.

Special Case: SystemBotID (0) always returns SystemBot without caching.

Sources: store/user.go92-157

---

Timestamp Handling

Display Time Configuration

Memos support configurable display timestamps via instance settings:

Setting	Behavior
DisplayWithUpdateTime = false	display_time maps to created_ts
DisplayWithUpdateTime = true	display_time maps to updated_ts

This affects:

• Memo ordering in lists
• Display time shown to users
• RSS feed publication dates

Sources: server/router/api/v1/memo_service.go54-64 server/router/api/v1/memo_service.go408-418

Custom Timestamp Creation

The API allows setting custom timestamps on memo creation:

Priority order:
1. Explicit create_time/update_time in request
2. display_time mapped based on instance setting
3. Server-generated current time (default)

Sources: server/router/api/v1/memo_service.go54-75 store/db/sqlite/memo.go29-39

---

Attachment and Relation Management

Attachment Linking

Attachments are managed via separate operations after memo creation:

1. CreateMemo() - Create base memo
2. SetMemoAttachments() - Link attachments by memo ID
3. Attachments stored in 'attachment' table with memo_id FK

Sources: server/router/api/v1/memo_service.go106-124

Memo Relations

Relations connect memos for two purposes:

Relation Type	Purpose	Implementation
REFERENCE	Memo references another memo	Stored in memo_relation table
COMMENT	Memo is a comment on another memo	Creates parent-child relationship

Comment Implementation:

1. Create comment as regular memo
2. Insert memo_relation record with type=COMMENT
3. Parent memo UID populated via JOIN in queries

Sources: server/router/api/v1/memo_service.go125-133 server/router/api/v1/memo_service.go586-595 store/db/sqlite/memo.go132-141

---

Protocol Buffer Message Definitions

Message Hierarchy

Sources: proto/api/v1/memo_service.proto153-385

Field Masks for Partial Updates

The UpdateMemoRequest uses Protocol Buffer field masks to specify which fields to update:

Supported update paths:
- content: Updates content and rebuilds payload
- visibility: Changes access control
- pinned: Toggles pin status
- state: Archives/unarchives memo
- create_time: Modifies creation timestamp
- update_time: Modifies update timestamp
- display_time: Updates display time
- location: Changes geographic location
- attachments: Replaces attachment list
- relations: Replaces relation list

Sources: server/router/api/v1/memo_service.go369-439 proto/api/v1/memo_service.proto302-309

---

Data Validation and Constraints

UID Format Validation

All memo UIDs must match the base.UIDMatcher pattern:

• Length: 1-32 characters
• Characters: Alphanumeric and hyphens only
• Constraints: Cannot start or end with hyphen

Sources: store/memo.go110-112 server/router/api/v1/memo_service.go37-39

Unique Constraints

Database-level unique constraints:

• memo.uid: Must be unique across all memos
• user.username: Must be unique across all users
• Duplicate inserts return appropriate error codes

Sources: server/router/api/v1/memo_service.go96-103

Content Length Limits

Memo content length is configurable per instance via InstanceMemoRelatedSetting:

Default: Configurable limit
Validation: Enforced on create and update
Error: Returns InvalidArgument with limit in message

Sources: server/router/api/v1/memo_service.go80-86 server/router/api/v1/memo_service.go371-377