基于Rails与PostgreSQL的可追溯匿名调研记录技术方案问询

Let's tackle this tricky balance of semi-traceability and full database anonymity for your Rails/PostgreSQL survey app. I've worked through similar requirements before, so here's a practical, secure approach that checks all your boxes:

Core Approach Overview #

The key is to separate user identity data from anonymous answer data using a one-way, salted hash as an anonymous identifier. This lets you:

• Track which real users received which survey categories (semi-traceability)
• Group all answers from a single user for trend analysis
• Ensure no one can link individual answers back to a specific user (full database anonymity)

Since users can't store private keys on their devices, we'll handle the anonymous ID generation server-side and pass it via survey links in emails—no extra work needed from users.

Database Schema Breakdown #

Here's how to structure your tables to enforce the rules:

1. users Table (Identity Data) #

Stores real user info, completely isolated from answers:

# db/migrate/XXXXXX_create_users.rb
create_table :users do |t|
  t.string :email, null: false, unique: true
  t.timestamps
end

2. survey_templates Table (Survey Metadata) #

Tracks survey categories and their questions:

# db/migrate/XXXXXX_create_survey_templates.rb
create_table :survey_templates do |t|
  t.string :category, null: false # e.g., "Mental Health", "Product Usage"
  t.jsonb :questions, null: false # Stores question text/options
  t.timestamps
end

3. survey_deliveries Table (Traceability Log) #

Logs which real user received which survey—this is your semi-traceability layer:

# db/migrate/XXXXXX_create_survey_deliveries.rb
create_table :survey_deliveries do |t|
  t.references :user, null: false, foreign_key: true
  t.references :survey_template, null: false, foreign_key: true
  t.string :anonymized_user_id, null: false # Salted hash of user's identifier
  t.datetime :sent_at, null: false
  t.timestamps
end

4. answers Table (Anonymous Response Data) #

Stores answers without any link to real user identities:

# db/migrate/XXXXXX_create_answers.rb
create_table :answers do |t|
  t.string :anonymized_user_id, null: false
  t.references :survey_template, null: false, foreign_key: true
  t.integer :question_index, null: false # Maps to question in survey_template.questions
  t.text :content, null: false
  t.datetime :submitted_at, null: false
  t.timestamps
end

# Index for trend analysis: group answers by anonymous user
add_index :answers, :anonymized_user_id

Rails Implementation Steps #

1. Generate the Anonymous User ID #

Create a helper method to generate an irreversible, salted hash. Store the salt in your Rails credentials (never in the database):

# app/helpers/anonymization_helper.rb
module AnonymizationHelper
  def generate_anonymized_id(user)
    # Use a long, random salt stored in config/credentials.yml.enc
    salt = Rails.application.credentials.anonymization_salt
    Digest::SHA256.hexdigest("#{user.email}#{salt}")
  end
end

2. Send Surveys with Anonymous Links #

When sending a survey email, generate the anonymous ID, log the delivery, and include the ID in the survey link:

# app/services/survey_delivery_service.rb
class SurveyDeliveryService
  include AnonymizationHelper

  def initialize(user, survey_template)
    @user = user
    @survey_template = survey_template
  end

  def deliver
    anonymized_id = generate_anonymized_id(@user)
    
    # Log the delivery for traceability
    SurveyDelivery.create!(
      user: @user,
      survey_template: @survey_template,
      anonymized_user_id: anonymized_id,
      sent_at: Time.current
    )

    # Send email with survey link containing the anonymous ID
    SurveyMailer.survey_link(@user, @survey_template, anonymized_id).deliver_now
  end
end

3. Handle Answer Submissions #

Create a controller action that accepts the anonymous ID and saves answers without authenticating the user:

# app/controllers/answers_controller.rb
class AnswersController < ApplicationController
  skip_before_action :authenticate_user! # No user login needed

  def create
    # Optional: Validate that the anonymous ID was actually sent to this survey
    valid_delivery = SurveyDelivery.exists?(
      anonymized_user_id: params[:anonymized_user_id],
      survey_template_id: params[:survey_template_id]
    )

    if valid_delivery
      Answer.create!(answer_params)
      redirect_to survey_thank_you_path
    else
      redirect_to survey_invalid_path
    end
  end

  private

  def answer_params
    params.permit(
      :anonymized_user_id,
      :survey_template_id,
      answers: [] # Adjust based on your question structure
    ).merge(submitted_at: Time.current)
  end
end

Edge Cases & Security Checks #

• User email changes: If users update their email, generate a new anonymous ID? Or create a persistent_anonymized_id column on the users table (generated once on user creation) to keep their answer history linked.
• Salt security: Rotate the salt periodically if needed, but note that old answers will no longer link to new ones unless you maintain a mapping of old/new salts (only store this server-side, not in the database).
• Log filtering: Ensure your application logs don't record the mapping between real user IDs and anonymized IDs—this would break anonymity.
• Database access: Restrict database permissions so that analysts can access the answers and survey_templates tables, but not the users or survey_deliveries tables (unless they need traceability access).

Alternatives if Rails Isn't Your Jam #

This logic translates perfectly to any stack:

• Node.js/Express + PostgreSQL: Use crypto.createHash to generate the salted hash
• Python/Django + PostgreSQL: Use hashlib.sha256 with a salt from environment variables
  The core principle—separating identity data from anonymous response data with a one-way hash—stays the same.

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