Database Schema

PostgreSQL database schema for TMA Cloud.

Tables

users

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_reservations

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.

files

Files and folders.

Indexes: user_id, parent_id, deleted_at, created_at, partial index and unique index on path where path IS NOT NULL, (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, last access, and effective size.

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.

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.

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. 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 hour per item, 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.

share_links

Share link metadata.

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

share_link_files

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_settings

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.

sessions

Active user sessions.

Indexes: (user_id, created_at DESC), (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 02:45 UTC worker job.

file_operation_results

Idempotency results for durable file operations.

Indexes: completed_at

The copy transaction writes its result with the new file rows. If pg-boss 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 02:50 UTC worker job.

mfa_backup_codes

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_heartbeats

Browser-session presence and Electron desktop client heartbeat records.

Indexes: user_id, last_seen_at

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_log

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 02:15 UTC worker job. cleanup_old_audit_logs(30) deletes at most 10,000 rows per call, and one job processes at most 20 batches.

Views

audit_activity

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;

pgboss.*

pg-boss job queue tables (managed automatically).

bulk_import_items

Durable rollback records used by the administrative bulk-import scripts. 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.

migrations

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.

Relationships

• User → Sub-users (parent-child, self-referential via parent_user_id, CASCADE)
• User → Files (one-to-many, CASCADE)
• File → Files (parent-child, self-referential, CASCADE)
• 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 Queries

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 Topics

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