Platform (Self-Host)
Database Schema
Complete reference for the PostgreSQL schema — all tables, columns, constraints, and relations managed by Drizzle ORM.
Overview
NIN Connect uses PostgreSQL via Drizzle ORM. The schema is defined in apps/web/src/lib/db/schema.ts.
Entity Relationship
developers ──< api_keys
developers ──< connected_accounts >── plugins
developers ──< execution_logs >── tools
developers ──< plugins (created_by)
developers ──< plugins (reviewed_by)
plugins ──< auth_methods
plugins ──< tools
plugins ──< mcp_tool_cache
plugins ──< plugin_categories >── categories
connected_accounts >── auth_methods (nullable for MCP-only)
plugins ──< pending_connectionsTables
developers
Platform users who sign in via Firebase and use the SDK.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
firebase_uid | text | unique, not null | Firebase Auth UID |
email | text | unique, not null | |
name | text | nullable | |
avatar_url | text | nullable | |
role | text | not null, default "developer" | developer or admin |
plan | text | not null, default "free" | |
created_at | timestamp | not null, default now |
api_keys
Developer API keys for SDK/API authentication.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
developer_id | uuid | FK → developers, cascade delete | |
key_hash | text | not null | SHA-256 hash of the raw key |
prefix | text | not null | Visible portion (nk_live_...) |
label | text | nullable | User-assigned label |
rate_limit | integer | not null, default 1000 | |
last_used_at | timestamp | nullable | Updated on each API call |
created_at | timestamp | not null, default now |
plugins
Service integrations — created by any user, go live after admin approval.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
slug | text | unique, not null | URL-safe identifier |
name | text | not null | Display name |
description | text | nullable | |
icon_url | text | nullable | |
base_url | text | nullable | API root URL |
plugin_prompt | text | nullable | LLM context injected during tool calls |
status | text | not null, default "active" | active, beta, deprecated |
tool_source | text | not null, default "manual" | manual, mcp, or both |
mcp_config | jsonb | nullable | MCP server configuration (serverUrl, clientId, oauthMetadata) |
created_by | uuid | FK → developers, set null on delete, nullable | Who created this plugin (null for legacy/seed data) |
review_status | text | not null, default "approved" | draft, pending_review, approved, declined, revision_requested |
admin_notes | text | nullable | Admin feedback when declining or requesting revision |
reviewed_by | uuid | FK → developers, set null on delete, nullable | Which admin reviewed this plugin |
reviewed_at | timestamp | nullable | When the review decision was made |
created_at | timestamp | not null, default now |
The default review_status is "approved" so all existing (pre-migration) plugins remain unaffected. New plugins created through the dashboard or API start as "draft" and must be approved by an admin before they appear in the public SDK/API.
categories
Tags for organizing plugins.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
slug | text | unique, not null | |
name | text | not null | |
icon | text | nullable | |
created_at | timestamp | not null, default now |
plugin_categories
Many-to-many join between plugins and categories.
| Column | Type | Constraints |
|---|---|---|
id | uuid | PK, default random |
plugin_id | uuid | FK → plugins, cascade delete |
category_id | uuid | FK → categories, cascade delete |
Unique constraint on (plugin_id, category_id).
auth_methods
Authentication options for a plugin.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
plugin_id | uuid | FK → plugins, cascade delete | |
type | text | not null | oauth2, bearer_token, api_key, service_account, no_auth, custom |
label | text | not null | Human-readable name |
is_default | boolean | not null, default false | |
config | jsonb | not null, default {} | Type-specific configuration |
created_at | timestamp | not null, default now |
Unique constraint on (plugin_id, type).
tools
API actions within a plugin.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
plugin_id | uuid | FK → plugins, cascade delete | |
slug | text | not null | Unique within plugin |
name | text | not null | |
description | text | not null | Shown to AI agents |
method | text | not null | GET, POST, PUT, PATCH, DELETE |
path | text | not null | URL path appended to plugin base URL |
input_schema | jsonb | nullable | JSON Schema for parameters |
output_schema | jsonb | nullable | JSON Schema for response |
headers | jsonb | nullable | Static headers merged per request |
enabled | boolean | not null, default true | |
created_at | timestamp | not null, default now |
Unique constraint on (plugin_id, slug).
connected_accounts
End-user credentials linking a user to a plugin. Supports both REST and MCP credentials.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
developer_id | uuid | FK → developers, cascade delete | |
user_id | text | not null | Developer's end-user ID (opaque) |
plugin_id | uuid | FK → plugins, cascade delete | |
auth_method_id | uuid | FK → auth_methods, nullable | Null for MCP-only connections |
credentials | text | nullable | AES-256-GCM encrypted JSON (null for MCP-only) |
account_label | text | nullable | |
scopes | text[] | nullable | |
expires_at | timestamp | nullable | Token expiry for REST OAuth |
mcp_access_token | text | nullable | AES-256-GCM encrypted MCP access token |
mcp_refresh_token | text | nullable | AES-256-GCM encrypted MCP refresh token |
mcp_token_expires_at | timestamp | nullable | MCP token expiry |
created_at | timestamp | not null, default now |
Unique constraint on (developer_id, user_id, plugin_id).
A single record can hold both REST credentials and MCP tokens when the plugin uses "both" tool source.
execution_logs
Audit trail for every tool execution.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
developer_id | uuid | FK → developers, cascade delete | |
user_id | text | nullable | |
tool_id | uuid | FK → tools, set null on delete | |
status | text | not null | success, error, auth_expired |
latency_ms | integer | nullable | |
error_message | text | nullable | |
created_at | timestamp | not null, default now |
mcp_tool_cache
Cached tool definitions discovered from MCP servers.
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
plugin_id | uuid | FK → plugins, cascade delete | |
mcp_tool_name | text | not null | Original tool name from MCP server |
slug | text | not null | Normalized uppercase slug (e.g. NOTION_SEARCH) |
name | text | not null | Display name |
description | text | not null, default "" | Tool description |
input_schema | jsonb | nullable | JSON Schema for tool parameters |
last_synced_at | timestamp | not null, default now | Last sync timestamp |
Unique constraint on (plugin_id, mcp_tool_name).
pending_connections
Temporary records during OAuth flows (both REST and MCP).
| Column | Type | Constraints | Description |
|---|---|---|---|
id | uuid | PK, default random | |
developer_id | uuid | FK → developers, cascade delete | |
user_id | text | not null | |
plugin_id | uuid | FK → plugins, cascade delete | |
auth_method_id | uuid | FK → auth_methods, nullable | Null for MCP OAuth flows |
state_token | text | unique, not null | HMAC-signed state parameter |
redirect_url | text | not null | Where to redirect after completion |
expires_at | timestamp | not null | TTL for the pending connection |
created_at | timestamp | not null, default now |
Drizzle Commands
| Command | Description |
|---|---|
pnpm db:generate | Generate migration files from schema changes |
pnpm db:migrate | Run pending migrations |
pnpm db:push | Push schema directly (dev only) |
pnpm db:studio | Open Drizzle Studio (visual DB browser) |
pnpm db:seed | Seed with sample plugins (Gmail, Slack, Stripe, etc.) |