Database Patterns

Database Patterns

Quick Start

import { db } from '$lib/server/db';
import { nanoid } from 'nanoid';

// SELECT with user_id (row-level security)
const contact = db
	.prepare('SELECT * FROM contacts WHERE id = ? AND user_id = ?')
	.get(id, user_id) as Contact | undefined;

// INSERT with nanoid and timestamps
const stmt = db.prepare(
	'INSERT INTO contacts (id, user_id, name, created_at, updated_at) VALUES (?, ?, ?, ?, ?)',
);
stmt.run(nanoid(), user_id, name, Date.now(), Date.now());

Core Principles

• Prepared statements: Use for all queries (SQL injection

prevention)

• ID generation: Use nanoid() for all primary keys (no

auto-increment)

• Timestamps: Store as Unix epoch with Date.now() (milliseconds)
• Row-level security: Always include user_id in WHERE clause

(never query by ID alone)

• Transactions: Use for multi-table operations (all-or-nothing)
• Synchronous: better-sqlite3 is sync - no async/await needed

Reference Files

• schema.md - Complete schema with columns and

types

• relationships.md - Table

relationships and foreign keys

• query-examples.md - Joins,

transactions, and advanced patterns

Query Examples

Joins with Related Data

Contact with Interaction Count

import { db } from '$lib/server/db';

const stmt = db.prepare(`
  SELECT c.*, COUNT(i.id) as interaction_count
  FROM contacts c
  LEFT JOIN interactions i ON c.id = i.contact_id
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Contact with Tags

const stmt = db.prepare(`
  SELECT
    c.*,
    GROUP_CONCAT(t.name, ', ') as tag_names,
    GROUP_CONCAT(t.id) as tag_ids
  FROM contacts c
  LEFT JOIN contact_tags ct ON c.id = ct.contact_id
  LEFT JOIN tags t ON ct.tag_id = t.id
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Contact with All Related Data

const stmt = db.prepare(`
  SELECT
    c.*,
    COUNT(DISTINCT i.id) as interaction_count,
    COUNT(DISTINCT f.id) as pending_followups,
    COUNT(DISTINCT s.id) as social_link_count,
    GROUP_CONCAT(DISTINCT t.name, ', ') as tag_names
  FROM contacts c
  LEFT JOIN interactions i ON c.id = i.contact_id
  LEFT JOIN follow_ups f ON c.id = f.contact_id AND f.completed = 0
  LEFT JOIN social_links s ON c.id = s.contact_id
  LEFT JOIN contact_tags ct ON c.id = ct.contact_id
  LEFT JOIN tags t ON ct.tag_id = t.id
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Recent Interactions with Contact Details

const stmt = db.prepare(`
  SELECT i.*, c.name as contact_name, c.email as contact_email
  FROM interactions i
  JOIN contacts c ON i.contact_id = c.id
  WHERE i.user_id = ?
  ORDER BY i.created_at DESC
  LIMIT ?
`);
const recent_interactions = stmt.all(user_id, limit);

Transactions for Multi-Table Operations

Insert Contact with Tags

import { db } from '$lib/server/db';
import { nanoid } from 'nanoid';

const insert_contact_with_tags = db.transaction(
	(contact_data, tag_ids) => {
		// Insert contact
		const contact_stmt = db.prepare(`
    INSERT INTO contacts (id, user_id, name, email, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?)
  `);
		const contact_id = nanoid();
		const now = Date.now();
		contact_stmt.run(
			contact_id,
			user_id,
			contact_data.name,
			contact_data.email,
			now,
			now,
		);

		// Insert tags
		const tag_stmt = db.prepare(`
    INSERT INTO contact_tags (id, contact_id, tag_id, created_at)
    VALUES (?, ?, ?, ?)
  `);
		for (const tag_id of tag_ids) {
			tag_stmt.run(nanoid(), contact_id, tag_id, now);
		}

		return contact_id;
	},
);

// Execute transaction
const contact_id = insert_contact_with_tags(
	{ name: 'John Doe', email: 'john@example.com' },
	['tag_id_1', 'tag_id_2'],
);

Update Contact and Add Interaction

const update_contact_and_add_interaction = db.transaction(
	(contact_id, contact_updates, interaction_data) => {
		const now = Date.now();

		// Update contact
		const update_stmt = db.prepare(`
    UPDATE contacts
    SET name = ?, email = ?, updated_at = ?
    WHERE id = ? AND user_id = ?
  `);
		update_stmt.run(
			contact_updates.name,
			contact_updates.email,
			now,
			contact_id,
			user_id,
		);

		// Add interaction
		const interaction_stmt = db.prepare(`
    INSERT INTO interactions (id, user_id, contact_id, type, notes, created_at)
    VALUES (?, ?, ?, ?, ?, ?)
  `);
		interaction_stmt.run(
			nanoid(),
			user_id,
			contact_id,
			interaction_data.type,
			interaction_data.notes,
			now,
		);

		return { contact_id, interaction_id: nanoid() };
	},
);

// Execute
const result = update_contact_and_add_interaction(
	'contact_123',
	{ name: 'John Doe', email: 'john@example.com' },
	{ type: 'email', notes: 'Sent proposal' },
);

Bulk Insert Contacts

const bulk_insert_contacts = db.transaction((contacts_data) => {
	const stmt = db.prepare(`
    INSERT INTO contacts (id, user_id, name, email, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?)
  `);

	const now = Date.now();
	const inserted_ids = [];

	for (const contact of contacts_data) {
		const id = nanoid();
		stmt.run(id, user_id, contact.name, contact.email, now, now);
		inserted_ids.push(id);
	}

	return inserted_ids;
});

// Execute
const ids = bulk_insert_contacts([
	{ name: 'Alice', email: 'alice@example.com' },
	{ name: 'Bob', email: 'bob@example.com' },
	{ name: 'Charlie', email: 'charlie@example.com' },
]);

Reactive Updates with SvelteKit

Trigger Revalidation After Mutations

import { invalidate } from '$app/navigation';

// After INSERT/UPDATE/DELETE
stmt.run(/* ... */);
invalidate('app:contacts'); // Triggers reload of contacts data

Multiple Dependencies

// After updating a contact
const stmt = db.prepare(
	'UPDATE contacts SET name = ? WHERE id = ? AND user_id = ?',
);
stmt.run(name, id, user_id);

// Invalidate multiple dependencies
invalidate('app:contacts');
invalidate('app:interactions');
invalidate('app:dashboard');

In Remote Functions

import { command } from '$app/server';
import { db } from '$lib/server/db';
import * as v from 'valibot';

export const update_contact = command(
	v.object({
		id: v.string(),
		name: v.string(),
	}),
	async ({ id, name }) => {
		const stmt = db.prepare(
			'UPDATE contacts SET name = ?, updated_at = ? WHERE id = ? AND user_id = ?',
		);
		stmt.run(name, Date.now(), id, user_id);

		// Return invalidation hints
		return {
			success: true,
			invalidate: ['app:contacts'],
		};
	},
);

Pagination

Limit and Offset

const page = 1;
const per_page = 20;
const offset = (page - 1) * per_page;

const stmt = db.prepare(`
  SELECT * FROM contacts
  WHERE user_id = ?
  ORDER BY created_at DESC
  LIMIT ? OFFSET ?
`);
const contacts = stmt.all(user_id, per_page, offset);

// Get total count
const count_stmt = db.prepare(
	'SELECT COUNT(*) as total FROM contacts WHERE user_id = ?',
);
const { total } = count_stmt.get(user_id);

Cursor-Based Pagination

const cursor = '2024-01-01T00:00:00.000Z'; // Last item's created_at
const limit = 20;

const stmt = db.prepare(`
  SELECT * FROM contacts
  WHERE user_id = ? AND created_at < ?
  ORDER BY created_at DESC
  LIMIT ?
`);
const contacts = stmt.all(user_id, Date.parse(cursor), limit);

Search and Filtering

Full-Text Search

const search_term = '%john%';

const stmt = db.prepare(`
  SELECT * FROM contacts
  WHERE user_id = ?
    AND (
      name LIKE ? OR
      email LIKE ? OR
      company LIKE ? OR
      notes LIKE ?
    )
  ORDER BY name
`);
const results = stmt.all(
	user_id,
	search_term,
	search_term,
	search_term,
	search_term,
);

Filter by Tags

const tag_ids = ['tag1', 'tag2'];

const placeholders = tag_ids.map(() => '?').join(',');
const stmt = db.prepare(`
  SELECT DISTINCT c.*
  FROM contacts c
  JOIN contact_tags ct ON c.id = ct.contact_id
  WHERE c.user_id = ?
    AND ct.tag_id IN (${placeholders})
`);
const contacts = stmt.all(user_id, ...tag_ids);

Filter by Date Range

const start_date = Date.parse('2024-01-01');
const end_date = Date.parse('2024-12-31');

const stmt = db.prepare(`
  SELECT * FROM interactions
  WHERE user_id = ?
    AND created_at >= ?
    AND created_at <= ?
  ORDER BY created_at DESC
`);
const interactions = stmt.all(user_id, start_date, end_date);

Aggregations

Contact Statistics

const stmt = db.prepare(`
  SELECT
    COUNT(*) as total_contacts,
    COUNT(CASE WHEN email IS NOT NULL THEN 1 END) as with_email,
    COUNT(CASE WHEN company IS NOT NULL THEN 1 END) as with_company
  FROM contacts
  WHERE user_id = ?
`);
const stats = stmt.get(user_id);

Interaction Trends

const stmt = db.prepare(`
  SELECT
    type,
    COUNT(*) as count,
    DATE(created_at / 1000, 'unixepoch') as date
  FROM interactions
  WHERE user_id = ?
    AND created_at >= ?
  GROUP BY type, date
  ORDER BY date DESC
`);
const trends = stmt.all(
	user_id,
	Date.now() - 30 * 24 * 60 * 60 * 1000,
); // Last 30 days

Top Contacts by Interaction

const stmt = db.prepare(`
  SELECT
    c.id,
    c.name,
    c.email,
    COUNT(i.id) as interaction_count,
    MAX(i.created_at) as last_interaction
  FROM contacts c
  LEFT JOIN interactions i ON c.id = i.contact_id
  WHERE c.user_id = ?
  GROUP BY c.id
  ORDER BY interaction_count DESC
  LIMIT ?
`);
const top_contacts = stmt.all(user_id, 10);

Table Relationships

Overview

The database uses foreign keys with CASCADE deletes to maintain referential integrity. All tables are user-scoped via the user_id column.

Core Tables

contacts

Primary contact management table with user-scoped access.

Columns:

• id (TEXT PRIMARY KEY)
• user_id (TEXT) - FK to users table
• name (TEXT)
• email (TEXT)
• company (TEXT)
• position (TEXT)
• phone (TEXT)
• notes (TEXT)
• created_at (INTEGER)
• updated_at (INTEGER)
• in_network_since (INTEGER)

Relationships:

• One-to-many with interactions
• One-to-many with follow_ups
• One-to-many with social_links
• Many-to-many with tags (via contact_tags)

interactions

Communication history linked to contacts.

Columns:

• id (TEXT PRIMARY KEY)
• user_id (TEXT) - FK to users table
• contact_id (TEXT) - FK to contacts table
• type (TEXT) - e.g., 'email', 'call', 'meeting'
• notes (TEXT)
• created_at (INTEGER)

Relationships:

• Many-to-one with contacts

follow_ups

Scheduled follow-up tasks with completion tracking.

Columns:

• id (TEXT PRIMARY KEY)
• user_id (TEXT) - FK to users table
• contact_id (TEXT) - FK to contacts table
• due_date (INTEGER)
• notes (TEXT)
• completed (INTEGER) - Boolean (0/1)
• created_at (INTEGER)
• updated_at (INTEGER)

Relationships:

• Many-to-one with contacts

tags

User-defined tags for organizing contacts.

Columns:

• id (TEXT PRIMARY KEY)
• user_id (TEXT) - FK to users table
• name (TEXT)
• color (TEXT)
• created_at (INTEGER)

Relationships:

• Many-to-many with contacts (via contact_tags)

contact_tags

Join table for many-to-many contact/tag relationships.

Columns:

• id (TEXT PRIMARY KEY)
• contact_id (TEXT) - FK to contacts table
• tag_id (TEXT) - FK to tags table
• created_at (INTEGER)

Relationships:

• Many-to-one with contacts
• Many-to-one with tags

social_links

Social media profiles for contacts.

Columns:

• id (TEXT PRIMARY KEY)
• user_id (TEXT) - FK to users table
• contact_id (TEXT) - FK to contacts table
• platform (TEXT) - e.g., 'linkedin', 'twitter', 'github'
• url (TEXT)
• username (TEXT)
• created_at (INTEGER)

Relationships:

• Many-to-one with contacts

CASCADE Behavior

Deleting a user cascades to all their data:

users → contacts → interactions, follow_ups, social_links, contact_tags
users → tags → contact_tags

User-Scoped Queries

All queries must include user_id in the WHERE clause for row-level security:

// ✅ Correct
const stmt = db.prepare(
	'SELECT * FROM contacts WHERE id = ? AND user_id = ?',
);
const contact = stmt.get(id, user_id);

// ❌ Wrong - security vulnerability
const stmt = db.prepare('SELECT * FROM contacts WHERE id = ?');
const contact = stmt.get(id);

Common Join Patterns

Contact with Tags

const stmt = db.prepare(`
  SELECT c.*, GROUP_CONCAT(t.name) as tag_names
  FROM contacts c
  LEFT JOIN contact_tags ct ON c.id = ct.contact_id
  LEFT JOIN tags t ON ct.tag_id = t.id
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Contact with Interaction Count

const stmt = db.prepare(`
  SELECT c.*, COUNT(i.id) as interaction_count
  FROM contacts c
  LEFT JOIN interactions i ON c.id = i.contact_id
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Contact with Pending Follow-ups

const stmt = db.prepare(`
  SELECT c.*, COUNT(f.id) as pending_followups
  FROM contacts c
  LEFT JOIN follow_ups f ON c.id = f.contact_id AND f.completed = 0
  WHERE c.user_id = ?
  GROUP BY c.id
`);
const contacts = stmt.all(user_id);

Database Schema Reference

Complete schema for devhub-crm SQLite database.

Core Tables

contacts

Contact management with user-scoped access.

CREATE TABLE contacts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  company TEXT,
  title TEXT,
  github_username TEXT,
  avatar_url TEXT,
  is_vip INTEGER DEFAULT 0,
  birthday TEXT,
  notes TEXT,
  last_contacted_at INTEGER,
  in_network_since INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

interactions

Communication history with contacts.

CREATE TABLE interactions (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  type TEXT NOT NULL,
  note TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
)

follow_ups

Scheduled follow-up tasks.

CREATE TABLE follow_ups (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  due_date INTEGER NOT NULL,
  note TEXT,
  completed INTEGER DEFAULT 0,
  completed_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
)

tags

User-defined tags for organization.

CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  color TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

contact_tags

Many-to-many relationship between contacts and tags.

CREATE TABLE contact_tags (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  tag_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  UNIQUE(contact_id, tag_id)
)

social_links

Social media profiles for contacts.

CREATE TABLE social_links (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  platform TEXT NOT NULL,
  url TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
)

Supporting Tables

interaction_types

Customizable interaction type definitions.

CREATE TABLE interaction_types (
  id TEXT PRIMARY KEY,
  user_id TEXT,
  value TEXT NOT NULL,
  label TEXT NOT NULL,
  icon TEXT NOT NULL,
  color TEXT NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
  UNIQUE(user_id, value)
)

github_following_cache

Cached GitHub following data.

CREATE TABLE github_following_cache (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  profile_data TEXT NOT NULL,
  cached_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

Auth Tables (better-auth)

user

CREATE TABLE user (
  id TEXT NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  emailVerified INTEGER NOT NULL,
  image TEXT,
  createdAt DATE NOT NULL,
  updatedAt DATE NOT NULL
)

account

CREATE TABLE account (
  id TEXT NOT NULL PRIMARY KEY,
  accountId TEXT NOT NULL,
  providerId TEXT NOT NULL,
  userId TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE,
  accessToken TEXT,
  refreshToken TEXT,
  idToken TEXT,
  accessTokenExpiresAt DATE,
  refreshTokenExpiresAt DATE,
  scope TEXT,
  password TEXT,
  createdAt DATE NOT NULL,
  updatedAt DATE NOT NULL
)

session

CREATE TABLE session (
  id TEXT NOT NULL PRIMARY KEY,
  expiresAt DATE NOT NULL,
  token TEXT NOT NULL UNIQUE,
  createdAt DATE NOT NULL,
  updatedAt DATE NOT NULL,
  ipAddress TEXT,
  userAgent TEXT,
  userId TEXT NOT NULL REFERENCES user(id) ON DELETE CASCADE
)

User Profile Tables

user_profiles

CREATE TABLE user_profiles (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  github_username TEXT,
  bio TEXT,
  tagline TEXT,
  location TEXT,
  website TEXT,
  visibility TEXT NOT NULL DEFAULT 'public',
  custom_slug TEXT UNIQUE,
  qr_code_url TEXT,
  qr_settings TEXT,
  github_synced_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

user_social_links

CREATE TABLE user_social_links (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  platform TEXT NOT NULL,
  url TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

user_preferences

CREATE TABLE user_preferences (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL UNIQUE,
  date_format TEXT NOT NULL DEFAULT 'YYYY-MM-DD',
  time_format TEXT NOT NULL DEFAULT '24h',
  default_contact_sort TEXT NOT NULL DEFAULT 'name',
  default_follow_up_days INTEGER NOT NULL DEFAULT 7,
  default_interaction_type TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
)

profile_views

CREATE TABLE profile_views (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  viewer_id TEXT,
  qr_scan INTEGER DEFAULT 0,
  referrer TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE,
  FOREIGN KEY (viewer_id) REFERENCES user(id) ON DELETE SET NULL
)

Relationships

user (1) ----< (*) contacts
user (1) ----< (*) tags
user (1) ----< (*) user_profiles
user (1) ----< (*) account
user (1) ----< (*) session

contacts (1) ----< (*) interactions
contacts (1) ----< (*) follow_ups
contacts (1) ----< (*) social_links
contacts (*) ----< (*) tags (via contact_tags)

Naming Conventions

• Tables: snake_case plural (contacts, follow_ups)
• Columns: snake_case (user_id, created_at)
• Primary Keys: Always "id" (TEXT from nanoid)
• Foreign Keys: table_name_id pattern (user_id, contact_id)
• Timestamps: INTEGER Unix epoch milliseconds

Cascade Rules

• DELETE user → CASCADE deletes all user-owned data
• DELETE contact → CASCADE deletes interactions, follow_ups,

social_links, contact_tags

• DELETE tag → CASCADE removes contact_tags entries

Indexes

Recommended indexes for performance:

CREATE INDEX idx_contacts_user_id ON contacts(user_id);
CREATE INDEX idx_interactions_contact_id ON interactions(contact_id);
CREATE INDEX idx_follow_ups_contact_id ON follow_ups(contact_id);
CREATE INDEX idx_contact_tags_contact_id ON contact_tags(contact_id);
CREATE INDEX idx_contact_tags_tag_id ON contact_tags(tag_id);