> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Model

> Database schema, tables, record APIs, and JSON schema generation

# Data Model

TrailBase's data model is built on SQLite with automatic API generation, type safety through JSON schemas, and support for relationships and foreign key expansion.

## Database Schema

### SQLite Foundation

TrailBase uses **SQLite** as its primary database engine with several enhancements:

* **STRICT Tables**: Enforced type checking for data integrity
* **Foreign Keys**: Enabled by default for referential integrity
* **WAL Mode**: Write-Ahead Logging for better concurrency
* **Custom Extensions**: JSON validation, UUID generation, and more

<Info>
  All schema changes are tracked through migrations stored in `traildepot/migrations/`.
</Info>

### Built-in Tables

TrailBase automatically creates system tables during initialization:

#### `_user` Table

```sql theme={null}
CREATE TABLE _user (
  id                              BLOB PRIMARY KEY NOT NULL,
  email                           TEXT UNIQUE NOT NULL,
  password_hash                   TEXT NOT NULL,
  verified                        INTEGER NOT NULL DEFAULT 0,
  admin                          INTEGER NOT NULL DEFAULT 0,
  created                        INTEGER NOT NULL DEFAULT (CAST(unixepoch('subsec') * 1000 AS INTEGER)),
  updated                        INTEGER NOT NULL DEFAULT (CAST(unixepoch('subsec') * 1000 AS INTEGER)),
  email_verification_code        TEXT UNIQUE,
  email_verification_code_sent_at INTEGER,
  pending_email                  TEXT UNIQUE,
  password_reset_code            TEXT UNIQUE,
  password_reset_code_sent_at    INTEGER,
  authorization_code             TEXT UNIQUE,
  authorization_code_sent_at     INTEGER,
  pkce_code_challenge            TEXT,
  provider_id                    INTEGER NOT NULL DEFAULT 0,
  provider_user_id               TEXT,
  provider_avatar_url            TEXT
) STRICT;
```

<Card title="User Model" icon="user" href="/home/daytona/workspace/source/crates/core/src/auth/user.rs:14-42">
  The `DbUser` struct represents database records from the `_user` table.
</Card>

#### Other System Tables

* **`_refresh_token`**: Stores refresh tokens for authentication
* **`_session`**: Tracks user sessions
* **`_chat`**: Stores chat messages (if enabled)
* **`_file_deletions`**: Queues file cleanup operations
* **`_http_logs`**: Request/response logging

## Schema Metadata

<Card title="Connection Metadata" icon="sitemap" href="/home/daytona/workspace/source/crates/core/src/schema_metadata.rs:8-10">
  TrailBase parses SQLite schema into structured metadata for API generation.
</Card>

The `ConnectionMetadata` struct provides a complete representation of your database schema:

```rust theme={null}
// From trailbase_schema crate
pub struct ConnectionMetadata {
  pub tables: HashMap<QualifiedName, TableMetadata>,
  pub views: HashMap<QualifiedName, ViewMetadata>,
}

pub struct TableMetadata {
  pub schema: Table,                    // Parsed CREATE TABLE
  pub column_metadata: Vec<ColumnMetadata>,
  pub record_pk_column: Option<ColumnMetadata>,
}
```

### Column Types

TrailBase understands SQLite's type affinity system:

```rust theme={null}
// From trailbase_schema::sqlite
pub enum ColumnDataType {
  Integer,   // INTEGER, INT, BIGINT
  Real,      // REAL, FLOAT, DOUBLE
  Text,      // TEXT, VARCHAR, CHAR
  Blob,      // BLOB
  Numeric,   // NUMERIC, DECIMAL
  Any,       // No type specified (not in STRICT mode)
}
```

<Warning>
  TrailBase strongly recommends using STRICT tables to ensure type safety.
</Warning>

### Column Metadata

Each column is analyzed for:

* **Data Type**: SQLite affinity and explicit type
* **Nullability**: Whether NULL values are allowed
* **Primary Key**: Is this the record identifier?
* **Foreign Keys**: References to other tables
* **File Columns**: Special handling for file uploads
* **JSON Schema**: Custom validation rules

```rust theme={null}
// From column metadata detection
pub struct ColumnMetadata {
  pub column: Column,
  pub is_file: bool,
  pub json_schema: Option<JsonColumnMetadata>,
  pub foreign_keys: Vec<ForeignKeyInfo>,
}
```

## Record APIs

<Card title="Record API System" icon="layer-group" href="/home/daytona/workspace/source/crates/core/src/records/record_api.rs:20-23">
  Record APIs provide type-safe, auto-generated REST endpoints for tables and views.
</Card>

### What is a Record API?

A Record API exposes a SQLite table or view through REST endpoints with automatic:

* Type validation
* Access control
* JSON serialization
* Foreign key expansion
* Real-time subscriptions

### Configuration

Record APIs are configured in `config.textproto`:

```protobuf theme={null}
record_apis {
  name: "posts"
  table_name: "post"
  
  # Access control lists
  acl_world: [READ]
  acl_authenticated: [CREATE, READ, UPDATE, DELETE]
  
  # Row-level access rules
  read_access_rule: "TRUE"
  create_access_rule: "_USER_.id = :user_id"
  update_access_rule: "_ROW_.user_id = _USER_.id"
  delete_access_rule: "_ROW_.user_id = _USER_.id"
  
  # Foreign key expansion
  expand: ["user_id", "category_id"]
  
  # Optional settings
  excluded_columns: ["internal_notes"]
  listing_hard_limit: 100
  enable_subscriptions: true
}
```

### Record API Structure

```rust theme={null}
// Internal structure from record_api.rs:20-164
pub struct RecordApi {
  state: Arc<RecordApiState>,
}

struct RecordApiState {
  conn: Arc<Connection>,
  metadata: RwLock<Arc<ConnectionMetadata>>,
  schema: RecordApiSchema,
  api_name: String,
  acl: [u8; 2],  // [world, authenticated]
  read_access_query: Option<Arc<str>>,
  create_access_query: Option<Arc<str>>,
  update_access_query: Option<Arc<str>>,
  delete_access_query: Option<Arc<str>>,
}
```

## JSON Schema Generation

<Info>
  TrailBase automatically generates JSON schemas for validation and TypeScript type generation.
</Info>

### Schema Modes

JSON schemas are generated with different strictness levels:

```rust theme={null}
pub enum JsonSchemaMode {
  Insert,   // Creating new records (required fields enforced)
  Update,   // Updating existing records (all fields optional)
  Select,   // Reading records (reflects actual data)
}
```

### Generated Schema Example

For a table like:

```sql theme={null}
CREATE TABLE post (
  id           INTEGER PRIMARY KEY,
  title        TEXT NOT NULL,
  content      TEXT,
  user_id      BLOB NOT NULL REFERENCES _user(id),
  published    INTEGER NOT NULL DEFAULT 0,
  created_at   INTEGER NOT NULL
) STRICT;
```

TrailBase generates:

```json theme={null}
{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "post",
  "type": "object",
  "properties": {
    "id": { "type": "integer" },
    "title": { "type": "string" },
    "content": { "type": ["string", "null"] },
    "user_id": { "type": "string", "format": "byte" },
    "published": { "type": "integer" },
    "created_at": { "type": "integer" }
  },
  "required": ["title", "user_id", "published", "created_at"]
}
```

### Custom JSON Schemas

You can define custom validation schemas:

```protobuf theme={null}
schemas {
  name: "email_address"
  schema: |
    {
      "type": "string",
      "format": "email",
      "maxLength": 255
    }
}
```

Then reference in table columns:

```sql theme={null}
CREATE TABLE contacts (
  id     INTEGER PRIMARY KEY,
  email  TEXT NOT NULL CHECK(json_schema_valid('email_address', email))
) STRICT;
```

## Relationships

### Foreign Keys

TrailBase automatically detects and handles foreign key relationships:

```sql theme={null}
CREATE TABLE comment (
  id       INTEGER PRIMARY KEY,
  post_id  INTEGER NOT NULL REFERENCES post(id) ON DELETE CASCADE,
  user_id  BLOB NOT NULL REFERENCES _user(id),
  content  TEXT NOT NULL
) STRICT;
```

### Foreign Key Expansion

<Card title="Expansion Feature" icon="expand" href="/home/daytona/workspace/source/crates/core/src/records/expand.rs">
  Foreign key expansion allows nested loading of related records.
</Card>

Without expansion:

```json theme={null}
{
  "id": 1,
  "post_id": { "id": 42 },
  "user_id": { "id": "base64_user_id" },
  "content": "Great post!"
}
```

With `?expand=post_id,user_id`:

```json theme={null}
{
  "id": 1,
  "post_id": {
    "id": 42,
    "data": {
      "id": 42,
      "title": "My Post",
      "content": "Post content...",
      "user_id": { "id": "base64_user_id" }
    }
  },
  "user_id": {
    "id": "base64_user_id",
    "data": {
      "id": "base64_user_id",
      "email": "user@example.com"
    }
  },
  "content": "Great post!"
}
```

## File Handling

<Card title="File Columns" icon="file" href="/home/daytona/workspace/source/crates/core/src/records/files.rs">
  TrailBase provides special handling for file upload columns.
</Card>

### File Column Detection

Columns with JSON schema containing `contentMediaType` and `contentEncoding` are treated as file columns:

```protobuf theme={null}
schemas {
  name: "image_upload"
  schema: |
    {
      "type": "string",
      "contentMediaType": "image/*",
      "contentEncoding": "base64",
      "maxLength": 5242880
    }
}
```

### File Storage

Files are stored in:

* **Local**: `traildepot/uploads/` directory
* **S3**: Configured S3-compatible object storage

File metadata is stored as JSON in the column:

```json theme={null}
{
  "name": "avatar.jpg",
  "size": 45231,
  "mimeType": "image/jpeg",
  "path": "<object_store_path>"
}
```

### Automatic Cleanup

TrailBase installs triggers to clean up files when records are updated or deleted:

```rust theme={null}
// From connection.rs:466-511
fn setup_file_deletion_triggers_sync(
  conn: &rusqlite::Connection,
  metadata: &ConnectionMetadata,
) -> Result<(), Error>
```

## Views and Read-Only APIs

Views can be exposed through Record APIs for read-only access:

```sql theme={null}
CREATE VIEW post_summary AS
  SELECT 
    p.id,
    p.title,
    p.created_at,
    u.email as author_email,
    COUNT(c.id) as comment_count
  FROM post p
  JOIN _user u ON p.user_id = u.id
  LEFT JOIN comment c ON p.id = c.post_id
  GROUP BY p.id;
```

Configure as a Record API:

```protobuf theme={null}
record_apis {
  name: "post_summaries"
  table_name: "post_summary"
  acl_world: [READ]
}
```

<Warning>
  Views only support READ and SCHEMA operations. CREATE, UPDATE, and DELETE are disabled.
</Warning>

## Multi-Database Support

TrailBase supports attaching multiple SQLite databases:

```protobuf theme={null}
record_apis {
  name: "analytics"
  table_name: "analytics.events"
  attached_databases: ["analytics"]
}
```

The `ConnectionManager` handles database attachment and maintains separate metadata per connection.

## Best Practices

<CardGroup cols={2}>
  <Card title="Use STRICT Tables" icon="check">
    Always create tables with STRICT mode for type safety
  </Card>

  <Card title="Define Primary Keys" icon="key">
    Use INTEGER PRIMARY KEY or BLOB PRIMARY KEY with UUIDv7
  </Card>

  <Card title="Enable Foreign Keys" icon="link">
    Use REFERENCES for relationships and ON DELETE CASCADE
  </Card>

  <Card title="Index Frequently Queried Columns" icon="bolt">
    Create indexes for foreign keys and filter columns
  </Card>
</CardGroup>

## Next Steps

<CardGroup cols={2}>
  <Card title="APIs" icon="code" href="./apis">
    Learn how to use Record APIs in your application
  </Card>

  <Card title="Authentication" icon="shield" href="./authentication">
    Understand user management and access control
  </Card>
</CardGroup>