Database Schema
Every table, column, index, and RLS policy in the Cursorist database — explained.
The Cursorist database runs on Supabase (PostgreSQL). It stores users, organizations, teams, and the entire plugin lifecycle — from creation through versioning to installation tracking. Every table has Row Level Security (RLS) enabled, so data access is enforced at the database level.
The entire schema lives in a single migration file: supabase/migrations/001_init.sql. Run it in the SQL Editor to set up everything. This page is a reference for understanding what each table does and how they connect.
Schema Diagram
Core Tables
users
Stores every authenticated user. Created automatically when a user signs in via GitHub OAuth for the first time.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Matches Supabase Auth user ID |
github_id | TEXT | GitHub numeric ID (unique) |
github_username | TEXT | GitHub handle (unique) |
email | TEXT | Email from GitHub profile |
avatar_url | TEXT | Profile picture URL |
is_admin | BOOLEAN | Platform admin flag (only one admin allowed) |
onboarding_completed | BOOLEAN | Whether user finished the onboarding flow |
created_at | TIMESTAMPTZ | Account creation time |
updated_at | TIMESTAMPTZ | Last profile update (auto-managed by trigger) |
organizations
Top-level grouping for teams and plugins. Every user who creates an org becomes its owner.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Organization ID |
owner_id | UUID (FK → users) | The user who created the org |
name | TEXT | Display name |
slug | TEXT | URL-safe identifier (unique) |
description | TEXT | Optional description |
avatar_url | TEXT | Org logo/avatar |
A trigger (add_owner_to_org_members) automatically adds the owner as a member with role owner when an org is created.
teams
Groups within an organization. Plugins belong to teams, not directly to orgs.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Team ID |
organization_id | UUID (FK → organizations) | Parent org |
name | TEXT | Display name |
slug | TEXT | URL-safe identifier (unique per org) |
description | TEXT | Optional description |
A trigger (add_creator_to_team_members) automatically adds the org owner as a team admin when a team is created.
organization_members
Links users to organizations with a role.
| Column | Type | Description |
|---|---|---|
user_id | UUID (FK → users) | Member |
organization_id | UUID (FK → organizations) | Org they belong to |
role | ENUM (owner, member) | Permission level |
team_members
Links users to teams with a role. Controls who can create and manage plugins.
| Column | Type | Description |
|---|---|---|
user_id | UUID (FK → users) | Member |
team_id | UUID (FK → teams) | Team they belong to |
role | ENUM (admin, member) | Permission level — admins can delete any plugin in the team |
Plugin Tables
plugins
The central plugin record. Belongs to a team, authored by a user.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Plugin ID |
team_id | UUID (FK → teams) | Owning team |
author_id | UUID (FK → users) | Creator |
name | TEXT | Display name |
slug | TEXT | URL-safe identifier (unique per team) |
description | TEXT | Short description |
type | ENUM | Category: conventions, snippets, templates, workflows, linting, testing, documentation, other |
stack | TEXT[] | Technology tags (e.g. ["typescript", "react"]), indexed with GIN |
homepage_url | TEXT | External homepage link |
repository_url | TEXT | Source code link |
documentation_url | TEXT | Docs link |
is_published | BOOLEAN | Whether publicly visible |
published_at | TIMESTAMPTZ | First publish timestamp |
install_count | INTEGER | Total installs (incremented by increment_plugin_install_count()) |
plugin_versions
Every cursorist plugin deploy creates a new version row. Versions are immutable — assets are never overwritten in place.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Version ID |
plugin_id | UUID (FK → plugins) | Parent plugin |
version | TEXT | Semver string (e.g. 1.2.0), unique per plugin |
changelog | TEXT | Optional release notes |
manifest | JSONB | Snapshot of plugin.json at deploy time |
is_prerelease | BOOLEAN | Beta/RC flag |
is_yanked | BOOLEAN | Withdrawn flag (hidden from install but not deleted) |
yanked_reason | TEXT | Why it was yanked |
plugin_assets
Individual files within a version — rules, skills, commands, MCP configs.
| Column | Type | Description |
|---|---|---|
id | UUID (PK) | Asset ID |
version_id | UUID (FK → plugin_versions) | Parent version |
asset_type | TEXT | File type: rule, skill, command, mcp, agent, hook |
path | TEXT | Relative file path (e.g. rules/project-standards.mdc), unique per version |
content_raw | TEXT | Full file content |
content_hash | TEXT | SHA hash for deduplication |
size_bytes | INTEGER | File size |
plugin_channels
Named release channels (e.g. stable, beta). Each channel points to a specific version.
| Column | Type | Description |
|---|---|---|
plugin_id | UUID (FK → plugins) | Parent plugin |
name | TEXT | Channel name (unique per plugin) |
version_id | UUID (FK → plugin_versions) | Currently pinned version |
is_default | BOOLEAN | Whether this is the default install channel |
plugin_installs
Tracks which users have installed which plugins. Used for install counts, upgrade notifications, and analytics.
| Column | Type | Description |
|---|---|---|
user_id | UUID (FK → users) | Who installed |
plugin_id | UUID (FK → plugins) | What was installed (unique per user) |
version_id | UUID (FK → plugin_versions) | Installed version |
channel_id | UUID (FK → plugin_channels) | Install channel |
installed_via | TEXT | How: web, cli, mcp, deeplink |
auto_update | BOOLEAN | Whether to auto-upgrade on new versions |
plugin_favorites
User bookmarks for plugins.
| Column | Type | Description |
|---|---|---|
user_id | UUID (FK → users) | Who bookmarked |
plugin_id | UUID (FK → plugins) | What was bookmarked (unique per user) |
Utility Tables
api_keys
Personal API keys for CLI and MCP authentication.
| Column | Type | Description |
|---|---|---|
user_id | UUID (FK → users) | Key owner |
name | TEXT | Display name for the key |
key_hash | TEXT | Hashed key value (never stored in plain text) |
expires_at | TIMESTAMPTZ | Optional expiration |
invitations
Team invitations sent by admins. Expire after 7 days by default.
| Column | Type | Description |
|---|---|---|
team_id | UUID (FK → teams) | Target team |
inviter_id | UUID (FK → users) | Who sent the invite |
email | TEXT | Recipient email |
github_username | TEXT | Optional GitHub handle |
status | ENUM | pending, accepted, expired, cancelled |
token | TEXT | Unique invite token |
expires_at | TIMESTAMPTZ | Defaults to 7 days from creation |
site_notifications
Admin-created banners displayed on the welcome page.
| Column | Type | Description |
|---|---|---|
type | ENUM | info or alert |
message | TEXT | Notification content |
is_active | BOOLEAN | Whether currently shown |
expires_at | TIMESTAMPTZ | Optional auto-hide time |
site_config
Key-value configuration for feature flags and site settings. See Site Configuration for full details.
| Column | Type | Description |
|---|---|---|
key | TEXT (PK) | Config key (e.g. maintenance_mode) |
value | JSONB | Config value |
description | TEXT | Human-readable explanation |
Database Functions
| Function | Purpose |
|---|---|
increment_plugin_install_count(plugin_uuid) | Atomically increments a plugin's install count |
publish_plugin_version(version_uuid) | Marks a plugin as published and pins the version to the stable channel |
get_latest_stable_plugin_version(plugin_uuid) | Returns the newest non-prerelease, non-yanked version |
update_updated_at_column() | Trigger function that sets updated_at = NOW() on row update |
add_owner_to_org_members() | Trigger: auto-adds org creator as owner member |
add_creator_to_team_members() | Trigger: auto-adds org owner as team admin |
Views
| View | Description |
|---|---|
user_organizations | Joins users → org_members → organizations for easy org listing |
user_teams | Joins users → team_members → teams → organizations for easy team listing |
published_plugins | Joins plugins → teams → organizations → users, filtered to is_published = TRUE |
Row Level Security Summary
Every table has RLS enabled. Here is who can do what:
| Table | SELECT | INSERT | UPDATE | DELETE |
|---|---|---|---|---|
users | Everyone (public profiles) | Own ID only | Own profile only | — |
organizations | Everyone | Authenticated users | Owner only | Owner only |
organization_members | Org members | Org owner | Org owner | Org owner |
teams | Org members | Org owner | Org owner | Org owner |
team_members | Team members | Team admins | Team admins | Team admins |
plugins | Published = everyone; unpublished = team members | Team members | Author only | Author or team admin |
plugin_versions | Visible if parent plugin is visible | Author only | Author only | Author only |
plugin_assets | Visible if parent version is visible | Author only | Author only | Author only |
plugin_channels | Visible if parent plugin is visible | Author only | Author only | Author only |
plugin_installs | Own installs only | Own only | Own only | Own only |
plugin_favorites | Own favorites only | Own only | Own only | Own only |
api_keys | Own keys only | Own only | Own only | Own only |
invitations | Team members | Team admins | Team admins | Team admins |
site_notifications | Active ones = everyone | Admins only | Admins only | Admins only |
site_config | Everyone | Admins only | Admins only | Admins only |