1. Contract → 2. Migration → 3. RLS (if new table) → 4. Repository → 5. Tests → 6. Verify

- <domain>.<action>(params) -> Returns description. Guards:
  - guard 1
  - guard 2

----------------------------------------------------------------------
-- RPC: <domain>_<action>
----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.<domain>_<action>(
  p_param_one   uuid,
  p_param_two   text DEFAULT NULL
)
RETURNS <return_type>
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
  v_user_id uuid := auth.uid();
  v_row     public.<table_name>;
BEGIN
  -- 1. Authentication check
  PERFORM public._assert_authenticated();

  -- 2. Membership/authorization check
  PERFORM public._assert_home_member(p_home_id);

  -- 3. Input validation
  IF COALESCE(btrim(p_param_two), '') = '' THEN
    PERFORM public.api_error(
      'INVALID_INPUT',
      'Field is required.',
      '22023',
      jsonb_build_object('field', 'param_two')
    );
  END IF;

  -- 4. Business logic guards (use api_assert for boolean checks)
  PERFORM public.api_assert(
    EXISTS (SELECT 1 FROM ... WHERE ...),
    'ERROR_CODE',
    'Human-readable message.',
    '42501',
    jsonb_build_object('key', 'value')
  );

  -- 5. Paywall/quota check (if applicable)
  PERFORM public._home_assert_quota(
    p_home_id,
    jsonb_build_object('active_items', 1)
  );

  -- 6. DML operation
  INSERT INTO public.<table> (...)
  VALUES (...)
  RETURNING * INTO v_row;

  -- 7. Usage tracking (if applicable)
  PERFORM public._home_usage_apply_delta(
    p_home_id,
    jsonb_build_object('active_items', 1)
  );

  RETURN v_row;
END;
$$;

ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;

-- SELECT: members can read their home's data
CREATE POLICY "<table>_select_member"
ON public.<table>
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.memberships m
    WHERE m.home_id = <table>.home_id
      AND m.user_id = auth.uid()
      AND m.is_current = TRUE
  )
);

-- INSERT/UPDATE/DELETE: typically via RPC only (SECURITY DEFINER)
-- Add policies only if direct access is needed

abstract class <Domain>Repository {
  Future<<ReturnType>> <action>({
    required String paramOne,
    String? paramTwo,
  });
}

import 'package:kinly/contracts/<domain>/models.dart';
import 'package:kinly/core/supabase/supabase_error_mapper.dart';
import 'package:supabase_flutter/supabase_flutter.dart';

class Supabase<Domain>Repository implements <Domain>Repository {
  Supabase<Domain>Repository({SupabaseClient? client})
    : _client = client ?? Supabase.instance.client;

  final SupabaseClient _client;

  @override
  Future<<ReturnType>> <action>({
    required String paramOne,
    String? paramTwo,
  }) async {
    try {
      final response = await _client.rpc(
        '<domain>_<action>',
        params: {
          'p_param_one': paramOne,
          if (paramTwo != null) 'p_param_two': paramTwo,
        },
      );
      return <ReturnType>.fromJson(
        (response as Map).cast<String, dynamic>(),
      );
    } catch (error) {
      throw SupabaseErrorMapper.map<Action>(error);
    }
  }
}

static Exception map<Action>(Object error) {
  final code = _extractCode(error);
  return switch (code) {
    'INVALID_INPUT' => InvalidInputException(_extractMessage(error)),
    'NOT_HOME_MEMBER' => NotMemberException(),
    _ => UnknownException(error.toString()),
  };
}

SET search_path = pgtap, public, auth, extensions;

BEGIN;

SELECT no_plan();

-- ============================================================
-- FIXTURES
-- ============================================================

CREATE TEMP TABLE tmp_users (
  label   text PRIMARY KEY,
  user_id uuid,
  email   text
);

CREATE TEMP TABLE tmp_homes (
  label   text PRIMARY KEY,
  home_id uuid
);

CREATE TEMP TABLE tmp_invites (
  label text PRIMARY KEY,
  code  text
);

-- Error assertion helper
CREATE OR REPLACE FUNCTION pg_temp.expect_api_error(
  p_sql         text,
  p_error_code  text,
  p_description text
)
RETURNS text
LANGUAGE sql
AS $$
  SELECT throws_like(
    p_sql,
    '%' || p_error_code || '%',
    p_description
  );
$$;

-- Seed starter avatars (required by handle_new_user trigger)
INSERT INTO public.avatars (id, storage_path, category, name)
VALUES ('00000000-0000-4000-8000-000000000001', 'avatars/default.png', 'animal', 'Test Avatar')
ON CONFLICT (id) DO NOTHING;

-- Seed test users
INSERT INTO tmp_users (label, user_id, email) VALUES
  ('owner',    '10000000-0000-4000-9000-000000000001', 'owner@test.com'),
  ('member',   '10000000-0000-4000-9000-000000000002', 'member@test.com'),
  ('outsider', '10000000-0000-4000-9000-000000000003', 'outsider@test.com');

INSERT INTO auth.users (id, instance_id, email, raw_user_meta_data, raw_app_meta_data, aud, role, encrypted_password)
SELECT user_id, '00000000-0000-0000-0000-000000000000'::uuid, email,
       '{}'::jsonb, '{"provider":"email"}'::jsonb, 'authenticated', 'authenticated', 'secret'
FROM tmp_users
ON CONFLICT (id) DO NOTHING;

-- ============================================================
-- SETUP: Create home + members
-- ============================================================

SELECT set_config('request.jwt.claim.sub', (SELECT user_id::text FROM tmp_users WHERE label = 'owner'), true);
SELECT set_config('request.jwt.claim.role', 'authenticated', true);

WITH res AS (SELECT public.homes_create_with_invite() AS payload)
INSERT INTO tmp_homes (label, home_id)
SELECT 'primary', (payload->'home'->>'id')::uuid FROM res;

INSERT INTO tmp_invites (label, code)
SELECT 'primary', code::text
FROM public.invites
WHERE home_id = (SELECT home_id FROM tmp_homes WHERE label = 'primary')
  AND revoked_at IS NULL
LIMIT 1;

-- Member joins
SELECT set_config('request.jwt.claim.sub', (SELECT user_id::text FROM tmp_users WHERE label = 'member'), true);
SELECT public.homes_join((SELECT code FROM tmp_invites WHERE label = 'primary'));

-- ============================================================
-- TESTS
-- ============================================================

-- Test: Happy path
SELECT set_config('request.jwt.claim.sub', (SELECT user_id::text FROM tmp_users WHERE label = 'owner'), true);
SELECT is(
  (SELECT public.<domain>_<action>(
    (SELECT home_id FROM tmp_homes WHERE label = 'primary'),
    'test value'
  )).id IS NOT NULL,
  true,
  'Owner can call <action> successfully'
);

-- Test: Non-member denied
SELECT set_config('request.jwt.claim.sub', (SELECT user_id::text FROM tmp_users WHERE label = 'outsider'), true);
SELECT pg_temp.expect_api_error(
  format($sql$
    SELECT public.<domain>_<action>('%s', 'test');
  $sql$, (SELECT home_id FROM tmp_homes WHERE label = 'primary')),
  'NOT_HOME_MEMBER',
  'Non-member cannot call <action>'
);

-- Test: Invalid input
SELECT set_config('request.jwt.claim.sub', (SELECT user_id::text FROM tmp_users WHERE label = 'owner'), true);
SELECT pg_temp.expect_api_error(
  format($sql$
    SELECT public.<domain>_<action>('%s', '');
  $sql$, (SELECT home_id FROM tmp_homes WHERE label = 'primary')),
  'INVALID_INPUT',
  'Empty input rejected'
);

-- ============================================================
-- CLEANUP
-- ============================================================

SELECT * FROM finish();

ROLLBACK;

# Run all checks
dart run tool/check_all.dart

# Run specific test
supabase test db --linked -- supabase/tests/<domain>_<feature>.sql

# Or run all DB tests
supabase test db --linked