Database View
The Database View describes the persistence layer of the fla-- application, including the relational schema, entity relationships, and how each table supports the application's functionality.
The database consists of two groups of tables: application tables (user_files, file_versions) that support the document management system, and authentication tables (user, account, session, verification) managed by Better-Auth.
ER Diagram
Table Descriptions
Application Tables
user_files
Stores metadata for each Turing Machine document saved by a user. Each row represents a distinct document (e.g. a saved TM configuration). The user_id foreign key links the file to its owner in the user table. When a user saves changes to a document, a new entry is created in file_versions rather than overwriting the existing file, enabling version history.
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique file identifier |
user_id | string (FK) | Owner of the file |
filename | string | Display name of the document |
created_at | datetime | File creation timestamp |
updated_at | datetime | Last modification timestamp |
file_versions
Tracks every saved version of a user_files document. Each version records where the file is stored externally (storage_path), its MIME type, and size. The application retains only the last four versions per file, pruning older entries to limit storage growth. The actual file content is stored on an external file storage service (referenced via FILE_SERVER_URL), not in the database.
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique version identifier |
file_id | string (FK) | Parent file in user_files |
version_number | int | Sequential version number |
storage_path | string | Path on external file storage |
mime_type | string | Content type (e.g. application/json) |
size_bytes | int | File size in bytes |
created_at | datetime | Version creation timestamp |
Authentication Tables (Better-Auth)
These tables are managed by the Better-Auth library and handle user identity, authentication sessions, and email verification.
user
The central identity table. Each row represents a registered user of the application. The emailVerified flag is set when the user completes the email verification flow (tracked via the verification table).
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique user identifier |
name | string | Display name |
email | string | Login email address |
emailVerified | boolean | Whether email has been verified |
image | string | Avatar URL |
createdAt | datetime | Account creation timestamp |
updatedAt | datetime | Last profile update timestamp |
account
Stores authentication provider credentials for each user. Supports multiple providers per user (e.g. Google OAuth, email/password). The providerId field identifies which authentication service the account belongs to. OAuth tokens (accessToken, refreshToken, idToken) are stored here with their expiry timestamps.
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique account identifier |
accountId | string | Provider-specific account ID |
providerId | string | Auth provider identifier |
userId | string (FK) | Associated user |
accessToken | string | OAuth access token |
refreshToken | string | OAuth refresh token |
idToken | string | OIDC identity token |
accessTokenExpiresAt | datetime | Access token expiry |
refreshTokenExpiresAt | datetime | Refresh token expiry |
scope | string | Granted OAuth scopes |
password | string | Hashed password (email auth) |
createdAt | datetime | Account link timestamp |
updatedAt | datetime | Last update timestamp |
issuer | string | Token issuer URL |
session
Tracks active login sessions. Each session has a unique token used for authentication and an expiresAt timestamp. The ipAddress and userAgent fields provide audit information. Expired sessions are invalidated server-side.
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique session identifier |
expiresAt | datetime | Session expiry timestamp |
token | string | Session authentication token |
createdAt | datetime | Session creation timestamp |
updatedAt | datetime | Last activity timestamp |
ipAddress | string | Client IP at session start |
userAgent | string | Client browser/device info |
userId | string (FK) | Associated user |
verification
Stores email verification tokens. When a user registers, a verification record is created with a unique identifier (the email address) and a value (the verification token). The record expires after a set period, after which the user must request a new verification email.
| Column | Type | Description |
|---|---|---|
id | string (PK) | Unique verification identifier |
identifier | string | Email address being verified |
value | string | Verification token |
expiresAt | datetime | Token expiry timestamp |
createdAt | datetime | Record creation timestamp |
updatedAt | datetime | Last update timestamp |
Relationship Summary
user (1) ──< (N) user_files ──< (N) file_versions
user (1) ─< (N) session
user (1) ──< (N) account
user (1) ──< (1) verification
- A user owns many user_files (their saved TM documents).
- Each user_file has many file_versions (version history, pruned to the last 4).
- A user has many sessions (active logins across devices).
- A user has many accounts (one per auth provider, e.g. Google + email/password).
- A user has at most one pending verification record at a time.
Original Schema Diagram
The ER diagram exported from the database design tool is available here: