Database Schema for Karakeep: Complete Drizzle ORM Table Reference

The Karakeep database schema is defined in 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, 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.
• 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), 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.

Creating a New User

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.

Adding a Bookmark with a Tag

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

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 – Instantiates the Drizzle client with the SQLite connection.
• packages/db/drizzle.config.ts – Configuration for the migration runner, specifying the schema path and database URL.
• packages/db/migrate.ts – CLI entry point for applying pending migrations.
• 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) that keep the database synchronized with schema changes.

Summary

• Single source of truth: The entire schema lives in 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.

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. This file contains all table definitions, column types, indexes, and Drizzle relation mappings. Companion files like drizzle.ts and 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). The migrate.ts script applies these migrations using the configuration from 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) 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}