Course outline

01 · Phase 1 — Project Foundation16/16

02 · Phase 2 — Organization Service20/20

03 · Phase 3 — User Service10/10

04 · Phase 4 — Authentication Service10/10

05 · Phase 5 — API Gateway9/9

06 · Phase 6 — Flat Service6/6

07 · Phase 7 — Resident Service7/7

08 · Phase 8 — Maintenance Service7/7

09 · Phase 9 — Payment Service7/7

10 · Phase 10 — Complaint Service7/7

11 · Phase 11 — Notice Service7/7

12 · Phase 12 — Visitor Service6/6

13 · Phase 13 — Staff Service6/6

14 · Phase 14 — Notification Service7/7

15 · Phase 15 — Redis Integration6/6

16 · Phase 16 — Kafka Integration8/8

17 · Phase 17 — Observability7/7

18 · Phase 18 — Performance Engineering6/6

19 · Phase 19 — Production Deployment11/11

20 · Phase 20 — Production Readiness8/8

Stage 7 · Master

Phase 12 — Visitor Service

History

Write a real visit audit trail and make single-use redemption race-safe so two guard tablets scanning one QR code cannot both admit the same person.

14 min readHOA Backend Engineering CourseMaster

Why visit history is a security record and not just analytics

The history table exists for the same reason badge systems keep logs: after an incident, somebody will need to answer who entered, when they entered, and which guard processed them. That is not optional reporting. It is operational evidence. The record therefore belongs in a dedicated visitor_visits table with check-in and optional check-out timestamps, not only in ephemeral application logs that roll off disk or disappear behind sampling.

Tenant isolation matters here too. A guard or admin listing visits for one society must never be able to query another society's visit history by invitation ID alone. Every audit query stays scoped by org_id, even when the primary business lookup is invitation_id or flat_id.

Terminal — create the visit-history migration

$mkdir -p services/visitor-service/db/migrations

$touch services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

Create services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql before adding the code

$mkdir -p services/visitor-service/db/migrations && touch services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

CREATE TABLE visitor_visits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL,
    invitation_id UUID NOT NULL REFERENCES visitor_invitations(id),
    checked_in_at TIMESTAMPTZ NOT NULL,
    checked_in_by_guard UUID NOT NULL,
    checked_out_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT visitor_visits_single_checkin_key UNIQUE (invitation_id),
    CONSTRAINT visitor_visits_checkout_after_checkin_chk CHECK (
        checked_out_at IS NULL OR checked_out_at >= checked_in_at
    )
);

CREATE INDEX visitor_visits_org_checked_in_idx
    ON visitor_visits (org_id, checked_in_at DESC);

CREATE INDEX visitor_visits_org_invitation_idx
    ON visitor_visits (org_id, invitation_id);

The unique constraint on invitation_id reflects the product rule: one invitation can produce at most one successful check-in.

Terminal — create the visit-history rollback migration

$mkdir -p services/visitor-service/db/migrations

$touch services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

Create services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql before adding the code

$mkdir -p services/visitor-service/db/migrations && touch services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

DROP TABLE IF EXISTS visitor_visits;

History rolls back independently of invitations and approvals because the tables were introduced additively in order.

How atomic redemption kills the two-tablet race

The critical security bug in visitor systems is not obvious invalid tokens; it is check-then-act logic under concurrency. If tablet A SELECTs used=false, tablet B SELECTs used=false a millisecond later, and both then run UPDATE used=true, the system just admitted the same visitor twice. That is a classic time-of-check-to-time-of-use bug. The safe version collapses the check and the state transition into one atomic UPDATE ... WHERE used=false RETURNING statement.

Pattern	Read step	Write step	Result under simultaneous scans
Naive SELECT then UPDATE	Two guards can both observe used = false	Both updates succeed because the decision was already made outside the write	Double admission is possible
Atomic UPDATE WHERE used = false RETURNING	No separate read decides the winner	Only one transaction can flip the row from false to true	Exactly one admission wins and the rest see replay

Two guards scanning the same QR code

Rendering diagram…

Terminal — create the visit-history service file

$mkdir -p services/visitor-service/internal/visitor

$touch services/visitor-service/internal/visitor/history.go

Create services/visitor-service/internal/visitor/history.go before adding the code

$mkdir -p services/visitor-service/internal/visitor && touch services/visitor-service/internal/visitor/history.go

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/internal/visitor/history.go

package visitor

import (
    "context"
    "errors"
    "time"

    "github.com/google/uuid"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type VisitRecord struct {
    ID               uuid.UUID
    OrgID            uuid.UUID
    InvitationID     uuid.UUID
    CheckedInAt      time.Time
    CheckedInByGuard uuid.UUID
    CheckedOutAt     *time.Time
}

type invitationLookup struct {
    ID          uuid.UUID
    OrgID       uuid.UUID
    FlatID      uuid.UUID
    HostUserID  uuid.UUID
    Used        bool
    ValidUntil  time.Time
    DatabaseNow time.Time
}

var (
    ErrInvitationTokenNotFound = errors.New("visitor: invitation token not found")
    ErrInvitationExpired       = errors.New("visitor: invitation expired")
    ErrInvitationAlreadyUsed   = errors.New("visitor: invitation already used")
)

type HistoryRepository struct {
    pool *pgxpool.Pool
}

func NewHistoryRepository(pool *pgxpool.Pool) *HistoryRepository {
    return &HistoryRepository{pool: pool}
}

type HistoryService struct {
    repo *HistoryRepository
}

func NewHistoryService(repo *HistoryRepository) *HistoryService {
    return &HistoryService{repo: repo}
}

func (s *HistoryService) CheckInByToken(ctx context.Context, orgID, guardUserID uuid.UUID, rawToken string) (VisitRecord, error) {
    invitation, err := s.repo.FindInvitationByToken(ctx, orgID, hashToken(rawToken))
    if err != nil {
        return VisitRecord{}, err
    }
    if !invitation.ValidUntil.After(invitation.DatabaseNow) {
        return VisitRecord{}, ErrInvitationExpired
    }
    if invitation.Used {
        return VisitRecord{}, ErrInvitationAlreadyUsed
    }

    visit, err := s.repo.ClaimInvitationAndInsertVisit(ctx, orgID, invitation.ID, guardUserID)
    if errors.Is(err, pgx.ErrNoRows) {
        current, stateErr := s.repo.FindInvitationByID(ctx, orgID, invitation.ID)
        if stateErr != nil {
            return VisitRecord{}, stateErr
        }
        if !current.ValidUntil.After(current.DatabaseNow) {
            return VisitRecord{}, ErrInvitationExpired
        }
        return VisitRecord{}, ErrInvitationAlreadyUsed
    }
    return visit, err
}

func (s *HistoryService) CheckOut(ctx context.Context, orgID, invitationID uuid.UUID) (VisitRecord, error) {
    return s.repo.MarkCheckedOut(ctx, orgID, invitationID)
}

func (s *HistoryService) ListByFlat(ctx context.Context, orgID, flatID uuid.UUID, limit int) ([]VisitRecord, error) {
    if limit <= 0 || limit > 100 {
        limit = 20
    }
    return s.repo.ListByFlat(ctx, orgID, flatID, limit)
}

func (r *HistoryRepository) FindInvitationByToken(ctx context.Context, orgID uuid.UUID, tokenHash string) (invitationLookup, error) {
    var invitation invitationLookup
    err := r.pool.QueryRow(
        ctx,
        "SELECT id, org_id, flat_id, host_user_id, used, valid_until, now() "+
            "FROM visitor_invitations WHERE org_id = $1 AND token_hash = $2",
        orgID,
        tokenHash,
    ).Scan(
        &invitation.ID,
        &invitation.OrgID,
        &invitation.FlatID,
        &invitation.HostUserID,
        &invitation.Used,
        &invitation.ValidUntil,
        &invitation.DatabaseNow,
    )
    if errors.Is(err, pgx.ErrNoRows) {
        return invitationLookup{}, ErrInvitationTokenNotFound
    }
    return invitation, err
}

func (r *HistoryRepository) FindInvitationByID(ctx context.Context, orgID, invitationID uuid.UUID) (invitationLookup, error) {
    var invitation invitationLookup
    err := r.pool.QueryRow(
        ctx,
        "SELECT id, org_id, flat_id, host_user_id, used, valid_until, now() "+
            "FROM visitor_invitations WHERE org_id = $1 AND id = $2",
        orgID,
        invitationID,
    ).Scan(
        &invitation.ID,
        &invitation.OrgID,
        &invitation.FlatID,
        &invitation.HostUserID,
        &invitation.Used,
        &invitation.ValidUntil,
        &invitation.DatabaseNow,
    )
    if errors.Is(err, pgx.ErrNoRows) {
        return invitationLookup{}, ErrInvitationTokenNotFound
    }
    return invitation, err
}

func (r *HistoryRepository) ClaimInvitationAndInsertVisit(ctx context.Context, orgID, invitationID, guardUserID uuid.UUID) (VisitRecord, error) {
    tx, err := r.pool.BeginTx(ctx, pgx.TxOptions{})
    if err != nil {
        return VisitRecord{}, err
    }
    defer tx.Rollback(ctx)

    var visit VisitRecord
    err = tx.QueryRow(
        ctx,
        "WITH claimed AS ("+
            "UPDATE visitor_invitations "+
            "SET used = TRUE, used_at = now() "+
            "WHERE org_id = $1 AND id = $2 AND used = FALSE AND valid_until > now() "+
            "RETURNING id, org_id, used_at"+
            ") "+
            "INSERT INTO visitor_visits (org_id, invitation_id, checked_in_at, checked_in_by_guard) "+
            "SELECT org_id, id, used_at, $3 FROM claimed "+
            "RETURNING id, org_id, invitation_id, checked_in_at, checked_in_by_guard, checked_out_at",
        orgID,
        invitationID,
        guardUserID,
    ).Scan(
        &visit.ID,
        &visit.OrgID,
        &visit.InvitationID,
        &visit.CheckedInAt,
        &visit.CheckedInByGuard,
        &visit.CheckedOutAt,
    )
    if err != nil {
        return VisitRecord{}, err
    }

    if err := tx.Commit(ctx); err != nil {
        return VisitRecord{}, err
    }
    return visit, nil
}

func (r *HistoryRepository) MarkCheckedOut(ctx context.Context, orgID, invitationID uuid.UUID) (VisitRecord, error) {
    var visit VisitRecord
    err := r.pool.QueryRow(
        ctx,
        "UPDATE visitor_visits SET checked_out_at = now() "+
            "WHERE org_id = $1 AND invitation_id = $2 AND checked_out_at IS NULL "+
            "RETURNING id, org_id, invitation_id, checked_in_at, checked_in_by_guard, checked_out_at",
        orgID,
        invitationID,
    ).Scan(
        &visit.ID,
        &visit.OrgID,
        &visit.InvitationID,
        &visit.CheckedInAt,
        &visit.CheckedInByGuard,
        &visit.CheckedOutAt,
    )
    return visit, err
}

func (r *HistoryRepository) ListByFlat(ctx context.Context, orgID, flatID uuid.UUID, limit int) ([]VisitRecord, error) {
    rows, err := r.pool.Query(
        ctx,
        "SELECT vv.id, vv.org_id, vv.invitation_id, vv.checked_in_at, vv.checked_in_by_guard, vv.checked_out_at "+
            "FROM visitor_visits vv "+
            "JOIN visitor_invitations vi ON vi.id = vv.invitation_id AND vi.org_id = vv.org_id "+
            "WHERE vv.org_id = $1 AND vi.flat_id = $2 "+
            "ORDER BY vv.checked_in_at DESC LIMIT $3",
        orgID,
        flatID,
        limit,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    visits := make([]VisitRecord, 0, limit)
    for rows.Next() {
        var visit VisitRecord
        if err := rows.Scan(
            &visit.ID,
            &visit.OrgID,
            &visit.InvitationID,
            &visit.CheckedInAt,
            &visit.CheckedInByGuard,
            &visit.CheckedOutAt,
        ); err != nil {
            return nil, err
        }
        visits = append(visits, visit)
    }
    return visits, rows.Err()
}

The replay-safe path is the CTE. If the UPDATE finds no row because another request already flipped used to true, the INSERT has no source row and the request fails cleanly.

Why replay and invalid-token branches diverge internally

From the visitor's point of view you may still choose a generic error message, but the system itself needs to know the difference between 'that token never existed' and 'that token existed and was already redeemed'. The first could be a typo or a fake screenshot. The second can indicate someone reused a copied QR code after a legitimate entry. Security staff challenge those situations differently, so the service should surface distinct internal errors even if the public response is equally terse.

The concrete failure mode to remember is the dropped-response race: tablet A wins the UPDATE, but the Wi-Fi dies before it receives 200 OK. If tablet A retries, the second request now looks exactly like a replay unless the deployment layer adds a same-device idempotency cache. That is the next lesson. At the storage layer, though, the only acceptable invariant is still exactly one successful row update.

Terminal — verify replay protection locally

$curl -s -X POST http://localhost:8080/v1/visitor-checkins -H 'Authorization: Bearer '$GUARD_TOKEN -H 'Content-Type: application/json' -d '{"token":"'$RAW_TOKEN'"}'

// The first redemption should insert one visitor_visits row.

$curl -s -X POST http://localhost:8080/v1/visitor-checkins -H 'Authorization: Bearer '$GUARD_TOKEN -H 'Content-Type: application/json' -d '{"token":"'$RAW_TOKEN'"}'

// A second redemption of the same token should fail as replay.

$go test ./services/visitor-service/internal/visitor -run TestCheckInByTokenAllowsExactlyOneWinner -v

// The concurrency test from the next lesson is the proof that simultaneous scans still produce one winner.

Applied exercise

Prove only one goroutine can win the same token

25 minutes

Two security guards at opposite ends of the gate scan the same QR code, and you want an automated test that reproduces the race with far more pressure than two human taps.

Design a test that starts N goroutines, all calling the same CheckInByToken method with the same raw token and org_id.
State the exact success counter and replay counter assertions that must hold after all goroutines finish.
Explain why a naive SELECT-then-UPDATE implementation could let more than one goroutine report success even if each query looks correct in isolation.
Describe what internal error value you would use for replay so security operations can distinguish it from a non-existent token.

Deliverable

A concurrency test plan that names the goroutine fan-out, the winner and loser assertions, and the storage invariant the SQL statement must enforce.

Completion checks

Your assertions say exactly 1 success and N-1 replay outcomes, not merely 'at least one success'.
The explanation calls out TOCTOU by name or clearly describes check-then-act racing.
The proposed internal replay error is distinct from the invalid-token error.

Quiz & Practice

Question 1 / 3

What makes SELECT-then-UPDATE unsafe for visitor token redemption?

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.

Course outline

01 · Phase 1 — Project Foundation16/16

02 · Phase 2 — Organization Service20/20

03 · Phase 3 — User Service10/10

04 · Phase 4 — Authentication Service10/10

05 · Phase 5 — API Gateway9/9

06 · Phase 6 — Flat Service6/6

07 · Phase 7 — Resident Service7/7

08 · Phase 8 — Maintenance Service7/7

09 · Phase 9 — Payment Service7/7

10 · Phase 10 — Complaint Service7/7

11 · Phase 11 — Notice Service7/7

12 · Phase 12 — Visitor Service6/6

13 · Phase 13 — Staff Service6/6

14 · Phase 14 — Notification Service7/7

15 · Phase 15 — Redis Integration6/6

16 · Phase 16 — Kafka Integration8/8

17 · Phase 17 — Observability7/7

18 · Phase 18 — Performance Engineering6/6

19 · Phase 19 — Production Deployment11/11

20 · Phase 20 — Production Readiness8/8

Stage 7 · Master

Phase 12 — Visitor Service

History

Write a real visit audit trail and make single-use redemption race-safe so two guard tablets scanning one QR code cannot both admit the same person.

14 min readHOA Backend Engineering CourseMaster

Why visit history is a security record and not just analytics

Terminal — create the visit-history migration

$mkdir -p services/visitor-service/db/migrations

$touch services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

Create services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql before adding the code

$mkdir -p services/visitor-service/db/migrations && touch services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/db/migrations/0055_create_visitor_visits.up.sql

CREATE TABLE visitor_visits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL,
    invitation_id UUID NOT NULL REFERENCES visitor_invitations(id),
    checked_in_at TIMESTAMPTZ NOT NULL,
    checked_in_by_guard UUID NOT NULL,
    checked_out_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT visitor_visits_single_checkin_key UNIQUE (invitation_id),
    CONSTRAINT visitor_visits_checkout_after_checkin_chk CHECK (
        checked_out_at IS NULL OR checked_out_at >= checked_in_at
    )
);

CREATE INDEX visitor_visits_org_checked_in_idx
    ON visitor_visits (org_id, checked_in_at DESC);

CREATE INDEX visitor_visits_org_invitation_idx
    ON visitor_visits (org_id, invitation_id);

The unique constraint on invitation_id reflects the product rule: one invitation can produce at most one successful check-in.

Terminal — create the visit-history rollback migration

$mkdir -p services/visitor-service/db/migrations

$touch services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

Create services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql before adding the code

$mkdir -p services/visitor-service/db/migrations && touch services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/db/migrations/0055_create_visitor_visits.down.sql

DROP TABLE IF EXISTS visitor_visits;

History rolls back independently of invitations and approvals because the tables were introduced additively in order.

How atomic redemption kills the two-tablet race

Pattern	Read step	Write step	Result under simultaneous scans
Naive SELECT then UPDATE	Two guards can both observe used = false	Both updates succeed because the decision was already made outside the write	Double admission is possible
Atomic UPDATE WHERE used = false RETURNING	No separate read decides the winner	Only one transaction can flip the row from false to true	Exactly one admission wins and the rest see replay

Two guards scanning the same QR code

Rendering diagram…

Terminal — create the visit-history service file

$mkdir -p services/visitor-service/internal/visitor

$touch services/visitor-service/internal/visitor/history.go

Create services/visitor-service/internal/visitor/history.go before adding the code

$mkdir -p services/visitor-service/internal/visitor && touch services/visitor-service/internal/visitor/history.go

// touch preserves an existing file, so the same command is safe when revisiting the lesson.

services/visitor-service/internal/visitor/history.go

package visitor

import (
    "context"
    "errors"
    "time"

    "github.com/google/uuid"
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type VisitRecord struct {
    ID               uuid.UUID
    OrgID            uuid.UUID
    InvitationID     uuid.UUID
    CheckedInAt      time.Time
    CheckedInByGuard uuid.UUID
    CheckedOutAt     *time.Time
}

type invitationLookup struct {
    ID          uuid.UUID
    OrgID       uuid.UUID
    FlatID      uuid.UUID
    HostUserID  uuid.UUID
    Used        bool
    ValidUntil  time.Time
    DatabaseNow time.Time
}

var (
    ErrInvitationTokenNotFound = errors.New("visitor: invitation token not found")
    ErrInvitationExpired       = errors.New("visitor: invitation expired")
    ErrInvitationAlreadyUsed   = errors.New("visitor: invitation already used")
)

type HistoryRepository struct {
    pool *pgxpool.Pool
}

func NewHistoryRepository(pool *pgxpool.Pool) *HistoryRepository {
    return &HistoryRepository{pool: pool}
}

type HistoryService struct {
    repo *HistoryRepository
}

func NewHistoryService(repo *HistoryRepository) *HistoryService {
    return &HistoryService{repo: repo}
}

func (s *HistoryService) CheckInByToken(ctx context.Context, orgID, guardUserID uuid.UUID, rawToken string) (VisitRecord, error) {
    invitation, err := s.repo.FindInvitationByToken(ctx, orgID, hashToken(rawToken))
    if err != nil {
        return VisitRecord{}, err
    }
    if !invitation.ValidUntil.After(invitation.DatabaseNow) {
        return VisitRecord{}, ErrInvitationExpired
    }
    if invitation.Used {
        return VisitRecord{}, ErrInvitationAlreadyUsed
    }

    visit, err := s.repo.ClaimInvitationAndInsertVisit(ctx, orgID, invitation.ID, guardUserID)
    if errors.Is(err, pgx.ErrNoRows) {
        current, stateErr := s.repo.FindInvitationByID(ctx, orgID, invitation.ID)
        if stateErr != nil {
            return VisitRecord{}, stateErr
        }
        if !current.ValidUntil.After(current.DatabaseNow) {
            return VisitRecord{}, ErrInvitationExpired
        }
        return VisitRecord{}, ErrInvitationAlreadyUsed
    }
    return visit, err
}

func (s *HistoryService) CheckOut(ctx context.Context, orgID, invitationID uuid.UUID) (VisitRecord, error) {
    return s.repo.MarkCheckedOut(ctx, orgID, invitationID)
}

func (s *HistoryService) ListByFlat(ctx context.Context, orgID, flatID uuid.UUID, limit int) ([]VisitRecord, error) {
    if limit <= 0 || limit > 100 {
        limit = 20
    }
    return s.repo.ListByFlat(ctx, orgID, flatID, limit)
}

func (r *HistoryRepository) FindInvitationByToken(ctx context.Context, orgID uuid.UUID, tokenHash string) (invitationLookup, error) {
    var invitation invitationLookup
    err := r.pool.QueryRow(
        ctx,
        "SELECT id, org_id, flat_id, host_user_id, used, valid_until, now() "+
            "FROM visitor_invitations WHERE org_id = $1 AND token_hash = $2",
        orgID,
        tokenHash,
    ).Scan(
        &invitation.ID,
        &invitation.OrgID,
        &invitation.FlatID,
        &invitation.HostUserID,
        &invitation.Used,
        &invitation.ValidUntil,
        &invitation.DatabaseNow,
    )
    if errors.Is(err, pgx.ErrNoRows) {
        return invitationLookup{}, ErrInvitationTokenNotFound
    }
    return invitation, err
}

func (r *HistoryRepository) FindInvitationByID(ctx context.Context, orgID, invitationID uuid.UUID) (invitationLookup, error) {
    var invitation invitationLookup
    err := r.pool.QueryRow(
        ctx,
        "SELECT id, org_id, flat_id, host_user_id, used, valid_until, now() "+
            "FROM visitor_invitations WHERE org_id = $1 AND id = $2",
        orgID,
        invitationID,
    ).Scan(
        &invitation.ID,
        &invitation.OrgID,
        &invitation.FlatID,
        &invitation.HostUserID,
        &invitation.Used,
        &invitation.ValidUntil,
        &invitation.DatabaseNow,
    )
    if errors.Is(err, pgx.ErrNoRows) {
        return invitationLookup{}, ErrInvitationTokenNotFound
    }
    return invitation, err
}

func (r *HistoryRepository) ClaimInvitationAndInsertVisit(ctx context.Context, orgID, invitationID, guardUserID uuid.UUID) (VisitRecord, error) {
    tx, err := r.pool.BeginTx(ctx, pgx.TxOptions{})
    if err != nil {
        return VisitRecord{}, err
    }
    defer tx.Rollback(ctx)

    var visit VisitRecord
    err = tx.QueryRow(
        ctx,
        "WITH claimed AS ("+
            "UPDATE visitor_invitations "+
            "SET used = TRUE, used_at = now() "+
            "WHERE org_id = $1 AND id = $2 AND used = FALSE AND valid_until > now() "+
            "RETURNING id, org_id, used_at"+
            ") "+
            "INSERT INTO visitor_visits (org_id, invitation_id, checked_in_at, checked_in_by_guard) "+
            "SELECT org_id, id, used_at, $3 FROM claimed "+
            "RETURNING id, org_id, invitation_id, checked_in_at, checked_in_by_guard, checked_out_at",
        orgID,
        invitationID,
        guardUserID,
    ).Scan(
        &visit.ID,
        &visit.OrgID,
        &visit.InvitationID,
        &visit.CheckedInAt,
        &visit.CheckedInByGuard,
        &visit.CheckedOutAt,
    )
    if err != nil {
        return VisitRecord{}, err
    }

    if err := tx.Commit(ctx); err != nil {
        return VisitRecord{}, err
    }
    return visit, nil
}

func (r *HistoryRepository) MarkCheckedOut(ctx context.Context, orgID, invitationID uuid.UUID) (VisitRecord, error) {
    var visit VisitRecord
    err := r.pool.QueryRow(
        ctx,
        "UPDATE visitor_visits SET checked_out_at = now() "+
            "WHERE org_id = $1 AND invitation_id = $2 AND checked_out_at IS NULL "+
            "RETURNING id, org_id, invitation_id, checked_in_at, checked_in_by_guard, checked_out_at",
        orgID,
        invitationID,
    ).Scan(
        &visit.ID,
        &visit.OrgID,
        &visit.InvitationID,
        &visit.CheckedInAt,
        &visit.CheckedInByGuard,
        &visit.CheckedOutAt,
    )
    return visit, err
}

func (r *HistoryRepository) ListByFlat(ctx context.Context, orgID, flatID uuid.UUID, limit int) ([]VisitRecord, error) {
    rows, err := r.pool.Query(
        ctx,
        "SELECT vv.id, vv.org_id, vv.invitation_id, vv.checked_in_at, vv.checked_in_by_guard, vv.checked_out_at "+
            "FROM visitor_visits vv "+
            "JOIN visitor_invitations vi ON vi.id = vv.invitation_id AND vi.org_id = vv.org_id "+
            "WHERE vv.org_id = $1 AND vi.flat_id = $2 "+
            "ORDER BY vv.checked_in_at DESC LIMIT $3",
        orgID,
        flatID,
        limit,
    )
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    visits := make([]VisitRecord, 0, limit)
    for rows.Next() {
        var visit VisitRecord
        if err := rows.Scan(
            &visit.ID,
            &visit.OrgID,
            &visit.InvitationID,
            &visit.CheckedInAt,
            &visit.CheckedInByGuard,
            &visit.CheckedOutAt,
        ); err != nil {
            return nil, err
        }
        visits = append(visits, visit)
    }
    return visits, rows.Err()
}

The replay-safe path is the CTE. If the UPDATE finds no row because another request already flipped used to true, the INSERT has no source row and the request fails cleanly.

Why replay and invalid-token branches diverge internally

Terminal — verify replay protection locally

$curl -s -X POST http://localhost:8080/v1/visitor-checkins -H 'Authorization: Bearer '$GUARD_TOKEN -H 'Content-Type: application/json' -d '{"token":"'$RAW_TOKEN'"}'

// The first redemption should insert one visitor_visits row.

$curl -s -X POST http://localhost:8080/v1/visitor-checkins -H 'Authorization: Bearer '$GUARD_TOKEN -H 'Content-Type: application/json' -d '{"token":"'$RAW_TOKEN'"}'

// A second redemption of the same token should fail as replay.

$go test ./services/visitor-service/internal/visitor -run TestCheckInByTokenAllowsExactlyOneWinner -v

// The concurrency test from the next lesson is the proof that simultaneous scans still produce one winner.

Applied exercise

Prove only one goroutine can win the same token

25 minutes

Two security guards at opposite ends of the gate scan the same QR code, and you want an automated test that reproduces the race with far more pressure than two human taps.

Design a test that starts N goroutines, all calling the same CheckInByToken method with the same raw token and org_id.
State the exact success counter and replay counter assertions that must hold after all goroutines finish.
Explain why a naive SELECT-then-UPDATE implementation could let more than one goroutine report success even if each query looks correct in isolation.
Describe what internal error value you would use for replay so security operations can distinguish it from a non-existent token.

Deliverable

A concurrency test plan that names the goroutine fan-out, the winner and loser assertions, and the storage invariant the SQL statement must enforce.

Completion checks

Your assertions say exactly 1 success and N-1 replay outcomes, not merely 'at least one success'.
The explanation calls out TOCTOU by name or clearly describes check-then-act racing.
The proposed internal replay error is distinct from the invalid-token error.

Quiz & Practice

Question 1 / 3

What makes SELECT-then-UPDATE unsafe for visitor token redemption?

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.