Data Model

public schema tables ship with RLS turned on. Six ordered migrations under supabase/ build enums, tables, and policies: run them in Supabase SQL before writing app code.

Enums

workspace_role      : OWNER | ADMIN | MEMBER | VIEWER
product_status      : DRAFT | ACTIVE | ARCHIVED
subscription_status : active | trialing | past_due | canceled | incomplete

Core tables

`workspaces`

The top-level tenant container. All workspace-scoped data references this table.

Column	Type	Notes
`id`	uuid PK
`name`	text	Display name
`slug`	text unique	URL-safe identifier
`setup_complete`	boolean	Onboarding flag (default: false)
`require_2fa`	boolean	Enforce 2FA for all members (default: false)
`created_by`	uuid -> auth.users
`created_at`	timestamptz

`profiles`

Public user metadata synced from auth.users via a database trigger.

Column	Type	Notes
`id`	uuid PK -> auth.users
`email`	text
`full_name`	text
`avatar_url`	text
`updated_at`	timestamptz

`memberships`

Joins users to workspaces with a role.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`user_id`	uuid -> auth.users
`role`	workspace_role	OWNER, ADMIN, MEMBER, or VIEWER
`created_at`	timestamptz

Unique constraint on (workspace_id, user_id).

`invitations`

Pending email invitations.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`email`	text	Invitee's email
`role`	workspace_role	Role to grant on acceptance
`token`	text unique	Random 32-byte hex token
`invited_by`	uuid -> auth.users
`expires_at`	timestamptz	Default: now + 7 days
`accepted_at`	timestamptz	NULL until accepted
`created_at`	timestamptz

`products`

The primary domain entity (replace with your own model). See Adding Your Own Model.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`name`	text
`description`	text	Nullable
`status`	product_status	Default: DRAFT
`custom_fields`	jsonb	Custom field values (default: )
`created_by`	uuid -> auth.users
`created_at`	timestamptz
`updated_at`	timestamptz	Auto-updated via trigger
`deleted_at`	timestamptz	NULL = active, set = soft-deleted

`subscriptions`

One row per workspace, managed by the Stripe webhook handler.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces (unique)
`stripe_customer_id`	text
`stripe_subscription_id`	text
`plan`	text	starter, pro, enterprise
`status`	subscription_status
`current_period_end`	timestamptz
`cancel_at_period_end`	boolean
`created_at`	timestamptz

`audit_logs`

Append-only event log. No updates or deletes.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`actor_user_id`	uuid -> auth.users
`action`	text	e.g. product.created
`resource_type`	text	e.g. product
`resource_id`	text	ID of the affected row
`metadata`	jsonb	Extra data (old/new values, etc.)
`created_at`	timestamptz

Collaboration tables

`tags`

Workspace-scoped colored labels.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`name`	text	Unique per workspace
`color`	text	Hex color (default: #6B7280)
`created_at`	timestamptz

`record_tags`

Many-to-many: assigns tags to any record type.

Column	Type	Notes
`record_id`	uuid
`tag_id`	uuid -> tags
`record_type`	text	e.g. product, task
`created_at`	timestamptz

Primary key: (record_id, tag_id, record_type).

`comments`

Threaded comments on any record.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`record_id`	uuid
`record_type`	text	e.g. product
`author_id`	uuid -> auth.users
`body`	text	1-5000 characters
`mentions`	uuid[]	Mentioned user IDs
`parent_id`	uuid -> comments	For threaded replies
`created_at`	timestamptz
`updated_at`	timestamptz	Auto-updated via trigger

`attachments`

File attachments on any record.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`record_id`	uuid
`record_type`	text	Default: product
`file_name`	text
`file_size`	bigint
`mime_type`	text
`storage_path`	text	Supabase Storage path
`uploaded_by`	uuid -> auth.users
`created_at`	timestamptz

Developer platform tables

`api_keys`

Workspace API keys for programmatic access.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`name`	text
`key_hash`	text	SHA-256 hash of the full key
`key_prefix`	text	e.g. sk_live_
`last_four`	text	Last 4 characters for display
`scopes`	text[]	Default: `{read}`
`last_used_at`	timestamptz
`expires_at`	timestamptz
`created_by`	uuid -> auth.users
`created_at`	timestamptz
`revoked_at`	timestamptz

`webhooks`

Outgoing webhook registrations.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`url`	text	Target URL
`events`	text[]	Subscribed event types
`secret`	text	Signing secret
`is_active`	boolean
`created_by`	uuid -> auth.users
`created_at`	timestamptz

`webhook_deliveries`

Delivery tracking for outgoing webhooks.

Column	Type	Notes
`id`	uuid PK
`webhook_id`	uuid -> webhooks
`event_type`	text
`payload`	jsonb
`response_status`	integer	HTTP status code
`response_body`	text
`attempts`	integer	Retry count
`delivered_at`	timestamptz
`created_at`	timestamptz

`webhook_events`

Incoming webhook events from external services.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`source`	text	Service name
`event_type`	text
`payload`	jsonb
`processed_at`	timestamptz
`created_at`	timestamptz

User feature tables

`bookmarks`

Per-user bookmarks within a workspace.

Column	Type	Notes
`id`	uuid PK
`user_id`	uuid -> auth.users
`workspace_id`	uuid -> workspaces
`resource_type`	text
`resource_id`	uuid
`resource_name`	text	Display name
`sort_order`	integer
`created_at`	timestamptz

Unique constraint on (user_id, workspace_id, resource_type, resource_id).

`saved_views`

Saved data table configurations.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`created_by`	uuid -> auth.users
`name`	text
`resource_type`	text	Default: product
`filters`	jsonb
`sort`	jsonb
`columns`	jsonb
`is_shared`	boolean	Visible to all members
`created_at`	timestamptz

`custom_field_definitions`

Schema for dynamic custom fields.

Column	Type	Notes
`id`	uuid PK
`workspace_id`	uuid -> workspaces
`resource_type`	text	Default: product
`name`	text
`field_type`	text	text, number, date, select, multi_select, url, boolean
`options`	jsonb	For select/multi_select types
`is_required`	boolean
`sort_order`	integer
`created_at`	timestamptz

`notification_preferences`

Per-user notification settings.

`login_history`

Sign-in attempt records with IP and device info.

`scheduled_actions`

Scheduled tasks with execution tracking.

`workspace_ip_allowlist`

IP CIDR ranges for workspace access restriction (Enterprise).

Security tables

All security-related data is stored in dedicated tables. See Authentication & Security for details.

Soft delete pattern

Products use soft delete via a deleted_at column. When a product is "deleted," the timestamp is set instead of removing the row.

Active products: WHERE deleted_at IS NULL
Trashed products: WHERE deleted_at IS NOT NULL
Permanent delete: Removes the row entirely (OWNER/ADMIN only)

Partial indexes optimize both query paths:

products_not_deleted_idx -- covers active product queries
products_deleted_idx -- covers trash queries

To add soft delete to your own tables, follow the pattern in supabase/003_soft_delete_and_dashboard.sql.

Database functions

`daily_audit_counts(p_workspace_id, p_days)`

Returns (day DATE, count BIGINT) for the dashboard activity chart. Days with no activity return 0.

SELECT * FROM daily_audit_counts('workspace-uuid', 30);

`is_workspace_member(workspace_id)`

RLS helper. Returns true if the current user is a member of the specified workspace.

`workspace_role(workspace_id)`

RLS helper. Returns the current user's role in the specified workspace.

Key relations

auth.users --< memberships >-- workspaces
                                 ├── products
                                 ├── invitations
                                 ├── subscriptions
                                 ├── audit_logs
                                 ├── tags / record_tags
                                 ├── comments
                                 ├── attachments
                                 ├── api_keys
                                 ├── webhooks / webhook_deliveries
                                 ├── webhook_events
                                 ├── bookmarks
                                 ├── saved_views
                                 ├── custom_field_definitions
                                 ├── notification_preferences
                                 ├── scheduled_actions
                                 └── workspace_ip_allowlist

auth.users --< login_history

Workspace deletion cascades to all child tables except profiles (user-owned).