columnist-db

Lightning-fast, semantic-ready client-side database with IndexedDB persistence, full-text search, vector search, React hooks, and cross-device synchronization.

Features

🚀 Lightning Fast - Columnar storage with smart indexing
🔍 Full-Text Search - TF-IDF scoring with relevance ranking
🤖 Vector Search - Semantic search with cosine/dot/euclidean similarity
💾 Real Persistence - IndexedDB with automatic schema management
⚛️ React Ready - Hooks for reactive queries and subscriptions
🔒 Type Safe - Full TypeScript support with schema inference
📊 Analytics - Built-in performance statistics
🔄 Real-time - Live subscriptions and automatic UI updates
📱 Offline First - Works completely client-side
🤝 Cross-Device Sync - Multi-device synchronization with conflict resolution
🛠️ Developer Tools - Built-in database inspector
🔄 Zod 4.1 Codecs - Bi-directional data transformations with automatic serialization
🤖 MCP Integration - Model Context Protocol server for AI integration

Zod 4.1 Codec Integration

Columnist now leverages Zod 4.1's new codec API for seamless bi-directional data transformations:

import { defineTable } from 'columnist-db-core'
import { z } from 'zod'

// Define a table with automatic Date and JSON transformations
const userTable = defineTable()
  .column('id', 'number')
  .column('name', 'string')
  .column('createdAt', 'date')
  .column('preferences', 'json')
  .primaryKey('id')
  .codec() // Creates automatic codec for Date ↔ ISO string and JSON ↔ string

// Dates and JSON objects are automatically converted:
await db.insert({
  name: "John Doe",
  createdAt: new Date(), // Automatically converted to ISO string for storage
  preferences: { theme: 'dark', notifications: true } // Automatically stringified
})

// When retrieved, they're automatically converted back:
const user = await db.find({ where: { name: "John Doe" } })
console.log(user.createdAt instanceof Date) // true
console.log(typeof user.preferences) // object

Supported Codecs

Date Codec: Automatic Date ↔ ISO string conversion
JSON Codec: Automatic object ↔ JSON string conversion
BigInt Codec: Automatic BigInt ↔ string conversion
Custom Codecs: Create your own with Zod's z.codec() API

Quick Start

Complete Package (All-in-One)

npm install columnist-db zod

Modular Installation (shadcn-style)

Install only what you need:

# Core database engine
npm install columnist-db-core zod

# React hooks integration
npm install columnist-db-hooks

# Prebuilt table schemas
npm install columnist-db-table-notes
npm install columnist-db-table-tasks
npm install columnist-db-table-chat

# Plugin extensions
npm install columnist-db-plugin-convex-sync
npm install columnist-db-plugin-openai-embedding

Basic Usage

import { ColumnistDB } from 'columnist-db-core'

// Initialize database with auto-initialization
const db = await ColumnistDB.init('my-app', {
  autoInitialize: true, // Automatically setup required tables
  sync: {
    enabled: false,     // Disable sync for simple use cases
    autoRegisterDevices: false
  },
  vectorSearch: {
    autoConfigure: true,
    defaultDimension: 384
  }
})

// Insert records
await db.insert({ content: "Hello world", timestamp: new Date() }, 'messages')

// Full-text search
const results = await db.search("hello", { table: 'messages' })

// Get all records
const allMessages = await db.getAll('messages')

Chat Application Example

import { ColumnistDB } from 'columnist-db-core'

// Chat message schema with vector embeddings
const messageSchema = {
  columns: {
    id: "number",
    role: "string",
    content: "string",
    timestamp: "date",
    embedding: { type: 'vector', dimension: 384 }
  },
  primaryKey: "id",
  searchableFields: ["content"],
  secondaryIndexes: ["timestamp", "role"]
}

// Initialize with chat schema
const db = await ColumnistDB.init('chat-memory', {
  tables: {
    messages: messageSchema  // Auto-create tables on init
  },
  autoInitialize: true,
  sync: {
    enabled: false
  }
})

// Store chat messages
await db.insert({
  role: "user",
  content: "Hello, how are you?",
  timestamp: new Date(),
  embedding: await generateEmbedding("Hello, how are you?")
}, 'messages')

// Semantic search for similar messages
const similar = await db.search("greeting", { 
  table: 'messages',
  vectorField: 'embedding'
})

Advanced Schema Definition

import { Columnist, defineTable } from 'columnist-db'
import { z } from 'zod'
      timestamp: z.date().default(() => new Date())
    }))
    .build()
}

// Initialize database
const db = await Columnist.init("my-app", { schema })

// Insert data
await db.insert({
  content: "Hello world!",
  user_id: 1
}, "messages")

// Search with TF-IDF
const results = await db.search("hello", {
  table: "messages",
  limit: 10
})

// Indexed queries
const recent = await db.find({
  table: "messages",
  where: { user_id: 1 },
  orderBy: { field: "timestamp", direction: "desc" },
  limit: 50
})

React Integration

import { useColumnist, useLiveQuery } from 'columnist-db/hooks'
// or for modular approach:
import { useColumnist, useLiveQuery } from 'columnist-db-hooks'

function ChatApp() {
  const { insert, isLoading, error } = useColumnist({
    name: "chat-app",
    schema: mySchema
  })
  
  // Reactive query with auto-updates
  const { data: messages } = useLiveQuery({
    table: "messages",
    where: { user_id: currentUserId },
    orderBy: "timestamp",
    deps: [currentUserId]
  })

  const sendMessage = async (content: string) => {
    await insert({ content, user_id: currentUserId })
    // UI automatically updates via subscription!
  }

  return (
    <div>
      {messages.map(msg => (
        <div key={msg.id}>{msg.content}</div>
      ))}
    </div>
  )
}

API Reference

Core Database

`Columnist.init(name, options)`

Initialize a database instance.

const db = await Columnist.init("my-app", {
  schema: mySchema,
  version: 1,
  migrations: {
    2: (db, tx, oldVersion) => {
      // Migration logic
    }
  }
})

CRUD Operations

// Insert
const { id } = await db.insert(record, "table")

// Update
await db.update(id, updates, "table")

// Delete
await db.delete(id, "table")

// Upsert
await db.upsert(record, "table")

// Get all
const records = await db.getAll("table", limit)

Querying

// Indexed queries
const results = await db.find({
  table: "messages",
  where: { 
    user_id: 1,
    timestamp: { $gte: new Date("2025-01-01") }
  },
  orderBy: { field: "timestamp", direction: "desc" },
  limit: 100,
  offset: 0
})

// Full-text search
const matches = await db.search("query", {
  table: "messages",
  limit: 50,
  user_id: 1 // Additional filters
})

// Paginated queries
const { data, nextCursor } = await db.findPage({
  table: "messages",
  orderBy: "id",
  limit: 50,
  cursor: previousCursor
})

Vector Search

// Register embedder
db.registerEmbedder("messages", async (text: string) => {
  // Your embedding function - must return Float32Array
  const response = await fetch('/api/embed', {
    method: 'POST',
    body: JSON.stringify({ text })
  })
  const { embedding } = await response.json()
  return new Float32Array(embedding)
})

// Vector search
const similar = await db.vectorSearch("messages", queryVector, {
  metric: "cosine", // "cosine" | "dot" | "euclidean"
  limit: 10,
  where: { user_id: 1 } // Optional filters
})

Analytics & Export

// Database statistics
const stats = await db.getStats()
// { totalTables: 2, tables: {...}, overallBytes: 1024000 }

const tableStats = await db.getStats("messages")
// { count: 1500, totalBytes: 500000 }

// Export/Import
const backup = await db.export({ tables: ["messages"] })
await db.import(backup, "replace") // or "merge"

// Subscriptions
const unsubscribe = db.subscribe("messages", (event) => {
  console.log(event.type, event.record) // "insert" | "update" | "delete"
})

React Hooks

`useColumnist(options)`

Database instance hook with convenience methods.

const {
  db,              // Database instance
  insert,          // Insert function
  update,          // Update function  
  delete: del,     // Delete function
  find,            // Find function
  search,          // Search function
  isLoading,       // Loading state
  error            // Error state
} = useColumnist({
  name: "my-app",
  schema: mySchema,
  version: 1
})

`useLiveQuery(options)`

Reactive query hook with automatic subscriptions.

const {
  data,            // Query results
  isLoading,       // Loading state
  error,           // Error state
  refetch          // Manual refetch
} = useLiveQuery({
  table: "messages",
  where: { user_id: currentUserId },
  orderBy: "timestamp",
  limit: 100,
  deps: [currentUserId], // Re-query dependencies
  subscribe: true        // Auto-subscribe to changes
})

`useSearch(options)`

Reactive search hook.

`useDeviceManager()`

Device management hook for cross-device synchronization.

const {
  currentDevice,     // Current device information
  allDevices,        // All known devices
  onlineDevices,     // Currently online devices
  updateLastSeen,    // Update device presence
  isLoading,
  error
} = useDeviceManager()

Advanced Features

Model Context Protocol (MCP) Integration

Built-in MCP server for AI integration with full database access:

import { ColumnistMCPServer } from 'columnist-db';

// Start MCP server for AI integration
const mcpServer = new ColumnistMCPServer({
  databaseName: 'my-chat-app',
  authToken: 'your-secret-token',
  port: 3000
});

await mcpServer.start();

// AI systems can now access:
// - Chat message history and context
// - User profiles and preferences  
// - Knowledge base content
// - Real-time conversation data
// - Semantic search across all tables

MCP Resource Examples:

mcp://columnist/chat-app/messages/?conversation=123&limit=50
mcp://columnist/chat-app/users/online
mcp://columnist/chat-app/messages/search?query=project+deadline
mcp://columnist/knowledge-base/articles/vector?text=AI+ethics

Cross-Device Synchronization

Real-time sync across multiple devices with intelligent conflict resolution:

// Get current device information
const deviceManager = await db.getDeviceManager()
const currentDevice = await deviceManager.getCurrentDevice()

// Device-aware conflict resolution prefers:
// 1. Online devices over offline devices  
// 2. Most recent timestamps as fallback
// 3. Local device when all else is equal

// Track device presence with heartbeat
await deviceManager.startPresenceTracking(30000) // 30s heartbeat

// Get all known devices
const allDevices = await deviceManager.getAllDevices()
const onlineDevices = await deviceManager.getOnlineDevices()

Modular Architecture (shadcn-style)

Columnist follows a modular, composable architecture inspired by shadcn/ui:

Core Packages:

columnist-db-core - Base database functionality
columnist-db-hooks - React hooks for database operations

Table Packages:

columnist-db-table-notes - Pre-built notes table schema
columnist-db-table-tasks - Pre-built tasks table schema
columnist-db-table-chat - Pre-built chat table schema

Plugin Packages:

columnist-db-plugin-convex-sync - Sync with Convex backend
columnist-db-plugin-openai-embedding - Vector search with OpenAI

Usage Pattern:

import { notesSchema } from 'columnist-db-table-notes'
import { convexSync } from 'columnist-db-plugin-convex-sync'
import { useNotes, useLiveNotes } from 'columnist-db-hooks'

// Compose your database
const db = Columnist.init('my-app', {
  schema: { notes: notesSchema },
  plugins: [convexSync()]
})

// Use generated hooks
const { notes, createNote } = useNotes()
const liveNotes = useLiveNotes() // Real-time subscription

// Get current device information
const currentDevice = await db.getCurrentDevice()
// {
//   deviceId: "unique-fingerprint",
//   deviceName: "Windows Chrome",
//   platform: "Win32",
//   os: "Windows 10",
//   browser: "Chrome",
//   capabilities: ["offline", "encryption"],
//   createdAt: Date,
//   lastSeen: Date
// }

// Get all known devices
const allDevices = await db.getAllDevices()

// Get online devices
const onlineDevices = await syncManager.getOnlineDevices()

// Update device presence
await db.getDeviceManager().updateLastSeen()

Device-Aware Conflict Resolution

The sync system now includes device-aware conflict resolution that prefers:

Online devices over offline devices
Most recent timestamps as fallback
Local device when all else is equal

Device Presence Tracking

// Start presence tracking (heartbeat every 30 seconds)
await db.startDevicePresenceTracking(30000)

// Get device status
const status = await syncManager.getDeviceStatus(deviceId)
// "online" | "offline"

MCP Server Security Features

Enterprise-grade security for AI database access:

// JWT Authentication
const authManager = new AuthManager({
  secret: process.env.MCP_AUTH_SECRET,
  requireAuth: true
});

// Security policies
const securityManager = new SecurityManager({
  maxConnections: 100,
  rateLimit: { windowMs: 60000, max: 100 },
  blockedTables: ['passwords', 'api_keys'],
  allowedOperations: ['query', 'search', 'read']
});

// Field-level security
const sanitizedData = securityManager.sanitizeOutput(userData, ['admin']);
// Removes sensitive fields like passwords, tokens, private keys

`useSearch(options)`

Reactive search hook.

const {
  data,            // Search results with scores
  isLoading,
  error,
  refetch
} = useSearch({
  table: "messages",
  query: searchTerm,
  limit: 50,
  deps: [searchTerm]
})

`useStats(options)`

Database statistics hook.

const {
  stats,           // Statistics object
  isLoading,
  error,
  refetch
} = useStats({
  table: "messages",      // Optional: specific table
  refreshInterval: 5000   // Auto-refresh interval
})

// Memory usage helper
const { estimatedRAM } = useMemoryUsage()

Schema Definition

Fluent Builder API

import { defineTable } from 'columnist-db-core'
import { z } from 'zod'

const messageTable = defineTable()
  .column("id", "number")
  .column("content", "string")
  .column("user_id", "number")
  .column("timestamp", "date")
  .column("metadata", "json")
  .column("priority", "number")
  .primaryKey("id")                    // Default: "id"
  .searchable("content")               // Full-text search fields
  .indexes("user_id", "timestamp")     // Secondary indexes
  .validate(z.object({                 // Optional validation
    content: z.string().min(1),
    user_id: z.number().positive(),
    priority: z.number().min(1).max(5).default(3),
    timestamp: z.date().default(() => new Date())
  }))
  .build()

Vector Configuration

const documentsTable = defineTable()
  .column("id", "number")
  .column("title", "string")
  .column("content", "string")
  .searchable("title", "content")
  .vector({
    field: "content",    // Source field for embeddings
    dims: 384           // Embedding dimensions
  })
  .build()

Type Inference

// Automatic TypeScript types
type MessageType = InferTableType<typeof messageTable>
// { id: number; content: string; user_id: number; timestamp: Date; ... }

// Type-safe database
const typed = db.typed<typeof schema>()
await typed.insert({
  content: "Hello",    // ✅ Typed and validated
  user_id: 1
}, "messages")

Migrations

const db = await Columnist.init("my-app", {
  schema: mySchemaV2,
  version: 2,
  migrations: {
    2: (db, tx, oldVersion) => {
      // Add new index
      const store = tx.objectStore("messages")
      if (!Array.from(store.indexNames).includes("priority")) {
        store.createIndex("priority", "priority")
      }
    }
  }
})

Performance

Memory Usage

~48 bytes per record in RAM (excluding content)
Compressed token indexing for search
Lazy loading of large result sets
Efficient date/JSON serialization

Query Performance

Indexed queries: O(log n) lookup time
Full-text search: O(k) where k = matching documents
Vector search: O(n) with planned optimizations
Range queries: Optimal with proper indexing

Storage

Columnar IndexedDB storage
Automatic compression where possible
Delta-based statistics tracking
Efficient schema evolution

Showcase Application

A complete demonstration app is included in the /showcase directory, featuring:

Real-time CRUD operations with automatic UI updates
Cross-device synchronization demo with multiple device simulation
Advanced search capabilities including full-text and vector search
Device management interface showing online/offline status
Performance metrics and memory usage monitoring

To run the showcase:

cd showcase
npm install
npm run dev

Examples

Chat Application

const chatSchema = {
  messages: defineTable()
    .column("id", "number")
    .column("room_id", "string")
    .column("user_id", "number") 
    .column("content", "string")
    .column("timestamp", "date")
    .searchable("content")
    .indexes("room_id", "user_id", "timestamp")
    .build(),
    
  users: defineTable()
    .column("id", "number")
    .column("name", "string")
    .column("avatar", "string")
    .searchable("name")
    .build()
}

const db = await Columnist.init("chat-app", { schema: chatSchema })

// Send message
await db.insert({
  room_id: "general",
  user_id: currentUser.id,
  content: message
}, "messages")

// Search chat history
const results = await db.search(query, {
  table: "messages", 
  room_id: currentRoom
})

Document Store with Vector Search

// Register OpenAI embedder
db.registerEmbedder("documents", async (text) => {
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: text
  })
  return new Float32Array(response.data[0].embedding)
})

// Semantic document search
const similar = await db.vectorSearch("documents", queryEmbedding, {
  metric: "cosine",
  limit: 5
})

Browser Support

Chrome 58+ (full functionality)
Firefox 55+ (full functionality)
Safari 10+ (full functionality)
Edge 79+ (full functionality)
Mobile browsers with IndexedDB support

Cross-device sync requires:

Modern browser with IndexedDB and crypto support
Network connectivity for device presence tracking
Same-origin or CORS-enabled sync endpoints

Development

This is a monorepo managed with Turborepo. To develop locally:

# Install dependencies
npm install

# Build all packages
npm run build

# Run tests
npm test

# Develop a specific package
cd packages/core
npm run dev

# Run showcase app
cd showcase
npm run dev

Contributing

We welcome contributions! Please see our Contributing Guide for details.

Name		Name	Last commit message	Last commit date
Latest commit History 37 Commits
.github/ISSUE_TEMPLATE		.github/ISSUE_TEMPLATE
__tests__		__tests__
app		app
components		components
dist		dist
docs		docs
hooks		hooks
lib		lib
packages		packages
public		public
scripts		scripts
showcase		showcase
src		src
styles		styles
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE		LICENSE
README.md		README.md
components.json		components.json
debug-vector-test.js		debug-vector-test.js
implementation_plan.md		implementation_plan.md
install.txt		install.txt
jest.config.js		jest.config.js
middleware.ts		middleware.ts
next-env.d.ts		next-env.d.ts
next.config.mjs		next.config.mjs
package-lock.json		package-lock.json
package.json		package.json
pnpm-lock.yaml		pnpm-lock.yaml
postcss.config.mjs		postcss.config.mjs
tailwind.config.ts		tailwind.config.ts
test-browser-debug.html		test-browser-debug.html
test-browser-vector.js		test-browser-vector.js
test-local.js		test-local.js
test-vector-search.js		test-vector-search.js
test-vector-storage.js		test-vector-storage.js
test.js		test.js
theme-config.json		theme-config.json
tsconfig.base.json		tsconfig.base.json
tsconfig.json		tsconfig.json
tsconfig.lib.json		tsconfig.lib.json

License

codeisdemode/columnist-db

Folders and files

Latest commit

History

Repository files navigation

columnist-db

Features

Zod 4.1 Codec Integration

Supported Codecs

Quick Start

Complete Package (All-in-One)

Modular Installation (shadcn-style)

Basic Usage

Chat Application Example

Advanced Schema Definition

React Integration

API Reference

Core Database

Columnist.init(name, options)

CRUD Operations

Querying

Vector Search

Analytics & Export

React Hooks

useColumnist(options)

useLiveQuery(options)

useSearch(options)

useDeviceManager()

Advanced Features

Model Context Protocol (MCP) Integration

Cross-Device Synchronization

Modular Architecture (shadcn-style)

Device-Aware Conflict Resolution

Device Presence Tracking

MCP Server Security Features

useSearch(options)

useStats(options)

Schema Definition

Fluent Builder API

Vector Configuration

Type Inference

Migrations

Performance

Memory Usage

Query Performance

Storage

Showcase Application

Examples

Chat Application

Document Store with Vector Search

Browser Support

Development

Contributing

License

About

Resources

License

Contributing

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Contributors 2

Uh oh!

Languages

`Columnist.init(name, options)`

`useColumnist(options)`

`useLiveQuery(options)`

`useSearch(options)`

`useDeviceManager()`

`useSearch(options)`

`useStats(options)`

Packages