How to implement User Authentication in a locally hosted Supabase for a a Flutter Web App beside the standard Supabase Auth?

Question

As I am new to both Flutter and Supabase, I would like to ask how to implement a user authentication beside the standard Supabase auth. I am working on a Survey Web app project in Flutter that will have an Web Admin page and a Survey App (both Web and Mobile App) which will both connect to the same Supabase database. I will be reserving the standard Supabase auth for the Web Admin app. In the (web) Survey app, the respondents will login given their household number and assigned PIN. The table/schema is like this:

-- Table for Respondents
CREATE TABLE respondents (
    id UUID DEFAULT gen_random_uuid(),
    household_number TEXT NOT NULL UNIQUE,
    pin_password TEXT NOT NULL,
    pin_expiry DATE NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    ...
    barangay_id INT REFERENCES barangays(id),
    purok_id INT REFERENCES puroks(id),
    PRIMARY KEY (id)
);

How should I check if the credentials are correct and redirect it to the survey page with a session or user details? Here is my existing code so far:

main.dart

import 'package:flutter/material.dart';
import 'package:flutter_dotenv/flutter_dotenv.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import 'package:surveyweb/pages/login_page.dart';

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  // load env
  await dotenv.load();
  // initialiaze Supabase
  String supabaseUrl = dotenv.env['SUPABASE_URL'] ?? '';
  String supabaseKey = dotenv.env['SUPABASE_KEY'] ?? '';
  await Supabase.initialize(url: supabaseUrl, anonKey: supabaseKey);

  runApp(const WebSurvey());
}

class WebSurvey extends StatelessWidget {
  const WebSurvey({super.key});

  // This widget is the root of your application.
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'XXXX',
      theme: ThemeData(
        colorScheme: ColorScheme.fromSeed(seedColor: Colors.deepPurple),
        useMaterial3: true,
      ),
      home: const LoginPage(),
    );
  }
}

Here is the login_page that I am having an issue with. How can I check the credentials and how to handle if it returns null or an error? login_page.dart

import 'package:flutter/material.dart';
import 'package:supabase_flutter/supabase_flutter.dart';

class LoginPage extends StatefulWidget {
  const LoginPage({super.key});

  @override
  // ignore: library_private_types_in_public_api
  State<LoginPage> createState() => _LoginPageState();
}

class _LoginPageState extends State<LoginPage> {
  final _householdNumberController = TextEditingController();
  final _pinPasswordController = TextEditingController();

  @override
  void dispose() {
    _householdNumberController.dispose();
    _pinPasswordController.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return Center(
      child: Card(
        margin: const EdgeInsets.all(30.0),
        child: SizedBox(
          width: 300,
          child: Padding(
            padding: const EdgeInsets.all(16.0),
            child: Column(
              mainAxisSize: MainAxisSize.min,
              children: <Widget>[
                TextField(
                  controller: _householdNumberController,
                  decoration:
                      const InputDecoration(labelText: 'Household Number'),
                ),
                const SizedBox(height: 10),
                TextField(
                  controller: _pinPasswordController,
                  decoration: const InputDecoration(labelText: 'PIN'),
                  obscureText: true,
                ),
                const SizedBox(height: 20),
                ElevatedButton(
                  onPressed: () async {
                    final supabase = Supabase.instance.client;
                    final response = await supabase
                        .from('respondents')
                        .select()
                        .eq('household_number', _householdNumberController.text)
                        .eq('pin_password', _pinPasswordController.text)
                        .single();
                    // Need to modify this part and/or the above query
                    if (response.isNotEmpty) {
                      print('Ok');
                    } else {
                      print('Not ok');
                    }
                  },
                  child: const Text('Login'),
                ),
              ],
            ),
          ),
        ),
      ),
    );
  }
}

I have been using Microsoft Bing/ChatGPT but its answers are incorrect or deprecated. I will be creating a Class Respondents for the data model but I am still figuring how to do the authentication especially if the the returned result is null or error.