Under the Hood: Integration Data Model & Token Encryption (v0.1.0)

Focus Engine v0.1.0 ships the foundational infrastructure that every integration will depend on: a well-typed integrations database table, an AES-256-GCM encryption library for keeping OAuth tokens safe at rest, and a tRPC router that exposes the full integration lifecycle to the rest of the application.

This post explains the design decisions behind each piece.

---

The integrations Table

Every supported integration provider — GitHub, Google, Outlook, Gmail, Notion, Linear, Jira, Asana, Slack — is represented by a single row in the integrations table. The schema is defined in src/db/schema.ts.

Key fields:

Field	Purpose
userId	Foreign key to users with cascade delete
provider	Enum: one of the nine supported providers
providerAccountId	The external account ID from the provider
accessTokenEncrypted	AES-256-GCM ciphertext of the access token
refreshTokenEncrypted	AES-256-GCM ciphertext of the refresh token
scopes	OAuth scopes granted, space/comma-separated
expiresAt	Token expiry timestamp
status	Enum: active, inactive, error, expired
lastSyncAt	Timestamp of the last successful data sync

A unique constraint on (userId, provider) ensures a user can have at most one connection per provider. If a user reconnects an existing provider, upsert updates the existing row rather than creating a duplicate.

Three TypeScript types are exported directly from the schema for use throughout the codebase: Integration, IntegrationProvider, and IntegrationStatus.

---

Token Encryption

OAuth tokens are credentials. Storing them in plaintext is never acceptable. src/lib/encryption.ts wraps Node's built-in crypto module to provide authenticated encryption using AES-256-GCM.

How it works

// Encrypt — called before writing tokens to the database
encrypt(plaintext: string): string
// Returns: "<iv_hex>:<authTag_hex>:<ciphertext_hex>"

// Decrypt — called only when a sync job needs the actual token
decrypt(ciphertext: string): string | null

// Nullable convenience wrapper
encryptOptional(value: string | null | undefined): string | null

Random IV per call. Each encrypt() invocation generates a fresh 96-bit (12-byte) IV. This means two encryptions of the same token produce different ciphertexts, preventing comparison attacks on the stored values.

Authenticated encryption. GCM mode produces a 128-bit authentication tag alongside the ciphertext. decrypt() verifies this tag before returning any plaintext — any modification to the stored value causes decryption to throw rather than silently return garbage.

Key configuration. The encryption key is read from the ENCRYPTION_KEY environment variable and must be a 64-character hex string (32 bytes). Generate one with:

node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

---

The Integrations tRPC Router

src/lib/routers/integrations.ts exposes the integration lifecycle through six tRPC procedures, available under the integrations namespace.

Procedures

integrations.list

Returns all integrations for the authenticated user. Tokens are never included in this response. Instead, the router replaces accessTokenEncrypted and refreshTokenEncrypted with boolean flags hasAccessToken and hasRefreshToken — enough for the UI to know whether a connection is established, without ever exposing credentials to the client.

integrations.getByProvider

Looks up a single integration by provider name. Same token-exclusion behaviour as list.

integrations.upsert

Called after a successful OAuth callback. Accepts the raw tokens and provider details, encrypts the tokens using encrypt(), then inserts or updates the row. This is the only entry point for writing tokens to the database.

integrations.updateStatus

Allows the application to mark an integration as error, expired, or inactive — for example, when a token refresh attempt fails or a user manually disconnects a provider.

integrations.disconnect

Deletes the integration row entirely, removing all stored tokens. Used when a user revokes an integration from the settings UI.

integrations.getTokens

A server-only procedure that returns decrypted access and refresh tokens for a given provider. This is intended exclusively for background sync jobs that need to make authenticated API calls on behalf of a user. It must not be called from client-side code.

---

Test Coverage

The encryption library ships with a full Vitest test suite in tests/lib/encryption.test.ts. Tests cover:

• Round-trip: decrypt(encrypt(value)) === value
• IV randomness: two encryptions of the same value produce different ciphertexts
• Output format: valid iv:authTag:ciphertext structure
• Tamper detection: modifying the ciphertext causes decrypt() to throw
• Edge cases: empty strings, null, undefined inputs

---

Setting Up

1. Add the environment variable

Copy .env.example to .env.local and fill in the required values. At minimum, ENCRYPTION_KEY is required for the integrations system to function:

ENCRYPTION_KEY=<your-64-char-hex-string>

2. Apply the database migration

npm run db:push
# or
npm run db:generate && npm run db:migrate

3. Verify CI

The included GitHub Actions workflow (.github/workflows/ci.yml) runs npm run build and npm test on every push and pull request to main, with all required environment variables pre-configured for the CI environment.

---

What's Next

With the data model and encryption layer in place, the next releases will build the OAuth connection flows for individual providers, starting with GitHub and Google Calendar — the two integrations most central to the daily briefing feature.