Timify Database Schema Reference (SQLite + Drizzle)

Timify’s database is a single SQLite file managed by Drizzle ORM. The schema lives in src/db/schema.ts and is split into two groups: four auth tables owned by better-auth, and four application tables that hold your projects, time entries, and tags. All primary keys use text UUIDs; timestamps are stored as Unix epoch integers and surfaced as JavaScript Date objects by Drizzle’s { mode: "timestamp" } option.

Auth Tables

These four tables are created and maintained by better-auth. You should not write to them directly; interact with them through the better-auth client/server APIs.

`user`

Stores each registered user’s core identity. The email column carries a unique constraint enforced at the database level.

string

required

Primary key. A text UUID assigned by better-auth at registration.

name

string

required

Display name of the user.

string

required

Unique email address. Used as the login identifier.

emailVerified

boolean

required

Whether the user has confirmed their email address. Defaults to false.

image

string

Optional URL to the user’s avatar image.

createdAt

Date

required

Timestamp of account creation. Defaults to the current Unix epoch via unixepoch().

updatedAt

Date

required

Timestamp of the last update. Automatically refreshed on every write via $onUpdateFn.

`session`

Holds active authentication sessions. Each user may have at most one session at a time, enforced by a unique index on userId.

string

required

Primary key. A text UUID.

expiresAt

Date

required

The point in time after which this session is invalid and must be re-authenticated.

token

string

required

Unique opaque session token sent to the client. Carries a database-level UNIQUE constraint.

ipAddress

string

Optional IP address captured at session creation.

userAgent

string

Optional User-Agent header captured at session creation.

userId

string

required

Foreign key → user.id. Cascade-deletes this session when the parent user is removed. Covered by unique index session_userId_idx.

createdAt

Date

required

Session creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

The unique index on userId means a user can only hold one active session. better-auth handles session rotation automatically; avoid inserting rows directly.

`account`

Links a user to an OAuth provider account or a local credential. A unique index on userId (account_userId_idx) ensures one account record per user.

string

required

Primary key. A text UUID.

accountId

string

required

The user’s identifier within the external provider (e.g. a GitHub user ID).

providerId

string

required

Identifier for the auth provider, e.g. "github" or "credential".

userId

string

required

Foreign key → user.id. Cascade-deletes this account record when the parent user is removed.

accessToken

string

OAuth access token, if issued by the provider.

refreshToken

string

OAuth refresh token, if issued by the provider.

idToken

string

OIDC identity token, if issued by the provider.

accessTokenExpiresAt

Date

Expiry timestamp for the access token.

refreshTokenExpiresAt

Date

Expiry timestamp for the refresh token.

scope

string

Space-separated OAuth scopes that were granted.

password

string

Hashed password for credential-based (email/password) accounts. null for OAuth accounts.

createdAt

Date

required

Record creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

`verification`

Stores short-lived tokens used for email verification and password-reset flows. A unique index on identifier (verification_identifier_idx) prevents duplicate pending verifications for the same target.

string

required

Primary key. A text UUID.

identifier

string

required

The target being verified — typically an email address or phone number. Unique per row.

value

string

required

The secret token or OTP value to be checked against user input.

expiresAt

Date

required

Expiry timestamp after which the verification record should be considered invalid.

createdAt

Date

required

Record creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

Application Tables

These tables form the core of Timify’s time-tracking domain. All IDs are auto-generated via crypto.randomUUID() through Drizzle’s $defaultFn.

`projects`

Represents a named billable or non-billable project that groups time entries together.

string

required

Primary key. UUID generated at insert time via crypto.randomUUID().

userId

string

required

Foreign key → user.id. Cascade-deletes all projects when the parent user is removed.

name

string

required

Human-readable project name shown throughout the UI.

description

string

Optional free-text description of the project’s purpose.

color

string

Hex color code used to visually distinguish the project in the UI. Defaults to #3b82f6 (blue).

hourlyRate

number

Default billing rate in the user’s chosen currency. Defaults to 0. Individual time entries can override this value.

isActive

boolean

Whether the project is currently active. Inactive projects are hidden from timers but retained for reporting. Defaults to true.

createdAt

Date

required

Record creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

`time_entries`

Each row represents a single tracked interval of work. A running entry has isRunning = true and a null endTime; a stopped entry has both endTime and duration populated.

string

required

Primary key. UUID generated at insert time via crypto.randomUUID().

userId

string

required

Foreign key → user.id. Cascade-deletes all time entries when the parent user is removed.

projectId

string

Optional foreign key → projects.id. When the referenced project is deleted, this column is set to null (the entry is preserved but un-linked).

title

string

Short label for what was worked on during this interval.

description

string

Optional longer description or notes about the work.

startTime

Date

When the timer was started. Defaults to the current Unix epoch at insert time.

endTime

Date

When the timer was stopped. null for currently running entries.

duration

number

Pre-calculated duration in seconds. Populated when the entry is stopped. null for running entries.

isRunning

boolean

true while the timer is actively counting. Defaults to true at insert.

billable

boolean

Whether this entry should count toward billable hours. Defaults to false.

hourlyRate

number

Per-entry rate override. When set, this takes precedence over the parent project’s hourlyRate. Optional.

createdAt

Date

required

Record creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

duration is a cached computed field. It equals endTime - startTime in seconds and is written when the entry is stopped. Always treat it as a derived value; for running entries, compute elapsed time from startTime and the current clock.

`tags`

User-defined labels used to categorize time entries. Each tag belongs to one user and can be applied to many time entries.

string

required

Primary key. UUID generated at insert time via crypto.randomUUID().

userId

string

required

Foreign key → user.id. Cascade-deletes all tags when the parent user is removed.

name

string

required

Display name of the tag, e.g. "deep-work" or "client-abc".

color

string

Hex color code for the tag badge. Defaults to #9ca3af (gray).

createdAt

Date

required

Record creation timestamp. Defaults to the current Unix epoch.

updatedAt

Date

required

Last-modified timestamp. Automatically refreshed on every write.

`time_entry_tags`

Join table implementing the many-to-many relationship between time_entries and tags. A composite unique index on (timeEntryId, tagId) prevents the same tag from being applied to the same entry twice.

string

required

Primary key. UUID generated at insert time via crypto.randomUUID().

timeEntryId

string

required

Foreign key → time_entries.id. Cascade-deletes this join row when the parent time entry is removed.

tagId

string

required

Foreign key → tags.id. Cascade-deletes this join row when the referenced tag is removed.

The unique index unique_time_entry_tag on (time_entry_id, tag_id) is the canonical guard against duplicate tag assignments. Rely on this constraint rather than application-level deduplication.

Relations

Drizzle relations provide TypeScript type safety for eager-loading queries. They do not create extra SQL constraints — all referential integrity is handled by the foreign keys described above.

Show usersRelations

Defined on the user table. Gives a user access to all owned records:

Relation field	Type	Points to
`projects`	`many`	`projects`
`timeEntries`	`many`	`time_entries`
`tags`	`many`	`tags`
`sessions`	`many`	`session`
`accounts`	`many`	`account`

Show projectsRelations

Defined on the projects table.

Relation field	Type	Points to	Join fields
`user`	`one`	`user`	`projects.userId → user.id`
`timeEntries`	`many`	`time_entries`	—

Show timeEntriesRelations

Defined on the time_entries table.

Relation field	Type	Points to	Join fields
`user`	`one`	`user`	`time_entries.userId → user.id`
`project`	`one`	`projects`	`time_entries.projectId → projects.id`
`tags`	`many`	`time_entry_tags`	—

Show tagsRelations

Defined on the tags table.

Relation field	Type	Points to	Join fields
`user`	`one`	`user`	`tags.userId → user.id`
`timeEntries`	`many`	`time_entry_tags`	—

Show timeEntryTagsRelations

Defined on the time_entry_tags join table.

Relation field	Type	Points to	Join fields
`timeEntry`	`one`	`time_entries`	`time_entry_tags.timeEntryId → time_entries.id`
`tag`	`one`	`tags`	`time_entry_tags.tagId → tags.id`

Show sessionRelations / accountRelations

Both session and account expose a single user one-relation pointing back to user.id, enabling reverse lookups from auth records to the owning user.

Cascade Delete Summary

Understanding delete propagation is critical when removing records.

Deleted record	Affected child records	Behavior
`user`	`session`, `account`, `projects`, `time_entries`, `tags`	Cascade delete — all owned rows are removed
`projects` row	`time_entries.projectId`	Set null — entries are kept, project link cleared
`time_entries` row	`time_entry_tags`	Cascade delete — join rows are removed
`tags` row	`time_entry_tags`	Cascade delete — join rows are removed

Unique Constraints Summary

Table	Constraint	Columns
`user`	`user_email_unique`	`email`
`session`	`session_token_unique`	`token`
`session`	`session_userId_idx`	`user_id`
`account`	`account_userId_idx`	`user_id`
`verification`	`verification_identifier_idx`	`identifier`
`time_entry_tags`	`unique_time_entry_tag`	`time_entry_id, tag_id`

Example: Querying with Drizzle

The db singleton exported from src/db/index.ts connects via @libsql/client and is the entry point for all queries.

import { db } from '@/db';
import { projects, timeEntries } from '@/db/schema';
import { eq } from 'drizzle-orm';

// Get all time entries for a project
const entries = await db
  .select()
  .from(timeEntries)
  .where(eq(timeEntries.projectId, projectId));

You can also load nested data by using explicit joins:

import { db } from '@/db';
import { tags, timeEntries, timeEntryTags } from '@/db/schema';
import { eq } from 'drizzle-orm';

// Get time entries for a project, including their tags
const rows = await db
  .select({
    timeEntry: timeEntries,
    tag: tags,
  })
  .from(timeEntries)
  .leftJoin(timeEntryTags, eq(timeEntries.id, timeEntryTags.timeEntryId))
  .leftJoin(tags, eq(timeEntryTags.tagId, tags.id))
  .where(eq(timeEntries.projectId, projectId));

// Group tags onto each time entry
const result = rows.reduce((acc, row) => {
  const existing = acc.find((e) => e.id === row.timeEntry.id);
  if (existing) {
    if (row.tag) existing.tags.push(row.tag);
  } else {
    acc.push({ ...row.timeEntry, tags: row.tag ? [row.tag] : [] });
  }
  return acc;
}, [] as Array<typeof timeEntries.$inferSelect & { tags: typeof tags.$inferSelect[] }>);

Server Actions

Data Model

Timify Database Schema Reference (SQLite + Drizzle)

Auth Tables

`user`

`session`

`account`

`verification`

Application Tables

`projects`

`time_entries`

`tags`

`time_entry_tags`

Relations

Cascade Delete Summary

Unique Constraints Summary

Example: Querying with Drizzle

Build docs developers (and LLMs) love

Server Actions

Data Model

Documentation Index

​Auth Tables

​user

​session

​account

​verification

​Application Tables

​projects

​time_entries

​tags

​time_entry_tags

​Relations

​Cascade Delete Summary

​Unique Constraints Summary

​Example: Querying with Drizzle

Build docs developers (and LLMs) love

Auth Tables

`user`

`session`

`account`

`verification`

Application Tables

`projects`

`time_entries`

`tags`

`time_entry_tags`

Relations

Cascade Delete Summary

Unique Constraints Summary

Example: Querying with Drizzle