# Database Schema for Karakeep: Complete Drizzle ORM Table Reference

> Explore the Karakeep database schema defined with Drizzle ORM and SQLite. Discover 25+ tables for users, bookmarks, tags, and more with type-safe relations and optimized indexes.

- Repository: [Karakeep App/karakeep](https://github.com/karakeep-app/karakeep)
- Tags: api-reference
- Published: 2026-07-07

---

**The Karakeep database schema is defined in [`packages/db/schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/schema.ts) using Drizzle ORM and SQLite, modeling users, bookmarks, tags, lists, assets, and auxiliary entities across 25+ tables with type-safe relations and composite indexes for performance.**

The open-source bookmarking application Karakeep (formerly Hoarder) persists all application data in a SQLite database managed by Drizzle ORM. Understanding the database schema for Karakeep is essential for developers contributing features, building third-party integrations, or optimizing self-hosted instances. The entire schema definition resides in a single TypeScript file at [`packages/db/schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/schema.ts), where tables, indexes, and relations are declared using Drizzle's type-safe API.

## Core Tables and Entity Relationships

The schema models user authentication, content storage, organizational structures, and system automation. All tables use **integer columns with `mode: "timestamp"`** for temporal fields and store booleans as integers with `mode: "boolean"` to ensure SQLite compatibility.

### User Authentication and API Access

The authentication layer supports local credentials and OAuth providers via Auth.js:

- **`users`** – Stores account profiles, quotas, and settings including `bookmarkQuota`, `storageQuota`, `readerFontSize`, and `autoTaggingEnabled`. Defined at lines 32-44 in [`schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/schema.ts).
- **`accounts`** – Links users to OAuth providers with a composite primary key on `(provider, providerAccountId)`.
- **`sessions`** – Manages cookie-based session tokens with `sessionToken` as the primary key.
- **`apiKeys`** – Stores scoped API credentials with a unique constraint on `(name, userId)`.
- **`invites`** – Tracks registration tokens with a unique `token` column for invite-only instances.

### Bookmarks and Content Storage

Bookmarks serve as the central content entity, supporting links, text notes, and assets:

- **`bookmarks`** – The core table containing `title`, `archived`, `favourited`, `type` (link/text/asset), and `source`. It includes composite indexes like `userId_createdAt_id_idx` for efficient pagination (lines 88-112).
- **`bookmarkLinks`** – Crawled metadata including `url`, `description`, `author`, `publisher`, and `crawlStatus` to track worker ingestion progress.
- **`assets`** – Binary files (screenshots, PDFs, avatars) linked to bookmarks via `bookmarkId` with indexes on `assetType` and `userId`.
- **`highlights`** – Text selections within archived pages storing `startOffset`, `endOffset`, `color`, and the highlighted `text` content.
- **`userReadingProgress`** – Tracks reading position per bookmark with a unique constraint on `(bookmarkId, userId)`.

### Organization and Taxonomy

Content organization relies on a flexible tagging and list system:

- **`bookmarkTags`** – User-owned tag definitions with `normalizedName` for case-insensitive lookups. Unique constraint on `(userId, name)` (lines 525-545).
- **`tagsOnBookmarks`** – Junction table implementing the many-to-many relationship between tags and bookmarks, with `attachedAt` and `attachedBy` audit columns. Primary key on `(bookmarkId, tagId)` with a composite index `tagId_bookmarkId` (lines 555-571).
- **`bookmarkLists`** – Collections supporting both manual curation and smart lists via the `query` field (for saved searches). Includes `parentId` for nested hierarchies and `public` for sharing.
- **`bookmarksInLists`** – Junction table linking bookmarks to lists with `addedAt` timestamps.
- **`listCollaborators`** – Permissions for shared list access with roles, unique on `(listId, userId)`.
- **`listInvitations`** – Pending collaboration invites tracking `status` and `invitedAt`.

### Automation and System Configuration

Supporting tables enable RSS ingestion, webhooks, and rule-based automation:

- **`rssFeedsTable`** – RSS subscription sources for auto-import with `lastFetchedAt` and `importTags` configuration.
- **`webhooksTable`** – User-defined HTTP endpoints for event-driven integrations.
- **`ruleEngineRulesTable`** and **`ruleEngineActionsTable`** – Define event-triggered automations with conditions and actions (tagging, list assignment).
- **`customPrompts`** – User-defined AI prompts for automated tagging and summarization.
- **`subscriptions`** – Stripe billing data including `stripeCustomerId`, `status`, and `tier`.
- **`importSessions`**, **`importSessionBookmarks`**, **`importStagingBookmarks`** – Multi-stage import pipeline tables tracking status and provenance.
- **`backupsTable`** – Metadata for user data exports linking to assets.
- **`config`** – Simple key-value store for application settings.

## Schema Implementation Details

### Type-Safe Relations

The schema defines explicit relations using Drizzle's `relations` API (lines ~969-1270 in [`schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/schema.ts)), enabling type-safe joins throughout the backend. These include `userRelations` (linking users to their bookmarks, tags, and webhooks), `bookmarkRelations` (connecting bookmarks to their tags, highlights, and assets), and `listCollaboratorsRelations` for shared access control.

### Indexing Strategy

Performance-critical tables implement composite indexes for cursor-based pagination. The `bookmarks` table uses indexes like `userId_archived_createdAt_id_idx` to support efficient filtering by archive status while maintaining chronological sort order. Junction tables consistently index both foreign key columns to optimize join performance in both directions.

## Working with the Schema

The following examples demonstrate common operations using the Drizzle client exported from [`packages/db/drizzle.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/drizzle.ts).

### Creating a New User

```typescript
import { db } from '@karakeep/db/drizzle';
import { users } from '@karakeep/db/schema';
import { eq } from 'drizzle-orm';

// Insert a new user
await db
  .insert(users)
  .values({
    name: 'Alice Example',
    email: 'alice@example.com',
    password: '<hashed‑password>',
    salt: '<random‑salt>',
    role: 'user',
  })
  .returning();

// Fetch a user by email
const alice = await db.select().from(users).where(eq(users.email, 'alice@example.com'));

```

*Schema reference*: `users` table definition at lines 32-44 in [`packages/db/schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/schema.ts).

### Adding a Bookmark with a Tag

```typescript
import { db } from '@karakeep/db/drizzle';
import { bookmarks, bookmarkTags, tagsOnBookmarks } from '@karakeep/db/schema';
import { eq } from 'drizzle-orm';

// Insert a bookmark
const [bk] = await db
  .insert(bookmarks)
  .values({
    title: 'OpenAI Blog',
    userId: alice.id,
    type: 'link',
    source: 'web',
    archived: 0,
    favourited: 0,
  })
  .returning();

// Ensure a tag exists (upsert)
const [tag] = await db
  .insert(bookmarkTags)
  .values({ name: 'ai', userId: alice.id })
  .onConflictDoNothing()
  .returning();

// Attach the tag to the bookmark
await db
  .insert(tagsOnBookmarks)
  .values({
    bookmarkId: bk.id,
    tagId: tag.id,
    attachedBy: 'human',
  });

```

*Schema references*: `bookmarks` (lines 88-112), `bookmarkTags` (lines 525-545), and `tagsOnBookmarks` (lines 555-571).

### Querying Paginated Bookmarks

```typescript
import { db } from '@karakeep/db/drizzle';
import { bookmarks } from '@karakeep/db/schema';
import { desc, eq } from 'drizzle-orm';

const PAGE_SIZE = 20;

const page = await db
  .select()
  .from(bookmarks)
  .where(eq(bookmarks.userId, alice.id))
  .orderBy(desc(bookmarks.createdAt), desc(bookmarks.id))
  .limit(PAGE_SIZE);

```

This query leverages the composite index `bookmarks_userId_createdAt_id_idx` for efficient retrieval without table scans.

## Migration and Configuration Files

The database layer includes several supporting files beyond the schema definition:

- **[`packages/db/drizzle.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/drizzle.ts)** – Instantiates the Drizzle client with the SQLite connection.
- **[`packages/db/drizzle.config.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/drizzle.config.ts)** – Configuration for the migration runner, specifying the schema path and database URL.
- **[`packages/db/migrate.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/migrate.ts)** – CLI entry point for applying pending migrations.
- **[`packages/db/instrumentation.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/instrumentation.ts)** – Query logging wrapper for debugging database operations.
- **`packages/db/drizzle/*.sql`** – Incremental migration files (e.g., [`0085_add_embedding_status.sql`](https://github.com/karakeep-app/karakeep/blob/main/0085_add_embedding_status.sql)) that keep the database synchronized with schema changes.

## Summary

- **Single source of truth**: The entire schema lives in [`packages/db/schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/schema.ts), using Drizzle ORM to define 25+ tables with strict TypeScript types.
- **SQLite foundation**: All data persists in SQLite with boolean values stored as integers and timestamps using millisecond precision.
- **Performance optimized**: Composite indexes on `bookmarks`, `tagsOnBookmarks`, and junction tables support efficient pagination and filtering.
- **Relations defined**: The `relations` API (lines ~969-1270) provides type-safe joins for the tRPC routers and background workers.
- **Version controlled**: Migration files in `packages/db/drizzle/` track schema evolution and can be applied via the CLI script in [`migrate.ts`](https://github.com/karakeep-app/karakeep/blob/main/migrate.ts).

## Frequently Asked Questions

### What database engine does Karakeep use?

Karakeep uses **SQLite** as its underlying database engine, accessed through the Drizzle ORM. This choice provides zero-configuration deployment for self-hosted instances while supporting the relational complexity needed for tags, lists, and user management.

### Where is the database schema defined in the source code?

The schema is defined entirely in **[`packages/db/schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/packages/db/schema.ts)**. This file contains all table definitions, column types, indexes, and Drizzle relation mappings. Companion files like [`drizzle.ts`](https://github.com/karakeep-app/karakeep/blob/main/drizzle.ts) and [`drizzle.config.ts`](https://github.com/karakeep-app/karakeep/blob/main/drizzle.config.ts) handle client instantiation and migration configuration.

### How does Karakeep handle database migrations?

Migrations are managed through SQL files stored in `packages/db/drizzle/`. Each schema change is accompanied by an incremental SQL script (e.g., [`0085_add_embedding_status.sql`](https://github.com/karakeep-app/karakeep/blob/main/0085_add_embedding_status.sql)). The [`migrate.ts`](https://github.com/karakeep-app/karakeep/blob/main/migrate.ts) script applies these migrations using the configuration from [`drizzle.config.ts`](https://github.com/karakeep-app/karakeep/blob/main/drizzle.config.ts), ensuring the database schema remains synchronized with the TypeScript definitions.

### Are table relationships enforced at the database level?

While SQLite supports foreign keys, Karakeep relies primarily on **Drizzle's relations API** (defined at lines ~969-1270 in [`schema.ts`](https://github.com/karakeep-app/karakeep/blob/main/schema.ts)) for managing relationships in application code. This provides type-safe joins and cascade behavior through the ORM layer, with composite indexes ensuring referential lookups remain performant.