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 application tables (user_files, file_versions, contributors) for document management and sharing, 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 |
contributors
Associates an invited user with a saved document. The file owner adds or removes contributor rows; owners and contributors pass the editor's collaboration access check.
| Column | Type | Description |
|---|---|---|
file_id | string (FK) | Shared file in user_files |
user_id | string (FK) | User granted collaborative access |
added_at | datetime | When access was granted |
Authentication Tables (Better-Auth)
These tables are managed by Better-Auth and handle user identity, OAuth accounts, sessions, and verification tokens.
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. The configured social providers are GitHub and Google. The providerId field identifies the provider; OAuth tokens and expiry timestamps are managed by Better-Auth.
| 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 verification tokens managed by Better-Auth. Verification records are keyed by their identifier and are not directly related to a user row by a user_id foreign key.
| 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) contributors >── (1) user_files
user (1) ─< (N) session
user (1) ──< (N) account
- 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 contributors row grants a user collaborative access to a file; only the owner manages contributors.
- A user has many sessions (active logins across devices).
- A user has many accounts (one per auth provider, e.g. Google + email/password).
- Verification records are managed by Better-Auth and are not linked directly to a user row in this schema.
Original Schema Diagram
The ER diagram exported from the database design tool is available here: