Supabase gen types {"message":"Cannot read properties of undefined (reading 'startsWith')"

[Xyeut]

Describe the bug
I'm noticing an odd issue with supabase gen types typescript --project-id pjlbzgujomiba***** --schema public,messaging,stripe,autosend > ../types/src/supabase/supabase.ts
Whenever I run this, I'm encountered with this error

It seems to only affect my project, this was working fine the night before. However, I pushed some migrations and the next morning I wasn't able to generate any types. If I create a test project in supabase and try to generate types, I'm able to do so without any issues.

I'm also not able to generate types through the dashboard.

To Reproduce
Steps to reproduce the behavior:

1. Try to generate types for the api.athleticscholar.net project
2. Encounter error

Expected behavior
A clear and concise description of what you expected to happen.
I expect generated types to appear

Screenshots

System information
Rerun the failing command with --create-ticket flag.

• Ticket ID: 8bee073e313c4f83a69a351b772e3f37
• Version of OS: Mac Tahoe v26.0.1
• Version of CLI: v2.51.0
• Versions of services:
  SERVICE IMAGE | LOCAL | LINKED
  ------------------------|------------------------|------------
  supabase/postgres | 17.6.1.021 | 17.6.1.021
  supabase/gotrue | v2.180.0 | v2.180.0
  postgrest/postgrest | v13.0.7 | -
  supabase/realtime | v2.53.2 | -
  supabase/storage-api | v1.28.1 | -
  supabase/edge-runtime | v1.68.3 | -
  supabase/studio | 2025.10.09-sha-433e578 | -
  supabase/postgres-meta | v0.91.7 | -
  supabase/logflare | 1.23.0 | -
  supabase/supavisor | 2.7.3 | -

[sweatybridge]

@avallete can we check if this is reproducible?

[Xyeut]

@sweatybridge
I understand you’re looking into this, but this issue is currently halting our development since we rely on generated types for our project.

Is there any temporary workaround or method we can use to generate types until a fix is deployed?

Happy to provide any additional info about our schema or migrations if it helps.

[avallete]

Hi @Xyeut,

I’m investigating this now thanks for your patience. In the meantime, here are a few temporary workarounds you can try so you’re not blocked:

1. Revert the last migration that introduced the issue (since there were no changes on our side, it’s likely caused by a recent schema change).
2. Temporarily use any as your Database type — this will disable strict inference but let your app keep building.
3. Or revert to a previous schema version (via version control) where type generation worked, and manually patch any new tables/functions until this is fixed.

To help us pinpoint the issue, could you please:

• Share the migration file(s) that triggered the failure.

• Try running the same command with the latest beta CLI and let us know if it still happens:

  npx supabase@beta gen types typescript \
    --project-id pjlbzgujomiba***** \
    --schema public,messaging,stripe,autosend \
    > ../types/src/supabase/supabase.ts

This will help us confirm whether the fix already landed in beta. 🙏

[Xyeut]

@avallete

Yeah of course, while I waited I tried to revert the migrations to the day prior when it was all working. Unfortunately, that seemed to still exhibit the same error. I attempted to use
supabase migration repair --status reverted 20251017003721
on all of the migrations that happened the day I started to encounter that error.

I could temporarily use any which might be what I have to do sadly. It just increases the odds of errors I might end up missing later down.

I've attempted to run npx supabase@beta and still encountered the issue, if it's any help I ran it with the --debug argument and this was the ouput:

npx supabase@beta gen types typescript \
  --project-id pjlbzgujomibanvnseyw \
  --schema public,messaging,stripe,autosend \
  > ../types/src/supabase/supabase.ts --debug
open supabase/.temp/profile: no such file or directory
Using workdir /Users/fxncis/Desktop/AthleticScholar/Platform
Supabase CLI 2.53.7
Using profile: supabase (supabase.co)
Loading project ref from flag: pjlbzgujomibanvnseyw
Using access token for profile: supabase
2025/10/21 10:35:47 HTTP GET: https://api.supabase.com/v1/projects/pjlbzgujomibanvnseyw/types/typescript?included_schemas=public%2Cmessaging%2Cstripe%2Cautosend
failed to retrieve generated types: {"message":"Cannot read properties of undefined (reading 'startsWith')","errorEventId":"00329a76597f4f00bbffd1e779097bd1"}

For the migration files, I don't know which one caused it, I have 3 migrations for that day which were pushed to production & I'm not sure entirely that I'd be able to pinpoint the specific one. However, these are it:

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION autosend."getAutoSendCoachesFromPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
      FROM autosend.autosend
      JOIN "userProfile" profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id AND coaches.status = 'Active'
      WHERE profiles.id = _id AND coaches.email IS NOT NULL;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendMatchedPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS jsonb
 LANGUAGE plpgsql
 SET search_path TO 'public'
AS $function$
  DECLARE
    result JSONB;

  BEGIN
    WITH "matchedCoaches" AS (
      SELECT colleges.id, colleges.name, colleges.city, colleges.state, coaches.name as "coachName"
      FROM autosend.autosend
      JOIN "userProfile" profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id AND coaches.status = 'Active'
      WHERE autosend.id = _id 
    ),

    "coachesCount" AS (
      SELECT COUNT(*) AS "totalCoaches" FROM "matchedCoaches"
    ),

    "limitedSchools" AS (
      SELECT DISTINCT id, name, city, state
      FROM "matchedCoaches"
      LIMIT 5
    )


    SELECT JSON_BUILD_OBJECT(
      'schools', (SELECT JSON_AGG(coach) FROM (SELECT * FROM "limitedSchools") coach),
      'totalSchools', (SELECT "totalCoaches" from "coachesCount")
    ) INTO result;

    RETURN result;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendTemplateCoach"(_id uuid)
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "autoSendProfile" AS (
        SELECT "userProfile".id, sport
        FROM autosend.autosend
        JOIN "userProfile" ON "userProfile".id = autosend.profile  
        WHERE autosend.id = _id
      ),

      "preferredCoach" AS (
        SELECT coach.id, coach.name, coach.title, coach."collegeId", coach."collegeName"
        FROM autosend.autosend
        JOIN "userProfile" profiles ON profiles.id = autosend.profile
        JOIN autosend."getAutoSendCoachesFromPreferences"(profiles.id, 
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'states')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'affiliations')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'divisions'))) coach ON true
        WHERE autosend.id = _id
      ),

      "fallBackCoach" AS (
        SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
        FROM autosend.autosend
        JOIN profiles ON profiles.id = autosend.profile
        JOIN sports ON sports.sport = profiles.sport
        JOIN coaches ON coaches.sport = sports.id AND coaches.status = 'Active'
        JOIN colleges ON colleges.id = sports.college
      )

      SELECT *
      FROM (
        SELECT *
        FROM "preferredCoach"
        ORDER BY random()
        LIMIT 1
      ) PC

      UNION ALL

      SELECT *
      FROM "fallBackCoach"
      WHERE NOT EXISTS (SELECT 1 FROM "preferredCoach")
      LIMIT 1;

  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendWeeklyCoaches"(_id uuid, _search text DEFAULT NULL::text, _offset integer DEFAULT 0, "_isAllCoaches" boolean DEFAULT NULL::boolean, "_isToMessage" boolean DEFAULT NULL::boolean, "_isMessaged" boolean DEFAULT NULL::boolean)
 RETURNS TABLE(id uuid, name text, "collegeId" uuid, "collegeName" text, "collegeState" text, "collegeAffiliation" text, "collegeDivision" text, "collegeConference" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT weekly_coaches.id, coaches.name, colleges.id, colleges.name, colleges.state, colleges."athleticAffiliation", colleges."athleticDivision", colleges."athleticConference"
    FROM autosend.autosend
    JOIN autosend.weekly_coaches weekly_coaches ON weekly_coaches.autosend = autosend.id
    JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = weekly_coaches.coach
    JOIN coaches ON coaches.id = autosend_coaches.coach AND coaches.status = 'Active'
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE 
      (autosend.id = _id) AND
      (_search IS NULL OR 
        coaches.name ILIKE '%' || _search || '%' OR
        colleges.name ILIKE '%' || _search || '%'
      ) AND
      ("_isToMessage" IS NULL OR weekly_coaches.status = 'Pending') AND
      ("_isMessaged" IS NULL OR weekly_coaches.status = 'Sent')
    OFFSET _offset
    LIMIT 7;  
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getDailyAutoSendCoaches"(_id uuid, "_dailyAmount" integer)
 RETURNS TABLE(id uuid, name text, timezone text, "collegeId" uuid)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT autosend.weekly_coaches.coach, coaches.name, colleges.timezone, colleges.id
    FROM autosend.weekly_coaches
    JOIN autosend.coaches "autoSendCoaches" ON "autoSendCoaches".id = autosend.weekly_coaches.coach
    JOIN coaches ON coaches.id = "autoSendCoaches".coach AND coaches.status = 'Active'
    JOIN sports ON sports.id = coaches.sport 
    JOIN colleges ON colleges.id = sports.college
    WHERE EXTRACT(HOUR FROM NOW() AT TIME ZONE colleges.timezone) BETWEEN 8 AND 20
    ORDER BY RANDOM()
    LIMIT ("_dailyAmount");
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getPendingMessage"(_id uuid)
 RETURNS TABLE(id uuid, name text, "userEmail" text, email text, "autoSendCoachId" uuid, "coachId" uuid, "coachName" text, "coachEmail" text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT 
      autosend.profile as "profileId",
      users.name,
      users.email,
      profiles.email,
      autosend_coaches.id,
      coaches.id,
      coaches.name as "coachName",
      coaches.email as "coachEmail",
      colleges.id as "collegeId",
      colleges.name as "collegeName"
    FROM autosend.coaches autosend_coaches
    JOIN autosend.autosend ON autosend.id = autosend_coaches.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN users ON users.id = profiles.user
    JOIN coaches ON coaches.id = autosend_coaches.coach AND coaches.status = 'Active'
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE autosend_coaches.id = _id;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getScheduledEmails"()
 RETURNS TABLE(id uuid, autosend uuid, "isLastScheduledMessage" boolean)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "coachTimezones" AS (
        SELECT autosend_coaches.id as "coachId", colleges.timezone
        FROM autosend.coaches autosend_coaches
        JOIN coaches ON coaches.id = autosend_coaches.coach AND coaches.status = 'Active'
        JOIN sports ON sports.id = coaches.sport
        JOIN colleges ON colleges.id = sports.college
      ),
      "allMessages" AS (
        SELECT autosend_coaches.id, autosend_coaches.autosend, autosend.daily_coaches.status, ROW_NUMBER() OVER (
                PARTITION BY autosend_coaches.autosend 
                ORDER BY daily_coaches."scheduledAt" DESC
            ) AS row_num, autosend.daily_coaches."scheduledAt", "coachTimezones".timezone
        FROM autosend.daily_coaches
        JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = daily_coaches.coach
        JOIN "coachTimezones" ON "coachTimezones"."coachId" = autosend_coaches.id 
      ),
      "filteredMessages" AS (
        SELECT "allMessages".id, "allMessages".autosend, "scheduledAt", (row_num = 1) AS "isLastScheduledMessage"
        FROM "allMessages"
        WHERE status = 'Pending' AND ("scheduledAt" AT TIME ZONE timezone) <= (now() AT TIME ZONE timezone)
      )

      SELECT "filteredMessages".id, "filteredMessages".autosend, "filteredMessages"."isLastScheduledMessage"
      FROM "filteredMessages"
      ORDER BY "isLastScheduledMessage", "scheduledAt" DESC;
  END;
$function$
;

create policy "Disable access to autosend coaches"
on "autosend"."coaches"
as permissive
for all
to authenticated
using (false);


create policy "Disable access to authenticated athletes"
on "autosend"."daily_coaches"
as permissive
for all
to authenticated
using (false);


create policy "Disable access for authenticated"
on "autosend"."weekly_coaches"
as permissive
for all
to authenticated
using (false);



alter table "messaging"."messages" alter column status type "public"."MessageStatus" using status::text::"public"."MessageStatus";

create policy "Disable access for authenticated"
on "messaging"."domains"
as permissive
for all
to authenticated
using (false);


create policy "Disabled access for authenticated"
on "messaging"."messages"
as permissive
for all
to authenticated
using (false);



drop function if exists "public"."get_public_college_coaches"(_college uuid, _sport text, _gender text);


alter table "public"."users" alter column "notifications" set default '{"coachSendsMeAMessage": true}'::jsonb;

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION public.root()
 RETURNS jsonb
 LANGUAGE sql
 SET search_path TO 'public'
AS $function$
  SELECT jsonb_build_object(
    'swagger', '2.0',
    'info', jsonb_build_object(
      'title', 'Unauthorized Access Detected',
      'description',
        'Your request could not be processed due to insufficient permissions. ' ||
        'This attempt has been logged for security monitoring. Repeated unauthorized access ' ||
        'may result in account suspension or further investigation. If you believe this is in error, ' ||
        'please contact our support team.'
    )
  );
$function$
;

CREATE OR REPLACE FUNCTION public.get_public_college_coaches(_college uuid, _sport text, _gender text)
 RETURNS TABLE(id uuid, name text, title text, "hasEmail" boolean)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
BEGIN
    RETURN QUERY
    SELECT coaches.id, coaches.name, coaches.title, (coaches.email IS NOT NULL AND coaches.email <> '') AS "hasEmail"
    FROM colleges
    JOIN sports ON sports.college = colleges.id AND sports.sport = _sport AND sports.gender = _gender
    JOIN coaches ON coaches.sport = sports.id
    WHERE colleges.id = _college AND coaches.status = 'Active';
END;
$function$
;

create policy "Disable access for authenticated users"
on "public"."conversations"
as permissive
for all
to authenticated
using (false);


create policy "Disable access for authenticated users"
on "public"."messages"
as permissive
for all
to authenticated
using (false);

migration 2:

set check_function_bodies = off;

CREATE OR REPLACE FUNCTION autosend."getAutoSendOverview"("_autoSendId" uuid)
 RETURNS TABLE("allMessagesSent" bigint, "totalRepliesReceived" bigint, "replyRate" bigint)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT
      COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id),
      COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id),
      COALESCE(
        (
          COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id)::numeric
          / NULLIF(COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id), 0)
        ),
        0
      ) * 100 AS "replyRate"
    FROM autosend.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN conversations ON conversations.creator = 'Athlete' AND conversations.profile = profiles.id AND conversations."isAutomated" = TRUE
    JOIN messages ON messages.conversation = conversations.id
    WHERE autosend.id = "_autoSendId";
  END;
$function$
;

migration 3:

set check_function_bodies = off;

DROP FUNCTION autosend."clearDailyCoaches"(uuid);

CREATE OR REPLACE FUNCTION autosend."clearDailyCoaches"(_id uuid DEFAULT NULL::uuid)
 RETURNS integer
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'autosend'
AS $function$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM autosend.daily_coaches
    WHERE (_id IS NULL OR autosend.daily_coaches.autosend = _id);

    GET DIAGNOSTICS deleted_count = ROW_COUNT;

    RETURN deleted_count;
END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendCoachesFromPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
      FROM autosend.autosend
      JOIN user_profile profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id
      WHERE profiles.id = _id AND coaches.email IS NOT NULL;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendMatchedPreferences"(_id uuid, _states text[] DEFAULT NULL::text[], _affiliations text[] DEFAULT NULL::text[], _divisions text[] DEFAULT NULL::text[], _conferences text[] DEFAULT NULL::text[])
 RETURNS jsonb
 LANGUAGE plpgsql
 SET search_path TO 'public'
AS $function$
  DECLARE
    result JSONB;

  BEGIN
    WITH "matchedCoaches" AS (
      SELECT colleges.id, colleges.name, colleges.city, colleges.state, coaches.name as "coachName"
      FROM autosend.autosend
      JOIN user_profile profiles ON profiles.id = autosend.profile
      JOIN colleges ON (
        (_states IS NULL OR colleges.state = ANY(_states)) AND
        (_affiliations IS NULL OR colleges."athleticAffiliation" = ANY(_affiliations)) AND
        (_divisions IS NULL OR colleges."athleticDivision" = ANY(_divisions)) AND
        (_conferences IS NULL OR colleges."athleticConference" = ANY(_conferences)))
      JOIN sports ON sports.college = colleges.id AND sports.sport = profiles.sport AND sports.gender = profiles."preferredGender"
      JOIN coaches ON coaches.sport = sports.id
      WHERE autosend.id = _id 
    ),

    "coachesCount" AS (
      SELECT COUNT(*) AS "totalCoaches" FROM "matchedCoaches"
    ),

    "limitedSchools" AS (
      SELECT DISTINCT id, name, city, state
      FROM "matchedCoaches"
      LIMIT 5
    )


    SELECT JSON_BUILD_OBJECT(
      'schools', (SELECT JSON_AGG(coach) FROM (SELECT * FROM "limitedSchools") coach),
      'totalSchools', (SELECT "totalCoaches" from "coachesCount")
    ) INTO result;

    RETURN result;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendOverview"("_autoSendId" uuid)
 RETURNS TABLE("allMessagesSent" bigint, "totalRepliesReceived" bigint, "replyRate" bigint)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT
      COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id),
      COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id),
      COALESCE((COUNT(*) FILTER (WHERE messages.sender = 'Coach' AND conversations.profile = profiles.id) / NULLIF(COUNT(*) FILTER (WHERE messages.sender = 'Athlete' AND conversations.profile = profiles.id), 0)), 0)
    FROM autosend.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN conversations ON conversations.creator = 'Athlete' AND conversations.profile = profiles.id AND conversations."isAutomated" = TRUE
    JOIN messages ON messages.conversation = conversations.id
    WHERE autosend.id = "_autoSendId";
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendTemplateCoach"(_id uuid)
 RETURNS TABLE(id uuid, name text, title text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "autoSendProfile" AS (
        SELECT user_profile.id, sport
        FROM autosend.autosend
        JOIN user_profile ON user_profile.id = autosend.profile  
        WHERE autosend.id = _id
      ),

      "preferredCoach" AS (
        SELECT coach.id, coach.name, coach.title, coach."collegeId", coach."collegeName"
        FROM autosend.autosend
        JOIN user_profile profiles ON profiles.id = autosend.profile
        JOIN autosend."getAutoSendCoachesFromPreferences"(profiles.id, 
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'states')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'affiliations')),
          ARRAY(SELECT jsonb_array_elements_text(autosend.preferences -> 'divisions'))) coach ON true
        WHERE autosend.id = _id
      ),

      "fallBackCoach" AS (
        SELECT coaches.id, coaches.name, coaches.title, colleges.id, colleges.name
        FROM autosend.autosend
        JOIN profiles ON profiles.id = autosend.profile
        JOIN sports ON sports.sport = profiles.sport
        JOIN coaches ON coaches.sport = sports.id
        JOIN colleges ON colleges.id = sports.college
      )

      SELECT *
      FROM (
        SELECT *
        FROM "preferredCoach"
        ORDER BY random()
        LIMIT 1
      ) PC

      UNION ALL

      SELECT *
      FROM "fallBackCoach"
      WHERE NOT EXISTS (SELECT 1 FROM "preferredCoach")
      LIMIT 1;

  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getAutoSendWeeklyCoaches"(_id uuid, _search text DEFAULT NULL::text, _offset integer DEFAULT 0, "_isAllCoaches" boolean DEFAULT NULL::boolean, "_isToMessage" boolean DEFAULT NULL::boolean, "_isMessaged" boolean DEFAULT NULL::boolean)
 RETURNS TABLE(id uuid, name text, "collegeId" uuid, "collegeName" text, "collegeState" text, "collegeAffiliation" text, "collegeDivision" text, "collegeConference" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT weekly_coaches.id, coaches.name, colleges.id, colleges.name, colleges.state, colleges."athleticAffiliation", colleges."athleticDivision", colleges."athleticConference"
    FROM autosend.autosend
    JOIN autosend.weekly_coaches weekly_coaches ON weekly_coaches.autosend = autosend.id
    JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = weekly_coaches.coach
    JOIN coaches ON coaches.id = autosend_coaches.coach
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE 
      (autosend.id = _id) AND
      (_search IS NULL OR 
        coaches.name ILIKE '%' || _search || '%' OR
        colleges.name ILIKE '%' || _search || '%'
      ) AND
      ("_isToMessage" IS NULL OR weekly_coaches.status = 'Pending') AND
      ("_isMessaged" IS NULL OR weekly_coaches.status = 'Sent')
    OFFSET _offset
    LIMIT 7;  
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getDailyAutoSendCoaches"(_id uuid, "_dailyAmount" integer)
 RETURNS TABLE(id uuid, name text, timezone text, "collegeId" uuid)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT autosend.weekly_coaches.coach, coaches.name, colleges.timezone, colleges.id
    FROM autosend.weekly_coaches
    JOIN autosend.coaches "autoSendCoaches" ON "autoSendCoaches".id = autosend.weekly_coaches.coach
    JOIN coaches ON coaches.id = "autoSendCoaches".coach
    JOIN sports ON sports.id = coaches.sport 
    JOIN colleges ON colleges.id = sports.college
    ORDER BY RANDOM()
    LIMIT ("_dailyAmount");
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getPendingMessage"(_id uuid)
 RETURNS TABLE(id uuid, name text, "userEmail" text, email text, "autoSendCoachId" uuid, "coachId" uuid, "coachName" text, "coachEmail" text, "collegeId" uuid, "collegeName" text)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
    SELECT 
      autosend.profile as "profileId",
      users.name,
      users.email,
      profiles.email,
      autosend_coaches.id,
      coaches.id,
      coaches.name as "coachName",
      coaches.email as "coachEmail",
      colleges.id as "collegeId",
      colleges.name as "collegeName"
    FROM autosend.coaches autosend_coaches
    JOIN autosend.autosend ON autosend.id = autosend_coaches.autosend
    JOIN profiles ON profiles.id = autosend.profile
    JOIN users ON users.id = profiles.user
    JOIN coaches ON coaches.id = autosend_coaches.coach
    JOIN sports ON sports.id = coaches.sport
    JOIN colleges ON colleges.id = sports.college
    WHERE autosend_coaches.id = _id;
  END;
$function$
;

CREATE OR REPLACE FUNCTION autosend."getScheduledEmails"()
 RETURNS TABLE(id uuid, autosend uuid, "isLastScheduledMessage" boolean)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  BEGIN
    RETURN QUERY
      WITH "coachTimezones" AS (
        SELECT autosend_coaches.id as "coachId", colleges.timezone
        FROM autosend.coaches autosend_coaches
        JOIN coaches ON coaches.id = autosend_coaches.coach
        JOIN sports ON sports.id = coaches.sport
        JOIN colleges ON colleges.id = sports.college
      ),
      "allMessages" AS (
        SELECT autosend_coaches.id, autosend_coaches.autosend, autosend.daily_coaches.status, ROW_NUMBER() OVER (
                PARTITION BY autosend_coaches.autosend 
                ORDER BY daily_coaches."scheduledAt" DESC
            ) AS row_num, autosend.daily_coaches."scheduledAt", "coachTimezones".timezone
        FROM autosend.daily_coaches
        JOIN autosend.coaches autosend_coaches ON autosend_coaches.id = daily_coaches.coach
        JOIN "coachTimezones" ON "coachTimezones"."coachId" = autosend_coaches.id 
      ),
      "filteredMessages" AS (
        SELECT "allMessages".id, "allMessages".autosend, "scheduledAt", (row_num = 1) AS "isLastScheduledMessage"
        FROM "allMessages"
        -- JOIN autosend ON autosend.id = "allMessages".autosend
        -- JOIN users ON 
        WHERE status = 'Pending' AND ("scheduledAt" AT TIME ZONE timezone) <= (now() AT TIME ZONE timezone)
      )

      SELECT "filteredMessages".id, "filteredMessages".autosend, "filteredMessages"."isLastScheduledMessage"
      FROM "filteredMessages"
      ORDER BY "isLastScheduledMessage", "scheduledAt" DESC;
  END;
$function$
;

CREATE OR REPLACE VIEW user_profile
WITH (security_invoker = ON) AS
SELECT
  profiles.id,
  users.id as "userId",
  users.name,
  users.status,
  users.image,
  users.email as "userEmail",
  profiles.email,
  profiles.image as "profileImage",
  sport,
  data,
  "isArchived",
  users.gender,
  users."preferredGender"
FROM profiles
JOIN users ON users.id = profiles.user
WHERE CURRENT_USER = 'postgres' OR CURRENT_USER = 'service_role' OR users.id = auth.uid();

[Xyeut]

Just to add, to give more context this is my migration list with the reverted changes to see if that could possibly fix something

and this is right after wehn I attempt to gen types using the cli command you gave me

[avallete]

Hi @Xyeut,

Sadly, I cannot reproduce this on my end. Please open a ticket to supabase support via the dashboard: https://supabase.com/dashboard/support/new

Allowing support access to your project should help debugging this.

One more thing you could try on your end is to check if the error still occurs if you use the --db-url flag instead of the --project-id:

npx supabase@beta gen types typescript \
  --db-url 'postgresql://postgres:<your-password>@db.pjlbzgujomibanvnseyw.supabase.co:5432/postgres' \
  --schema public,messaging,stripe,autosend

[Xyeut]

@avallete Good news,

I don't know what you changed specifically but running gave me a successful generated file for types, I immediately tried to run the old command I've been doing and that still failed.

npx supabase@beta gen types typescript \
  --db-url 'postgresql://postgres:<your-password>@db.pjlbzgujomibanvnseyw.supabase.co:5432/postgres' \
  --schema public,messaging,stripe,autosend

Anything you'd need from me?

[avallete]

Hey @Xyeut happy to know it unlocked your use case.

Nothing has changed, but it seems like the cli have troubles connecting to your database via the project-id to extract the informations it needs while it can if passing the connection string directly. I'll try to reproduce this on my end.