Skip to main content

REDUCE DB DATA REDUNDANCY / DB SECURITY

Feedback, Bugs, & Feature Requests

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.

Posted by Harshil S ·
Status: In Progress

Log in to comment and vote

No comments yet

Be the first to share your thoughts.