Schema

Defining tables

Use defineSchema() from @tthr/schema to define your tables:

import { defineSchema, text, integer, real, timestamp } from '@tthr/schema';

export default defineSchema({
  users: {
    email: text().notNull().unique(),
    name: text(),
  },

  posts: {
    title: text().notNull(),
    content: text(),
    authorId: text().notNull().references('users'),
  },
});

Column types

Tether supports the following column types:

Type	TypeScript	Description
text()	string	Text/string values
integer()	number	Integer numbers
real()	number	Floating point numbers
blob()	Uint8Array	Binary data
timestamp()	Date	Date/time (ISO 8601 string)
boolean()	boolean	True/false (stored as SQLite integer 0/1)
json<T>()	T	JSON data with type parameter
asset()	string	File reference (UUID linking to storage)

Using JSON columns

Define complex nested data with type safety:

import { defineSchema, text, json } from '@tthr/schema';

interface UserPreferences {
  theme: 'light' | 'dark';
  notifications: boolean;
  language: string;
}

export default defineSchema({
  users: {
    email: text().notNull(),
    preferences: json<UserPreferences>().default({
      theme: 'light',
      notifications: true,
      language: 'en',
    }),
  },
});

Using asset columns

Store file references that link to Tether's storage system:

import { defineSchema, text, asset } from '@tthr/schema';

export default defineSchema({
  posts: {
    title: text().notNull(),
    coverImage: asset(), // Stores asset UUID
    attachments: json<string[]>(), // Multiple asset UUIDs
  },
});

Column modifiers

Chain modifiers to add constraints:

Modifier	Description
.primaryKey()	Mark as primary key (also sets notNull)
.autoincrement()	Auto-increment (integer columns only)
.notNull()	Disallow null values
.unique()	Enforce uniqueness constraint
.default(value)	Set a default value
.references('table')	Foreign key reference (auto-references _id)
.onDelete(action)	Foreign key delete behaviour: 'cascade', 'set null', 'restrict'
.oneOf([...])	Constrain to a set of allowed values (text columns only)

Timestamp modifiers

Timestamps have additional convenience modifiers:

Modifier	Description
.defaultNow()	Auto-set to current timestamp on insert
.onUpdate()	Auto-update to current timestamp on modification

export default defineSchema({
  posts: {
    title: text().notNull(),
    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp().defaultNow().onUpdate(),
  },
});

Foreign keys

Define relationships between tables using .references():

export default defineSchema({
  users: {
    email: text().notNull().unique(),
  },

  posts: {
    title: text().notNull(),
    authorId: text().notNull().references('users'),
  },

  comments: {
    content: text().notNull(),
    postId: text().notNull().references('posts').onDelete('cascade'),
    authorId: text().references('users').onDelete('set null'),
  },
});

Delete behaviours

Action	Description
'cascade'	Delete child records when parent is deleted
'set null'	Set foreign key to null when parent is deleted
'restrict'	Prevent deletion if child records exist (default)

Enum constraints

Use .oneOf() to restrict a text column to a set of allowed values. This generates a SQL CHECK constraint and narrows the TypeScript type to a union of string literals.

export default defineSchema({
  serverMembers: {
    serverId: text().notNull().references('servers').onDelete('cascade'),
    userId: text().notNull().references('users').onDelete('cascade'),
    role: text().oneOf(['owner', 'admin', 'moderator', 'member']).notNull(),
  },

  channels: {
    serverId: text().notNull().references('servers').onDelete('cascade'),
    name: text().notNull(),
    type: text().oneOf(['text', 'announcements']).notNull(),
  },

  friends: {
    requesterId: text().notNull().references('users'),
    addresseeId: text().notNull().references('users'),
    status: text().oneOf(['pending', 'accepted', 'declined', 'blocked']).notNull(),
  },
});

The generated TypeScript type for role will be 'owner' | 'admin' | 'moderator' | 'member' instead of string, giving you compile-time safety. The database will also reject any values not in the list.

System columns

Tether automatically adds system columns to every table. You don't need to define these in your schema:

Column	Description
_id	UUID primary key (auto-generated if not provided on insert)
_createdAt	ISO 8601 timestamp when the record was created
_updatedAt	ISO 8601 timestamp, auto-updated on every modification

These columns are always available in your queries and are excluded from CreateInput types since they're managed automatically.

Generating types

Run tthr generate to create TypeScript types from your schema:

tthr generate

This creates tether/_generated/db.ts with typed interfaces for all your tables.