Comprehensive Database Schema Optimization & Security Plan

1. Problem Statement & Root Cause Analysis

A. Metric Accuracy & Role Tracking Issues

• Symptom: Admin dashboard counts are mismatched (e.g., showing 25 users in one place but 26 in another; 24 startups vs 22 actual startups).

• Root Cause: Role tracking is currently split between profiles.role and a separate user_roles table. The

  OverviewPage.tsx counts "Total Users" from profiles but counts "Startups/Partners" from user_roles. If a newly registered user (especially via Google OAuth) is inserted into profiles but has a mismatched or missing entry in user_roles, the counts skew.

• Goal: Unify role tracking to a single source of truth (profiles.role) and eliminate the redundant user_roles table entirely.

B. Security Vulnerabilities (Supabase Linter Errors)

• Symptom: Database linter is throwing SECURITY DEFINER and RLS Disabled errors.

• Root Cause:

  1. partner_wallet_stats view joins directly with auth.users AND is marked SECURITY DEFINER, exposing sensitive authentication data globally.

  2. app_unified_revenue and live_datasets are also SECURITY DEFINER views which bypass RLS of the querying user.

  3. Several tables (app_ad_summaries, app_daily_ad_stats, app_dataset_summaries, domain_taxonomy) simply lack Row Level Security (RLS) entirely.

• Goal: Convert views to use SECURITY INVOKER or remove them, decouple from auth.users, and apply strict RLS policies to all remaining tables.

C. Schema Redundancy & Dead Code

• Symptom: Bloated tables, confusing settings relationships, empty or unused LLM tracking tables.

• Root Cause:

  • Settings are scattered: app_settings holds general preferences, while app_profiles holds app-level info, and enterprise_profiles holds partner info. They share redundant fields (like bank details, notifications).

  • Ad stats are fragmented: app_daily_ad_stats and app_ad_summaries track clicks/impressions separately from the main app_daily_revenue_stats pipeline.

  • Wise (TransferWise) is no longer being used as the payout provider, but wise_* fields pollute multiple tables.

  • Outdated or empty LLM pipeline tables (dpo_triplets, interaction_pairs) from legacy features.

• Goal: Merge settings into role-specific profiles, aggregate stats accurately, and purge unused columns/tables to slim down the schema significantly.

2. Infrastructure & Database Changes (Phase 1)

We will execute these changes via a new SQL migration file (e.g., 20260518_schema_optimization_and_rls.sql).

1. Drop Wise Integrations

• ALTER TABLE platform_settings DROP COLUMN wise_payment_links;

• ALTER TABLE payouts DROP COLUMN wise_transfer_id;

• ALTER TABLE enterprise_profiles DROP COLUMN wise_recipient_id;

• ALTER TABLE app_profiles DROP COLUMN wise_recipient_id;

2. Eliminate Unused/Redundant Tables

• DROP TABLE user_roles; (Migrating data to profiles.role first if needed)

• DROP TABLE app_settings; (Fields migrating to app_profiles and enterprise_profiles)

• DROP TABLE dpo_triplets CASCADE; (Unused LLM data)

• DROP TABLE interaction_pairs CASCADE; (Unused LLM data)

• DROP TABLE app_ad_summaries CASCADE; (Replaced by app_daily_revenue_stats)

• DROP TABLE app_daily_ad_stats CASCADE;

3. Consolidate Settings into Profiles

• Extend app_profiles to hold startup-specific notification defaults and bank details that were previously in app_settings.

• Extend enterprise_profiles to hold its respective financial/contact info if missing.

4. Refactor Security & Views

• Drop the app_unified_revenue view. We will query unified metrics directly from app_daily_revenue_stats via the frontend or a simple RPC function to prevent security bypass.

• Drop and recreate partner_wallet_stats to be SECURITY INVOKER. Replace the dependency on auth.users by joining billing_transactions, dataset_purchases, and campaigns explicitly against profiles.

• Recreate live_datasets as SECURITY INVOKER.

• Remove admin_platform_stats view entirely, or refactor it into an RPC to fetch active totals.

5. Apply 100% RLS Coverage

• ALTER TABLE app_dataset_summaries ENABLE ROW LEVEL SECURITY; + Policy

• ALTER TABLE domain_taxonomy ENABLE ROW LEVEL SECURITY; + Policy (Likely just a public read policy: FOR SELECT USING (true) )

3. Application & Frontend Updates (Phase 2)

We will make ZERO changes to the UI design/layout. All updates are scoped exclusively to the data layer, React Hooks, and Zustand Stores.

A. Auth & Role Handling (

src/stores/auth-store.ts)

• Remove all references to the user_roles table.

• Overhaul

  fetchAppUser to solely check profiles.role to determine if a user is a startup, partner, or admin.

• Update

  setUserRole to only fire an UPDATE on profiles.

B. Admin Dashboard Metrics (

src/pages/admin/OverviewPage.tsx&UsersPage.tsx)

• Action: Fix the metric inaccuracies (e.g., 25 vs 26 count bug).

• Change: Remove queries to user_roles. In

  OverviewPage.tsx, we will fetch the total count, startup count, and partner count natively from the profiles table via a single .select('role, id') call.

• Change: In

  UsersPage.tsx, map the user list from the profiles join cleanly.

C. Startup Dashboard Settings (

src/stores/startup-store.ts)

• Change: Remove the

  fetchAppSettings and

  upsertAppSettings methods.

• Replace their functionality inside

  fetchAppProfile and

  upsertAppProfile. Settings queries that originally looked for discord_notify or currency will now pull directly from the consolidated app_profiles row.

D. Revenue Graphs & Ad Stats (

src/lib/startup-revenue.ts)

• Action: Ensure the UI doesn't rely on dropped tables (app_ad_summaries or app_daily_ad_stats).

• Change: The

  fetchStartupRevenueSnapshot logic will be streamlined to pull purely from app_daily_revenue_stats and payouts, ensuring clicks, impressions, and revenue are strictly tied to our ledger-verified statistics rather than detached logs.

E. Partner Enterprise Config (

src/lib/partner-enterprise.tsor related)

• Change: Remove attempts to read/write wise_recipient_id or query legacy partner_wallet_stats view with exposed auth dependencies.

4. Execution Strategy

To ensure zero downtime or breaking changes in the browser, the execution will follow this precise order:

1. Migration File Generation: Write the SQL to copy any straggling data (user_roles -> profiles.role, app_settings -> app_profiles), drop the tables, alter the views, and enable RLS.

2. Frontend Wiring: Update the TypeScript interfaces, Zustand stores (

   auth-store.ts,

   startup-store.ts), and helper utilities (

   startup-revenue.ts) to match the new schema structure.

3. Admin Panel Update: Overhaul the data-fetching queries in

   OverviewPage.tsx and

   UsersPage.tsx to restore 100% metric accuracy.

4. Validation: Run TypeScript type-checking to ensure no broken imports or missing keys remain.