Skip to content

Database Schema ​

SkySend uses SQLite with Drizzle ORM. The database file is located at data/db/skysend.db.

SQLite Configuration ​

sql
PRAGMA journal_mode = WAL;        -- Concurrent reads + serialized writes
PRAGMA busy_timeout = 5000;       -- Wait up to 5s on lock contention
PRAGMA synchronous = NORMAL;      -- Safe with WAL, better write performance
PRAGMA foreign_keys = ON;         -- Enforce referential integrity

WAL (Write-Ahead Logging) mode allows concurrent reads while writes are serialized. This is more than sufficient for a single-instance self-hosted service.

Tables ​

uploads ​

ColumnTypeDefaultDescription
idTEXT (PK)-UUID v4
ownerTokenTEXT NOT NULL-Base64url-encoded owner token
authTokenTEXT NOT NULL-Base64url-encoded auth token
saltBLOB NOT NULL-HKDF salt (16 bytes)
encryptedMetaBLOBNULLAES-256-GCM encrypted metadata
nonceBLOBNULLMetadata IV (12 bytes)
sizeINTEGER NOT NULL-Total payload size in bytes
fileCountINTEGER1Number of files (1 = single, >1 = archive)
hasPasswordINTEGER0Whether password protection is active
passwordSaltBLOBNULLPassword KDF salt (16 bytes)
passwordAlgoTEXTNULL"argon2id-v2"
maxDownloadsINTEGER NOT NULL-Maximum allowed downloads
downloadCountINTEGER0Current download count
expiresAtTIMESTAMP NOT NULL-Expiry timestamp (Unix epoch)
createdAtTIMESTAMPcurrent_unix_timeCreation timestamp
storagePathTEXT NOT NULL-Filename on disk (UUID.bin)

Indexes ​

IndexColumnPurpose
idx_uploads_expires_atexpiresAtEfficient cleanup queries

notes ​

ColumnTypeDefaultDescription
idTEXT (PK)-UUID v4
ownerTokenTEXT NOT NULL-Base64url-encoded owner token
authTokenTEXT NOT NULL-Base64url-encoded auth token
saltBLOB NOT NULL-HKDF salt (16 bytes)
encryptedContentBLOB NOT NULL-AES-256-GCM encrypted note content
nonceBLOB NOT NULL-AES-GCM IV (12 bytes)
contentTypeTEXT NOT NULL-"blocks", or for a note from before v3 "text", "password", "code", "markdown" or "sshkey"
hasPasswordINTEGER NOT NULL0Whether password protection is active
passwordSaltBLOBNULLPassword KDF salt (16 bytes)
passwordAlgoTEXTNULL"argon2id-v2"
maxViewsINTEGER NOT NULL-Maximum allowed views (0 = unlimited)
viewCountINTEGER NOT NULL0Current view count
expiresAtTIMESTAMP NOT NULL-Expiry timestamp (Unix epoch)
createdAtTIMESTAMP NOT NULLcurrent_unix_timeCreation timestamp

Indexes (notes) ​

IndexColumnPurpose
idx_notes_expires_atexpiresAtEfficient cleanup queries

file_requests ​

ColumnTypeDefaultDescription
idTEXT (PK)-UUID v4
vaultBLOB NOT NULL-The requester's private key, sealed with a key from the inbox link (146 bytes)
vaultNonceBLOB NOT NULL-Vault IV (12 bytes)
inboxAuthTokenTEXT NOT NULL-SHA-256 of the token from the inbox link for reading, base64url
inboxOwnerTokenTEXT NOT NULL-SHA-256 of the token from the inbox link for managing, base64url
uploadTokenTEXT NOT NULL-SHA-256 of the token from the upload link, base64url
briefCiphertextBLOBNULLAES-256-GCM encrypted brief: title, what is asked for, note template. Set for every request, nullable because migration 0004 renamed the title columns.
briefNonceBLOBNULLBrief IV (12 bytes)
hasPasswordINTEGER NOT NULL0Whether the inbox link is password protected
maxUploadsINTEGER NOT NULL-Uploads the request takes
maxSizeINTEGER NOT NULL-Most bytes one upload may have
downloadsINTEGER NOT NULL5How often the requester can download each upload. Rows from before migration 0005 got 5.
reservedUploadsINTEGER NOT NULL0Uploads running or finished. Set back to finishedUploads at startup.
finishedUploadsINTEGER NOT NULL0Uploads that finished. Never goes down, so a deleted upload keeps its slot.
finishedBytesINTEGER NOT NULL0Bytes of uploads that finished
closedINTEGER NOT NULL0Closed by the requester
closesAtTIMESTAMP NOT NULL-When the request stops taking uploads
createdAtTIMESTAMP NOT NULLcurrent_unix_timeCreation timestamp

The public key of a request is never stored. It only exists in the upload link and inside the vault.

request_uploads ​

ColumnTypeDefaultDescription
idTEXT (PK)-UUID v4
requestIdTEXT NOT NULL-The request, deleted with it (ON DELETE CASCADE)
sizeINTEGER NOT NULL-Encrypted size in bytes
fileCountINTEGER NOT NULL1Number of files (1 = single, >1 = archive)
saltBLOB NOT NULL-HKDF salt of the file (32 bytes)
wrapEncBLOB NOT NULL-HPKE encapsulated key (65 bytes)
wrapCiphertextBLOB NOT NULL-The file secret sealed to the request's public key (48 bytes)
encryptedMetaBLOB NOT NULL-AES-256-GCM encrypted metadata
metaNonceBLOB NOT NULL-Metadata IV (12 bytes)
maxDownloadsINTEGER NOT NULL-The downloads of its request at the time of the upload
downloadCountINTEGER NOT NULL0Current download count
expiresAtTIMESTAMP NOT NULL-FILE_REQUEST_RETENTION_SEC after the upload arrived
createdAtTIMESTAMP NOT NULLcurrent_unix_timeCreation timestamp
storagePathTEXT NOT NULL-Filename on disk (UUID.bin)

Files of a request live in this table and never in uploads, so no route of a normal upload can serve one.

Indexes (file requests) ​

IndexColumnPurpose
idx_file_requests_closes_atclosesAtCleanup of requests that are over
idx_request_uploads_request_idrequestIdListing an inbox
idx_request_uploads_expires_atexpiresAtEfficient cleanup queries

Concurrency ​

Download and view count updates are atomic SQL operations that hold the write lock for microseconds. WAL mode allows thousands of such writes per second while reads are never blocked.

The download route uses an atomic SQL update with a WHERE condition to prevent race conditions:

sql
UPDATE uploads
SET downloadCount = downloadCount + 1
WHERE id = ? AND downloadCount < maxDownloads

The note view route uses the same pattern:

sql
UPDATE notes
SET view_count = view_count + 1
WHERE id = ? AND (max_views = 0 OR view_count < max_views)

If the limit is already reached, the update affects zero rows and the request is rejected. For notes with max_views = 0 (unlimited), the view count is always incremented.

Migrations ​

Database migrations are managed by Drizzle ORM and stored in apps/server/src/db/migrations/. Migrations run automatically on server startup.