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 7 — Resident Service

Family Members

Households as a grouping, not a bigger residents table

14 min readHOA Backend Engineering CourseMaster

Why Households Are a Separate Table

The instinct is to add a head_of_household_id column to residents and call it done. That collapses two different questions into one column: 'who is the primary contact for billing and notices' and 'who lives together as a unit.' A household is its own row so a primary contact can change without rewriting every member's record, and so a household can exist with members added incrementally as family joins later.

Schema: One Primary Member per Household

Terminal — resident-service

$mkdir -p services/resident-service/migrations

$touch services/resident-service/migrations/0002_create_households.up.sql services/resident-service/migrations/0002_create_households.down.sql

Create services/resident-service/migrations/0002_create_households.up.sql before adding the code

$mkdir -p services/resident-service/migrations && touch services/resident-service/migrations/0002_create_households.up.sql

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

services/resident-service/migrations/0002_create_households.up.sql

CREATE TYPE household_relationship AS ENUM ('self', 'spouse', 'child', 'parent', 'dependent', 'domestic_help');

CREATE TABLE households (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id  UUID NOT NULL,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE household_members (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL,
    household_id  UUID NOT NULL REFERENCES households(id),
    resident_id   UUID NOT NULL REFERENCES residents(id),
    relationship  household_relationship NOT NULL,
    is_primary    BOOLEAN NOT NULL DEFAULT false,
    added_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Exactly one primary member per household — a partial unique index is
-- the entire enforcement mechanism, no application-level "check then
-- write" race is needed for this rule.
CREATE UNIQUE INDEX household_members_one_primary_key
    ON household_members (household_id)
    WHERE is_primary;

-- A resident cannot belong to the same household twice.
CREATE UNIQUE INDEX household_members_household_resident_key
    ON household_members (household_id, resident_id);

CREATE INDEX household_members_tenant_id_idx ON household_members (tenant_id);
CREATE INDEX household_members_resident_id_idx ON household_members (resident_id);

household_members_one_primary_key is a partial unique index on household_id filtered to WHERE is_primary — Postgres rejects a second TRUE row for the same household at the constraint level, no transaction-and-check dance required in application code.

Create services/resident-service/migrations/0002_create_households.down.sql before adding the code

$mkdir -p services/resident-service/migrations && touch services/resident-service/migrations/0002_create_households.down.sql

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

services/resident-service/migrations/0002_create_households.down.sql

DROP TABLE IF EXISTS household_members;
DROP TABLE IF EXISTS households;
DROP TYPE IF EXISTS household_relationship;

Adding a Member Inside One Transaction

Creating a household and adding its first (primary) member is one business operation, not two independent writes. If the member insert failed after the household insert committed, you would have an orphaned household with nobody in it. Both statements run inside a single pgx transaction.

Terminal — resident-service

$mkdir -p services/resident-service/internal/repository/postgres

$touch services/resident-service/internal/repository/postgres/household_repository.go

Create services/resident-service/internal/repository/postgres/household_repository.go before adding the code

$mkdir -p services/resident-service/internal/repository/postgres && touch services/resident-service/internal/repository/postgres/household_repository.go

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

services/resident-service/internal/repository/postgres/household_repository.go

package postgres

import (
	"context"
	"errors"

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

	"github.com/hoa-platform/backend/services/resident-service/internal/domain"
)

type HouseholdRepository struct {
	pool *pgxpool.Pool
}

func NewHouseholdRepository(pool *pgxpool.Pool) *HouseholdRepository {
	return &HouseholdRepository{pool: pool}
}

// CreateWithPrimaryMember runs both inserts in one transaction. If the
// member insert fails — including on the one-primary-per-household
// constraint, which cannot fire on a brand new household but will on
// AddMember below — the household insert is rolled back too.
func (r *HouseholdRepository) CreateWithPrimaryMember(
	ctx context.Context, tenantID uuid.UUID, name string, primaryResidentID uuid.UUID,
) (domain.Household, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return domain.Household{}, err
	}
	defer tx.Rollback(ctx) // no-op if Commit succeeds first

	var household domain.Household
	const insertHousehold = `
		INSERT INTO households (tenant_id, name) VALUES ($1, $2)
		RETURNING id, created_at`
	if err := tx.QueryRow(ctx, insertHousehold, tenantID, name).Scan(&household.ID, &household.CreatedAt); err != nil {
		return domain.Household{}, err
	}
	household.TenantID = tenantID
	household.Name = name

	const insertMember = `
		INSERT INTO household_members (tenant_id, household_id, resident_id, relationship, is_primary)
		VALUES ($1, $2, $3, 'self', true)`
	if _, err := tx.Exec(ctx, insertMember, tenantID, household.ID, primaryResidentID); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" {
			return domain.Household{}, domain.ErrResidentNotFoundForHousehold
		}
		return domain.Household{}, err
	}

	if err := tx.Commit(ctx); err != nil {
		return domain.Household{}, err
	}
	return household, nil
}

func (r *HouseholdRepository) AddMember(
	ctx context.Context, tenantID, householdID, residentID uuid.UUID, relationship string,
) error {
	const q = `
		INSERT INTO household_members (tenant_id, household_id, resident_id, relationship, is_primary)
		VALUES ($1, $2, $3, $4, false)`

	_, err := r.pool.Exec(ctx, q, tenantID, householdID, residentID, relationship)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return domain.ErrResidentAlreadyInHousehold
		}
		return err
	}
	return nil
}

AddMember always inserts is_primary = false, so the household_members_one_primary_key index can only ever be exercised by an explicit change-of-primary operation, never by an accidental double-add.

Create services/resident-service/internal/domain/household.go before adding the code

$mkdir -p services/resident-service/internal/domain && touch services/resident-service/internal/domain/household.go

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

services/resident-service/internal/domain/household.go

package domain

import (
	"errors"
	"time"

	"github.com/google/uuid"
)

type Household struct {
	ID        uuid.UUID
	TenantID  uuid.UUID
	Name      string
	CreatedAt time.Time
}

var (
	ErrHouseholdNotFound             = errors.New("household: not found")
	ErrResidentNotFoundForHousehold  = errors.New("household: resident id does not exist")
	ErrResidentAlreadyInHousehold    = errors.New("household: resident is already a member")
)

Rejecting an Impossible Family Graph

Terminal — verify the primary-member constraint

$migrate -database "$RESIDENT_DATABASE_URL" -path services/resident-service/migrations up

$curl -s -H 'X-Tenant-Id: 11111111-1111-1111-1111-111111111111' -X POST localhost:8082/households -d '{"name":"Rao Family","primary_resident_id":"$RID"}'

// 201 — household created with Asha as primary in one transaction

$psql "$RESIDENT_DATABASE_URL" -c "UPDATE household_members SET is_primary = true WHERE resident_id != '$RID'"

// attempt to force a second primary directly in SQL — ERROR: duplicate key value violates unique constraint

Applied exercise

Transfer primary status without a moment of zero primaries

30 min

A household's primary member is moving abroad and a spouse must become the new primary contact. A naive two-step UPDATE (clear the old primary, then set the new one) briefly leaves the household with zero primary members, and if the process crashes between the two statements, it stays that way.

Implement TransferPrimary(ctx, tenantID, householdID, newPrimaryResidentID) as a single transaction that updates both rows.
Because the unique index enforces at most one primary at a time, decide and justify the statement order that avoids ever violating it mid-transaction (hint: clearing the old primary and setting the new one in the same UPDATE statement, not two).
Write a test that runs TransferPrimary and asserts exactly one primary exists both before and after, never zero.

Deliverable

A TransferPrimary repository method plus a passing test that inspects household_members state at every step.

Completion checks

The unique constraint is never violated during the transfer.
A crash simulated mid-transaction (rollback) leaves the original primary intact, not zero primaries.
The chosen statement order is explained in a code comment, not left implicit.

Quiz & Practice

Question 1 / 2

Why is the one-primary-per-household rule enforced with a partial unique index rather than an application-level 'check for existing primary, then insert' sequence?

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 7 — Resident Service

Family Members

Households as a grouping, not a bigger residents table

14 min readHOA Backend Engineering CourseMaster

Why Households Are a Separate Table

Schema: One Primary Member per Household

Terminal — resident-service

$mkdir -p services/resident-service/migrations

$touch services/resident-service/migrations/0002_create_households.up.sql services/resident-service/migrations/0002_create_households.down.sql

Create services/resident-service/migrations/0002_create_households.up.sql before adding the code

$mkdir -p services/resident-service/migrations && touch services/resident-service/migrations/0002_create_households.up.sql

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

services/resident-service/migrations/0002_create_households.up.sql

CREATE TYPE household_relationship AS ENUM ('self', 'spouse', 'child', 'parent', 'dependent', 'domestic_help');

CREATE TABLE households (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id  UUID NOT NULL,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE household_members (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL,
    household_id  UUID NOT NULL REFERENCES households(id),
    resident_id   UUID NOT NULL REFERENCES residents(id),
    relationship  household_relationship NOT NULL,
    is_primary    BOOLEAN NOT NULL DEFAULT false,
    added_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Exactly one primary member per household — a partial unique index is
-- the entire enforcement mechanism, no application-level "check then
-- write" race is needed for this rule.
CREATE UNIQUE INDEX household_members_one_primary_key
    ON household_members (household_id)
    WHERE is_primary;

-- A resident cannot belong to the same household twice.
CREATE UNIQUE INDEX household_members_household_resident_key
    ON household_members (household_id, resident_id);

CREATE INDEX household_members_tenant_id_idx ON household_members (tenant_id);
CREATE INDEX household_members_resident_id_idx ON household_members (resident_id);

Create services/resident-service/migrations/0002_create_households.down.sql before adding the code

$mkdir -p services/resident-service/migrations && touch services/resident-service/migrations/0002_create_households.down.sql

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

services/resident-service/migrations/0002_create_households.down.sql

DROP TABLE IF EXISTS household_members;
DROP TABLE IF EXISTS households;
DROP TYPE IF EXISTS household_relationship;

Adding a Member Inside One Transaction

Terminal — resident-service

$mkdir -p services/resident-service/internal/repository/postgres

$touch services/resident-service/internal/repository/postgres/household_repository.go

Create services/resident-service/internal/repository/postgres/household_repository.go before adding the code

$mkdir -p services/resident-service/internal/repository/postgres && touch services/resident-service/internal/repository/postgres/household_repository.go

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

services/resident-service/internal/repository/postgres/household_repository.go

package postgres

import (
	"context"
	"errors"

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

	"github.com/hoa-platform/backend/services/resident-service/internal/domain"
)

type HouseholdRepository struct {
	pool *pgxpool.Pool
}

func NewHouseholdRepository(pool *pgxpool.Pool) *HouseholdRepository {
	return &HouseholdRepository{pool: pool}
}

// CreateWithPrimaryMember runs both inserts in one transaction. If the
// member insert fails — including on the one-primary-per-household
// constraint, which cannot fire on a brand new household but will on
// AddMember below — the household insert is rolled back too.
func (r *HouseholdRepository) CreateWithPrimaryMember(
	ctx context.Context, tenantID uuid.UUID, name string, primaryResidentID uuid.UUID,
) (domain.Household, error) {
	tx, err := r.pool.Begin(ctx)
	if err != nil {
		return domain.Household{}, err
	}
	defer tx.Rollback(ctx) // no-op if Commit succeeds first

	var household domain.Household
	const insertHousehold = `
		INSERT INTO households (tenant_id, name) VALUES ($1, $2)
		RETURNING id, created_at`
	if err := tx.QueryRow(ctx, insertHousehold, tenantID, name).Scan(&household.ID, &household.CreatedAt); err != nil {
		return domain.Household{}, err
	}
	household.TenantID = tenantID
	household.Name = name

	const insertMember = `
		INSERT INTO household_members (tenant_id, household_id, resident_id, relationship, is_primary)
		VALUES ($1, $2, $3, 'self', true)`
	if _, err := tx.Exec(ctx, insertMember, tenantID, household.ID, primaryResidentID); err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23503" {
			return domain.Household{}, domain.ErrResidentNotFoundForHousehold
		}
		return domain.Household{}, err
	}

	if err := tx.Commit(ctx); err != nil {
		return domain.Household{}, err
	}
	return household, nil
}

func (r *HouseholdRepository) AddMember(
	ctx context.Context, tenantID, householdID, residentID uuid.UUID, relationship string,
) error {
	const q = `
		INSERT INTO household_members (tenant_id, household_id, resident_id, relationship, is_primary)
		VALUES ($1, $2, $3, $4, false)`

	_, err := r.pool.Exec(ctx, q, tenantID, householdID, residentID, relationship)
	if err != nil {
		var pgErr *pgconn.PgError
		if errors.As(err, &pgErr) && pgErr.Code == "23505" {
			return domain.ErrResidentAlreadyInHousehold
		}
		return err
	}
	return nil
}

AddMember always inserts is_primary = false, so the household_members_one_primary_key index can only ever be exercised by an explicit change-of-primary operation, never by an accidental double-add.

Create services/resident-service/internal/domain/household.go before adding the code

$mkdir -p services/resident-service/internal/domain && touch services/resident-service/internal/domain/household.go

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

services/resident-service/internal/domain/household.go

package domain

import (
	"errors"
	"time"

	"github.com/google/uuid"
)

type Household struct {
	ID        uuid.UUID
	TenantID  uuid.UUID
	Name      string
	CreatedAt time.Time
}

var (
	ErrHouseholdNotFound             = errors.New("household: not found")
	ErrResidentNotFoundForHousehold  = errors.New("household: resident id does not exist")
	ErrResidentAlreadyInHousehold    = errors.New("household: resident is already a member")
)

Rejecting an Impossible Family Graph

Terminal — verify the primary-member constraint

$migrate -database "$RESIDENT_DATABASE_URL" -path services/resident-service/migrations up

$curl -s -H 'X-Tenant-Id: 11111111-1111-1111-1111-111111111111' -X POST localhost:8082/households -d '{"name":"Rao Family","primary_resident_id":"$RID"}'

// 201 — household created with Asha as primary in one transaction

$psql "$RESIDENT_DATABASE_URL" -c "UPDATE household_members SET is_primary = true WHERE resident_id != '$RID'"

// attempt to force a second primary directly in SQL — ERROR: duplicate key value violates unique constraint

Applied exercise

Transfer primary status without a moment of zero primaries

30 min

Implement TransferPrimary(ctx, tenantID, householdID, newPrimaryResidentID) as a single transaction that updates both rows.
Because the unique index enforces at most one primary at a time, decide and justify the statement order that avoids ever violating it mid-transaction (hint: clearing the old primary and setting the new one in the same UPDATE statement, not two).
Write a test that runs TransferPrimary and asserts exactly one primary exists both before and after, never zero.

Deliverable

A TransferPrimary repository method plus a passing test that inspects household_members state at every step.

Completion checks

The unique constraint is never violated during the transfer.
A crash simulated mid-transaction (rollback) leaves the original primary intact, not zero primaries.
The chosen statement order is explained in a code comment, not left implicit.

Quiz & Practice

Question 1 / 2

Why is the one-primary-per-household rule enforced with a partial unique index rather than an application-level 'check for existing primary, then insert' sequence?

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