Ascenda Loyalty Point System

Intro

Tech Stack Chosen

• Golang with gin frame work for API building

• Postgres is our choice of database since relational database have a stricter rules in terms of how each fields are defined.

• Next.js to build a simple UI to carry out the basic interaction with the backend API

• Cypress for frontend testing

• Golang’s testing package for backend unit and integration testing

Features

Sample Architecture

My thoughts

• The validation of membership can be done in the backend when the user try to submit a credit request and reject when it is not correct, there is no need for a dedicated endpoint for it

• The approval of the credit requests can be built using an admin system to allow the third party loyal program to interact with the API to update the database accordingly
• Realtime update, whenever the third party approves, customer will get notify immediately instead of waiting for the server to fetch the hand back file and process
• Reduce the potential human error when the third party programs create the hand back files
• It definitely more work on both frontend and backend

• A better way to notify user will be just through email, instead of webhook since this is a webapp and not a phone app, most people do not turn on the notification feature on webapp (at least the people i know)

Planning

Use Case diagram

Software Development Process

Iterative and Incremental Model

Database Design

UML Diagram

Backend

SQLC

• documentation

Usage

1. Download the cli tool
1. MAC OS: brew install sqlc
2. Ubuntu: sudo snap install sqlc
3. Go: go install github.com/kyleconroy/sqlc/cmd/sqlc@latest
4. More details here

2. Initialise
1. sqlc init
2. sqlc.yaml should be generated
3. update the content

version: "1"
packages:
  - path: "./pkg/models"
    name: "models"
    engine: "postgresql"
    schema: "./pkg/models/migrations"
    queries: "./pkg/models/query"
    emit_json_tags: true

3. Database Migration
Before we start on any coding, the database structure has to be defined in the Postgres database.
• Download the sql file from the database design
• Use one of the database migration library. We use golang-migrate.
• copy the content in the sql file that is downloaded to the 000001_init.up.sql
• run migrate -source file://pkg/models/migrations -database "${PSQL_LINK}?sslmode=disable" up
• The command can be written into a makefile and simply be called using make migrate.
• Now open any Graphical User Interface (GUI) for Postgres and connect to the database, you should see the tables well defined and ready to use.

4. Write sql queries in the queries directory
1. use loyalty program as an example below
2. database schema must be defined first in the schema directory
3. schema and query are to be defined in different file in the directory specified in the sqlc.yaml file

//SCHEMA inside the ./pkg/models/migrations folder
CREATE TABLE "loyalty_program" (
  "id" bigserial PRIMARY KEY,
  "name" varchar NOT NULL,
  "currency_name" varchar NOT NULL,
  "processing_time" varchar NOT NULL,
  "description" varchar,
  "enrollment_link" varchar NOT NULL,
  "terms_condition_link" varchar NOT NULL,
  "format_regex" varchar NOT NULL,
  "partner_code" varchar NOT NULL,
  "initial_earn_rate" float NOT NULL
);

//query file loyalty_program.sql inside ./pkg/models/query
-- name: GetLoyaltyByID :one
SELECT * FROM loyalty_program
WHERE id = $1 LIMIT 1;

-- name: GetLoyaltyByName :one
SELECT * FROM loyalty_program
WHERE name = $1 LIMIT 1;

-- name: ListLoyalty :many
SELECT * FROM loyalty_program
ORDER BY name;

-- name: CreateLoyalty :one
INSERT INTO loyalty_program(
  name, currency_name,processing_time,description,enrollment_link,
  terms_condition_link,format_regex,partner_code,initial_earn_rate
) VALUES (
  $1, $2, $3, $4, $5, $6, $7, $8, $9
)
RETURNING *;

-- name: UpdateLoyalty :exec
UPDATE loyalty_program 
SET name = COALESCE($1,name),
currency_name = COALESCE($2,currency_name),
processing_time = COALESCE($3,processing_time),
description = COALESCE($4,description),
enrollment_link = COALESCE($5,enrollment_link),
terms_condition_link = COALESCE($6,terms_condition_link),
format_regex = COALESCE($7,format_regex),
partner_code = COALESCE($8,partner_code),
initial_earn_rate = COALESCE($9,initial_earn_rate)
WHERE id = $10;


-- name: DeleteLoyalty :exec
DELETE FROM loyalty_program
WHERE id = $1;

5. Generate CRUD
1. sqlc generate
2. db.go contains the database transaction and instantiation
3. models.go contains the struct of the data model
4. loyalty_program.go contains the go functions for the relevant operations matching to the sql query
5. Sample result

const createLoyalty = `-- name: CreateLoyalty :one
INSERT INTO loyalty_program(
  name, currency_name,processing_time,description,enrollment_link,
  terms_condition_link,format_regex,partner_code,initial_earn_rate
) VALUES (
  $1, $2, $3, $4, $5, $6, $7, $8, $9
)
RETURNING id, name, currency_name, processing_time, description, enrollment_link, terms_condition_link, format_regex, partner_code, initial_earn_rate
`

type CreateLoyaltyParams struct {
	Name               string  `json:"name"`
	CurrencyName       string  `json:"currency_name"`
	ProcessingTime     string  `json:"processing_time"`
	Description        string  `json:"description"`
	EnrollmentLink     string  `json:"enrollment_link"`
	TermsConditionLink string  `json:"terms_condition_link"`
	FormatRegex        string  `json:"format_regex"`
	PartnerCode        string  `json:"partner_code"`
	InitialEarnRate    float64 `json:"initial_earn_rate"`
}

func (q *Queries) CreateLoyalty(ctx context.Context, arg CreateLoyaltyParams) (LoyaltyProgram, error) {
	row := q.db.QueryRowContext(ctx, createLoyalty,
		arg.Name,
		arg.CurrencyName,
		arg.ProcessingTime,
		arg.Description,
		arg.EnrollmentLink,
		arg.TermsConditionLink,
		arg.FormatRegex,
		arg.PartnerCode,
		arg.InitialEarnRate,
	)
	var i LoyaltyProgram
	err := row.Scan(
		&i.ID,
		&i.Name,
		&i.CurrencyName,
		&i.ProcessingTime,
		&i.Description,
		&i.EnrollmentLink,
		&i.TermsConditionLink,
		&i.FormatRegex,
		&i.PartnerCode,
		&i.InitialEarnRate,
	)
	return i, err
}

Problems

1. sql.Null<type> is used for defining of fields that can contain NULL
1. It has the following struct

type NullString struct {
	Stringstring
	Validbool // Valid is true if String is not NULL
}

2. this struct is very problematic when is used by the gin framework to marshal and unmarshal the json data come of the api request body.
3. Some of the solutions that is found online are to define a wrapper class for this type and define a MarshalJSON function to define the marshal behaviour. Then put the wrapper class in the struct that needs these Null Fields
4. The problem is, the struct that uses these Null Fields is auto generated using sqlc using the sql queries defined.
5. It can be changed to the new wrapper class, but every there is a need to update the queries and sqlc generate is run to generate the code, the new code will overwrite the changes that you have made.
6. The work around is to define null fields as NOT NULL but with a DEFAULT to an empty string.

Reward Calculation

CREATE TABLE "promotion" (
  "id" bigserial PRIMARY KEY,
  "program" int NOT NULL,
  "promo_type" promo_type_enum NOT NULL,
  "start_date" date NOT NULL,
  "end_date" date NOT NULL,
  "earn_rate_type" earn_rate_type_enum NOT NULL,
  "constant" float NOT NULL,
  "card_tier" int,
  "loyalty_membership" int
);

• start and end dates (to specify the period of promotion)

• program which is a foreign key points to which loyalty program that this promotion is related with

• promot_type whether it is onetime (each customer can only claim once) or ongoing (valid for as long as the current date of making credit transfer falls inside ) promotion

• earn_rate_type whether the additional reward is granted using addition or multiplication

• constant the amount to multiply or add

• card_tier a foreign key points to a predefined card tier by the bank. Eg. Gold, Platinum… This field is optional, if provided, only user with that particular card tier will be able to enjoy the promotion

• loyalty_membership a foreign key points to a predefined card tier by the third party loyalty program. Eg. Gold, Platinum… This field is optional, if provided, only user with that particular membership will be able to enjoy the promotion. We later found that this field is not practical since the membership details is only available to the third party and we do not have access

Different Scenarios

• Reward without promotion
• reward = creditToTransfer * initalEarnRate

• Reward with on onging promotion
• reward = creditToTransfer * initalEarnRate (+/*) constant

• Reward with on onetime promotion
• Check the past credit to see if this user has any active credit transfer use the current promo
• if no
• reward = creditToTransfer * initalEarnRate (+/*) constant
• if yes
• reward = creditToTransfer * initalEarnRate

• Reward with a card tier specified
• Check the user has the matching card tier
• if yes
• reward = creditToTransfer * initalEarnRate (+/*) constant
• if no
• reward = creditToTransfer * initalEarnRate

• Reward with multiple promotions that is valid
• calculate all the reward that the user might get and return only the highest one

Code

func CalculateReward(c context.Context, query *models.Queries, body models.TransferParams) (result float64, promoUsed sql.NullInt32, err error) {
	if body.CreditToTransfer < 0 {
		// return 0,error
	}
	program, err := query.GetLoyaltyByID(c, int64(body.ProgramId))
	if err != nil {
		return 0, sql.NullInt32{Valid: false}, nil
	}

	getPromoParam := models.GetPromotionByDateRangeParams{
		Column1: time.Now().Format("2006-01-02"),
		Program: int32(program.ID),
	}

	promotions, err := query.GetPromotionByDateRange(c, getPromoParam)
	if err != nil {
		return 0, sql.NullInt32{Valid: false}, nil
	}

	user, err := query.GetUserByID(c, int64(body.UserId))
	if err != nil {
		return 0, sql.NullInt32{Valid: false}, nil
	}
	var base float64 = program.InitialEarnRate * body.CreditToTransfer
	var promoIdUsed int32 = 0
	var max float64 = base
	for _, promotion := range promotions {
		var tempReward float64 = 0
		if promotion.PromoType == "onetime" {
			args := models.GetCreditRequestByPromoParams{
				Program:   int32(program.ID),
				PromoUsed: sql.NullInt32{Valid: true, Int32: int32(promotion.ID)},
			}
			_, err = query.GetCreditRequestByPromo(c, args)

			//skip the loop if there is result found
			// if err.Error()!="sql: no rows in result set"{
			if err != sql.ErrNoRows {
				fmt.Println("no pass request made")
				continue
			}
		}

		if promotion.CardTier.Valid && user.CardTier.Valid {
			if promotion.CardTier.Int32 == user.CardTier.Int32 {
				tempReward = processReward(promotion, base)
			}
		} else if promotion.CardTier.Valid {
			continue

		} else {
			tempReward = processReward(promotion, base)

		}

		if tempReward != 0 {
			if tempReward > max {
				max = tempReward
				promoIdUsed = int32(promotion.ID)
			}
		}
	}
	if promoIdUsed != 0 {
		return max, sql.NullInt32{Int32: promoIdUsed, Valid: true}, nil
	} else {
		return max, sql.NullInt32{Valid: false}, nil
	}

}

Database Transaction

• Alice want to transfer 1000 dollar to Bob

• Right flow
• Deduct 1000 dollar from Alice’s account
• Credit 1000 dollar to Bob’s account

• if the database fails to deduct 1000 dollar from Alice’s account. It should abort entire flow and not credit the amount to bob’s account

Code to implement transaction

func (store *Store) execTx(ctx context.Context,fn func(*Queries) error) error{
  tx,err := store.db.BeginTx(ctx,nil)
  if err!=nil{
    return err
  }
  q := New(tx)
  err = fn(q)
  if err!=nil{
    if rbErr:=tx.Rollback(); rbErr!=nil{
      return fmt.Errorf("tx err: %v, rb err: %v",err,rbErr)
    }
    return err
  }
  return tx.Commit()
}

func (store *Store) CreditTransferOut(ctx context.Context,arg TransferParams,promo sql.NullInt32) (CreditRequest,error){
  var result CreditRequest
  err:= store.execTx(ctx, func (q *Queries)error{
    var err error
    request:=CreateCreditRequestParams{
      UserID:arg.UserId,
      Program:arg.ProgramId,
      MemberID:arg.MembershipId,
      CreditUsed:arg.CreditToTransfer,
      RewardShouldReceive:arg.RewardShouldReceive,
      PromoUsed:promo,
      TransactionTime:sql.NullTime{Time:time.Now(),Valid:true},
      TransactionStatus:TransactionStatusEnum("created"),
    }
    result,err =q.CreateCreditRequest(
     ctx,request, 
    )
    if err!=nil{
      return err
    }
    balanceParam := DecreBalanceParams {
      CreditBalance:arg.CreditToTransfer,
      ID: int64(arg.UserId),
    }

    //TODO prevent deadlock
    err = q.DecreBalance(ctx,balanceParam)
    if err!=nil{
      return err
    }
    return nil
      
  })
  return result,err 

}

Database Dead Lock Prevention

Testing

Code Snippets

Unit testing for database operations

func createLoyaltyObject() CreateLoyaltyParams {
	arg := CreateLoyaltyParams{
		Name:               utils.RandomString(6),
		CurrencyName:       utils.RandomString(6),
		ProcessingTime:     utils.RandomString(4),
		Description:        sql.NullString{String: utils.RandomString(20), Valid: true},
		EnrollmentLink:     utils.RandomString(20),
		TermsConditionLink: utils.RandomString(20),
		FormatRegex:        utils.RandomString(10),
		PartnerCode:        utils.RandomString(5),
		InitialEarnRate:    utils.RandomFloat(10),
	}
	return arg
}
func TestCreateLoyalty(t *testing.T) {
	obj := createLoyaltyObject()
	program, err := testQueries.CreateLoyalty(context.Background(), obj)
	require.NoError(t, err)
	require.NotEmpty(t, program)

	require.Equal(t, program.Name, obj.Name)
	require.Equal(t, program.CurrencyName, obj.CurrencyName)
	require.Equal(t, program.ProcessingTime, obj.ProcessingTime)
	require.Equal(t, program.Description.String, obj.Description.String)
	require.Equal(t, program.EnrollmentLink, obj.EnrollmentLink)
	require.Equal(t, program.TermsConditionLink, obj.TermsConditionLink)
	require.Equal(t, program.FormatRegex, obj.FormatRegex)
	require.Equal(t, program.PartnerCode, obj.PartnerCode)
	require.Equal(t, program.InitialEarnRate, obj.InitialEarnRate)
	require.NotZero(t, program.ID)
}
func TestDeleteLoyalty(t *testing.T) {
	obj := createLoyaltyObject()
	program, err := testQueries.CreateLoyalty(context.Background(), obj)
	require.NoError(t, err)
	require.NotEmpty(t, program)

	deleteErr := testQueries.DeleteLoyalty(context.Background(), program.ID)
	require.NoError(t, deleteErr)

	_, getErr := testQueries.GetLoyaltyByID(context.Background(), program.ID)
	require.EqualError(t, getErr, "sql: no rows in result set")
}

Integration testing on reward calculation

// when promo ask for cartier but use no cardtier/ user's cardtier is below what is requested
func TestRewardCalPromoOutOfRange(t *testing.T) {

	cardTierArgs := models.CreateCardTierParams{
		Name: utils.RandomString(7),
		Tier: 2,
	}
	cardTier, err := testQueries.CreateCardTier(context.Background(), cardTierArgs)
	createLoyaltyArgs := createLoyaltyObject()
	createUserArgs := createUserObject(sql.NullInt32{Valid: false})
	var creditToTransfer float64 = 100
	program, err := testQueries.CreateLoyalty(context.Background(), createLoyaltyArgs)
	require.NoError(t, err)
	user, err := testQueries.CreateUser(context.Background(), createUserArgs)
	require.NoError(t, err)
	args := models.TransferParams{
		UserId:           int32(user.ID),
		ProgramId:        int32(program.ID),
		CreditToTransfer: float64(creditToTransfer),
		MembershipId:     utils.RandomString(6),
	}
	startDate, err := time.Parse("2006-01-02", "2022-07-01")
	require.NoError(t, err)
	endDate, err := time.Parse("2006-01-02", "2022-07-15")
	require.NoError(t, err)

	var constant float64 = 1000
	createPromoArgs := models.CreatePromotionParams{
		Program:      int32(program.ID),
		PromoType:    models.PromoTypeEnum("ongoing"),
		StartDate:    startDate,
		EndDate:      endDate,
		EarnRateType: models.EarnRateTypeEnum("add"),
		Constant:     float64(constant),
		CardTier:     sql.NullInt32{Valid: true, Int32: int32(cardTier.ID)},
	}
	_, err = testQueries.CreatePromotion(context.Background(), createPromoArgs)
	require.NoError(t, err)
	result, _, err := CalculateReward(context.Background(), testQueries, args)
	require.NoError(t, err)
	expected := creditToTransfer * (program.InitialEarnRate)
	require.Equal(t, expected, result)
}

Makefile

include .env
export
docker-postgres:
	docker run --name postgres-db -p 55001:5432 -e POSTGRES_PASSWORD=postgrespw -d postgres
migrations:
	@read -p "Enter the name of the migration: " migration_name;\
	migrate create -ext sql -dir pkg/models/migrations -seq $$migration_name
migrate:
	migrate -source file://pkg/models/migrations -database "${PSQL_LINK}" up 

migrate-down:
	migrate -source file://pkg/models/migrations -database "${PSQL_LINK}" down 

test-model:
	go test esc/ascendaRoyaltyPoint/pkg/models -v -cover

test-controllers:
	go test esc/ascendaRoyaltyPoint/pkg/controllers -v -cover

sqlc:
	sqlc generate

Conclusion

Testing can only find the presence of errors, not their absence — Edsger W. Dijkstra