2.6 Wallet Addresses

Overview

The system automatically creates Ethereum-compatible wallet addresses for users upon profile creation. The wallet creation process is handled securely through a combination of database triggers, edge functions, and encrypted storage.

Architecture Components

Database Tables

Profile Table

Stores user profiles and their associated wallet addresses:

create table public.profile (
    id uuid not null,
    wallet_address text null,
    email text null,
    token_balance double precision null default 0,
    total_contribution_score double precision null default 0,
    file_count integer null default 0,
    created_at timestamp with time zone null default current_timestamp,
    updated_at timestamp with time zone null default current_timestamp,
    constraint profiles_pkey primary key (id),
    constraint profiles_email_key unique (email),
    constraint profiles_wallet_address_key unique (wallet_address),
    constraint profiles_id_fkey foreign key (id) references auth.users (id)
)

User Wallet Table

Securely stores wallet information with encrypted private keys:

create table public.user_wallet (
    id text not null,  -- wallet address
    user_id uuid not null references auth.users(id),
    private_key text null,  -- Encrypted private key
    is_primary boolean default true,
    created_at timestamp with time zone default current_timestamp,
    updated_at timestamp with time zone default current_timestamp,
    constraint user_wallet_pkey primary key (id, user_id)
)

Primary Wallet Management

The system supports multiple wallets per user with a primary wallet designation. A trigger automatically manages the primary wallet and keeps the profile’s wallet address in sync:

CREATE OR REPLACE FUNCTION update_profile_wallet_address()
RETURNS TRIGGER AS $$
BEGIN
    -- Only proceed if the new wallet is marked as primary
    IF NEW.is_primary THEN
        -- First, set all other wallets for this user to non-primary
        UPDATE user_wallet
        SET is_primary = false
        WHERE user_id = NEW.user_id 
        AND id != NEW.id;  -- Don't update the newly inserted wallet
        
        -- Then update the profile's wallet address
        UPDATE profile
        SET wallet_address = NEW.id
        WHERE user_id = NEW.user_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Trigger to manage primary wallet designation
CREATE TRIGGER after_wallet_insert
    AFTER INSERT
    ON user_wallet
    FOR EACH ROW
    EXECUTE FUNCTION update_profile_wallet_address();

This trigger ensures:

Only one wallet can be primary per user
When a new primary wallet is created, all other wallets are set to non-primary
The profile’s wallet address always reflects the current primary wallet
Automatic synchronization between wallet management and profile data

Security Features

Webhook Security
- Edge function protected by webhook secret
- Secret stored in Supabase Vault
- All requests validated against secret
Database Triggers
- Automatic timestamp updates
- Automated wallet creation on profile insert

Wallet Creation Flow

User profile is created
Database trigger handle_wallet_creation() is activated
Trigger makes secure HTTP call to edge function
Edge function validates webhook secret
New wallet is created using viem
Wallet details stored in database with encrypted private key
Profile updated with wallet address

Implementation Details

Database Trigger Function

create or replace function handle_wallet_creation()
returns trigger as $$
begin
    perform net.http_post(
        url := vault.get_secret('edge_function_url') || '/create-wallet',
        headers := jsonb_build_object(
            'Authorization', 'Bearer ' || vault.get_secret('webhook_secret'),
            'Content-Type', 'application/json'
        ),
        body := jsonb_build_object(
            'user_id', NEW.id,
            'email', NEW.email
        )
    );
    return NEW;
end;
$$ language plpgsql security definer;

Edge Function

Located at supabase/functions/create-wallet/index.ts, the edge function:

Validates incoming requests
Generates new Ethereum wallets
Handles database updates
Returns wallet addresses
Implements proper error handling

Required Environment Variables

WEBHOOK_SECRET=<secret_from_vault>
SUPABASE_URL=<project_url>
SUPABASE_SERVICE_ROLE_KEY=<service_role_key>

Development Setup

Enable Required Extensions

create extension if not exists pg_net;
create extension if not exists pgsodium;
create extension if not exists vault;

Create Development Secrets

select vault.create_secret(
  'webhook_secret',
  encode(gen_random_bytes(32), 'hex')
);
 
select vault.create_secret(
  'edge_function_url',
  'http://localhost:54321/functions/v1'
);

Deploy Edge Function

supabase functions deploy create-wallet --no-verify-jwt

Production Considerations

Update edge_function_url in vault to production URL
Generate new webhook secret for production
Set environment variables in Supabase dashboard
Ensure proper backup procedures for encrypted wallet data
Monitor wallet creation events and errors
Implement proper key rotation procedures

Security Recommendations

Regular rotation of webhook secrets
Monitoring of failed wallet creation attempts
Regular audit of database access logs
Backup procedures for encrypted wallet data
Proper handling of private keys in development environments
Regular updates of viem and other dependencies
Encrypt private key column

Fromo Docs

Explorer