如何在Hasura/PostgreSQL数据库中存储临时数据？——针对入职流程验证码的无残留Schema需求

Great question! Storing temporary verification data without cluttering your core schema is a super common need for onboarding flows—let’s break down the best approaches tailored for Hasura and PostgreSQL:

This is my go-to solution when I want to avoid schema bloat but still keep everything within PostgreSQL. Instead of creating separate tables for email, SMS, or other verification types, use a single table with a type field to distinguish between them.

Step 1: Create the Table #

First, set up a reusable table with all necessary fields, plus an enum to standardize verification types:

-- Create an enum for verification types (extend with 'sms', 'totp', etc. as needed)
CREATE TYPE verification_type AS ENUM ('email');

CREATE TABLE temp_verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id) ON DELETE CASCADE, -- Link to your user table
    type verification_type NOT NULL,
    code VARCHAR(10) NOT NULL, -- Adjust length based on your code format
    expires_at TIMESTAMPTZ NOT NULL, -- Auto-expire logic
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Add indexes to speed up queries and cleanup
CREATE INDEX idx_temp_verifications_user_type ON temp_verifications(user_id, type);
CREATE INDEX idx_temp_verifications_expires_at ON temp_verifications(expires_at);

Step 2: Configure Hasura Permissions #

Keep this table secure by restricting access:

• Insert: Only allow users to insert records where user_id matches their own session ID.
• Select: Only let users query their own records where expires_at > NOW() (so expired codes are invisible).
• Delete: Either let users delete their own records after verification, or automate cleanup.

Step 3: Automate Cleanup #

Use PostgreSQL’s pg_cron extension to auto-delete expired data (no manual work needed):

-- Install pg_cron if you haven't already
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule daily cleanup at 2 AM (adjust timing to your needs)
SELECT cron.schedule('cleanup-temp-verifications', '0 2 * * *', $$
    DELETE FROM temp_verifications WHERE expires_at < NOW();
$$);

Alternatively, use Hasura Event Triggers to delete a record right when it expires—set a delayed trigger on insert that fires at expires_at.

If you want to keep PostgreSQL completely untouched, use Redis for temporary storage (it’s built for short-lived data). Pair it with Hasura Actions to handle code generation and verification.

Step 1: Set Up Redis #

Spin up a Redis instance (local or managed) and configure TTL (time-to-live) for your verification keys—this automatically deletes codes after expiration.

Step 2: Define Hasura Actions #

Create two actions in Hasura:

1. Generate Verification Code: Takes a user ID and email, generates a code, stores it in Redis with a TTL (e.g., 5 minutes), and returns the code to the frontend.
2. Verify Code: Takes a user ID and code, checks Redis for the matching key, and returns a boolean for validity.

Example backend logic (Node.js snippet for the generate action):

const redis = require('redis');
const client = redis.createClient({ url: 'redis://your-redis-instance' });
client.connect();

async function generateEmailCode(req) {
  const { userId, email } = req.input;
  const code = Math.floor(100000 + Math.random() * 900000).toString(); // 6-digit code
  
  // Store code with 5-minute TTL
  await client.setEx(`verification:email:${userId}`, 300, code);
  
  return { success: true, code };
}

If you want to avoid creating a new table entirely, add a JSONB field to your existing users table to store all temporary verification data in one place.

Step 1: Add the JSONB Field #

ALTER TABLE users ADD COLUMN temp_verifications JSONB DEFAULT '{}'::JSONB;

Step 2: Store and Verify Codes #

Use Hasura mutations to update the JSONB field with verification data:

mutation SaveEmailCode($userId: UUID!, $code: String!, $expiresAt: timestamptz!) {
  update_users_by_pk(
    pk_columns: { id: $userId },
    _set: {
      temp_verifications: {
        "email": {
          "code": $code,
          "expires_at": $expiresAt
        }
      }::jsonb
    }
  ) {
    id
  }
}

Step 3: Clean Up Expired Data #

Use pg_cron to remove expired entries from the JSONB field:

SELECT cron.schedule('cleanup-user-verifications', '0 2 * * *', $$
  UPDATE users
  SET temp_verifications = temp_verifications #- '{email}'
  WHERE (temp_verifications->'email'->>'expires_at')::TIMESTAMPTZ < NOW();
$$);

• Universal Temporary Table: Best for most cases—clean, scalable, and fully integrated with Hasura’s permissions and events.
• Redis + Actions: Perfect if you want zero PostgreSQL schema changes or already use Redis for caching.
• JSONB Field: Good for small-scale apps where you want to minimize table count, though querying and cleanup are slightly more complex.

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