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

All schema changes are tracked through migrations stored in traildepot/migrations/.

Built-in Tables

TrailBase automatically creates system tables during initialization:

`_user` Table

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;

User Model

The DbUser struct represents database records from the _user table.

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

Connection Metadata

TrailBase parses SQLite schema into structured metadata for API generation.

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

// 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:

// 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)
}

TrailBase strongly recommends using STRICT tables to ensure type safety.

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

// 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

Record API System

Record APIs provide type-safe, auto-generated REST endpoints for tables and views.

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:

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

// 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

TrailBase automatically generates JSON schemas for validation and TypeScript type generation.

Schema Modes

JSON schemas are generated with different strictness levels:

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:

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:

{
  "$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:

schemas {
  name: "email_address"
  schema: |
    {
      "type": "string",
      "format": "email",
      "maxLength": 255
    }
}

Then reference in table columns:

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:

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

Expansion Feature

Foreign key expansion allows nested loading of related records.

Without expansion:

{
  "id": 1,
  "post_id": { "id": 42 },
  "user_id": { "id": "base64_user_id" },
  "content": "Great post!"
}

With ?expand=post_id,user_id:

{
  "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": "[email protected]"
    }
  },
  "content": "Great post!"
}

File Handling

File Columns

TrailBase provides special handling for file upload columns.

File Column Detection

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

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:

{
  "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:

// 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:

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:

record_apis {
  name: "post_summaries"
  table_name: "post_summary"
  acl_world: [READ]
}

Views only support READ and SCHEMA operations. CREATE, UPDATE, and DELETE are disabled.

Multi-Database Support

TrailBase supports attaching multiple SQLite databases:

record_apis {
  name: "analytics"
  table_name: "analytics.events"
  attached_databases: ["analytics"]
}

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

Best Practices

Use STRICT Tables

Always create tables with STRICT mode for type safety

Define Primary Keys

Use INTEGER PRIMARY KEY or BLOB PRIMARY KEY with UUIDv7

Enable Foreign Keys

Use REFERENCES for relationships and ON DELETE CASCADE

Index Frequently Queried Columns

Create indexes for foreign keys and filter columns

Next Steps

APIs

Learn how to use Record APIs in your application

Authentication

Understand user management and access control

Getting Started

Core Concepts

Guides

Deployment

​Data Model

​Database Schema

​SQLite Foundation

​Built-in Tables

​_user Table

User Model

​Other System Tables

​Schema Metadata

Connection Metadata

​Column Types

​Column Metadata

​Record APIs

Record API System

​What is a Record API?

​Configuration

​Record API Structure

​JSON Schema Generation

​Schema Modes

​Generated Schema Example

​Custom JSON Schemas

​Relationships

​Foreign Keys

​Foreign Key Expansion

Expansion Feature

​File Handling

File Columns

​File Column Detection

​File Storage

​Automatic Cleanup

​Views and Read-Only APIs

​Multi-Database Support

​Best Practices

Use STRICT Tables

Define Primary Keys

Enable Foreign Keys

Index Frequently Queried Columns

​Next Steps

APIs

Authentication

Build docs developers (and LLMs) love

Data Model

Database Schema

SQLite Foundation

Built-in Tables

`_user` Table

Other System Tables

Schema Metadata

Column Types

Column Metadata

Record APIs

What is a Record API?

Configuration

Record API Structure

JSON Schema Generation

Schema Modes

Generated Schema Example

Custom JSON Schemas

Relationships

Foreign Keys

Foreign Key Expansion

File Handling

File Column Detection

File Storage

Automatic Cleanup

Views and Read-Only APIs

Multi-Database Support

Best Practices

Next Steps