# Downriver Rates Backend Plan This document is the bridge from the current browser-only prototype to a real launchable Downriver Rates app. ## Recommended Stack - Frontend: keep the current prototype while backend foundations are built; later move to React/Next.js when needed. - Backend/database/auth: Supabase with PostgreSQL. - Payments: Stripe. - Email: Supabase Auth email verification first; Resend later for richer product emails. - Hosting: Vercel for a future app frontend, Supabase for database/auth/storage. ## Core Product Rules 1. Members and business members can create service requests about their current expenses. 2. Service providers can only view leads that match their verified industry. 3. Providers must pay before they can message a member. 4. Member identity remains private unless the platform explicitly allows specific details to be shared. Current rule: payment never reveals direct contact information. Members can choose to include contact details only inside their reply. 5. The website owner receives message fee revenue. 6. The system tracks provider spend, replies, response rate, and cost per response. 7. Admins can review platform health, provider verification, transactions, and disputes. ## Database Tables ### profiles Stores shared user/account information connected to Supabase Auth. Fields: - id - auth_user_id - role: provider, member, business, admin - display_name - email - phone - status - email_verified_at - created_at - updated_at ### provider_profiles Stores provider-specific verification and company data. Fields: - id - profile_id - individual_name - company_name - position_title - hired_year - industry - workplace_address - work_email - work_phone - employer_fein - verification_status - stripe_customer_id - stripe_account_id ### member_profiles Stores privacy-safe member/business member profile settings. Fields: - id - profile_id - profile_type - city - state - zip_code - region - preferred_contact - privacy_notes - business_name - fein - state_of_incorporation - account_holder_name - account_holder_title ### service_requests Stores member/business expense requests. Fields: - id - owner_profile_id - owner_role - category - monthly_expense_cents - current_provider - review_date - location_city - location_state - zip_code - visibility: anonymous, hidden - details - category_details - status - created_at - updated_at - archived_at ### paid_messages Stores provider-to-member paid message access and the first member reply. Payment grants message permission only. It does not unlock member identity, direct contact information, or sensitive personal details. Fields: - id - provider_profile_id - member_profile_id - service_request_id - amount_cents - message - reply - status - paid_at - replied_at - member_revealed_contact - revealed_contact_note - created_at ### paid_message_events Stores follow-up conversation messages after the member replies. The first paid message is still the only fee event. Once the member replies, the provider can send free follow-ups for 45 days inside the same thread. Fields: - id - paid_message_id - sender_role - body - member_revealed_contact - revealed_contact_note - created_at ### wallets Stores provider balances and payment state. Fields: - id - provider_profile_id - balance_cents - currency - stripe_customer_id - created_at - updated_at ### ledger_entries Stores all money movement. Fields: - id - provider_profile_id - paid_message_id - type: add_funds, message_fee, refund, owner_revenue, payout - amount_cents - currency - label - stripe_payment_intent_id - created_at ### saved_leads Stores provider pipeline state. Fields: - id - provider_profile_id - service_request_id - status - created_at - closed_at ### notifications Stores alerts for users and admins. Fields: - id - recipient_profile_id - audience - title - body - link - type - read_at - created_at ## Permissions ### Member Can: - Read and update their own profile. - Create, edit, and archive their own service requests. - Read messages connected to their own service requests. - Reply to provider messages. Cannot: - See other members. - See provider wallet details. - See admin metrics. ### Business Member Same as Member, plus: - Store business profile fields. - Create business-owned service requests. ### Service Provider Can: - Read and update their provider profile. - Search visible service requests matching their verified industry. - Save leads matching their industry. - Pay to message matching leads. - Read messages they paid to open. - See their own spend, response rate, and wallet. Cannot: - Search outside their verified industry. - See member name, street address, email, phone, or private details while browsing. - Receive member identity or direct contact information just because they paid to message. - Message without a paid access record. - See other providers' wallet or reporting. ### Admin Can: - View operational metrics. - Review provider verification. - Review transactions, disputes, and payout readiness. - Manage platform settings. Should avoid: - Exposing unnecessary member identity by default. ## Backend Build Order ### Phase 1: Supabase Foundation 1. Create Supabase project. 2. Add database schema. 3. Enable Row Level Security. 4. Add seed data for demo accounts. 5. Connect current frontend to Supabase in read-only mode for one area. ### Phase 2: Authentication 1. Replace prototype account switching with Supabase Auth. 2. Add email verification. 3. Create role-based profile records after sign up. 4. Add protected dashboard routes. ### Phase 3: Service Requests 1. Save requests to database. 2. Load member-owned service requests from database. 3. Enforce owner-only edit/archive. 4. Enforce provider industry matching. ### Phase 4: Messaging and Fees 1. Add Stripe test payments. 2. Add provider wallet funding. 3. Charge message fees. 4. Create paid message access records. 5. Track replies and response rates. 6. Let members reply through Downriver Rates and optionally share direct contact details in their own reply. 7. Open a 45-day no-extra-fee provider follow-up window after the member replies. ### Phase 5: Admin and Launch Readiness 1. Provider verification queue. 2. Admin reporting. 3. Audit logs. 4. Terms/privacy pages. 5. Backups, monitoring, error tracking, and deployment. ## First Backend Milestone The first backend milestone should be small: 1. Create Supabase project. 2. Run `supabase-schema.sql`. 3. Connect one page, likely Sign In or Service Requests, to real Supabase data. 4. Keep the current local-storage prototype working while backend pieces are added. ## Supabase Project Started Local project files now exist: - `supabase/config.toml` - `supabase/migrations/202605220001_downriver_rates_initial_schema.sql` - `.env.example` - `supabase/README.md` - `supabaseClient.js` Next action: 1. Create the hosted Supabase project. 2. Copy real project keys into a private `.env` file. 3. Run the first schema migration in Supabase SQL Editor or through the Supabase CLI. ## Frontend Backend Connector Started The prototype now loads the Supabase browser client and shows backend status on the Launch page. Service request sync is intentionally gated until real Supabase Auth is connected. This keeps Row Level Security intact and avoids opening private service request tables to anonymous public writes. ## Supabase Auth Started The Sign In page now has a real Supabase Auth panel for: - Creating a Supabase email/password auth account. - Signing in with Supabase Auth. - Signing out of Supabase Auth. - Showing the current Supabase Auth session status. - Creating/updating the user's row in the `profiles` table. This is separate from the prototype account switcher for now. The next step is connecting real Supabase Auth users to rows in the `profiles` table. Migration added: - `supabase/migrations/202605220002_auth_profile_insert_policy.sql` - `supabase/migrations/202605220003_authenticated_table_grants.sql` - `supabase/migrations/202605220004_profiles_rls_profile_creation_fix.sql` - `supabase/migrations/202605220005_profile_rpc_upsert.sql` - `supabase/migrations/202605220006_service_request_rpc.sql` - `supabase/migrations/202605240001_visibility_privacy_cleanup.sql` - `supabase/migrations/202605240002_provider_profile_rpc.sql` - `supabase/migrations/202605240003_paid_message_rpc.sql` This migration allows an authenticated Supabase user to insert their own `profiles` row. The grants migration gives authenticated users API-level table permissions while Row Level Security still controls allowed rows. The profile creation fix tightens the insert check to the signed-in user's auth id and email. The profile RPC migration adds a secure function the frontend can call to create/update the signed-in user's profile row without exposing broad table writes. The service request RPC migration adds a secure function for signed-in member/business users to save their own service requests. The visibility cleanup migration converts old `paid` visibility rows to `anonymous`, blocks future paid-visibility requests, and confirms that paid provider access is messaging-only. The provider profile RPC migration lets a signed-in provider set company and industry for backend lead search during prototype testing. Before launch, provider verification should become an admin-reviewed workflow. The paid message RPC migration records backend paid messages and ledger entries for matched provider leads. Stripe will replace the simulated fee later.