Database Schema

PostgreSQL database schema for TMA Cloud.

Tables

`users`

User accounts.

Column	Type	Description
`id`	VARCHAR(255)	Primary key
`email`	VARCHAR(255)	Unique, not null
`password`	VARCHAR(255)	Hashed (nullable for OAuth)
`name`	VARCHAR(255)	Display name
`google_id`	VARCHAR(255)	Unique (optional)
`mfa_enabled`	BOOLEAN	Default false
`mfa_secret`	TEXT	TOTP secret (nullable)
`token_version`	INTEGER	Token version for revocation
`last_token_invalidation`	TIMESTAMP	Last token invalidation time
`storage_limit`	BIGINT	Storage limit (nullable, bytes)
`created_at`	TIMESTAMPTZ	Default now()
`updated_at`	TIMESTAMPTZ	Default now()

`files`

Files and folders.

Column	Type	Description
`id`	VARCHAR(255)	Primary key
`name`	VARCHAR(255)	Not null
`type`	VARCHAR(50)	'file' or 'folder'
`size`	BIGINT	File size in bytes
`mime_type`	VARCHAR(255)	MIME type
`user_id`	VARCHAR(255)	FK → users.id
`parent_id`	VARCHAR(255)	FK → files.id (null for root)
`path`	TEXT	Full path
`starred`	BOOLEAN	Default false
`deleted_at`	TIMESTAMPTZ	Soft delete timestamp
`modified`	TIMESTAMPTZ	Last modification time

Indexes: user_id, parent_id, path, deleted_at, full-text on name

`share_links`

Share link metadata.

Column	Type	Description
`id`	VARCHAR(255)	Primary key (used as token)
`file_id`	VARCHAR(255)	FK → files.id
`user_id`	VARCHAR(255)	FK → users.id
`expires_at`	TIMESTAMPTZ	Expiration (null = no expiration)
`created_at`	TIMESTAMPTZ	Default now()

Indexes: Partial index on expires_at where expires_at IS NOT NULL

`share_link_files`

Junction table linking share links to files.

Column	Type	Description
`share_link_id`	VARCHAR(255)	FK → share_links.id
`file_id`	VARCHAR(255)	FK → files.id
`created_at`	TIMESTAMPTZ	Default now()

Primary Key: (share_link_id, file_id)

`app_settings`

Application-wide settings.

Column	Type	Description
`id`	TEXT	Primary key (always 'app_settings')
`signup_enabled`	BOOLEAN	Default true
`first_user_id`	TEXT	FK → users.id (immutable)
`share_base_url`	TEXT	Custom share link base URL (null = use request origin)
`max_upload_size_bytes`	BIGINT	Max single-file upload size in bytes (default 10737418240 = 10 GB)
`hide_file_extensions`	BOOLEAN	When true, file names are shown without extensions (default false)
`require_electron_client`	BOOLEAN	When true, only desktop app is allowed to use (default false)
`updated_at`	TIMESTAMPTZ	Default now()

`sessions`

Active user sessions.

Column	Type	Description
`id`	TEXT	Primary key
`user_id`	TEXT	FK → users.id
`token_version`	INTEGER	Token version when created
`user_agent`	TEXT	Browser user agent
`ip_address`	INET	Client IP
`created_at`	TIMESTAMPTZ	Default now()
`last_activity`	TIMESTAMPTZ	Default now() (updates on each request)

Indexes: (user_id, created_at DESC), (user_id, token_version), last_activity

`client_heartbeats`

Active Electron desktop client heartbeat records.

Column	Type	Description
`id`	VARCHAR(64)	Primary key
`user_id`	VARCHAR(255)	FK → users.id
`session_id`	VARCHAR(255)	JWT session ID (nullable)
`app_version`	VARCHAR(64)	Electron app version
`platform`	VARCHAR(64)	Client platform (`win32`, etc.)
`user_agent`	TEXT	Electron request user agent
`ip_address`	VARCHAR(45)	Client IP
`last_seen_at`	TIMESTAMPTZ	Last heartbeat timestamp
`created_at`	TIMESTAMPTZ	First heartbeat timestamp

Indexes: user_id, last_seen_at

`audit_logs`

Audit trail events.

Column	Type	Description
`id`	SERIAL	Primary key
`event_type`	VARCHAR(100)	Event type
`user_id`	VARCHAR(16)	FK → users.id (nullable)
`status`	VARCHAR(20)	'success' or 'failure'
`resource_type`	VARCHAR(50)	Resource type
`resource_id`	VARCHAR(255)	Resource ID
`ip_address`	INET	Client IP
`user_agent`	TEXT	Browser user agent
`metadata`	JSONB	Event-specific data
`created_at`	TIMESTAMP	Default CURRENT_TIMESTAMP

Indexes: user_id, event_type, created_at, (resource_type, resource_id)

`pgboss.*`

pg-boss job queue tables (managed automatically).

`migrations`

Migration tracking.

Column	Type	Description
`version`	VARCHAR(255)	Primary key
`applied_at`	TIMESTAMPTZ	Default now()

Relationships

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 → Client Heartbeats (one-to-many, CASCADE)
User → Audit Logs (one-to-many, SET NULL)

Common Queries

List user files:

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

Search files:

SELECT * FROM files
WHERE user_id = $1 AND deleted_at IS NULL
  AND (
    lower(name) LIKE lower($2) || '%'
    OR (lower(name) LIKE '%' || lower($2) || '%'
        AND similarity(lower(name), lower($2)) > 0.15)
  )
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 logs:

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

Architecture - System architecture
Audit Events - Audit event types

On this page