Step 1 : Domain Layer ( Create new entity )

internal/domain/refresh_token.go

This is stateful auth. DB owns refresh_tokens

package domain

import "time"

type RefreshToken struct {
	ID        string    `db:"id"`
	UserID    string    `db:"user_id"`
	TokenHash string    `db:"token_hash"`
	ExpiresAt time.Time `db:"expires_at"`
	Revoked   bool      `db:"revoked"`
	CreatedAt time.Time `db:"created_at"`
}

Step 2 : Database Migration

2.1 Create migration

migrate create -ext sql -dir migrations create_refresh_tokens_table

2.2 up.sql

CREATE TABLE refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT now() -- if user is deleted, all their refresh_tokens are automatically deleted
);

CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id);
CREATE UNIQUE INDEX idx_refresh_tokens_hash ON refresh_tokens(token_hash);

CREATE INDEX idx_refresh_tokens_user_id ON refresh_tokens(user_id)

What this does

• Creates a B-tree index on user_id
• Speeds up queries like:

SELECT *FROM refresh_tokens WHERE user_id = $1;
DELETE FROM refresh_tokens WHERE user_id = $1;

Without index:

• PostgreSQL scans the entire table

With index:

• PostgreSQL jumps directly to matching rows

Essential for scalable auth systems.