Database Schema¶

!!! info "TL;DR" PostgreSQL 15 with RLS enabled on all tables. Multi-tenant architecture with organization_id columns. Custom auth using HMAC-signed sessions (no Supabase Auth). Denormalized counts via triggers. Org-scoped RLS policies using current_organization_id(), is_org_member(), is_org_admin() functions.

Authentication Model¶

This app uses custom authentication with HMAC-signed session cookies, NOT Supabase Auth. RLS policies use:

• current_user_id() — Returns UUID from x-user-id header set by Next.js middleware
• current_organization_id() — Returns UUID from x-organization-id header
• is_org_member() — Checks if current user is in organization_members table
• is_org_admin() — Checks if current user has admin or owner role

These functions read request headers passed by PostgREST. All references to auth.users(id), auth.uid(), and auth.role() are incorrect — the actual schema uses the custom functions above.

Multi-Tenancy¶

Nearly every table has an organization_id column. RLS policies enforce org-scoped access:

-- Public read, scoped to current org
CREATE POLICY "submissions_org_read" ON submissions
  FOR SELECT USING (organization_id = current_organization_id());

-- Insert: user must be org member
CREATE POLICY "submissions_org_insert" ON submissions
  FOR INSERT WITH CHECK (
    organization_id = current_organization_id()
    AND current_user_id() = author_id
    AND is_org_member()
  );

-- Admin full access within org
CREATE POLICY "submissions_org_admin_all" ON submissions
  FOR ALL USING (
    organization_id = current_organization_id()
    AND is_org_admin()
  );

Identity & Auth Tables¶

users¶

Custom user table for HMAC-based auth. Not Supabase Auth.

• id UUID PRIMARY KEY
• email TEXT UNIQUE NOT NULL
• display_name TEXT
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Users can read/update their own profile
• Org admins can read user profiles

Indexes:

• idx_users_email on email
• idx_users_display_name on display_name

auth_magic_links¶

Magic link tokens for passwordless login.

• id UUID PRIMARY KEY
• token TEXT UNIQUE NOT NULL
• email TEXT NOT NULL
• used BOOLEAN DEFAULT FALSE
• expires_at TIMESTAMPTZ NOT NULL
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies: None (service role only)

Indexes:

• idx_magic_links_token on token
• idx_magic_links_email on email
• idx_magic_links_expires on expires_at WHERE used = FALSE

organizations¶

Multi-tenant organizations with plan limits and integrations.

• id UUID PRIMARY KEY
• slug TEXT UNIQUE NOT NULL — Subdomain identifier (e.g., acme.canviq.app)
• name TEXT NOT NULL
• plan TEXT DEFAULT 'free'
• plan_limits JSONB DEFAULT {"submissions_per_month": 100, "surveys": 1, "responses_per_month": 500, "integrations": [], "mcp_agents": 0}
• stripe_customer_id TEXT
• stripe_subscription_id TEXT
• stripe_subscription_status TEXT
• trial_ends_at TIMESTAMPTZ
• github_access_token TEXT — Encrypted GitHub personal access token
• github_repo TEXT
• jira_credentials JSONB — Encrypted Jira OAuth credentials
• jira_project_key TEXT
• branding JSONB DEFAULT {"primary_color": "#9333ea", "logo_url": null, "show_powered_by": true, "favicon_url": null}
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Public read access (needed for subdomain routing)
• Only org admins can update their org settings

Indexes:

• UNIQUE constraint on slug (implicit index)
• idx_organizations_stripe_customer on stripe_customer_id
• idx_organizations_branding GIN index on branding

organization_members¶

Organization membership and roles (replaces team_members for org-specific access).

• id UUID PRIMARY KEY
• organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE
• user_id UUID REFERENCES users(id) ON DELETE CASCADE
• role TEXT DEFAULT 'member' — 'owner', 'admin', or 'member'
• can_manage_billing BOOLEAN DEFAULT false
• can_manage_integrations BOOLEAN DEFAULT false
• can_invite_members BOOLEAN DEFAULT false
• invited_by UUID REFERENCES users(id)
• joined_at TIMESTAMPTZ DEFAULT NOW()
• UNIQUE(organization_id, user_id)

RLS Policies:

• Members can read their own org's member list
• Only owners and admins can manage members in their org

Indexes:

• idx_org_members_org on organization_id
• idx_org_members_user on user_id

team_members¶

Global admin access control (separate from org membership).

• id UUID PRIMARY KEY
• user_id UUID UNIQUE REFERENCES users(id)
• role TEXT DEFAULT 'admin'
• display_name TEXT
• avatar_url TEXT
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Public read access

Feedback Tables¶

submissions¶

User-submitted feedback (ideas and problems).

• id UUID PRIMARY KEY
• title TEXT NOT NULL
• description TEXT NOT NULL
• type submission_type NOT NULL DEFAULT 'idea' — ENUM: 'idea', 'problem'
• status submission_status NOT NULL DEFAULT 'open' — ENUM: 'open', 'under_review', 'planned', 'in_progress', 'shipped', 'declined', 'merged'
• category_id UUID REFERENCES categories(id)
• author_id UUID NOT NULL REFERENCES users(id)
• merged_into_id UUID REFERENCES submissions(id)
• vote_count INTEGER NOT NULL DEFAULT 0 — Denormalized via trigger
• comment_count INTEGER NOT NULL DEFAULT 0 — Denormalized via trigger
• status_explanation TEXT
• github_issue_url TEXT
• github_issue_number INTEGER
• locale TEXT NOT NULL DEFAULT 'en'
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• search_vector tsvector GENERATED — Locale-aware full-text search
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Public read within current org (excludes merged submissions)
• Org members can insert into their org
• Authors can update their own submissions within their org
• Org admins have full access to all submissions in their org

Indexes:

• idx_submissions_search GIN on search_vector
• idx_submissions_status on status
• idx_submissions_category on category_id
• idx_submissions_author on author_id
• idx_submissions_vote_count on vote_count DESC
• idx_submissions_created on created_at DESC
• idx_submissions_merged on merged_into_id WHERE merged_into_id IS NOT NULL
• idx_submissions_organization_id on organization_id
• idx_submissions_org_status on (organization_id, status)
• idx_submissions_org_created on (organization_id, created_at DESC)
• idx_submissions_org_votes on (organization_id, vote_count DESC)
• idx_submissions_title_trgm GIN on title gin_trgm_ops
• idx_submissions_description_trgm GIN on description gin_trgm_ops

votes¶

One vote per user per submission.

• id UUID PRIMARY KEY
• submission_id UUID NOT NULL REFERENCES submissions(id) ON DELETE CASCADE
• user_id UUID NOT NULL REFERENCES users(id)
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()
• UNIQUE(submission_id, user_id)

Trigger: trg_vote_count updates submissions.vote_count on INSERT/DELETE

RLS Policies:

• Org members can insert votes within their org
• Users can read their own votes within their org
• Users can delete their own votes within their org

Indexes:

• idx_votes_submission on submission_id
• idx_votes_user on user_id
• idx_votes_organization_id on organization_id

comments¶

Discussion threads on submissions (flat, no nested replies).

• id UUID PRIMARY KEY
• submission_id UUID NOT NULL REFERENCES submissions(id) ON DELETE CASCADE
• author_id UUID NOT NULL REFERENCES users(id)
• body TEXT NOT NULL
• is_official BOOLEAN NOT NULL DEFAULT FALSE — Team responses
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

Note: No parent_id column. Comments are flat.

Trigger: trg_comment_count updates submissions.comment_count on INSERT/DELETE

RLS Policies:

• Public read within current org
• Org members can insert comments
• Authors can update/delete their own comments within their org

Indexes:

• idx_comments_submission on submission_id
• idx_comments_author on author_id
• idx_comments_organization_id on organization_id
• idx_comments_created_at on created_at DESC

follows¶

User subscriptions to submission updates.

• id UUID PRIMARY KEY
• submission_id UUID NOT NULL REFERENCES submissions(id) ON DELETE CASCADE
• user_id UUID NOT NULL REFERENCES users(id)
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()
• UNIQUE(submission_id, user_id)

Trigger: trg_auto_follow auto-creates a follow when user votes

RLS Policies:

• Org members can insert follows within their org
• Users can read their own follows within their org
• Users can delete their own follows within their org

Indexes:

• idx_follows_user on user_id
• idx_follows_submission on submission_id
• idx_follows_organization_id on organization_id

categories¶

Categorize submissions (multilingual JSONB names).

• id UUID PRIMARY KEY
• name JSONB NOT NULL — Multilingual: {"en": "Feature Requests", "es": "..."}
• slug TEXT UNIQUE NOT NULL
• description JSONB
• color TEXT
• sort_order INTEGER DEFAULT 0
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()
• updated_at TIMESTAMPTZ DEFAULT NOW()

Note: No icon column. Column is sort_order, not order_index.

RLS Policies:

• Public read within current org
• Org admins can manage categories

Indexes:

• UNIQUE constraint on slug (implicit index)
• idx_categories_organization_id on organization_id

tags¶

Cross-cutting labels for submissions (multilingual JSONB names).

• id UUID PRIMARY KEY
• name JSONB NOT NULL — Multilingual
• slug TEXT UNIQUE NOT NULL
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Public read within current org
• Org admins can manage tags

Indexes:

• UNIQUE constraint on slug (implicit index)
• idx_tags_organization_id on organization_id

submission_tags¶

Many-to-many join table for submissions and tags.

• submission_id UUID REFERENCES submissions(id) ON DELETE CASCADE
• tag_id UUID REFERENCES tags(id) ON DELETE CASCADE
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• PRIMARY KEY (submission_id, tag_id)

RLS Policies:

• Public read within current org
• Org admins can manage submission tags

Indexes:

• idx_submission_tags_organization_id on organization_id

status_history¶

Audit trail for submission status changes.

• id UUID PRIMARY KEY
• submission_id UUID NOT NULL REFERENCES submissions(id) ON DELETE CASCADE
• old_status submission_status
• new_status submission_status NOT NULL
• changed_by UUID NOT NULL REFERENCES users(id)
• note TEXT
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Public read within current org

Indexes:

• idx_status_history_submission on submission_id
• idx_status_history_organization_id on organization_id

assignments¶

Team member assignment to submissions.

• id UUID PRIMARY KEY
• submission_id UUID NOT NULL REFERENCES submissions(id) ON DELETE CASCADE
• assignee_id UUID NOT NULL REFERENCES team_members(id)
• assigned_by UUID NOT NULL REFERENCES users(id)
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Org admins have full access within their org

Indexes:

• idx_assignments_submission on submission_id
• idx_assignments_assignee on assignee_id
• idx_assignments_organization_id on organization_id

moderation_queue¶

Spam and abuse review queue.

• id UUID PRIMARY KEY
• submission_id UUID REFERENCES submissions(id) ON DELETE CASCADE
• comment_id UUID REFERENCES comments(id) ON DELETE CASCADE
• reason TEXT NOT NULL
• status TEXT NOT NULL DEFAULT 'pending'
• reviewed_by UUID REFERENCES users(id)
• reviewed_at TIMESTAMPTZ
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()
• CHECK (submission_id IS NOT NULL OR comment_id IS NOT NULL)

RLS Policies:

• Org admins have full access within their org

Indexes:

• idx_moderation_status on status WHERE status = 'pending'
• idx_moderation_queue_organization_id on organization_id

notification_preferences¶

Per-user email digest settings.

• user_id UUID PRIMARY KEY REFERENCES users(id)
• status_changes BOOLEAN NOT NULL DEFAULT TRUE
• new_comments BOOLEAN NOT NULL DEFAULT TRUE
• official_responses BOOLEAN NOT NULL DEFAULT TRUE
• email_digest TEXT NOT NULL DEFAULT 'instant'
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• updated_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Users can manage their own preferences within their org

Indexes:

• idx_notification_preferences_organization_id on organization_id

Survey Tables¶

survey_surveys¶

Survey definitions with status lifecycle.

• id UUID PRIMARY KEY
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• title TEXT NOT NULL
• description TEXT
• status survey_status NOT NULL DEFAULT 'draft' — ENUM: 'draft', 'published', 'paused', 'archived'
• settings JSONB DEFAULT '{}'
• created_by UUID REFERENCES auth.users(id) — Note: Legacy reference, should be users(id)
• response_count INTEGER NOT NULL DEFAULT 0 — Denormalized via trigger
• completion_rate NUMERIC(5,2) NOT NULL DEFAULT 0
• published_at TIMESTAMPTZ
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Triggers:

• trg_survey_surveys_updated_at auto-updates updated_at on UPDATE

RLS Policies:

• Public read for published surveys
• Org admins have full access within their org (uses auth.uid() — should be updated to current_user_id())

Indexes:

• idx_survey_surveys_status on status
• idx_survey_surveys_created_by on created_by
• idx_survey_surveys_organization_id on organization_id

survey_questions¶

Questions belonging to surveys with type-specific JSONB config.

• id UUID PRIMARY KEY
• survey_id UUID NOT NULL REFERENCES survey_surveys(id) ON DELETE CASCADE
• type survey_question_type NOT NULL — ENUM: 'multiple_choice', 'rating', 'free_text', 'yes_no', 'matrix', 'ranking', 'image_selection', 'file_upload'
• text TEXT NOT NULL
• description TEXT
• required BOOLEAN NOT NULL DEFAULT false
• config JSONB DEFAULT '{}' — Question-specific settings (NOT settings)
• sort_order INTEGER NOT NULL DEFAULT 0 — NOT order_index
• conditional_logic JSONB
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

Note: Column is config, not settings. Column is sort_order, not order_index.

Triggers:

• trg_survey_questions_updated_at auto-updates updated_at on UPDATE

RLS Policies:

• Public read for questions of published surveys
• Org admins have full access (via survey's organization)

Indexes:

• idx_survey_questions_survey_id on survey_id
• idx_survey_questions_sort_order on (survey_id, sort_order)

survey_responses¶

Individual survey response sessions with resume capability.

• id UUID PRIMARY KEY
• survey_id UUID NOT NULL REFERENCES survey_surveys(id) ON DELETE CASCADE
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• user_id UUID REFERENCES auth.users(id) — Note: Legacy reference, should be users(id)
• status survey_response_status NOT NULL DEFAULT 'in_progress' — ENUM: 'in_progress', 'paused', 'completed', 'expired'
• resume_token TEXT UNIQUE
• resume_expires_at TIMESTAMPTZ
• consent_recorded_at TIMESTAMPTZ
• privacy_policy_version TEXT
• started_at TIMESTAMPTZ NOT NULL DEFAULT now()
• completed_at TIMESTAMPTZ
• user_agent TEXT
• locale TEXT

Triggers:

• trg_survey_response_count updates survey_surveys.response_count on INSERT/DELETE
• trg_survey_completion_rate updates survey_surveys.completion_rate on status change to 'completed'

RLS Policies:

• Authenticated users can create responses
• Users can read their own responses; admins can read all in their org
• Users can update their own in-progress/paused responses

Indexes:

• idx_survey_responses_survey_id on survey_id
• idx_survey_responses_user_id on user_id
• idx_survey_responses_status on status
• idx_survey_responses_resume_token on resume_token
• idx_survey_responses_organization_id on organization_id

survey_answers¶

Per-question answers stored as flexible JSONB values.

• id UUID PRIMARY KEY
• response_id UUID NOT NULL REFERENCES survey_responses(id) ON DELETE CASCADE
• question_id UUID NOT NULL REFERENCES survey_questions(id) ON DELETE CASCADE
• value JSONB
• file_urls TEXT[]
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
• UNIQUE(response_id, question_id)

RLS Policies:

• Users can insert answers to their own in-progress responses
• Users and org admins can read answers
• Users can update their own in-progress answers

Indexes:

• idx_survey_answers_response_id on response_id
• idx_survey_answers_question_id on question_id
• idx_survey_answers_question_response on (question_id, response_id)

survey_templates¶

Reusable survey templates with pre-configured questions.

• id UUID PRIMARY KEY
• organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE
• name TEXT NOT NULL
• description TEXT
• category TEXT
• questions JSONB NOT NULL DEFAULT '[]'
• default_settings JSONB DEFAULT '{}'
• suggested_triggers JSONB DEFAULT '[]'
• is_system BOOLEAN NOT NULL DEFAULT false
• created_by UUID REFERENCES auth.users(id) — Note: Legacy reference, should be users(id)
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()

RLS Policies:

• Public for system templates, org-scoped for custom
• Org admins have full access for org templates

Indexes:

• idx_survey_templates_category on category

survey_translations¶

Survey translations for multiple locales.

• id UUID PRIMARY KEY
• survey_id UUID NOT NULL REFERENCES survey_surveys(id) ON DELETE CASCADE
• locale TEXT NOT NULL
• title TEXT
• description TEXT
• thank_you_message TEXT
• questions JSONB DEFAULT '{}'
• status TEXT NOT NULL DEFAULT 'draft'
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
• UNIQUE(survey_id, locale)

Indexes:

• idx_survey_translations_survey_id on survey_id

survey_themes¶

Survey theming and branding.

• id UUID PRIMARY KEY
• organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE
• name TEXT NOT NULL
• colors JSONB DEFAULT '{}'
• typography JSONB DEFAULT '{}'
• spacing JSONB DEFAULT '{}'
• border_radius TEXT
• logo_url TEXT
• logo_position TEXT
• progress_bar JSONB DEFAULT '{}'
• is_system BOOLEAN NOT NULL DEFAULT false
• created_by UUID REFERENCES auth.users(id) — Note: Legacy reference, should be users(id)
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()

RLS Policies:

• System themes readable by all; org themes by org members

survey_triggers¶

Event and time-based survey activation rules.

• id UUID PRIMARY KEY
• survey_id UUID NOT NULL REFERENCES survey_surveys(id) ON DELETE CASCADE
• type TEXT NOT NULL DEFAULT 'manual'
• config JSONB DEFAULT '{}'
• enabled BOOLEAN NOT NULL DEFAULT true
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

RLS Policies:

• Org admins have full access (via survey's organization)

Indexes:

• idx_survey_triggers_survey_id on survey_id
• idx_survey_triggers_enabled on type WHERE enabled = true

survey_targeting_rules¶

Audience segmentation rules for survey distribution.

• id UUID PRIMARY KEY
• survey_id UUID NOT NULL REFERENCES survey_surveys(id) ON DELETE CASCADE
• rules JSONB NOT NULL DEFAULT '{}'
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

RLS Policies:

• Org admins have full access (via survey's organization)

Indexes:

• idx_survey_targeting_rules_survey_id on survey_id

survey_fatigue_config¶

Rate limiting configuration to prevent survey overload.

• id UUID PRIMARY KEY
• survey_id UUID REFERENCES survey_surveys(id) ON DELETE CASCADE
• is_global BOOLEAN NOT NULL DEFAULT false
• frequency_limit INTEGER DEFAULT 1
• cooldown_days INTEGER DEFAULT 7
• suppress_if_completed BOOLEAN DEFAULT true
• suppress_if_dismissed BOOLEAN DEFAULT true
• dismiss_cooldown_days INTEGER DEFAULT 30
• priority INTEGER DEFAULT 0
• created_at TIMESTAMPTZ NOT NULL DEFAULT now()
• CONSTRAINT fatigue_survey_or_global CHECK ((survey_id IS NOT NULL AND is_global = false) OR (survey_id IS NULL AND is_global = true))

RLS Policies:

• Org admins have full access; global configs readable by authenticated users

survey_fatigue_state¶

Per-user fatigue tracking state.

• id UUID PRIMARY KEY
• user_id UUID NOT NULL REFERENCES auth.users(id) — Note: Legacy reference, should be users(id)
• last_survey_shown_at TIMESTAMPTZ
• surveys_shown_today INTEGER NOT NULL DEFAULT 0
• surveys_shown_today_date DATE
• completion_rate_30d NUMERIC(5,2) DEFAULT 0
• survey_history JSONB DEFAULT '[]'
• updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
• UNIQUE(user_id)

RLS Policies:

• Users can manage their own fatigue state

Indexes:

• idx_survey_fatigue_state_user_id on user_id

Admin Tables¶

audit_log¶

Append-only audit trail for security-relevant actions (SOC 2 / ISO 27001).

• id UUID PRIMARY KEY
• actor_id UUID REFERENCES users(id)
• actor_type TEXT NOT NULL DEFAULT 'user'
• action TEXT NOT NULL
• resource_type TEXT NOT NULL
• resource_id UUID
• metadata JSONB
• ip_address INET
• user_agent TEXT
• organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
• created_at TIMESTAMPTZ DEFAULT NOW()

RLS Policies:

• Append-only insert (anyone can log)
• Org admins can read within their org

Indexes:

• idx_audit_log_actor on actor_id
• idx_audit_log_action on action
• idx_audit_log_resource on (resource_type, resource_id)
• idx_audit_log_created on created_at DESC
• idx_audit_log_organization_id on organization_id

beta_signups¶

(This table is not explicitly created in the baseline migration, but may exist via a separate migration or external process.)

Views¶

public_user_profiles¶

Public view of user profiles with redacted information.

CREATE OR REPLACE VIEW public_user_profiles AS
SELECT
  id,
  COALESCE(display_name, 'User') AS display_name
FROM users;

Grants: SELECT on authenticated, anon

Storage¶

organization-logos¶

Storage bucket for organization logos (public-read, 2MB limit).

• Bucket ID: organization-logos
• Public: Yes
• File size limit: 2MB
• Allowed MIME types: image/png, image/jpeg, image/webp

RLS Policies:

• Authenticated users can upload to org logos bucket
• Public read access to logos
• Authenticated users can delete org logos

Key RLS Patterns¶

Org-Scoped Read¶

CREATE POLICY "submissions_org_read" ON submissions
  FOR SELECT USING (organization_id = current_organization_id());

Org Member Insert¶

CREATE POLICY "submissions_org_insert" ON submissions
  FOR INSERT WITH CHECK (
    organization_id = current_organization_id()
    AND current_user_id() = author_id
    AND is_org_member()
  );

Own Update¶

CREATE POLICY "submissions_org_own_update" ON submissions
  FOR UPDATE USING (
    organization_id = current_organization_id()
    AND current_user_id() = author_id
  );

Admin Full Access¶

CREATE POLICY "submissions_org_admin_all" ON submissions
  FOR ALL USING (
    organization_id = current_organization_id()
    AND is_org_admin()
  );

Known Legacy References¶

Several survey tables still reference auth.users(id) in foreign keys:

• survey_surveys.created_by
• survey_responses.user_id
• survey_templates.created_by
• survey_themes.created_by
• survey_fatigue_state.user_id

RLS policies on these tables use auth.uid() and should be updated to use current_user_id(). These are legacy references from before the custom auth system was implemented.

What's Next¶

• System Overview — How the app uses these tables
• Authentication — RLS policies in depth
• Realtime — Realtime subscriptions to table changes