Integration Data Model & Token Encryption

Introduced in v0.1.1, the integrations data layer is the foundation that all third-party connections in Focus Engine are built on. This page explains the schema design, how tokens are stored securely, and how to interact with integrations via the tRPC API.

---

Supported Providers

The following integration providers are supported:

Provider	Type
github	Code / issue tracking
google	Calendar / email
outlook	Calendar / email
gmail	Email
notion	Tasks
linear	Tasks / issue tracking
jira	Tasks / issue tracking
asana	Tasks
slack	Communication

---

Integration Statuses

Each integration carries a status field reflecting the current state of the connection:

Status	Meaning
active	Connected and operational
inactive	Manually disconnected or disabled
error	Sync or auth error encountered
expired	OAuth token has expired

---

Database Schema

The integrations table has a unique constraint on (userId, provider) — each user can have at most one active connection per provider.

Column	Type	Notes
userId	FK → users	Cascade deletes on user removal
provider	enum	One of the supported providers above
providerAccountId	string	External account identifier from the provider
accessTokenEncrypted	string	AES-256-GCM ciphertext
refreshTokenEncrypted	string	AES-256-GCM ciphertext (nullable)
scopes	string	Space- or comma-separated OAuth scopes granted
expiresAt	timestamp	Token expiry
status	enum	Current connection status
lastSyncAt	timestamp	Timestamp of last successful data sync
createdAt / updatedAt	timestamp	Audit timestamps

Applying the Migration

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

---

Token Encryption

OAuth tokens are never stored in plaintext. All access and refresh tokens are encrypted before being written to the database using AES-256-GCM.

How it works

1. Encryption (encrypt(plaintext))

   • Generates a random 96-bit IV per call — each encrypted value is unique even for identical inputs
   • Produces a iv:authTag:ciphertext hex string
   • Requires ENCRYPTION_KEY (see below)

2. Decryption (decrypt(ciphertext))

   • Reverses the process; returns the original plaintext
   • Returns null for empty or missing input
   • GCM authentication tag verification detects any tampering — a modified ciphertext will fail to decrypt

3. Optional helper (encryptOptional(value))

   • Convenience wrapper that handles null/undefined refresh tokens gracefully

Security properties

• Authenticated encryption — GCM mode ensures integrity; tampering is detected at decrypt time
• Unique IV per write — prevents ciphertext comparison attacks across rows
• Tokens only decrypted on demand — the getTokens procedure is the only path to plaintext tokens, and it is server-side only

Required environment variable

# Generate a 64-character hex string (32 bytes)
node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

Set the result as ENCRYPTION_KEY in your environment. The app will not start without it.

---

tRPC Integrations Router

All integration management is exposed under the integrations tRPC namespace.

integrations.list

Returns all integrations for the authenticated user. Token fields are not returned; instead, boolean flags hasAccessToken and hasRefreshToken indicate whether tokens are stored.

const integrations = await trpc.integrations.list.query();
// [ { provider: 'github', status: 'active', hasAccessToken: true, ... } ]

integrations.getByProvider

Fetches a single integration by provider name.

const integration = await trpc.integrations.getByProvider.query({ provider: 'notion' });

integrations.upsert

Creates or updates an integration. Typically called after completing an OAuth flow. Tokens are encrypted automatically before the database write.

await trpc.integrations.upsert.mutate({
  provider: 'github',
  providerAccountId: 'octocat',
  accessToken: 'gho_...',
  refreshToken: null,
  scopes: 'repo read:user',
  expiresAt: new Date('2026-06-01'),
});

integrations.updateStatus

Updates the status of an existing integration — useful when a sync job detects an auth error or token expiry.

await trpc.integrations.updateStatus.mutate({ provider: 'github', status: 'error' });

integrations.disconnect

Deletes the integration record and removes all stored tokens for the given provider.

await trpc.integrations.disconnect.mutate({ provider: 'github' });

integrations.getTokens

Server-only. Returns the decrypted access and refresh tokens for a given provider. Intended exclusively for background sync jobs that need to make authenticated API calls on the user's behalf.

// Server-side only — never expose this to the client
const { accessToken, refreshToken } = await trpc.integrations.getTokens.query({ provider: 'github' });

---

Configuration Reference

See .env.example for the full list of required environment variables. The variables relevant to this feature are:

Variable	Required	Description
ENCRYPTION_KEY	✅ Yes	64-char hex string for AES-256-GCM token encryption
DATABASE_URL	✅ Yes	Neon Postgres connection string