Database Schema

PostgreSQL database schema for TMA Cloud.

TablesCopy Anchor Link

usersCopy Anchor Link

User accounts and sub-users.

Sub-users: A row with parent_user_id set is a sub-user of that account. The account any row belongs to is COALESCE(parent_user_id, id), which is the value stored in files.user_id.

Constraints:

• users_permissions_valid — every entry in permissions must be a known key (files.download, files.upload, files.edit, files.share, files.delete, files.trash)
• users_owner_has_no_permissions — owners keep an empty permissions array; they are never checked against it
• users_parent_not_self — a row cannot be its own parent

Triggers:

• trg_users_reject_nested_sub_user — refuses an insert or update whose parent is itself a sub-user, so sub-users cannot own sub-users
• trg_users_reject_demoting_parent — refuses turning an owner that already has sub-users into a sub-user

Indexes: Partial index on parent_user_id where parent_user_id IS NOT NULL

storage_reservationsCopy Anchor Link

Short-lived quota reservations for object-store work that cannot hold a database transaction open.

Statement-level insert and delete triggers maintain users.storage_reserved. The worker removes expired rows hourly in bounded batches.

filesCopy Anchor Link

Files and folders.

Indexes: user_id, parent_id, created_at, unique (path, user_id, type) where path IS NOT NULL (also used for lookups by path), (user_id, accessed_at DESC) where deleted_at IS NULL, dek_kek_version for encrypted files, (deleted_at, id) for bounded trash cleanup, covering indexes for active-file statistics and storage-usage reconciliation, and folder-list page indexes for name, modified time and last access. Listings sorted by size read the folder through one of those and sort it. No index holds the aggregate columns, so updating a folder's totals can be an in-place (HOT) update; the table's fillfactor is 90 to leave room on each page for them (migration 068_hot_folder_aggregates).

Folder totals and usage: files_aggregate_insert and files_aggregate_delete are statement-level triggers: they group the statement's rows by parent and add each group's totals to every ancestor folder once, so an upload batch, copy or permanent delete of many items updates each ancestor once rather than once per item. Moves (files_aggregate_update) are handled row by row. files_storage_insert and files_storage_delete keep users.storage_used per statement; files_storage_update is a row trigger that fires only when size, type or user_id changes, so starring, sharing and access-time updates cost nothing extra.

Hierarchy constraint: files_parent_not_self rejects a row whose parent_id equals its own id. Move operations also reject indirect descendant cycles, and recursive reads keep a visited-ID path so legacy bad data cannot loop forever.

Parent must be a folder: a parent is always a folder of the same account. Upload, bulk upload, folder creation, copy and move check that it is a live folder before writing, and answer 404 Target folder not found otherwise. The database enforces the type and account part: files_parent_insert and files_parent_update reject a row whose parent is a file or belongs to another account, and files_children_update refuses to change the type or account of a folder that still has children. They are row-level AFTER triggers, so a copy that inserts a folder and its children in one statement passes. Their WHEN clauses skip updates that leave parent_id, user_id and type alone. Migration 054_enforce_folder_parents moved rows an earlier paste had stored under a file up to their nearest folder, adding a (n) suffix on a name clash as a move does.

Triggers: files_storage_insert, files_storage_update, and files_storage_delete update the account owner's storage_used counter once per statement from transition tables. files_aggregate_insert, files_aggregate_update, and files_aggregate_delete adjust ancestor folder totals when a row is inserted, moved, resized, changes type, or is removed. files_changes_insert, files_changes_update and files_changes_delete record the folders whose listing changed in file_changes.

Lock order: every write locks its account's users row, then the folders whose totals change by id, then updates them. The files_a_lock_accounts_* triggers fire before the totals and storage triggers (triggers run in name order), and write transactions that lock rows themselves lock the account first. Row locks that do not change a key use FOR NO KEY UPDATE, so they do not block foreign-key checks. Before migration 066_lock_folders_in_order, writes locked folders in whatever order their walk produced and deadlocked under load. Access-time flushes skip rows a write holds (SKIP LOCKED) instead of waiting.

Aggregate columns: These three columns are the only source of a folder's size and counts; no read path recomputes them. Incremental trigger maintenance can drift, because a delta that would take a counter below zero is clamped rather than carried. reconcile_folder_aggregates(user_id) recomputes them for one account from its own rows and returns how many folders were stored wrong. The worker runs it weekly. Trashed descendants are counted, matching the triggers.

Name search is backed by a trigram GIN index from pg_trgm on lower(name) and a text_pattern_ops btree on lower(name) for prefix matching — not a PostgreSQL full-text index.

File timestamps: modified is the file's own timestamp — uploads and copies preserve the client's original mtime, so it can be years old on a row written seconds ago. created_at is when the row was written and is what orphan detection uses to tell an in-flight write from an orphan. accessed_at is when the item was last read, and accessed_by which signed-in user read it: the owner or a sub-user, or null for a share-link visitor. shared_at is set when an item joins a share and cleared when it is unshared. Re-sharing an active item does not change it. Renames and moves change neither path nor created_at, and they do not count as reads, so accessed_at is left alone as well. Reading an item never changes modified.

accessed_at precision: The value is written at most once per update window per item, one hour by default, so it can lag a read by up to that long. This follows NTFS, which guarantees its last-access time only to within an hour, and Linux's relatime. Writes are buffered in memory and flushed in batches, so a read never waits on the update. See File System.

file_locksCopy Anchor Link

Edit locks: who is editing a file in a desktop app or in OnlyOffice right now. A lock is a lease; an expired row counts as free and is replaced by the next lock.

file_changesCopy Anchor Link

The change feed behind GET /api/files/changes: one row per folder whose listing a statement changed.

Inserts and deletes on files add one row per folder per statement. Updates add rows only when a column a listing shows changes (name, folder, trash state, type, size, modified time, content version, MIME type, owner), so folder totals and access times write nothing here. Taking, releasing or handing over an edit lock adds a row for the file's folder; renewing one does not.

file_change_floorCopy Anchor Link

One row: the newest transaction whose changes were pruned. A cursor at or below it may have missed changes, so its client starts over with a full resync.

share_linksCopy Anchor Link

Share link metadata.

Indexes: file_id; partial index on expires_at where expires_at IS NOT NULL

user_id is always the owner of file_id. Links are only created for the caller's own files, and the public lookup joins on files.user_id = share_links.user_id. Migration 048_remove_cross_owner_share_links deletes rows that break this rule.

share_link_filesCopy Anchor Link

Junction table linking share links to files.

Primary Key: (share_id, file_id)

Indexes: file_id

Note the column is share_id, not share_link_id. Both foreign keys cascade on delete.

app_settingsCopy Anchor Link

Application-wide settings.

The table holds exactly one row, keyed 'app_settings'. first_user_id has a RESTRICT foreign key, so the first user cannot be deleted while the row references them. The app_settings_storage_complete check constraint requires the storage_* columns other than storage_updated_at and storage_config_version to be all set or all null. The app_settings_activity_ranges check constraint holds the session and access-time columns to their ranges.

kek_checksCopy Anchor Link

One key check value per master key (KEK) version. See Security Model.

A row is written the first time a key version passes the startup check. Deleting a row makes the next startup verify that key against stored data again.

sessionsCopy Anchor Link

Active user sessions.

Indexes: (user_id, token_version), last_activity

A session is invalid once its token_version falls behind the user's current one, which is how "logout everywhere" and a password change end every session at once.

Retention: rows older than 30 days are removed in bounded batches by the daily 04:20 UTC worker job.

file_operation_resultsCopy Anchor Link

Idempotency results for durable file operations.

Indexes: completed_at

The copy transaction writes its result with the new file rows. If the queue retries the same job after that commit, the worker returns this result instead of inserting another copy. Rows older than 30 days are removed in bounded batches by the daily 04:50 UTC worker job.

mfa_backup_codesCopy Anchor Link

One row per single-use MFA backup code.

Codes are stored hashed, never in plain text, so a lost code cannot be recovered — only replaced. Ten rows are written when MFA is enabled and again on each regeneration, which first deletes the old set. Disabling MFA deletes all of a user's rows.

Indexes: user_id; partial index on (user_id, used) where used = FALSE

client_heartbeatsCopy Anchor Link

Browser-session presence and desktop app heartbeat records.

Indexes: user_id. The table holds one row per desktop install or web session, so last_seen_at is left unindexed and each heartbeat update stays a cheap in-place (HOT) update.

The Active Sessions list treats a session as online only when a matching row was seen in the last three minutes. Browser presence rows use web for app_version and platform; the Active Desktop Clients admin list excludes them.

audit_logCopy Anchor Link

Audit trail events.

user_id is always the login that acted. For a sub-user, account_owner_id is the owner whose files were touched; for an owner the two match.

Indexes: (user_id, created_at DESC) where user_id IS NOT NULL, (account_owner_id, created_at DESC), action, created_at DESC, request_id, (resource_type, resource_id) where both are set, and (created_at DESC, action, error_message) where status is failure or error. There is no general metadata GIN index; application queries use the indexed columns, avoiding the write cost of maintaining an index for administrative JSON searches.

Retention: rows older than 30 days are removed by the daily 03:40 UTC worker job. cleanup_old_audit_logs(30) deletes at most 10,000 rows per call, and one job processes at most 20 batches.

job_queueCopy Anchor Link

Background jobs (migration 055). Workers claim rows with FOR UPDATE SKIP LOCKED and are woken by NOTIFY tma_jobs. See Background Workers.

Indexes: ready jobs per queue (queue, created_at) where state = 'created'; (queue, singleton_key, state) for keyed jobs; started_at of active jobs; completed_at of finished jobs.

job_schedulesCopy Anchor Link

Cron schedules: the maintenance jobs and one row per open OnlyOffice document (onlyoffice:<document key>).

bulk_import_itemsCopy Anchor Link

Durable rollback records used by tma-server import. Each row records the import run, created file or folder ID, storage key when present, item type, and tree depth. A completed import removes its rows. A failed import consumes them in batches, deleting files before folders. Only a killed process leaves rows behind; a daily worker job removes rows older than seven days.

migrationsCopy Anchor Link

Migration tracking.

Applied versions are not run again. Changes needed by an existing installation must be shipped in a new forward migration rather than only editing an older file.

Each migration and its migrations row are committed in one transaction, so a crash cannot leave a migration applied but unrecorded. An advisory lock keeps two instances from applying migrations at the same time.

ViewsCopy Anchor Link

audit_activityCopy Anchor Link

Read-only view over audit_log that resolves user IDs to names and emails, so the trail can be read without writing joins.

All other columns are passed through from audit_log.

SELECT created_at, actor_email, actor_role, action, resource_id
FROM audit_activity
WHERE account_email = 'owner@example.com'
ORDER BY created_at DESC
LIMIT 100;

RelationshipsCopy Anchor Link

• User → Sub-users (parent-child, self-referential via parent_user_id, CASCADE)
• User → Files (one-to-many, CASCADE)
• File → Files (parent-child, self-referential, no cascade: deletes remove the deepest rows first, so a folder goes only after its contents)
• User → Share Links (one-to-many, CASCADE)
• Share Link → Files (many-to-many via share_link_files)
• User → Sessions (one-to-many, CASCADE)
• User → File Operation Results (logical account ownership through user_id)
• User → MFA Backup Codes (one-to-many, CASCADE)
• User → Client Heartbeats (one-to-many, CASCADE)
• User → Audit Log (one-to-many, SET NULL — on both user_id and account_owner_id)

Deleting an owner cascades to its sub-users. Deleting a sub-user removes only that login; the account's files stay because they are stored under the owner's ID.

Common QueriesCopy Anchor Link

List account files:

$1 is the account ID — the owner's ID, not the sub-user's.

SELECT * FROM files
WHERE user_id = $1 AND parent_id = $2 AND deleted_at IS NULL
ORDER BY type, name;

Resolve the account for a login:

SELECT COALESCE(parent_user_id, id) AS account_id, permissions
FROM users
WHERE id = $1;

List an owner's sub-users:

SELECT id, email, name, permissions, created_at
FROM users
WHERE parent_user_id = $1
ORDER BY created_at;

Search files:

This is the query used for search terms of three characters or more. Terms of one or two characters take a prefix-only branch instead, skipping the trigram similarity work.

SELECT * FROM files
WHERE user_id = $1 AND deleted_at IS NULL
  AND (
    lower(name) LIKE lower($2) || '%'
    OR lower(name) % lower($2)
  )
ORDER BY
  CASE
    WHEN lower(name) = lower($2) THEN 1
    WHEN lower(name) LIKE lower($2) || '%' THEN 2
    ELSE 3
  END ASC,
  similarity(lower(name), lower($2)) DESC NULLS LAST,
  modified DESC;

Query audit log:

SELECT action, status, metadata, created_at
FROM audit_log
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 100;

Query everything done under one account, including its sub-users:

SELECT created_at, user_id, actor_role, action, resource_id
FROM audit_log
WHERE account_owner_id = $1
ORDER BY created_at DESC
LIMIT 100;

Related TopicsCopy Anchor Link

• Architecture - System architecture
• Audit Events - Audit event types