Flutter Web应用中Supabase的JSONB类型Upsert失败求助

问题背景 #

开发Flutter Web调研应用，已实现从自托管Supabase获取并展示问题，但保存答案到responses表时无任何错误提示，但Upsert操作完全不生效。

相关表结构 #

CREATE TABLE responses (
    id SERIAL PRIMARY KEY,
    respondent_id UUID REFERENCES respondents(id) NOT NULL,
    assessment_cycle_id INT REFERENCES assessment_cycle(id) NOT NULL,
    response_date DATE,
    answers JSONB,
    finished BOOLEAN
);

answers字段存储<int, int>键值对（问题ID -> 评分答案）。

数据模型代码 #

class Response {
  final int? id;
  final String respondentId;
  final int assessmentCycleId;
  final DateTime responseDate;
  final Map<int, dynamic> answers;
  final bool? finished;

  Response({
    this.id,
    required this.respondentId,
    required this.assessmentCycleId,
    required this.responseDate,
    required this.answers,
    this.finished,
  });

  factory Response.fromJson(Map<String, dynamic> json) {
    return Response(
      id: json['id'],
      respondentId: json['respondent_id'],
      assessmentCycleId: json['assessment_cycle_id'],
      responseDate: DateTime.parse(json['response_date']),
      answers: Map<int, dynamic>.from(json['answers']),
      finished: json['finished'],
    );
  }

  Map<String, dynamic> toJson() {
    return {
      'id': id,
      'respondent_id': respondentId,
      'assessment_cycle_id': assessmentCycleId,
      'response_date': responseDate.toIso8601String(),
      'answers': answers,
      'finished': finished,
    };
  }
}

仓库层Upsert代码 #

class ResponseRepository {
  final supabase = Supabase.instance.client;

  Future<void> upsertResponse(Response res) async {
    try {
      print(
          'Entering upsert ${res.respondentId} ${res.assessmentCycleId} ${res.answers}');

      final checkIfExist = await supabase
          .from('responses')
          .select()
          .eq('respondent_id', res.respondentId)
          .eq('assessment_cycle_id', res.assessmentCycleId);

      if (checkIfExist.isEmpty) {
        await supabase.from('responses').insert({
          'respondent_id': res.respondentId,
          'assessment_cycle_id': res.assessmentCycleId,
          'response_date': res.responseDate.toIso8601String().split('T')[0],
          'answers': res.answers,
        });
      } else {
        await supabase.from('responses').update({
          'response_date': res.responseDate.toIso8601String().split('T')[0],
          'answers': res.answers
        }).eq('id', checkIfExist[0]['id']);
      }
      /*
      final response = await supabase.from('responses').upsert({
        'respondent_id': res.respondentId,
        'assessment_cycle_id': res.assessmentCycleId,
        'response_date': res.responseDate.toIso8601String().split('T')[0],
        'answers': res.answers,
      }, onConflict: 'respondent_id,assessment_cycle_id');
     */
    } catch (error) {
      throw Exception('Error updating answers: $error');
    }
  }
}

组件调用代码片段 #

class _SurveyPageState extends ConsumerState<SurveyPage> {
  final assessmentCycleId = 1;
  final ResponseRepository _responseRepository = ResponseRepository();
  final Map<int, dynamic> _answers = {};

  Future<void> _fetchInitialAnswer(String respondentId) async {
    print('Fetching Initial Answer');
    try {
      Response? initialAnswer = await _responseRepository.getResponse(
          respondentId, assessmentCycleId);
      if (initialAnswer != null) {
        _answers.clear();
        _answers.addAll(initialAnswer.answers);
      }
    } catch (error) {
      const SnackBar(
        content: Text('Unexpected Error in fetching saved answers'),
      );
    }
  }

  Future<void> saveAnswers() async {
    try {
      Response res = Response(
          respondentId: respondentId,
          assessmentCycleId: assessmentCycleId,
          responseDate: DateTime.now(),
          answers: _answers);

      await _responseRepository.upsertResponse(res);
    } catch (error) {
      const SnackBar(
        content: Text('Unexpected Error in saving answers'),
      );
    }
  }

  // ... 其他组件代码
}

修复步骤 #

1. 检查并配置Supabase行级安全（RLS）策略 #

自托管Supabase默认会对新表启用RLS，若没有配置允许插入/更新的策略，请求会静默失败（无错误提示）。

临时验证方案

先关闭responses表的RLS，测试是否能正常保存：

ALTER TABLE responses DISABLE ROW LEVEL SECURITY;

若能正常保存，说明是RLS的问题，需要添加正确的策略：

-- 允许用户插入自己的响应
CREATE POLICY "Allow respondents to insert their own responses"
ON responses FOR INSERT
WITH CHECK (auth.uid() = respondent_id);

-- 允许用户更新自己的响应
CREATE POLICY "Allow respondents to update their own responses"
ON responses FOR UPDATE
USING (auth.uid() = respondent_id);

注：若respondent_id不是当前用户的UUID，需根据实际业务调整策略逻辑。

2. 修复JSONB字段的序列化问题 #

Flutter中Map<int, dynamic>序列化为JSON时，int类型的键会被自动转为字符串，但为了避免潜在兼容问题，建议显式转换：

修改仓库层的upsert代码，将answers转换为Map<String, dynamic>：

// 插入时
'answers': res.answers.map((key, value) => MapEntry(key.toString(), value)),

// 更新时同理
'answers': res.answers.map((key, value) => MapEntry(key.toString(), value)),

或者修改Response模型的toJson方法：

Map<String, dynamic> toJson() {
  return {
    'id': id,
    'respondent_id': respondentId,
    'assessment_cycle_id': assessmentCycleId,
    'response_date': responseDate.toIso8601String().split('T')[0], // 直接生成DATE格式字符串
    'answers': answers.map((key, value) => MapEntry(key.toString(), value)),
    'finished': finished,
  };
}

3. 启用原生Upsert并添加联合唯一约束 #

原注释中的Upsert方法无效，核心原因是respondent_id和assessment_cycle_id没有设置联合唯一约束，Supabase无法识别冲突条件。

第一步：添加联合唯一约束

执行SQL：

ALTER TABLE responses ADD CONSTRAINT unique_respondent_cycle UNIQUE (respondent_id, assessment_cycle_id);

第二步：修复仓库层的Upsert代码

替换原有的查询+判断逻辑，使用原生Upsert：

Future<void> upsertResponse(Response res) async {
  try {
    print('Entering upsert ${res.respondentId} ${res.assessmentCycleId} ${res.answers}');
    
    await supabase.from('responses').upsert(
      {
        'respondent_id': res.respondentId,
        'assessment_cycle_id': res.assessmentCycleId,
        'response_date': res.responseDate.toIso8601String().split('T')[0],
        'answers': res.answers.map((key, value) => MapEntry(key.toString(), value)),
      },
      onConflict: 'respondent_id,assessment_cycle_id',
      update: { // 明确指定更新字段，避免覆盖其他字段
        'response_date': res.responseDate.toIso8601String().split('T')[0],
        'answers': res.answers.map((key, value) => MapEntry(key.toString(), value)),
      },
    );
  } catch (error) {
    print('Upsert error: $error'); // 打印具体错误
    throw Exception('Error updating answers: $error');
  }
}

原生Upsert避免了先查询再操作的竞态条件，效率更高。

4. 增强错误处理与日志 #

原组件中的错误处理没有显示SnackBar，且没有打印具体错误，导致无法定位问题：

修改saveAnswers方法：

Future<void> saveAnswers() async {
  try {
    Response res = Response(
        respondentId: respondentId,
        assessmentCycleId: assessmentCycleId,
        responseDate: DateTime.now(),
        answers: _answers);

    await _responseRepository.upsertResponse(res);
    // 可选：保存成功提示
    if (mounted) {
      ScaffoldMessenger.of(context).showSnackBar(
        const SnackBar(content: Text('答案已保存')),
      );
    }
  } catch (error) {
    print('Save answers error: $error');
    if (mounted) {
      ScaffoldMessenger.of(context).showSnackBar(
        SnackBar(content: Text('保存失败: ${error.toString()}')),
      );
    }
  }
}

同时在仓库层的catch块中打印错误，方便调试。

5. 验证DateTime格式 #

确保response_date是正确的DATE格式（YYYY-MM-DD），原代码中的split('T')[0]是正确的，也可以直接使用DateFormat('yyyy-MM-dd').format(res.responseDate)（需要导入intl包）更直观。

内容的提问来源于stack exchange，提问作者Sheenergizer