Function resolvePreferredOrganization

server/src/db/users-db.ts:205–218

(workosUserId: string)

 * Returns null if the user has no memberships.
 */
export async function resolvePreferredOrganization(workosUserId: string): Promise<string | null> {
  const result = await query<{ workos_organization_id: string }>(
    `SELECT om.workos_organization_id
     FROM organization_memberships om
     JOIN organizations o ON o.workos_organization_id = om.workos_organization_id
     WHERE om.workos_user_id = $1
     ORDER BY
       CASE WHEN o.subscription_status = 'active' THEN 0 ELSE 1 END,
       om.created_at DESC
     LIMIT 1`,
    [workosUserId]
  );
  return result.rows[0]?.workos_organization_id ?? null;
}

/**
 * Backfill primary_organization_id for a user if not already set.