foresle/notgram
1
0
Fork 0
Code Issues Projects 1 Releases 2 Wiki Activity Actions

Database layer and migrations #3

New issue
Closed
opened 2026-06-13 14:53:14 +00:00 by foresle · 3 comments
foresle commented 2026-06-13 14:53:14 +00:00
Owner
Copy link

The SQLite store shared by both services.

Scope:

  • SQLite access through sqlx.
  • Migrations kept as versioned SQL files embedded in the binary, applied on startup by the ingestion service.
  • Schema covering the stored data: channels (Telegram identity, username, title, first seen), posts (channel, Telegram message id, posted and edited times, text in raw Telegram format, album membership), media (object reference, type, size, image dimensions or duration, content hash).
  • Content-addressed media so identical files are stored once.

Notes:

  • The database runs in WAL mode so the single writer (ingestion) and the reader (feed) do not block each other. The ingestion service enables WAL and must start first. A busy timeout is set on every connection.
  • The feed service only reads and issues no writes. Read-only here means behaviour, not a read-only mount: the process still needs filesystem write access to the WAL index files.
  • The database file lives on a volume shared by both containers on the same host, because SQLite file locking is not reliable over a network filesystem.
The SQLite store shared by both services. Scope: - SQLite access through `sqlx`. - Migrations kept as versioned SQL files embedded in the binary, applied on startup by the ingestion service. - Schema covering the stored data: channels (Telegram identity, username, title, first seen), posts (channel, Telegram message id, posted and edited times, text in raw Telegram format, album membership), media (object reference, type, size, image dimensions or duration, content hash). - Content-addressed media so identical files are stored once. Notes: - The database runs in WAL mode so the single writer (ingestion) and the reader (feed) do not block each other. The ingestion service enables WAL and must start first. A busy timeout is set on every connection. - The feed service only reads and issues no writes. Read-only here means behaviour, not a read-only mount: the process still needs filesystem write access to the WAL index files. - The database file lives on a volume shared by both containers on the same host, because SQLite file locking is not reliable over a network filesystem.
foresle added this to the 0.1.0 milestone 2026-06-13 14:53:14 +00:00
foresle self-assigned this 2026-06-13 14:53:14 +00:00
foresle added this to the Developing project 2026-06-13 14:53:14 +00:00
foresle added the due date 2026-06-13 2026-06-13 15:50:55 +00:00
foresle added reference dev 2026-06-14 11:20:05 +00:00
foresle modified the due date from 2026-06-13 to 2026-06-22 2026-06-14 11:20:20 +00:00
foresle stopped working 2026-06-18 12:42:54 +00:00
22 minutes 20 seconds
foresle stopped working 2026-06-18 17:47:46 +00:00
14 minutes 44 seconds
foresle stopped working 2026-06-19 15:31:00 +00:00
28 minutes 36 seconds
foresle commented 2026-06-21 14:59:12 +00:00
Author
Owner
Copy link

I chose to use a Postgres database because of its native support in sqlx and the conflict with grammers caused by both statically linking the libsql library.

I chose to use a Postgres database because of its native support in sqlx and the conflict with grammers caused by both statically linking the libsql library.
foresle commented 2026-06-21 18:08:41 +00:00
Author
Owner
Copy link

Migrations are no longer applied on startup. They run on demand through a dedicated migrate command.

Migrations are no longer applied on startup. They run on demand through a dedicated `migrate` command.
foresle stopped working 2026-06-21 18:08:44 +00:00
3 hours 17 minutes
foresle commented 2026-06-21 18:30:11 +00:00
Author
Owner
Copy link

Migrations are applied automatically at the start of every command that requires an up-to-date database.

Migrations are applied automatically at the start of every command that requires an up-to-date database.
Sign in to join this conversation.
dev
Branches Tags
Clear current reference
dev
Clear current reference
1.0.0
1.0.0-rc1
add_atom_feed
add_media_storage_test
fix_deploy
deploy
test_release
Labels
Clear labels   
bug

Something is not working

  
duplicate

This issue or pull request already exists

  
enhancement

New feature

  
help wanted

Need some help

  
invalid

Something is wrong

  
question

More information is needed

  
wontfix

This won't be fixed

No labels
bug
duplicate
enhancement
help wanted
invalid
question
wontfix
Milestone
Clear milestone
No items
No milestone
0.1.0
Projects
Clear projects
No items
No project
Developing
Assignees
Clear assignees
No assignees
foresle
1 participant
Notifications
Total time spent: 4 hours 23 minutes
foresle
4 hours 23 minutes
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

2026-06-22

Depends on
#1 Project scaffold and CLI skeleton
foresle/notgram
#2 Configuration loading
foresle/notgram
Reference
foresle/notgram#3
No description provided.