Database

Open Genie uses PostgreSQL with Drizzle ORM for type-safe database access and migrations.

Setup

The database client is initialized in lib/db/index.ts using the postgres driver:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

The Drizzle config (drizzle.config.ts) points to lib/db/schema.ts for schema definitions and outputs migrations to ./drizzle/.

Schema

All tables are defined in lib/db/schema.ts. Below is a reference for every table.

Users & Devices

users

Column	Type	Description
id	uuid (PK)	Auto-generated
name	text	Display name
email	text (unique)	Email address
createdAt	timestamp	Auto-set

devices

Column	Type	Description
id	uuid (PK)	Auto-generated
userId	uuid (FK → users)	Owner
name	text	Device display name
type	text	"phone" | "tablet" | "tv" | "web"
pushToken	text	Expo/Firebase push token
lastSeen	timestamp	Last WebSocket disconnect time
createdAt	timestamp	Auto-set

pairingCodes

Column	Type	Description
id	uuid (PK)	Auto-generated
code	text (unique)	6-character pairing code
userId	uuid (FK → users)	Who generated the code
expiresAt	timestamp	Code expiration (typically 10 min)
usedAt	timestamp	When code was consumed
deviceId	uuid	Device that used the code

Conversations & Messages

conversations

Column	Type	Description
id	uuid (PK)	Auto-generated
deviceId	uuid (FK → devices)	Owning device
title	text	Conversation title (auto-generated or manual)
createdAt	timestamp	Auto-set
updatedAt	timestamp	Auto-updated

messages

Column	Type	Description
id	uuid (PK)	Auto-generated
conversationId	uuid (FK → conversations)	Parent conversation
role	text	"user" | "assistant" | "system" | "tool"
content	text	Message text
toolCalls	jsonb	Tool call metadata (if role = assistant)
toolResult	jsonb	Tool execution result (if role = tool)
createdAt	timestamp	Auto-set

Memory

memoryEntries

Column	Type	Description
id	uuid (PK)	Auto-generated
category	text	Grouping (e.g., "family", "preferences")
key	text	Entry identifier within category
value	text	Stored information
source	text	How it was created ("user" | "extraction" | "api")
createdAt	timestamp	Auto-set
updatedAt	timestamp	Auto-updated

Unique constraint: (category, key) — upserts update existing entries.

Media

mediaFiles

Column	Type	Description
id	uuid (PK)	Auto-generated
path	text (unique)	Relative path within storage
name	text	Filename
type	text	"video" | "audio" | "image" | "document"
mimeType	text	MIME type
size	bigint	File size in bytes
duration	real	Duration in seconds (video/audio)
width	integer	Width in pixels (video/image)
height	integer	Height in pixels (video/image)
thumbnailPath	text	Path to generated thumbnail
metadata	jsonb	Extended metadata (EXIF, music tags, etc.)
lastPosition	real	Last playback position in seconds
createdAt	timestamp	Auto-set
updatedAt	timestamp	Auto-updated

playlists

Column	Type	Description
id	uuid (PK)	Auto-generated
name	text	Playlist name
description	text	Optional description
createdAt	timestamp	Auto-set

playlistItems

Column	Type	Description
id	uuid (PK)	Auto-generated
playlistId	uuid (FK → playlists)	Parent playlist
mediaFileId	uuid (FK → mediaFiles)	Media file
position	integer	Sort order within playlist
addedAt	timestamp	Auto-set

Automation

cronJobs

Column	Type	Description
id	uuid (PK)	Auto-generated
name	text	Job display name
schedule	text	Cron expression (e.g., "0 * * * *")
actionType	text	Action to execute
actionPayload	jsonb	Parameters for the action
enabled	boolean	Whether the job is active
lastRunAt	timestamp	Last execution time
nextRunAt	timestamp	Next scheduled execution
createdAt	timestamp	Auto-set

jobRuns

Column	Type	Description
id	uuid (PK)	Auto-generated
jobId	uuid (FK → cronJobs)	Parent job
status	text	"running" | "success" | "error"
output	jsonb	Execution result
error	text	Error message (if failed)
startedAt	timestamp	Auto-set
finishedAt	timestamp	Completion time

webhooks

Column	Type	Description
id	uuid (PK)	Auto-generated
name	text	Webhook display name
secret	text	Shared secret for validation
actionType	text	Action to execute on trigger
actionPayload	jsonb	Default parameters (merged with webhook data)
enabled	boolean	Whether the webhook is active
lastTriggeredAt	timestamp	Last trigger time
createdAt	timestamp	Auto-set

IoT Devices

Hardware integrations — ESP32 music boxes, Tuya smart plugs, Bluetooth audio sinks — are tracked separately from paired client devices.

iotDevices

Column	Type	Description
id	uuid (PK)	Auto-generated
name	varchar(255)	Display name (e.g. "Living Room Music Box")
kind	varchar(32)	"esp32_music_box" | "tuya_plug" | "bluetooth_audio"
externalId	varchar(255)	Hardware ID — ESP32 MAC, Tuya deviceId, or BT MAC
config	jsonb	Per-kind config (local key, auto-connect flag, …). Secrets stay in env/settings.
lastSeen	timestamp	Last WS connect or API ping
createdAt	timestamp	Auto-set

Indexes on kind and externalId for fast lookups by the WS handler.

iotEvents

Append-only event log — every state change is a row. Rows older than 30 days are pruned automatically.

Column	Type	Description
id	uuid (PK)	Auto-generated
iotDeviceId	uuid (FK → iotDevices, SET NULL on delete)	Source device
event	varchar(64)	Event name: "connected", "play", "stop", "bt_connect", …
detail	text	Human-readable description
level	varchar(16)	"info" | "warn" | "error"
createdAt	timestamp	Auto-set

Indexes on createdAt (for time-range queries) and iotDeviceId (for per-device views).

Cameras

cameraConfigs

Column	Type	Description
id	uuid (PK)	Auto-generated
name	text	Camera display name
url	text	RTSP or HTTP stream URL
type	text	"rtsp" | "http" | "mjpeg"
captureIntervalSec	integer	Seconds between captures (default: 60)
visionPrompt	text	Custom prompt for vision analysis
alertRules	jsonb	Array of alert rule definitions
enabled	boolean	Whether capture is active
createdAt	timestamp	Auto-set

cameraEvents

Column	Type	Description
id	uuid (PK)	Auto-generated
cameraId	uuid (FK → cameraConfigs)	Source camera
imagePath	text	Path to captured image
analysis	jsonb	Parsed vision response
alertLevel	text	null | "info" | "warning" | "critical"
alertMessage	text	Alert description (if triggered)
createdAt	timestamp	Auto-set

Notifications & Reminders

notifications

Column	Type	Description
id	uuid (PK)	Auto-generated
deviceId	uuid (FK → devices)	Target device
title	text	Notification title
body	text	Notification body
level	text	"info" | "warning" | "critical"
category	text	Push notification category
data	jsonb	Extra payload data
status	text	"pending" | "sent" | "delivered" | "read"
createdAt	timestamp	Auto-set
readAt	timestamp	When user read it

reminders

Column	Type	Description
id	uuid (PK)	Auto-generated
title	text	Reminder text
dueAt	timestamp	When to trigger
repeat	text	Repeat schedule (cron or keyword)
completed	boolean	Whether it's done
createdAt	timestamp	Auto-set

Plugins

Three tables manage the plugin runtime. Non-secret preferences live in data/plugins/<slug>/settings.json (on-disk, human-editable). Sensitive values are stored only in the database.

plugins

Install registry — source of truth for whether a plugin is installed and enabled.

Column	Type	Description
id	uuid (PK)	Auto-generated
slug	varchar(100) (unique)	Plugin identifier — matches the folder name
version	varchar(32)	Installed semver version
enabled	boolean	Whether the plugin is running
source	varchar(16)	"upload" | "hub"
permissionsGranted	jsonb	Permissions the user approved at install time
manifest	jsonb	Full parsed manifest.json
installedAt	timestamp	Auto-set
enabledAt	timestamp	Set when the plugin is first enabled

pluginOauthTokens

One row per (plugin, provider). Tokens are encrypted at rest.

Column	Type	Description
id	uuid (PK)	Auto-generated
pluginSlug	varchar(100)	Plugin that owns this token
provider	varchar(32)	"google" | "slack" | "github" | "microsoft"
accessTokenEnc	text	AES-256-GCM encrypted access token
refreshTokenEnc	text	AES-256-GCM encrypted refresh token
expiresAt	timestamp	Token expiry
scopes	jsonb	Granted OAuth scopes
accountLabel	varchar(255)	Display string shown in the UI (e.g. "you@gmail.com")
updatedAt	timestamp	Auto-updated

Index on pluginSlug for fast per-plugin lookups.

pluginSecrets

Encrypted key-value store for secret-type settings fields and anything the plugin stores via genie.secrets.*.

Column	Type	Description
pluginSlug	varchar(100)	Owning plugin
key	varchar(100)	Secret name
encryptedValue	text	AES-256-GCM encrypted value
updatedAt	timestamp	Auto-updated

Compound index on (pluginSlug, key) (acts as a composite primary key).

Encryption key: GENIE_SECRETS_KEY env var (32-byte hex). If unset, plugins that declare secret fields or OAuth providers cannot be enabled.

Relations

Key foreign-key relationships:

users ──< devices ──< conversations ──< messages
                  ──< notifications
                  
playlists ──< playlistItems >── mediaFiles

cameraConfigs ──< cameraEvents

cronJobs ──< jobRuns

iotDevices ──< iotEvents

plugins (no FK — linked to on-disk folder by slug)
pluginOauthTokens (linked to plugins by pluginSlug)
pluginSecrets (linked to plugins by pluginSlug)

All cascading deletes are configured so removing a parent removes its children.

Migrations

# Generate a migration from schema changes
npm run db:generate

# Apply pending migrations
npm run db:migrate

# Push schema directly (development only, may lose data)
npm run db:push

# Open Drizzle Studio for visual exploration
npm run db:studio

Migration files are stored in drizzle/ and should be committed to version control.