Database Configuration and Schema Reference for Timify

Timify stores all data in a local SQLite file through the @libsql/client driver, with Drizzle ORM providing type-safe query building on top. There is no external database server to provision — the entire database is a single file on disk, making Timify easy to self-host and run fully offline.

How the Database Connection Works

The database client is created in src/db/index.ts using @libsql/client and wrapped with drizzle-orm/libsql. The file path is read from the DB_FILE_NAME environment variable at startup:

src/db/index.ts

import { createClient } from "@libsql/client";
import { drizzle } from "drizzle-orm/libsql";

const client = createClient({
  url: process.env.DB_FILE_NAME || "file:./src/db/local.db",
});

export const db = drizzle(client);

The file: prefix in the URL is required by the libsql protocol — it tells the client to open a local file rather than connect to a remote server.

Because Timify uses @libsql/client (not the better-sqlite3 synchronous driver), database calls are asynchronous and fully compatible with React Server Components and Next.js Server Actions.

Configuring the Database Path

Set DB_FILE_NAME in your .env file to control where the SQLite file is stored:

DB_FILE_NAME

string

default:"file:./src/db/local.db"

The libsql URL pointing to the SQLite file. Must start with file:. The path after file: is resolved relative to the project root when using a relative path, or as an absolute filesystem path when starting with /.

# Relative path (default) — file lives inside the project
DB_FILE_NAME=file:./src/db/local.db

# Absolute path — recommended for production
DB_FILE_NAME=file:/var/data/timify/timify.db

Drizzle ORM and Schema

Timify uses Drizzle ORM for all database queries. The schema is defined in src/db/schema.ts, and drizzle.config.ts tells drizzle-kit where to find it:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/db/schema.ts",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DB_FILE_NAME || "file:./src/db/local.db",
  },
});

Generated migration files are placed in the ./drizzle/ directory at the project root.

Managing the Schema

Use the following drizzle-kit commands to work with the database:

Apply the schema to the database

Push all schema changes from src/db/schema.ts directly to the SQLite file. This is the primary development workflow — it applies changes in-place without generating migration files.

npx drizzle-kit push

Browse data with Drizzle Studio

Open the Drizzle Studio web UI to inspect and edit rows in every table through a browser interface.

npx drizzle-kit studio

Drizzle Studio starts a local server (usually at https://local.drizzle.studio) and connects to the database file configured in drizzle.config.ts.

Database Tables

The schema is split into two groups: auth tables managed entirely by better-auth, and application tables defined in src/db/schema.ts.

Auth Tables

These four tables are created and owned by better-auth. Do not modify their columns manually.

user — registered accounts

Column	Type	Notes
`id`	`text` PK
`name`	`text`	Display name, not null
`email`	`text`	Unique, not null
`email_verified`	`integer` (boolean)	Defaults to `false`
`image`	`text`	Avatar URL, nullable
`created_at`	`integer` (timestamp)	Defaults to `unixepoch()`
`updated_at`	`integer` (timestamp)	Auto-updated on change

session — active user sessions

Column	Type	Notes
`id`	`text` PK
`expires_at`	`integer` (timestamp)	Not null
`token`	`text`	Unique session token
`ip_address`	`text`	Nullable
`user_agent`	`text`	Nullable
`user_id`	`text` FK	References `user.id`, cascades on delete
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

account — auth provider links

Stores credentials per provider. For email/password auth, provider_id is credential and the hashed password is stored in the password column.

Column	Type	Notes
`id`	`text` PK
`account_id`	`text`	Provider-specific account identifier
`provider_id`	`text`	e.g. `credential`
`user_id`	`text` FK	References `user.id`, cascades on delete
`access_token`	`text`	Nullable
`refresh_token`	`text`	Nullable
`password`	`text`	Nullable — hashed password for credential accounts
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

verification — pending verification tokens

Used by better-auth for email verification flows.

Column	Type	Notes
`id`	`text` PK
`identifier`	`text`	Unique — email or other identifier
`value`	`text`	The verification token
`expires_at`	`integer` (timestamp)	Not null
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

Application Tables

These tables are defined in src/db/schema.ts and hold all Timify-specific data.

projects — time-tracking projects

Column	Type	Notes
`id`	`text` PK	UUID generated by `crypto.randomUUID()`
`user_id`	`text` FK	References `user.id`, cascades on delete
`name`	`text`	Not null
`description`	`text`	Nullable
`color`	`text`	Hex color, defaults to `#3b82f6`
`hourly_rate`	`real`	Defaults to `0`
`is_active`	`integer` (boolean)	Defaults to `true`
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

time_entries — individual tracked intervals

Column	Type	Notes
`id`	`text` PK	UUID
`user_id`	`text` FK	References `user.id`, cascades on delete
`project_id`	`text` FK	References `projects.id`, set null on delete
`title`	`text`	Nullable
`description`	`text`	Nullable
`start_time`	`integer` (timestamp)	Defaults to `unixepoch()`
`end_time`	`integer` (timestamp)	Nullable — null when timer is running
`duration`	`integer`	Seconds; calculated field
`is_running`	`integer` (boolean)	Defaults to `true`
`billable`	`integer` (boolean)	Defaults to `false`
`hourly_rate`	`real`	Nullable — overrides project rate when set
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

tags — categorization labels

Column	Type	Notes
`id`	`text` PK	UUID
`user_id`	`text` FK	References `user.id`, cascades on delete
`name`	`text`	Not null
`color`	`text`	Hex color, defaults to `#9ca3af`
`created_at`	`integer` (timestamp)
`updated_at`	`integer` (timestamp)	Auto-updated on change

time_entry_tags — many-to-many join table

Links time entries to tags. The pair (time_entry_id, tag_id) is unique.

Column	Type	Notes
`id`	`text` PK	UUID
`time_entry_id`	`text` FK	References `time_entries.id`, cascades on delete
`tag_id`	`text` FK	References `tags.id`, cascades on delete

Production Considerations

SQLite is an excellent fit for single-user or low-concurrency self-hosted deployments. Keep the following in mind when running Timify in production:

SQLite does not support concurrent writes from multiple processes. Timify must run as a single instance — do not point multiple Node.js workers or containers at the same database file, as this will cause write-lock conflicts.

Backup strategy: Because all data lives in a single file, backing up is as simple as copying it. Schedule a regular cp or rsync of the database file to a separate location — for example:

cp /var/data/timify/timify.db /backups/timify-$(date +%Y%m%d).db

Use an absolute path for DB_FILE_NAME in production so the database location is independent of the working directory:

.env (production)

DB_FILE_NAME=file:/var/data/timify/timify.db

Ensure the directory exists and is writable by the process running Timify before starting the app.

Get Started

Core Features

Configuration

Database Configuration and Schema Reference for Timify

How the Database Connection Works

Configuring the Database Path

Drizzle ORM and Schema

Managing the Schema

Database Tables

Auth Tables

Application Tables

Production Considerations

Build docs developers (and LLMs) love

Get Started

Core Features

Configuration

Documentation Index

​How the Database Connection Works

​Configuring the Database Path

​Drizzle ORM and Schema

​Managing the Schema

​Database Tables

​Auth Tables

​Application Tables

​Production Considerations

Build docs developers (and LLMs) love

How the Database Connection Works

Configuring the Database Path

Drizzle ORM and Schema

Managing the Schema

Database Tables

Auth Tables

Application Tables

Production Considerations