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

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": "[e-mail]"
    }
  },
  "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