wwf-match-logic

gistfile1.md

Daily Match System - Technical Documentation

Overview

The daily match system in the WWF app manages user daily matching limits based on their subscription level. The system uses a PostgreSQL view (daily_matches_status) that dynamically calculates daily limits, usage, and remaining matches.

Database Architecture

Core Tables

1. users Table

Contains user subscription information that determines daily limits:

-- Relevant fields:
id (uuid) - User identifier
current_subscription (subscriptionLevels) - User's subscription tier
first_name, last_name, email - User details

2. daily_matches_usage Table

Tracks actual daily usage per user:

CREATE TABLE public.daily_matches_usage (
    id bigint NOT NULL,
    user_id uuid,
    usage_date date DEFAULT CURRENT_DATE,
    used_count integer DEFAULT 0
);

-- Constraints:
-- Primary Key: id
-- Unique: (user_id, usage_date) - One record per user per day
-- Foreign Key: user_id -> users.id (CASCADE DELETE)
-- Index: (user_id, usage_date) for fast lookups

The daily_matches_status View

This is the main interface for checking daily match status. It's a VIEW, not a table - meaning it calculates values dynamically.

CREATE VIEW public.daily_matches_status AS
 SELECT u.id AS user_id,
    u.first_name,
    u.last_name,
    u.email,
    u.current_subscription,
    public.get_daily_matches_limit(u.current_subscription) AS daily_limit,
    COALESCE(dmu.used_count, 0) AS used_today,
    GREATEST(0, (public.get_daily_matches_limit(u.current_subscription) - COALESCE(dmu.used_count, 0))) AS remaining,
    CURRENT_DATE AS date
   FROM (public.users u
     LEFT JOIN public.daily_matches_usage dmu ON (((dmu.user_id = u.id) AND (dmu.usage_date = CURRENT_DATE))));

View Fields:

• user_id: User identifier
• daily_limit: Maximum matches allowed per day (based on subscription)
• used_today: Number of matches used today
• remaining: Matches remaining today (daily_limit - used_today, minimum 0)
• date: Current date
• Additional user info: first_name, last_name, email, current_subscription

Database Functions

1. get_daily_matches_limit(subscription)

Returns daily match limits based on subscription tier:

CREATE FUNCTION public.get_daily_matches_limit(p_subscription public."subscriptionLevels") 
RETURNS integer AS $$
BEGIN
    RETURN CASE p_subscription
        WHEN 'Basic' THEN 3
        WHEN 'Advantage' THEN 4
        WHEN 'Premium' THEN 6
        WHEN 'Elite' THEN 10
        WHEN 'Vip' THEN 15
        WHEN 'UnlockedPremium' THEN 6  -- Same as Premium
        ELSE 3
    END;
END;$$;

2. get_remaining_daily_matches(user_id)

Returns comprehensive match status for a user:

CREATE FUNCTION public.get_remaining_daily_matches(p_user_id uuid) 
RETURNS jsonb AS $$
DECLARE
    user_subscription public."subscriptionLevels";
    daily_limit integer;
    used_today integer;
    result jsonb;
BEGIN
    -- Get user's subscription
    SELECT current_subscription INTO user_subscription
    FROM public.users WHERE id = p_user_id;

    -- Get daily limit
    daily_limit := get_daily_matches_limit(user_subscription);

    -- Get today's usage
    SELECT COALESCE(used_count, 0) INTO used_today
    FROM daily_matches_usage
    WHERE user_id = p_user_id AND usage_date = CURRENT_DATE;

    result := jsonb_build_object(
        'subscription_type', user_subscription,
        'daily_limit', daily_limit,
        'used_today', COALESCE(used_today, 0),
        'remaining', GREATEST(0, daily_limit - COALESCE(used_today, 0)),
        'can_like', COALESCE(used_today, 0) < daily_limit
    );

    RETURN result;
END;$$;

3. use_daily_match(user_id)

Consumes a daily match and updates usage counter:

CREATE FUNCTION public.use_daily_match(p_user_id uuid) 
RETURNS jsonb AS $$
DECLARE
    user_subscription public."subscriptionLevels";
    daily_limit integer;
    current_usage integer;
BEGIN
    -- Check remaining matches
    WITH check_result AS (
        SELECT (get_remaining_daily_matches(p_user_id)->>'remaining')::int as remaining
    )
    SELECT remaining INTO current_usage FROM check_result;

    IF current_usage <= 0 THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'Daily matches limit reached'
        );
    END IF;

    -- Increment usage (UPSERT pattern)
    INSERT INTO daily_matches_usage (user_id, used_count)
    VALUES (p_user_id, 1)
    ON CONFLICT (user_id, usage_date)
    DO UPDATE SET used_count = daily_matches_usage.used_count + 1;

    RETURN jsonb_build_object(
        'success', true,
        'remaining', current_usage - 1
    );
END;$$;

How Updates Work

1. View Auto-Updates

The daily_matches_status view automatically reflects changes because:

• It's calculated dynamically on each query
• Joins with current date's usage data
• Recalculates remaining based on current used_count

2. Daily Reset Mechanism

The system automatically "resets" daily:

• View always filters daily_matches_usage by usage_date = CURRENT_DATE
• If no record exists for today, used_today defaults to 0
• remaining becomes the full daily limit

3. Usage Tracking

When a user performs a match action:

1. Check limit: Query daily_matches_status or call get_remaining_daily_matches()
2. Consume match: Call use_daily_match() or update daily_matches_usage directly
3. Auto-update: View immediately reflects new values

Flutter Implementation

Current Implementation Issues

❌ Problematic Code in home_page_model.dart (lines ~689-697):

// This DOESN'T WORK - can't directly update a view!
await DailyMatchesStatusTable().update(
  data: {
    'remaining': (dailyMatchLimit!.firstOrNull!.remaining!) - 1,
    'used_today': (dailyMatchLimit?.firstOrNull!.usedToday!) + 1,
  },
  matchingRows: (rows) => rows.eqOrNull('user_id', currentUserUid),
);

Correct Usage Patterns

✅ Reading Status:

// Check daily match status
List<DailyMatchesStatusRow>? dailyMatchLimit = await DailyMatchesStatusTable().queryRows(
  queryFn: (q) => q.eqOrNull('user_id', currentUserUid),
);

bool hasReachedLimit = dailyMatchLimit?.firstOrNull?.remaining == 0;

✅ Consuming Matches (Recommended):

// Use database function (preferred method)
final result = await supabase.rpc('use_daily_match', {'p_user_id': currentUserUid});

// OR update usage table directly
await DailyMatchesUsageTable().insert({
  'user_id': currentUserUid,
  'used_count': 1,
}).onConflict(
  // Increment existing count for today
);

Key Files in Codebase

Flutter Files

• lib/backend/supabase/database/tables/daily_matches_status.dart - View interface
• lib/backend/supabase/database/tables/daily_matches_usage.dart - Usage table interface
• lib/home/home_page/home_page_model.dart - Main usage logic (needs fixing)
• lib/profile/dating_tools/dating_tools_widget.dart - Display status
• lib/profile/subscriptions_page/subscriptions_page_widget.dart - Subscription-based display

Database Objects

• View: public.daily_matches_status
• Table: public.daily_matches_usage
• Functions: get_daily_matches_limit(), get_remaining_daily_matches(), use_daily_match()

Recommendations

1. Fix Flutter Implementation

Replace direct view updates with proper usage tracking:

// Instead of updating the view directly, update the usage table
Future<bool> consumeDailyMatch(String userId) async {
  try {
    final result = await supabase.rpc('use_daily_match', {'p_user_id': userId});
    return result['success'] == true;
  } catch (e) {
    // Fallback: direct table update
    await DailyMatchesUsageTable().insert({
      'user_id': userId,
      'usage_date': DateTime.now(),
      'used_count': 1,
    }).onError((error, stackTrace) {
      // Handle upsert logic
    });
    return true;
  }
}

2. Use Database Functions

Prefer calling use_daily_match() over manual table updates for better consistency and error handling.

3. Subscription Changes

When user subscription changes, the view automatically reflects new limits - no additional updates needed.

Data Flow Summary

User Action (Like/Match)
    ↓
Check daily_matches_status view
    ↓
If remaining > 0:
    ↓
Call use_daily_match() OR
Update daily_matches_usage table
    ↓
View automatically shows updated values
    ↓
UI reflects new remaining count

Troubleshooting

Common Issues

1. View not updating: Check if daily_matches_usage table is being updated correctly
2. Wrong limits: Verify user's current_subscription in users table
3. Date issues: Ensure timezone consistency between app and database
4. Negative remaining: Check for race conditions in concurrent updates

Debugging Queries

-- Check user's current status
SELECT * FROM daily_matches_status WHERE user_id = 'user-uuid';

-- Check raw usage data
SELECT * FROM daily_matches_usage WHERE user_id = 'user-uuid' AND usage_date = CURRENT_DATE;

-- Test function directly
SELECT get_remaining_daily_matches('user-uuid');