DEV Community: Akkarapon Phikulsri

Theory of Humanistic Architecture

Akkarapon Phikulsri — Sun, 06 Sep 2026 15:41:18 +0000

Humanistic Architecture Learning to See Problems Differently

I had the opportunity to attend a class called “Humanistic Architecture” by Mr. Chakrit Riddhagni.

The class was about applying humanistic principles to software development.

Before talking about what I learned, I would like to share a little about my own perspective on software development.

Personally, I have a quote and a belief about software development: it is both a science and an art.

I see software development as something that has an artistic side, while being grounded in logic, with almost endless possibilities.

“Crafting software requires artistry, guided by imagination, grounded in logic, endlessly enduring.”

This quote has been one of my inspirations since I started working as a developer.

What I mean by this is that I have always liked thinking about software development as a kind of literature. We are not simply writing code. We are solving problems. We are developing and creating something to solve a problem that either we or our customers are facing.

Because of this, as a developer, I naturally work with problems every day.

But… I never really thought much deeper about what a “problem” actually is.

Usually, when we solve a problem for a customer or develop software for them, we receive an issue or a scope of work and then start working on it.

We know that something is a problem, so we focus on solving it.

But we do not always stop and ask

What is the actual structure of this problem ?
Is this really the problem?
And does the solution we are building actually address the problem we are trying to solve?

That changed when I attended the “Humanistic Architecture” class.

One of the biggest things I gained from this class was a new perspective and a set of tools for defining what a “problem” really is.

Anatomy of a Problem

We can look at a problem through the Anatomy of a Problem, which consists of three parts:

Current State — where we are now
Gap — the difference between where we are and where we want to be
Desired State — where we want to go

The Current State is our situation at this moment.

The Desired State is the situation or outcome we want to reach.

And the Gap is the space between them — the part that needs to be changed in order to move from the Current State to the Desired State.

Beyond understanding this structure, I also learned more about how to find solutions that address the root cause of a problem as much as possible through psychological models.

One of them is Satir's Iceberg Model, where one of the most important concepts is Yearning — our deep desire, motivation, or inner need.

Another model is the 3 Centers Model

Heart, Head, and Body

Heart — using our feelings and values to determine direction
Head — using our thinking to make plans
Body — taking action and putting those plans into practice

The Anatomy of a Problem, together with these two models, became the main tools I learned for analyzing problems and finding solutions.

And I don't think these tools are useful only for software development.

I can also see how they can be applied to everyday life and many other situations.

For example, whenever we are not satisfied with our Current State and have a Desired State that we want to reach, a Gap exists between them.

And that Gap is where the problem lies.

This simple way of looking at problems has changed the way I think.

Overall, this class really resonated with me.

It is something that I can actually apply in my work, and I believe it will also be useful in my everyday life.

After coming back to work, I don't think I can look at problems in exactly the same way anymore.

Instead of simply seeing an issue and thinking about how to fix it, I will probably have this structure in my head first:

Current → Gap → Desired

And somehow, I think that's the biggest thing I took away from this class. 😆

Akkarapon Phikulsri

Sep 6

The Anatomy of a Problem

#humanistic #problemsolving #softwareengineering #humanisticarchitecture

4 min read

The Anatomy of a Problem

Akkarapon Phikulsri — Sun, 06 Sep 2026 13:42:12 +0000

I recently had the opportunity to attend a class called “Humanistic Architecture” by Mr.Chakrit Riddhagni. The class was about applying humanistic principles to software development.

Before I talk about what I learned, I want to share a little about my own perspective on software development.

Personally, I have a quote and a belief that I have carried with me throughout my career: software development is both a science and an art.

I see software development as something that has an artistic side, while being grounded in logic, with almost endless possibilities.

“Crafting software requires artistry, guided by imagination, grounded in logic, endlessly enduring.”

This quote has been one of my inspirations since I started working as a developer.

Because of this, being a developer means that we naturally work with problems every day.

But… I never really thought much deeper about what a “problem” actually is.

Usually, when we work on a problem for a customer or develop software for them, we receive an issue or a scope of work and then start working on it. We know that something is a problem, so we focus on solving it.

However, we do not always stop and think about the structure of the problem.

Is this really the problem?

What is actually causing it?

And does the solution we are building really address the problem we are trying to solve?

That changed when I attended the “Humanistic Architecture” class.

One of the biggest things I took away from the class was a new perspective and a set of tools for defining what a “problem” really is.

We can look at a problem through the Anatomy of a Problem, which can be divided into three parts:

Current State — where we are now
Desired State — where we want to be
Gap — the difference between the current state and the desired state

The Gap is where we need to focus in order to move from the Current State toward the Desired State.

Beyond understanding the structure and components of a problem, another important thing I learned was how to find solutions that address the root cause of a problem as effectively as possible.

The class introduced ways of looking at problems through psychological models, which helped me understand that solving a problem is not always about simply fixing what we can see on the surface.

Sometimes, we need to understand the people, their behaviors, their expectations, and the reasons behind the problem before we can design a solution that truly solves it.

And I think this is a perspective that can be very valuable for software developers.

Because sometimes, the hardest part of solving a problem is not writing the code. It is understanding what the real problem is.

The Anatomy of a Problem

The first topic we learned was “The Anatomy of a Problem,” which is about understanding the structure of a problem.

A problem can be divided into three parts:

Current State > where we are right now
Gap -> the difference between where we are and where we want to be
Desired State -> where we want to go

Current State is the situation we are in at the moment.

Desired State is the situation or goal that we want to reach.

Gap is the space or difference between the Current State and the Desired State. It represents what needs to change in order to move from where we are now to where we want to be.

For example:

Current State: I cannot sleep.
Gap: I either do not sleep, or I try to sleep but cannot fall asleep.
Desired State: I can sleep well.

This way of thinking may look simple, but it gives us a useful framework for looking at problems.

Instead of simply saying “I cannot sleep, so I need a solution,” we can step back and ask:

Where am I now? Where do I want to be? And what is preventing me from getting there?

I think this is an interesting way to start understanding a problem before jumping directly into finding a solution.

Whenever we are not satisfied with our current state and have a place we want to reach, a gap will exist between the two.

That gap is exactly where we need to focus our effort to solve the problem, because the problem already exists.

Before learning about “The Anatomy of a Problem,” I started with an exercise based on a simple but interesting question

“What makes us, as human beings, get up and take action to solve a problem?”

From my perspective, human beings always have needs or wants that they want to fulfill in order to feel complete. When things do not go as we expect, we experience disappointment. As a result, we usually look for a method or a tool to fill that gap or solve the problem in a way that we believe is appropriate.

After learning about “The Anatomy of a Problem,” I started to understand and reflect on this idea more deeply.

“A problem is a thing or situation that we cannot handle or solve at that moment.”

Very often, when we solve one problem, new problems appear. In fact, there are often other problems hidden inside a problem.

“Every problem or solution can always create new problems.”

In other words, every problem has a solution, and every solution can bring new problems.

However, a problem may not exist if we are satisfied with our Current State. When we are satisfied with where we are, there is no Desired State that we need to reach. Therefore, there is no Gap between the two.

So, this leads me to the following understanding:

“The problem is the gap between the Current State and the Desired State.”

Git - Commit Message Format

Akkarapon Phikulsri — Sun, 06 Sep 2026 07:29:58 +0000

Introduction

The commit message is something we write very frequently during the development process. It summarizes the changes made after completing a section or feature. It's important to keep commit messages clean and easy to read. Following best practices and using a pattern consistent with your team's or company's style is crucial. Here,

I will show you a pattern that helps structure and organize commit messages effectively, making them easy to read and understandable :)

Pattern

Each commit message consists of a header, a body, and a footer. The header has a special format that includes a type, a scope, and a subject:

<type>(<scope>): <subject>
<BLANK LINE>

<body>
<BLANK LINE>
<footer>

The header is mandatory and the scope of the header is optional.

Example

fix: remove unused dependency lodash.camelcase

Any line of the commit message cannot be longer than 100 characters. This allows the message to be easier to read on GitHub as well as in various git tools.

the component of message format are type, scope, subject, body and footer

Type
Must be one of the following example below

feat: A new feature.
fix: A bug fix.
docs: Documentation only changes.
style: Changes that do not affect the meaning of the code (white-space, formatting, missing semi-colons, etc).
refactor: A code change that neither fixes a bug nor adds a feature.
perf: A code change that improves performance.
test: Adding missing tests.
chore: Changes to the build process or auxiliary tools and libraries such as documentation generation.
ci: Changes to our CI configuration files and scripts.
build: Changes that affect the build system or external dependencies (example scopes: gulp, broccoli, npm).
revert: Reverts a previous commit.
wip: Work in progress. Commit that may be removed later.

but some time i just have no idea for prefix message usally i use are feat and fix haha

Scope
The scope is optional and could be anything specifying the place of the commit change. For example nsis, mac, linux, etc.

Subject
The subject contains a succinct description of the change:

Use the imperative, present tense: change not changed nor changes.
Don't capitalize the first letter.
No dot (.) at the end.

Body
Just as in the subject, use the imperative, present tense: "change" not "changed" nor "changes".
The body should include the motivation for the change and contrast this with the previous behavior.

Footer
The footer should contain any information about Breaking Changes and is also the place to reference GitHub issues that this commit Closes. Breaking Changes should start with the word BREAKING CHANGE: with a space or two newlines. The rest of the commit message is then used for this.

Best Practices

Write Meaningful Commit Messages: Ensure that each commit message clearly conveys what the change is and why it was made. This is crucial for maintaining a clear project history.
Keep Commits Small: Each commit should represent a single logical change. This makes it easier to review changes and revert them if necessary.
Use Branches: For each new feature or bug fix, create a new branch. This helps in keeping the main branch stable and allows for easier collaboration.
Squash Commits: Before merging a feature branch into the main branch, consider squashing commits to ensure a clean and concise project history.
Review Before Commit: Review your changes before committing. Ensure that your code adheres to the project's coding standards and passes all tests.
Automate Testing: Set up automated tests to run on each commit to ensure that changes do not introduce new bugs.
Follow a Consistent Style: Adhere to the established style guide for the project to maintain consistency across the codebase.

How ever
In the AI era, we normally ask AI to generate the commit message. It would be better if we provide a commit message pattern for the AI agent. I think it would be better to make commit history easier to read, not just dump messages that have no prefix or too much noise

Reference

FreeCodeCamp. (2022, March 2). How to write better Git commit messages. FreeCodeCamp. https://www.freecodecamp.org/news/how-to-write-better-git-commit-messages/
Beams, C. (n.d.). How to write a Git commit message. Retrieved July 16, 2024, from https://cbea.ms/git-commit/
2my. (2021, October 30). Git conventional commits. 2my Blog. https://blog.2my.xyz/2021/10/30/git-conventional-commits/
Akkaraponph. (n.d.). Commit messages. GitHub. Retrieved August 5, 2024, from https://github.com/akkaraponph/commit-messages

PostgreSQL Cheat Sheet: Investigating and Terminating Database Blocking Sessions

Akkarapon Phikulsri — Fri, 28 Aug 2026 04:07:47 +0000

If you run PostgreSQL in production for a long time, you will probably see this problem at some point:

Queries start waiting.
Connections increase.
The database becomes slower.
Connection usage gets close to max_connections.

One common cause is a session that is blocking other sessions.

A common example is a connection in the idle in transaction state. The application started a transaction but did not COMMIT or ROLLBACK it.

In this article, we will use pg_stat_activity and several PostgreSQL functions to investigate these problems.

We will also look at when to use:

pg_cancel_backend()
pg_terminate_backend()
pg_blocking_pids()

The goal is not only to kill a bad session. The goal is to understand why it happened and prevent it from happening again.

Why Can PostgreSQL Sessions Block Each Other?

PostgreSQL uses MVCC (Multi-Version Concurrency Control) and locking to support many users at the same time.

For example:

BEGIN;

UPDATE orders
SET status = 'PROCESSING'
WHERE id = 100;

The transaction may hold locks until it finishes with:

COMMIT;

or:

ROLLBACK;

The problem happens when the application starts a transaction but never finishes it.

The connection may then appear as:

idle in transaction

The session is not running a query anymore, but the transaction is still open.

This can cause several problems:

Locks may still be held.
Other queries may have to wait.
Old transaction snapshots may stay open.
VACUUM may not be able to clean some dead rows.
Tables may become larger over time.

This is why it is important to understand the difference between:

active

and:

idle in transaction

An active session is currently running a query.

An idle in transaction session is waiting for the client, but its transaction is still open.

Step 1: Inspect All Non-Idle Sessions

I normally start an investigation with this query:

SELECT
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

This removes normal idle connections from the result.

You can now see sessions that are:

running queries,
waiting,
or idle inside a transaction.

The application_name field is very useful when you have many services.

For example, your PostgreSQL connection may include:

application_name=order-service

Then you may see something like:

pid     application_name   state
12345   order-service      active
12346   payment-service    idle in transaction

This is much easier than trying to guess which application owns a PID.

For Go, Node.js, or other microservice systems, I strongly recommend setting application_name for every service.

Step 2: Use a Quick Triage Query

During an incident, sometimes you do not need every column.

You just want to quickly see what is happening.

Use:

SELECT
    pid,
    age(clock_timestamp(), query_start),
    state,
    query
FROM pg_stat_activity
WHERE state <> 'idle';

This shows:

PID
query age
session state
query text

I use this kind of query as a quick first check.

For example:

PID 1596183
State: active
Duration: 00:08:32
Query: SELECT ...

Now you know that one query has been running for more than eight minutes.

The next question is:

Is this query really slow, or is it waiting for another session?

That is an important difference.

Step 3: Find Idle-in-Transaction Sessions

To check only sessions that are idle inside a transaction:

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    state_change,
    now() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

Pay special attention to:

xact_start

This tells you when the transaction started.

A transaction that has been open for a few seconds may be normal.

A transaction that has been open for 30 minutes or several hours is much more suspicious.

Do not immediately kill every idle in transaction connection.

First check:

Which application created it?
How long has the transaction been open?
Is this normal for the application?
Is it blocking another session?

Step 4: Terminate an Idle Transaction

If you confirm that a session is stuck and should not still be open, you can terminate it.

For one PID:

SELECT pg_terminate_backend(1596183);

This closes the PostgreSQL session.

PostgreSQL will roll back its open transaction and release its locks.

For old idle transactions, you can also use a controlled query like:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '5 minutes'
  AND pid <> pg_backend_pid();

This example only terminates sessions that have been idle inside a transaction for more than five minutes.

The condition:

pid <> pg_backend_pid()

prevents the query from terminating your own PostgreSQL session.

Be careful with this command in production.

It is usually better to filter by things such as:

datname

usename

or:

application_name

For example:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND application_name = 'order-service'
  AND state_change < now() - interval '5 minutes'
  AND pid <> pg_backend_pid();

This is much safer than terminating every idle transaction in the cluster.

Step 5: Cancel a Running Query

Sometimes the connection itself is fine, but one query is taking too long.

In that case, try:

SELECT pg_cancel_backend(1596183);

pg_cancel_backend() stops the current query but keeps the database connection alive.

This makes it a good first choice for things like:

a very expensive SELECT,
a report query,
an accidental full-table operation,
or a query that a user wants to cancel.

Think about it like this:

pg_cancel_backend()
        |
        v
Stop the current query
        |
        v
Keep the connection

`pg_cancel_backend()` vs `pg_terminate_backend()`

These two functions are similar, but they solve different problems.

Function	What happens	Good for
`pg_cancel_backend(pid)`	Stops the current query but keeps the connection	Long-running or unwanted active queries
`pg_terminate_backend(pid)`	Closes the whole database session	Stuck sessions, idle transactions, serious blocking problems

My normal approach is:

Active bad query
     |
     v
pg_cancel_backend()
     |
     | still causing problems
     v
pg_terminate_backend()

For an idle in transaction session, pg_cancel_backend() normally does not solve the problem because there is no active query to cancel.

In that situation, you may need:

pg_terminate_backend()

Step 6: Find Who Is Blocking a Query

A long-running query is not always slow.

Sometimes it is simply waiting for another transaction.

PostgreSQL provides a very useful function:

pg_blocking_pids()

For example:

SELECT
    pid,
    usename,
    application_name,
    state,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

You may get something like:

pid       blocking_pids
-----------------------
20002     {20001}

This means:

PID 20001
    |
    | blocks
    v
PID 20002

Do not immediately kill PID 20002.

It is the blocked session, not necessarily the real problem.

You should investigate PID 20001.

Finding the Root Blocker

A production problem can be more complicated:

Session A
   |
   | blocks
   v
Session B
   |
   | blocks
   v
Session C

If you kill Session B, Session C may improve, but you have not fixed the real source of the problem.

The real blocker is:

Session A

That is why pg_blocking_pids() is useful.

You want to follow the blocking chain until you find the session that is not waiting for another backend.

That session is usually the root blocker.

Investigate that session first.

Step 7: Check Which Database Uses the Most Connections

If your PostgreSQL server contains several databases, check how connections are distributed:

SELECT
    datname,
    count(*)
FROM pg_stat_activity
GROUP BY datname
ORDER BY count(*) DESC;

Example:

datname        count
-------------  -----
order_prod       82
notification     15
postgres          3

Now you know where most connections are being used.

If one database has a very high number, investigate:

application connection pool size,
PgBouncer settings,
connection leaks,
long-running queries,
idle transactions.

A high connection count does not always mean PostgreSQL itself is the problem.

The application may simply be opening too many connections.

Step 8: Check Connection Headroom

You should also check how close PostgreSQL is to its configured connection limit.

SELECT
    (SELECT count(*) FROM pg_stat_activity) AS used_connections,
    (
        SELECT setting::int
        FROM pg_settings
        WHERE name = 'max_connections'
    ) AS max_connections;

For example:

used_connections | max_connections
-----------------+----------------
87               | 100

You are now using:

87 / 100

connections.

That means there is very little room left.

This becomes dangerous because when an incident happens, you may not even be able to open another connection to investigate the problem.

For production systems, I normally monitor connection usage before it gets close to the maximum.

Preventing Idle Transactions

Manually killing sessions is useful during an incident.

But it is not the real fix.

PostgreSQL can automatically terminate connections that stay idle inside a transaction for too long.

For example:

ALTER DATABASE mydb
SET idle_in_transaction_session_timeout = '2min';

Now PostgreSQL will close sessions that remain idle inside a transaction longer than the configured timeout.

You should choose the timeout based on your workload.

For example:

30 seconds

may work for a fast OLTP API.

While:

5 minutes

may be safer for applications with longer business operations.

Do not copy a timeout value without understanding your system first.

Protect Against Long-Running Queries

You can also use:

statement_timeout

For example:

ALTER DATABASE mydb
SET statement_timeout = '30s';

This prevents individual SQL statements from running forever.

However, be careful.

A timeout that is good for API requests may be too short for:

reports,
migrations,
ETL jobs,
batch processing.

Different applications may need different timeout values.

Log Lock Problems

Another useful PostgreSQL setting is:

log_lock_waits

You can enable it with:

ALTER SYSTEM SET log_lock_waits = on;

PostgreSQL can then log queries that wait too long for a lock.

This works together with:

deadlock_timeout

For example:

ALTER SYSTEM SET deadlock_timeout = '1s';

This makes lock problems much easier to investigate because PostgreSQL logs useful information when sessions wait for locks.

Remember to reload the configuration when required.

Fix the Problem in the Application

Database commands help during an incident.

But if idle in transaction keeps happening, the real bug is usually inside the application.

For example, in Go:

tx, err := db.Begin(ctx)
if err != nil {
    return err
}

defer tx.Rollback(ctx)

Then later:

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

The important idea is simple:

BEGIN
  |
  +---- success ----> COMMIT
  |
  +---- error ------> ROLLBACK

Every transaction must have a clear ending.

This is especially important when using libraries such as:

pgx
GORM
database/sql
Node.js pg

Every error path should either commit or roll back the transaction correctly.

Connection Pools Matter Too

Also check your connection pool configuration.

For example, imagine you have:

10 services

and every service allows:

20 connections

Your applications may try to create:

10 × 20 = 200 connections

But PostgreSQL may only allow:

max_connections = 100

That architecture will eventually create problems.

Tools such as PgBouncer can help reduce the number of PostgreSQL backend connections, but they do not replace correct transaction handling.

You still need to:

commit transactions,
roll back transactions,
set reasonable pool limits,
monitor connection usage.

My Production Troubleshooting Flow

When PostgreSQL starts becoming slow, I normally think about the problem in this order:

PostgreSQL is slow
        |
        v
Check pg_stat_activity
        |
        v
Are queries active or idle in transaction?
        |
        +------ idle in transaction
        |             |
        |             v
        |      Check transaction age
        |             |
        |             v
        |      Check if it is blocking
        |             |
        |             v
        |      pg_terminate_backend()
        |
        +------ active
                      |
                      v
              Check blocking_pids
                      |
              +-------+-------+
              |               |
           blocked         not blocked
              |               |
              v               v
         Find blocker    Investigate query
                              |
                              v
                      pg_cancel_backend()

The important point is:

Do not kill a PostgreSQL PID only because a query has been running for a long time.

First understand what that PID is doing.

Ask:

Is it running?

Is it waiting?

Is it blocking?

Is it being blocked?

Is it idle inside a transaction?

Then choose the correct action.

Useful Commands Summary

Check non-idle sessions:

SELECT
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

Find idle transactions:

SELECT
    pid,
    application_name,
    xact_start,
    now() - xact_start AS transaction_age,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Find blocked sessions:

SELECT
    pid,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Cancel a query:

SELECT pg_cancel_backend(1596183);

Terminate a connection:

SELECT pg_terminate_backend(1596183);

Check connections per database:

SELECT
    datname,
    count(*)
FROM pg_stat_activity
GROUP BY datname
ORDER BY count(*) DESC;

Check connection limit:

SELECT
    (SELECT count(*) FROM pg_stat_activity) AS used_connections,
    (
        SELECT setting::int
        FROM pg_settings
        WHERE name = 'max_connections'
    ) AS max_connections;

Final Thoughts

When PostgreSQL has a connection or blocking problem, pg_stat_activity is one of the first places I check.

But the most important skill is not knowing how to kill a PID.

It is knowing which PID should be killed and why.

A blocked query may only be a victim.

An idle in transaction session may be the real blocker.

And a high connection count may be caused by an application pool instead of PostgreSQL itself.

A good production investigation should therefore follow this process:

Observe
   ↓
Find the blocker
   ↓
Understand the transaction
   ↓
Mitigate the incident
   ↓
Fix the application
   ↓
Add protection and monitoring

Commands like pg_cancel_backend() and pg_terminate_backend() are useful emergency tools.

But the long-term solution is better transaction handling, good connection pool limits, proper timeouts, and enough monitoring to find the problem before production users notice it.

Fixing "GOPROXY list is not the empty string, but contains no entries" in Go

Akkarapon Phikulsri — Wed, 22 Jul 2026 10:05:57 +0000

If you've ever run go mod tidy and been greeted by a wall of red text ending in:

go: todo-services imports
        github.com/99designs/gqlgen: GOPROXY list is not the empty string, but contains no entries

...you're not alone. This error looks scary because it repeats for every single import in your project, but the actual fix takes about 30 seconds. Here's what's going on and how to solve it.

What This Error Actually Means

Go resolves modules through GOPROXY, an environment variable that tells the toolchain where to fetch dependencies from. The error message is oddly specific:

"GOPROXY list is not the empty string, but contains no entries"

This means GOPROXY is set to something, but once Go parses it, there are zero usable proxy URLs in it. Common causes:

A stray comma or pipe with no actual URL (GOPROXY=, or GOPROXY=|)
GOPROXY=off, which explicitly disables all proxying
A shell-level override with a blank or whitespace value that shadows your real Go config
A corporate/custom proxy URL that no longer resolves

This is especially common on Windows with Git Bash (MINGW64), where environment variables inherited from Windows user/system settings can silently override what Go has configured in its own env file.

Step-by-Step Fix

1. Check for a shell-level override

Environment variables set in your current shell session take priority over Go's own config file. Check what's actually there:

echo "GOPROXY=[$GOPROXY]"

If this prints GOPROXY=[], GOPROXY=[ ], or something malformed, that's your problem — the shell is passing Go a broken value.

2. Clear the session override

unset GOPROXY

This drops the bad value for the current session and lets Go fall back to its own configuration.

3. Persist a valid proxy in Go's config

This writes directly to Go's env file (%USERPROFILE%\AppData\Roaming\go\env on Windows, ~/.config/go/env on Linux/macOS):

go env -w GOPROXY=https://proxy.golang.org,direct

The ,direct fallback tells Go to bypass the proxy and fetch directly from source control if a module isn't available there — a good safety net.

If you're behind a corporate network or VPN, swap in your internal proxy instead:

go env -w GOPROXY=https://your-company-proxy.example.com,direct

4. Sanity-check related settings

go env GOPROXY GOFLAGS GOSUMDB

Expected healthy values:

Variable	Expected value
`GOPROXY`	`https://proxy.golang.org,direct` (or your internal proxy + `,direct`)
`GOFLAGS`	Empty, unless you intentionally use flags like `-mod=mod`
`GOSUMDB`	`sum.golang.org` (default), unless disabled for a reason

5. Clear Go's caches if the problem persists

Sometimes a bad proxy state gets baked into cached build artifacts or module downloads. If steps 1–4 didn't fully resolve things, nuke the caches:

go clean -cache -testcache -modcache

Flag	Clears
`-cache`	Compiled build cache
`-testcache`	Cached test results
`-modcache`	Downloaded modules (`$GOPATH/pkg/mod`)

This forces a full re-download of every dependency on your next build, using your now-corrected proxy settings.

6. Retry

go mod tidy

At this point, all those GOPROXY list is not the empty string... lines should be gone, replaced by normal module resolution output for todo-services.

Windows/MINGW64 Gotcha

If GOPROXY keeps reverting to a bad value in new terminal sessions, the override probably lives in Windows System Properties → Environment Variables, not just in go env. Go's own persisted config won't help if Windows keeps re-injecting a bad value into every new shell. Check it directly:

cat "$USERPROFILE/AppData/Roaming/go/env"

Quick Reference Checklist

echo "GOPROXY=[$GOPROXY]" — no stray/invalid shell override
unset GOPROXY — if a bad session override exists
go env -w GOPROXY=https://proxy.golang.org,direct — sets a valid persistent proxy
go env GOPROXY GOFLAGS GOSUMDB — confirms sane values
go clean -cache -testcache -modcache — if issues persist
go mod tidy — succeeds after the above

Command Cheat Sheet

go version                            # Confirm installed Go version
go env                                # Print all Go environment settings
go env GOPROXY                        # Print a specific setting
go env -w KEY=VALUE                   # Persist a setting to Go's env file
go env -u KEY                         # Unset a persisted setting (revert to default)
go mod tidy                           # Sync go.mod/go.sum with imports
go clean -cache -testcache -modcache  # Clear build, test, and module caches

If this saved you a headache, drop a — and if you've hit a different flavor of this error, I'd love to hear about it in the comments.

GitHub Spark - A First Look at GitHub’s New Developer Tool

Akkarapon Phikulsri — Sun, 15 Mar 2026 07:30:05 +0000

GitHub Spark is an intuitive way to bring your ideas to life.

Note: GitHub Spark is available to users on GitHub Copilot Pro+ and Enterprise plans.

What is GitHub Spark?

GitHub Spark is an AI-powered tool that helps you "spark" ideas into functional "sparks" (micro-apps) quickly. You don't need to write every line of code; instead, you describe your idea in plain English. Just "vibe it to life," and the AI builds the app for you.

You can get started with GitHub Spark here.

Quick prompt

We will quickly try out the cash flow app for managing personal finances in React Native. I started the process with a simple prompt, and i got some code and a quick preview

Referencing Repositories

As you can see in the image below, I can refer to my old repositories, and GitHub Spark will attempt to read the context of that code.

This makes it incredibly powerful for building on top of your existing work.

According to the picture, this is about the suggestions section from Spark, which is where you can add feature suggestions.

Comparison with Lovable

In my opinion, it feels similar to Lovable

lovable.dev

However, because Spark is native to GitHub, we can easily reference other repositories as context.

Playground

We can also easily quick-edit the code or try to change the line of code by hand using a quick editor

I liked the modal that told me what went wrong and showed the button to tell the agent to "Fix all" right away.

We can see the pair window of code and the preview section on the right side while AI is fixing.

Theme

It's easy for us to change the theme of our app; all we have to do is click and choose a color preset.

After that...

I just clicked a preset to quickly render and change the entire theme to a new color. We can also see how the theme changes based on adjustments to typography, color, and appearance.

Data Mockup

Table
JSON

We can quickly mock the data based on the table UI and also give JSON information.

Assets

The Assets tab is essentially your project's. We can drop files there that Spark needs to reference during the build or at runtime, like the logo image for our website, and can iterate to using them later.

Visuals: Logos, custom icons, or high-res background banners.
Data JSON or CSV files (like your personal spreadsheets).
Knowledge PDFs or text files that give the AI specific instructions or context.

Turning Assets into Action

The real power of Spark happens when you move from uploading to iterating. You can take old data and give it a modern "face" in seconds.

For my review, I tested this by bringing in

Brand Assets: A custom logo.png and hero banner. jpg.

for the logo, we can iterate ai to redesign landing page with logo like

Legacy Data A 5-year-old income-expense dataset from my GitHub.

akkaraponph / income-expense

for fundamental data science

Datasets-Income-Expense

For fundamental data science

Simple Datasets

Repo. Directory Structure

├── Datasets-Income-Expense
│   ├── English
│       ├── Income_Expense_lacakp_Q1_2564_Eng.csv 
│       └── Income_Expense_lacakp_Q2_2564_Eng.csv
│   ├── Thai
│       ├── Income_Expense_lacakp_Q1_2564_Thai.csv 
│       └── Income_Expense_lacakp_Q2_2564_Thai.csv

Exsamples

Date	Income	Expense	Category	Where	Payment Method
1-Jan-21	3000	owe	online	netbank	primary
1-Jan-21	3500	income	home	cash	primary
1-Jan-21	2800	rent fee, expense	apartment	cash	primary
1-Jan-21	40	water bill, expense	apartment	cash	primary
1-Jan-21	65	electricity bill, expense	apartment	cash	primary
1-Jan-21	853	music, expense	online	netbank	tertiary
1-Jan-21	1600	income, entertainment	online	cash	tertiary
3-Jan-21	90	car fare, expense	none	cash	primary
3-Jan-21	35	breakfast, expense	market	cash	primary
3-Jan-21	30	dinner, expense	market	cash	primary
3-Jan-21	10	expense	shop	cash	primary
3-Jan-21	40	expense	apartment	cash	secondary
4-Jan-21	65	breakfast, expense	Seven-Eleven	cash	primary
4-Jan-21	45	expense, eggs	Seven-Eleven	cash	primary
4-Jan-21	15	drinking water, expense	apartment	cash	primary
4-Jan-21	30	food, dinner, expense

…

View on GitHub

After dropping the files, I just iterate the prompt like
base on csv files give me mockup base on those data

The agent looked at the headers and quickly built a working dashboard without having to do any manual mapping or coding. It's very satisfying to see a static file turn into an interactive app component right in front of your eyes.

Publish

We can make our spark public (need to sign in with GitHub to view) and also change the visibility setting.

Is GitHub Spark the Future of Rapid Prototyping?

In summary, GitHub Spark is a game changer for bridging the gap between a raw idea and a functional reality; it empowers creators to take the concepts in their heads and manifest them into tangible "Sparks" using nothing more than natural language and some simple prompts.

Why it stands out

Speed to market Whether you're a developer or a visionary, you can build a working product or mockup prototype at an incredible pace.
Intuitive Customization The ability to tweak themes, typography, and data mockups with a few clicks makes it accessible without sacrificing control.
The GitHub Advantage The killer feature is the native ecosystem. Being able to reference your existing private repositories for context is a superpower that other tools simply can't match

If you are already a fan of tools like Lovable but prefer staying within the GitHub ecosystem, Spark is a fantastic alternative. At the current price point of $39 USD (GitHub Copilot Pro+), the value you get—combining AI-assisted coding with this rapid prototyping environment—is exceptionally comprehensive.

GitHub Spark isn't just a toy; It's a high-velocity engine for the next generation of builders :)

DeltaView - AI-Driven JSON Diff Viewer Built with GitHub Copilot

Akkarapon Phikulsri — Sat, 14 Feb 2026 11:40:32 +0000

This is a submission for the GitHub Copilot CLI Challenge

DeltaView - AI-Driven Diff Viewer Built with GitHub Copilot

What I Built

DeltaView is a powerful web-based JSON diff viewer that showcases AI-driven development using GitHub Copilot. The application parses JSON inputs, generates structural diffs, and displays them with beautiful syntax highlighting and side-by-side comparison views.

Key Features:

Intelligent JSON Diff Engine - Recursively compares JSON structures and categorizes changes (added, removed, modified)
Real-time Diff Visualization - Side-by-side comparison with color-coded changes
Syntax Highlighting - Professional code display with proper formatting
Error Handling - Graceful handling of invalid JSON and edge cases
Type-Safe Implementation - Built with TypeScript for reliability
Responsive UI - Modern, clean interface built with React, TailwindCSS, and shadcn/ui

Technology Stack:

Frontend: React + TypeScript
Styling: TailwindCSS + shadcn/ui components
Build Tool: Bun (ultra-fast JavaScript runtime)
Development Methodology: AI-driven architecture with markdown specifications

🎬 Features & Demo

Live demo available at: deltaview.refactorroom.com

DeltaView - All-in-One Developer Toolkit | JSON Converters, Generators, Editor & Schema Converters

All-in-one developer toolkit: JSON converters, generators, online editor, and schema converters. Compare JSON with visual diffs, format & validate, visualize as graphs or tables. Privacy-first, free forever.

deltaview.refactorroom.com

1. 📝 JSON Compare Input Panel

Easily input two JSON objects to compare. The interface features:

Side-by-side JSON input panels
Format buttons to validate and beautify JSON
Load example data for quick testing
Real-time error validation
Support for large JSON structures

2. 🔍 JSON Comparison Results

Get detailed, line-by-line diff visualization:

Orange highlights - Removed or old values
Purple highlights - Added or new values
Character-level precision - See exactly what changed within each line
Copy buttons - Easily copy individual sides
Expandable view - Toggle between split and fullscreen modes
Line numbers for easy reference

Key Comparison Features:

Recursive diff of nested objects
Array element tracking
Property addition/removal detection
Value modification detection
Type change detection

3. 📊 JSON Graph Visualization

Visualize JSON structure as an interactive graph:

Node-based representation of objects and arrays
Visual relationship mapping
Interactive node exploration
Zoom and pan controls
Perfect for understanding complex JSON hierarchies

Graph Features:

Auto-layout for clarity
Color-coded node types
Expandable/collapsible nodes
Connection visualization
Ideal for API responses and config files

4. 📋 Table View

Convert JSON data into structured table format:

Automatic table generation from JSON objects
Column headers from object keys
Clean, organized data presentation
Sortable columns
Perfect for data-driven JSON

Table Features:

Auto-detect and format columns
Handle nested objects gracefully
Array data visualization
Easy copy and export functionality
Responsive column sizing

5. ✨ JSON Formatter

Format, validate, and beautify JSON with professional formatting:

One-click JSON formatting
Automatic indentation (2 spaces)
Syntax highlighting
Error detection with line numbers
Copy formatted output

Formatter Capabilities:

Validate JSON syntax
Detect and report errors
Pretty-print with proper indentation
Remove unnecessary whitespace
Support for large files

🌐 Technology & Performance

Instant Validation - Real-time error checking
Responsive Design - Works perfectly on desktop, tablet, and mobile
Dark/Light Mode - Comfortable viewing in any lighting
Fast Processing - Handles large JSON files efficiently
Type-Safe - Built with TypeScript for reliability

My Experience with GitHub Copilot CLI

How I Used GitHub Copilot CLI

Architecture Definition - Used natural language in docs/architecture.md to define the diff engine specifications, component structure, and data contracts. GitHub Copilot CLI helped generate detailed TypeScript interfaces.
Component Generation - Leveraged gh copilot in the terminal to:
- Generate React component scaffolds with proper TypeScript types
- Create utility functions following project conventions
- Generate UI component wrappers for shadcn/ui integration
Complex Logic Implementation - Used Copilot Chat for:
- Implementing the recursive JSON diff algorithm
- Handling edge cases and data type comparisons
- Creating proper error handling patterns
Code Quality & Consistency - Copilot CLI suggested:
- Linting fixes and TypeScript improvements
- Best practice patterns for React hooks and state management
- Proper accessibility attributes for UI components

Impact on Development

Productivity Gains:

⚡ 60-80% faster development - Boilerplate and repetitive code generation done by AI
🎯 Consistency - All components follow the same patterns defined in agent markdown files
📚 Better Documentation - Architecture markdown files serve as both specs and living documentation
🐛 Fewer Bugs - Type-safe implementations and consistent error handling patterns

Developer Experience:

Natural language to code conversion eliminated context switching
AI understood project conventions and applied them automatically
Focus shifted from syntax to architecture and user experience
Iterative refinement became faster with AI assistance

Key Learnings

Markdown-First Development - Well-structured architecture docs enable AI to maintain consistency across large codebases
Context is King - Providing clear specifications in agent files dramatically improves code quality
Collaboration Model - Human architects design system, AI implements details, humans review and refine
Scalability - This methodology scales better than traditional development for complex projects

This project demonstrates that modern web applications can be built faster, with higher quality, and better maintainability when humans and AI work together with clear architectural specifications.

🚀 Get Started with GitHub Copilot - Easy, Fun & Productive!

Why GitHub Copilot?

GitHub Copilot transforms your coding experience into a productive conversation with AI! Whether you're building new features, debugging code, or learning new frameworks, Copilot is your 24/7 coding companion that makes development easier, faster, and more enjoyable.

✨ Key Benefits

🎯 Easy to Use - Just install and start coding! Copilot understands natural language and suggests code as you type
⚡ Boost Productivity - Cut development time in half by letting AI handle boilerplate, repetitive tasks, and scaffolding
💡 Learn While You Code - Discover best practices, new APIs, and coding patterns through intelligent suggestions
🎨 Focus on Creativity - Spend less time on syntax, more time on solving problems and building amazing features
🤖 CLI Power - Use gh copilot in your terminal for instant help, command suggestions, and code generation

📦 Super Simple Installation

Getting started with GitHub Copilot is as easy as 1-2-3:

Install the Extension - One click in VS Code or your favorite IDE
Sign In - Connect with your GitHub account
Start Coding - That's it! Copilot is ready to assist you

🎉 Why Developers Love It

"Instead of manually writing everything from scratch, I just describe what I want and Copilot generates it. This cuts out boilerplate and lets me focus on the actual logic and user experience."

Key Features That Make Coding Fun:

Natural Language → Code: Describe what you want in comments, get working code instantly
Context-Aware: Understands your project structure and adapts to your coding style
Multi-Language: Works seamlessly with dozens of programming languages
Terminal Integration: GitHub Copilot CLI for command suggestions and explanations right in your terminal
Real-Time Assistance: Get suggestions as you type, no interruptions to your flow

💪 Productivity Boosters

Generate entire functions from a single comment
Convert ideas into working code in seconds
Automate repetitive coding tasks
Get instant code explanations and documentation
Fix bugs faster with AI-powered suggestions

Ready to supercharge your coding? Get GitHub Copilot today!

🤖 AI-Driven Development with Agent Markdown Files

This project demonstrates a powerful AI-first development methodology using structured markdown documentation to guide AI coding agents. Instead of traditional specification documents, we use agent-optimized markdown files that serve as both documentation and instruction sets for AI assistants like GitHub Copilot.

📋 The Agent MD File Pattern

Our docs/ folder contains markdown files specifically designed for AI agents:

docs/
  ├── agent.md              # Core agent instructions & principles
  ├── architecture.md       # System architecture & structure
  ├── coding-standards.md   # Code quality rules
  ├── prompt.md             # Initial project prompt
  ├── ui-guidelines.md      # UI/UX specifications
  ├── vision.md             # Project scope & constraints
  └── tasks/                # Task breakdown

🎯 Why This Approach Works

Traditional Development:

Write specs → Translate to code → Review → Refactor
High cognitive load on developers
Specs often become outdated
Inconsistent implementation

AI-Driven Development:

Write clear markdown requirements → AI generates code → Human reviews
AI maintains consistency across codebase
Documentation stays synchronized with code
Faster iteration cycles

🔧 How We Use Agent Files

1. Architecture as Code (architecture.md)

Defines the entire system structure, component responsibilities, and technical constraints:

Tech stack decisions (React, TypeScript, shadcn/ui)
File organization and structure
Data contracts and interfaces
Performance constraints
Non-goals and boundaries

AI Agent Benefit: Understands the full context and makes consistent architectural decisions.

2. Agent Instructions (agent.md)

Core principles and rules for the AI coding agent:

Code style preferences
Component patterns to follow
JSON handling rules
UI tone and approach
Error handling standards

AI Agent Benefit: Maintains code quality and follows project conventions automatically.

3. Project Prompt (prompt.md)

The initial project specification in natural language:

Feature requirements
Component structure
UX guidelines
API contracts

AI Agent Benefit: Can regenerate or extend features while staying aligned with original vision.

4. Vision Document (vision.md)

Clear boundaries and constraints:

What the project IS
What the project IS NOT
Scope limitations

AI Agent Benefit: Prevents scope creep and keeps AI focused on core functionality.

✨ Benefits of AI-Driven Development

🚀 Speed

Generate entire components from markdown specifications
Boilerplate elimination – AI handles repetitive code
Parallel development – AI works on multiple files simultaneously

🎯 Consistency

Architectural alignment – AI follows defined patterns
Code style uniformity – Automatic adherence to standards
No drift – Documentation and code stay in sync

📚 Living Documentation

Markdown files are human-readable project docs
AI references them for every code generation
Easy to update and maintain

🧠 Knowledge Transfer

New team members read markdown files
AI onboards instantly with same context
Reduces tribal knowledge problems

🛠️ Practical Workflow Example

Step 1: Manual Project Initialization

Before leveraging GitHub Copilot, we start with a basic project setup. This project was initialized using Bun:

bun init

After running bun init, your project structure is ready:

This creates the initial project structure with:

Basic package.json
TypeScript configuration
Entry point files

Why manual first? This establishes the foundation and project structure that Copilot will then enhance and build upon. You control the tech stack decisions, and AI handles the implementation details.

Step 2: Trust Your Workspace

When you open your project folder in VS Code, you'll need to trust the workspace to enable all features:

Click "Yes, I trust the authors" to allow VS Code and GitHub Copilot to:

Access your project files
Provide context-aware suggestions
Run scripts and tools
Enable full extension functionality

This is a one-time security step that ensures you're comfortable with the code in your workspace.

Step 3: Start Prompting with Copilot

Now you're ready to start working with GitHub Copilot! Open the Copilot chat and start describing what you want to build:

Simply describe your requirements in natural language:

"Create a JSON formatter component following docs/architecture.md"
"Build a diff viewer with syntax highlighting"
"Add error handling for invalid JSON input"

Copilot understands your project context and generates code that fits your architecture!

Step 4: AI Takes Over Development

Once the foundation is set, GitHub Copilot accelerates development:

Scenario: Add a new "Export to CSV" feature

Traditional Approach:

Write feature spec
Manually code the export logic
Create UI components
Write tests
Update documentation
Code review
Refactor

AI-Driven Approach:

Update docs/architecture.md with CSV export requirements
Ask Copilot: "Implement CSV export feature following docs/architecture.md"
AI generates:
- Export logic in lib/export/csv.ts
- UI button component
- Integration with existing panels
- Error handling
- TypeScript types
Human reviews and refines
Documentation already updated (step 1)

Time saved: 60-80% reduction in implementation time

📖 Example: JSON Diff Implementation

Our architecture.md specifies:

## Diff Engine
Must be:
- Structural (parse JSON first)
- Recursive
- Deterministic
- Library-free

type DiffEntry = {
  path: string
  type: "added" | "removed" | "modified"
  oldValue?: unknown
  newValue?: unknown
}

AI Agent reads this and generates:

Complete recursive diff algorithm
Type-safe implementation
Proper error handling
Exact data contract adherence

Developer role: Review logic, test edge cases, refine algorithm

💡 Best Practices for Agent Markdown Files

Be Specific, Not Vague
- ❌ "Make it look nice"
- ✅ "Use monospace font, 2-space indentation, subtle colors (green for added, red for removed)"
Define Data Contracts
- Include TypeScript types in markdown
- Specify exact field names and types
- Show example inputs/outputs
Set Boundaries
- Explicitly state what NOT to do
- Define scope limitations
- Prevent feature creep
Keep It Organized
- One concern per file
- Cross-reference related files
- Use clear headings
Write for Both AI and Humans
- Natural language that humans can read
- Structured format AI can parse
- Examples and counterexamples

🎓 Getting Started with Agent-Driven Development

Create a docs/ folder in your project
Write an architecture.md – Define your system structure
Add agent.md – Specify coding principles and patterns
Create vision.md – Set clear boundaries
Reference these files when working with GitHub Copilot
Iterate – Update markdown files as requirements evolve

🔮 The Future of Development

AI-driven development with agent markdown files represents a paradigm shift:

Documentation-first development
AI as implementation partner
Human as architect and reviewer
Faster, more consistent codebases

This project (deltaview.refactorroom.com) was built almost entirely using this methodology, demonstrating that complex applications can be developed with AI assistance while maintaining high code quality and clear architecture.

🚀 Deploying to Cloudflare Pages

DeltaView is deployed to Cloudflare Pages for fast, global distribution. Here's how to deploy:

Prerequisites

Cloudflare Account - Sign up at cloudflare.com
Wrangler CLI - Install with:

   npm install -g wrangler

Authentication - Run wrangler login to connect your account

Your Current Setup

Your project is configured for Cloudflare Pages with:

Project name: deltaview
Build output: dist/
Build command: bun run build.ts

Deployment Steps

1. Authenticate with Cloudflare

wrangler login

2. Build Your Project

bun run build.ts

This generates the dist/ folder with your static assets.

3. Deploy to Pages

wrangler pages deploy dist

Or deploy to a specific environment:

wrangler pages deploy dist --branch=production
wrangler pages deploy dist --branch=preview

4. Add Custom Domain (via Dashboard)

Go to Cloudflare Dashboard
Select deltaview project
Navigate to Settings → Custom domains
Add your domain (e.g., deltaview.refactorroom.com)

Using Package Scripts

We've provided convenient npm scripts in package.json:

bun run pages:build      # Build the project
bun run pages:deploy     # Deploy to Pages
bun run pages:dev        # Local Pages preview

CI/CD Automatic Deployment

For automatic deployments on every push:

Go to Cloudflare Dashboard
Select deltaview project → Settings → Build and deployment
Connect your GitHub repository
Configure:
- Build command: bun run build.ts
- Build output directory: dist
Push to main branch - deployment happens automatically!

Live Site

Your site is live at:

Pages domain: https://deltaview-ap7.pages.dev
Custom domain: https://deltaview.refactorroom.com (once configured)

Project Setup

To install dependencies:

bun install

To start a development server:

bun dev

To run for production:

bun start

This project was created using bun init in bun v1.2.19. Bun is a fast all-in-one JavaScript runtime.

Comprehensive Research: Audit Log Paradigms & Go/PostgreSQL/GORM Design Patterns

Akkarapon Phikulsri — Sat, 07 Feb 2026 16:16:36 +0000

Audit Log Paradigms
Event Sourcing vs Audit Logging
Real-World Use Cases
Database Design Patterns for Audit Trails
State Transition Tracking
API Audit Logging Best Practices
Go/PostgreSQL/GORM Implementation Patterns
Complete Code Examples

1. Audit Log Paradigms

Core Concepts

Audit logging is the practice of recording changes to data in a system to provide:

Traceability: Who did what, when, and why
Compliance: Meeting regulatory requirements (GDPR, HIPAA, SOC 2, PCI DSS)
Security: Detecting suspicious activities and unauthorized access
Debugging: Understanding system behavior and data evolution
Recovery: Ability to rollback or replay changes

Key Principles

1. Append-Only Structure

Audit logs should NEVER be modified or deleted
All entries are immutable after creation
Use separate tables/storage from operational data

2. Essential Audit Fields

Every audit log entry should capture:

Timestamp: When the action occurred (with timezone)
Actor: Who performed the action (user ID, service account)
Action: What was done (CREATE, UPDATE, DELETE, READ)
Resource: What entity was affected (table name, record ID)
Before/After State: Previous and new values
Context: Additional metadata (IP address, session ID, request ID)

3. Performance Considerations

Audit logging should not significantly impact main operations
Use async/background processing for logging when possible
Implement log rotation and archival strategies
Index strategically for query performance

2. Event Sourcing vs Audit Logging

Comparison Matrix

Feature	Event Sourcing	Audit Log
Purpose	Maintain complete history of state changes as source of truth	Track user actions for security/compliance
Granularity	Very granular, every state change	High-level, significant actions
Structure	Append-only log of immutable events	Append-only log of action records
Events Recorded	Facts that occurred (irrefutable)	Account of actions performed
Reconstruction	Can reconstruct system state by replaying events	Not designed for reconstructing state
Querying	Often requires event replay	Direct querying for audit purposes
System Recovery	Facilitates rollback/recovery by replaying	Primarily for investigation/compliance
Complexity	Higher complexity, careful design needed	Generally simpler, auxiliary feature
Use Case	Core business logic and state management	Compliance, security, debugging

When to Use What?

Use Event Sourcing when:

Business logic depends on complete history
Need to replay events to rebuild state
Domain complexity requires detailed tracking
Audit trail is a critical business requirement

Use Audit Logging when:

Need compliance/security tracking only
Don't need state reconstruction
Want simpler implementation
Audit is secondary concern

Hybrid Approach:
Many systems use event sourcing for critical domains and traditional audit logging for other areas.

3. Real-World Use Cases

1. Financial Services

Scenario: Banking transaction processing

Use Case: Track every transaction, balance change, and access attempt
Requirements:
- Immutable records of all transactions
- 7-10 year retention for compliance
- Millisecond-precision timestamps
- Ability to prove no tampering

Implementation:
- Separate audit database with write-only access
- Cryptographic signatures on audit entries
- Regular backup to immutable storage (S3 Glacier)

2. Healthcare (HIPAA Compliance)

Scenario: Patient record access tracking

Use Case: Track who viewed/modified patient records
Requirements:
- Log every access to protected health information (PHI)
- Capture: user, timestamp, patient ID, action, reason
- Alert on suspicious access patterns
- Retain for 6+ years

Implementation:
- Real-time audit logging on every PHI access
- Automated alerting for unusual patterns
- Encrypted audit logs
- Regular compliance reports

3. E-Commerce Order Management

Scenario: Order lifecycle tracking

Use Case: Track order state transitions from creation to delivery
Requirements:
- Capture state changes: created → paid → shipped → delivered
- Record who authorized changes
- Support dispute resolution
- Enable order reconstruction

Implementation:
- State machine with audit trail
- Transition validation rules
- Rollback capability for specific states

4. API Access Control

Scenario: RESTful API request tracking

Use Case: Monitor API usage, detect abuse, support debugging
Requirements:
- Log API requests: endpoint, method, user, parameters
- Track rate limiting violations
- Capture response status and errors
- Enable analytics and billing

Implementation:
- Middleware-based request/response logging
- Async logging to not block requests
- Log aggregation (ELK stack, Splunk)
- Real-time alerting on anomalies

5. Multi-Tenant SaaS

Scenario: User action tracking across tenants

Use Case: Provide audit trails to enterprise customers
Requirements:
- Per-tenant audit logs
- Customer-accessible audit UI
- GDPR right-to-erasure support
- Role-based audit log access

Implementation:
- Tenant-isolated audit tables or schemas
- Audit API endpoints for customers
- Export functionality (CSV, JSON)
- Data retention policies per tenant

6. Incident Management System

Scenario: Tracking incident status and updates

Use Case: Track incident lifecycle and all updates
Requirements:
- Every status change logged
- Update history with timestamps
- Severity changes tracked
- Automated timeline generation

Implementation:
- Event-sourced incident aggregate
- Rich timeline reconstruction
- Audit log as secondary index
- Real-time status board

4. Database Design Patterns for Audit Trails

Pattern 1: Row Versioning (In-Place Audit)

Concept: Add versioning fields directly to the main table

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    customer_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),

    -- Audit fields
    version INT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by VARCHAR(100) NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMPTZ,
    deleted_by VARCHAR(100)
);

Pros:

Simple to implement
No extra tables
Easy to query current state

Cons:

Loses history of changes
No details on what changed
Can't rollback to previous versions

Pattern 2: Shadow/History Tables

Concept: Separate table mirrors main table with full history

-- Main table (current state)
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number VARCHAR(50) NOT NULL,
    customer_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- History table (all versions)
CREATE TABLE orders_history (
    history_id BIGSERIAL PRIMARY KEY,
    id BIGINT NOT NULL,
    order_number VARCHAR(50),
    customer_id BIGINT,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),

    -- Audit metadata
    operation VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by VARCHAR(100) NOT NULL,

    -- Track what changed
    change_reason TEXT
);

-- Trigger to populate history
CREATE OR REPLACE FUNCTION orders_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO orders_history (id, order_number, customer_id, total_amount, 
                                    status, operation, changed_by)
        VALUES (OLD.id, OLD.order_number, OLD.customer_id, OLD.total_amount,
                OLD.status, 'DELETE', current_user);
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO orders_history (id, order_number, customer_id, total_amount,
                                    status, operation, changed_by)
        VALUES (NEW.id, NEW.order_number, NEW.customer_id, NEW.total_amount,
                NEW.status, 'UPDATE', current_user);
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO orders_history (id, order_number, customer_id, total_amount,
                                    status, operation, changed_by)
        VALUES (NEW.id, NEW.order_number, NEW.customer_id, NEW.total_amount,
                NEW.status, 'INSERT', current_user);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_audit_trigger();

Pros:

Complete history preserved
Main table stays clean
Easy to query historical data

Cons:

Doubles storage requirements
Schema changes need updating both tables
Trigger overhead on every operation

Pattern 3: Generic Audit Log Table

Concept: Single table logs changes to any entity

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,

    -- What was changed
    table_name VARCHAR(100) NOT NULL,
    record_id VARCHAR(100) NOT NULL,

    -- What changed
    field_name VARCHAR(100),
    old_value TEXT,
    new_value TEXT,

    -- Who and when
    operation VARCHAR(10) NOT NULL, -- CREATE, UPDATE, DELETE, READ
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changed_by VARCHAR(100) NOT NULL,

    -- Context
    ip_address INET,
    user_agent TEXT,
    session_id VARCHAR(100),
    request_id VARCHAR(100),

    -- Additional metadata
    metadata JSONB
);

-- Indexes for common queries
CREATE INDEX idx_audit_logs_table_record ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_changed_at ON audit_logs(changed_at DESC);
CREATE INDEX idx_audit_logs_changed_by ON audit_logs(changed_by);
CREATE INDEX idx_audit_logs_operation ON audit_logs(operation);

Pros:

Works for any table
Flexible schema
Single place for all audits

Cons:

Less type safety
Complex queries for reconstruction
Can grow very large

Pattern 4: Event Store (Event Sourcing)

Concept: Store all changes as discrete events

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,

    -- Event identification
    aggregate_type VARCHAR(100) NOT NULL, -- e.g., "Order"
    aggregate_id VARCHAR(100) NOT NULL,
    event_type VARCHAR(100) NOT NULL, -- e.g., "OrderCreated", "OrderPaid"
    event_version INT NOT NULL,

    -- Event data
    event_data JSONB NOT NULL,

    -- Metadata
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    caused_by VARCHAR(100) NOT NULL,
    correlation_id VARCHAR(100),

    -- Ensure ordered sequence per aggregate
    CONSTRAINT unique_aggregate_version UNIQUE (aggregate_type, aggregate_id, event_version)
);

CREATE INDEX idx_events_aggregate ON events(aggregate_type, aggregate_id, event_version);
CREATE INDEX idx_events_occurred_at ON events(occurred_at);

Example events:

{
  "event_type": "OrderCreated",
  "aggregate_type": "Order",
  "aggregate_id": "ORD-12345",
  "event_version": 1,
  "event_data": {
    "order_number": "ORD-12345",
    "customer_id": 567,
    "items": [...],
    "total": 99.99
  }
}

{
  "event_type": "OrderPaid",
  "aggregate_type": "Order",
  "aggregate_id": "ORD-12345",
  "event_version": 2,
  "event_data": {
    "payment_method": "credit_card",
    "transaction_id": "TXN-98765"
  }
}

Pros:

Complete history with business context
Can rebuild state by replaying events
Natural fit for CQRS
Excellent for complex domains

Cons:

High complexity
Schema evolution challenges
Event versioning required
Querying current state requires rebuilding

5. State Transition Tracking

Finite State Machine Pattern

Concept: Track valid state transitions and maintain history

-- Define valid state transitions
CREATE TABLE state_transitions (
    from_state VARCHAR(50) NOT NULL,
    to_state VARCHAR(50) NOT NULL,
    PRIMARY KEY (from_state, to_state)
);

-- Seed valid transitions for orders
INSERT INTO state_transitions (from_state, to_state) VALUES
    ('INITIAL', 'created'),
    ('created', 'payment_pending'),
    ('payment_pending', 'paid'),
    ('payment_pending', 'cancelled'),
    ('paid', 'processing'),
    ('processing', 'shipped'),
    ('shipped', 'delivered'),
    ('delivered', 'completed'),
    ('paid', 'refunded'),
    ('processing', 'cancelled');

-- Main entity table
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id BIGINT NOT NULL,
    current_state VARCHAR(50) NOT NULL DEFAULT 'created',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- State history table
CREATE TABLE order_state_history (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    from_state VARCHAR(50) NOT NULL,
    to_state VARCHAR(50) NOT NULL,

    -- Enforce valid transitions
    FOREIGN KEY (from_state, to_state) 
        REFERENCES state_transitions(from_state, to_state),

    transitioned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    transitioned_by VARCHAR(100) NOT NULL,
    reason TEXT,
    metadata JSONB,

    -- Ensure transitions are sequential
    sort_key INT NOT NULL,
    is_current BOOLEAN NOT NULL DEFAULT true,

    CONSTRAINT unique_current_per_order 
        UNIQUE (order_id, is_current) WHERE is_current = true
);

-- Index for querying current state
CREATE INDEX idx_order_state_history_current 
    ON order_state_history(order_id, is_current) 
    WHERE is_current = true;

-- Index for timeline queries
CREATE INDEX idx_order_state_history_timeline 
    ON order_state_history(order_id, sort_key);

State Transition Function

CREATE OR REPLACE FUNCTION transition_order_state(
    p_order_id BIGINT,
    p_to_state VARCHAR(50),
    p_user VARCHAR(100),
    p_reason TEXT DEFAULT NULL
) RETURNS BOOLEAN AS $$
DECLARE
    v_current_state VARCHAR(50);
    v_next_sort_key INT;
    v_is_valid BOOLEAN;
BEGIN
    -- Get current state
    SELECT current_state INTO v_current_state
    FROM orders
    WHERE id = p_order_id
    FOR UPDATE; -- Lock row

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order not found: %', p_order_id;
    END IF;

    -- Check if transition is valid
    SELECT EXISTS(
        SELECT 1 FROM state_transitions
        WHERE from_state = v_current_state
        AND to_state = p_to_state
    ) INTO v_is_valid;

    IF NOT v_is_valid THEN
        RAISE EXCEPTION 'Invalid state transition: % -> %', 
            v_current_state, p_to_state;
    END IF;

    -- Get next sort key
    SELECT COALESCE(MAX(sort_key), 0) + 1
    INTO v_next_sort_key
    FROM order_state_history
    WHERE order_id = p_order_id;

    -- Mark current state as no longer current
    UPDATE order_state_history
    SET is_current = false
    WHERE order_id = p_order_id
    AND is_current = true;

    -- Insert new state
    INSERT INTO order_state_history (
        order_id, from_state, to_state, transitioned_by, 
        reason, sort_key, is_current
    ) VALUES (
        p_order_id, v_current_state, p_to_state, p_user,
        p_reason, v_next_sort_key, true
    );

    -- Update current state on main table
    UPDATE orders
    SET current_state = p_to_state
    WHERE id = p_order_id;

    RETURN true;
END;
$$ LANGUAGE plpgsql;

Querying State History

-- Get complete timeline for an order
SELECT 
    from_state,
    to_state,
    transitioned_at,
    transitioned_by,
    reason,
    sort_key
FROM order_state_history
WHERE order_id = 123
ORDER BY sort_key;

-- Find all orders in a specific state
SELECT o.id, o.order_number, o.current_state
FROM orders o
WHERE o.current_state = 'shipped';

-- Find orders that transitioned through a specific state
SELECT DISTINCT o.id, o.order_number
FROM orders o
JOIN order_state_history h ON o.id = h.order_id
WHERE h.to_state = 'processing';

-- Get duration in each state
SELECT 
    order_id,
    to_state,
    transitioned_at,
    LEAD(transitioned_at) OVER (PARTITION BY order_id ORDER BY sort_key) 
        - transitioned_at AS duration_in_state
FROM order_state_history
WHERE order_id = 123
ORDER BY sort_key;

6. API Audit Logging Best Practices

Essential Elements for API Audit Logs

Request Identification
- Request ID (unique identifier)
- Correlation ID (for distributed tracing)
- Session ID
Authentication/Authorization
- User ID or service account
- Authentication method (JWT, OAuth, API key)
- Scopes/permissions used
Request Details
- HTTP method (GET, POST, PUT, DELETE)
- Endpoint/route
- Request parameters
- Request body (sanitized of sensitive data)
Response Details
- Status code
- Response time
- Error messages (if applicable)
Context
- IP address
- User agent
- Geolocation
- Timestamp (with timezone)

API Audit Log Schema

CREATE TABLE api_audit_logs (
    id BIGSERIAL PRIMARY KEY,

    -- Request identification
    request_id UUID NOT NULL UNIQUE,
    correlation_id UUID,
    session_id VARCHAR(100),

    -- Authentication
    user_id VARCHAR(100),
    service_account VARCHAR(100),
    auth_method VARCHAR(50),

    -- Request details
    http_method VARCHAR(10) NOT NULL,
    endpoint TEXT NOT NULL,
    route_pattern VARCHAR(200), -- e.g., "/api/v1/orders/:id"
    query_params JSONB,
    request_body JSONB,
    request_headers JSONB,

    -- Response details
    status_code INT NOT NULL,
    response_time_ms INT,
    response_body JSONB,
    error_message TEXT,

    -- Context
    ip_address INET,
    user_agent TEXT,
    geo_location JSONB,

    -- Timing
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Additional metadata
    metadata JSONB
);

-- Indexes for common query patterns
CREATE INDEX idx_api_audit_user ON api_audit_logs(user_id, created_at DESC);
CREATE INDEX idx_api_audit_endpoint ON api_audit_logs(route_pattern, created_at DESC);
CREATE INDEX idx_api_audit_status ON api_audit_logs(status_code, created_at DESC);
CREATE INDEX idx_api_audit_created ON api_audit_logs(created_at DESC);
CREATE INDEX idx_api_audit_correlation ON api_audit_logs(correlation_id);

-- GIN index for JSONB queries
CREATE INDEX idx_api_audit_metadata ON api_audit_logs USING GIN(metadata);

API Audit Logging Best Practices

Log Asynchronously
- Don't block API responses
- Use queues (RabbitMQ, Kafka)
- Buffer and batch writes
Sanitize Sensitive Data
- Never log passwords, tokens, credit cards
- Mask or redact PII
- Use allowlist for safe fields
Structured Logging
- Use JSON format
- Consistent field names
- Enable easy parsing/analysis
Implement Log Rotation
- Archive old logs
- Partition tables by date
- Set retention policies
Monitor and Alert
- Set up alerts for anomalies
- Track error rates
- Monitor unusual access patterns
Compliance Considerations
- GDPR: Support right to erasure
- HIPAA: Encrypt at rest and in transit
- SOC 2: Tamper-proof logs

7. Go/PostgreSQL/GORM Implementation Patterns

Architecture Overview

┌─────────────────────────────────────────┐
│         HTTP Handler Layer              │
│  (Middleware: Context, Auth, Logging)   │
└──────────────┬──────────────────────────┘
               │
┌──────────────▼──────────────────────────┐
│         Service Layer                   │
│  (Business Logic, Validation)           │
└──────────────┬──────────────────────────┘
               │
┌──────────────▼──────────────────────────┐
│       Repository Layer                  │
│  (Data Access, GORM Operations)         │
└──────────────┬──────────────────────────┘
               │
┌──────────────▼──────────────────────────┐
│         Database Layer                  │
│  (PostgreSQL + Audit Tables)            │
└─────────────────────────────────────────┘

Pattern 1: GORM Callbacks for Automatic Audit Logging

Concept: Use GORM's hook system to automatically log changes

package models

import (
    "context"
    "encoding/json"
    "time"

    "gorm.io/gorm"
)

// AuditLog represents the audit log table
type AuditLog struct {
    ID          uint           `gorm:"primaryKey"`
    TableName   string         `gorm:"index;not null"`
    RecordID    string         `gorm:"index;not null"`
    Operation   string         `gorm:"index;not null"` // CREATE, UPDATE, DELETE, READ
    OldValue    json.RawMessage `gorm:"type:jsonb"`
    NewValue    json.RawMessage `gorm:"type:jsonb"`
    ChangedBy   string         `gorm:"index"`
    ChangedAt   time.Time      `gorm:"index;not null"`
    IPAddress   string
    UserAgent   string
    SessionID   string         `gorm:"index"`
    RequestID   string         `gorm:"index"`
    Metadata    json.RawMessage `gorm:"type:jsonb"`
}

// Auditable interface for models that support audit logging
type Auditable interface {
    TableName() string
    GetID() string
}

// Order model with audit support
type Order struct {
    ID           uint      `gorm:"primaryKey"`
    OrderNumber  string    `gorm:"uniqueIndex;not null"`
    CustomerID   uint      `gorm:"not null"`
    TotalAmount  float64
    Status       string
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

func (Order) TableName() string {
    return "orders"
}

func (o Order) GetID() string {
    return fmt.Sprintf("%d", o.ID)
}

// RegisterAuditCallbacks registers GORM callbacks for audit logging
func RegisterAuditCallbacks(db *gorm.DB) {
    // After Create
    db.Callback().Create().After("gorm:after_create").Register("audit:after_create", func(db *gorm.DB) {
        if db.Statement.Schema == nil {
            return
        }

        // Check if model implements Auditable
        if auditable, ok := db.Statement.Dest.(Auditable); ok {
            createAuditLog(db, auditable, "CREATE", nil, db.Statement.Dest)
        }
    })

    // After Update
    db.Callback().Update().After("gorm:after_update").Register("audit:after_update", func(db *gorm.DB) {
        if db.Statement.Schema == nil {
            return
        }

        // Get old values before update (stored in context)
        oldValue := db.Statement.Context.Value("audit:old_value")

        if auditable, ok := db.Statement.Dest.(Auditable); ok {
            createAuditLog(db, auditable, "UPDATE", oldValue, db.Statement.Dest)
        }
    })

    // Before Update - capture old values
    db.Callback().Update().Before("gorm:before_update").Register("audit:before_update", func(db *gorm.DB) {
        if db.Statement.Schema == nil {
            return
        }

        // Fetch current values
        if auditable, ok := db.Statement.Dest.(Auditable); ok {
            var oldModel interface{}
            // Clone the model
            db.Session(&gorm.Session{}).
                Where(db.Statement.Schema.PrioritizedPrimaryField.Name+" = ?", auditable.GetID()).
                First(&oldModel)

            // Store in context for after_update
            ctx := context.WithValue(db.Statement.Context, "audit:old_value", oldModel)
            db.Statement.Context = ctx
        }
    })

    // After Delete
    db.Callback().Delete().After("gorm:after_delete").Register("audit:after_delete", func(db *gorm.DB) {
        if db.Statement.Schema == nil {
            return
        }

        if auditable, ok := db.Statement.Dest.(Auditable); ok {
            createAuditLog(db, auditable, "DELETE", db.Statement.Dest, nil)
        }
    })
}

func createAuditLog(db *gorm.DB, auditable Auditable, operation string, oldValue, newValue interface{}) {
    // Get audit context from request context
    ctx := db.Statement.Context

    var oldJSON, newJSON json.RawMessage

    if oldValue != nil {
        oldJSON, _ = json.Marshal(oldValue)
    }
    if newValue != nil {
        newJSON, _ = json.Marshal(newValue)
    }

    auditLog := AuditLog{
        TableName:  auditable.TableName(),
        RecordID:   auditable.GetID(),
        Operation:  operation,
        OldValue:   oldJSON,
        NewValue:   newJSON,
        ChangedBy:  getFromContext(ctx, "user_id"),
        ChangedAt:  time.Now(),
        IPAddress:  getFromContext(ctx, "ip_address"),
        UserAgent:  getFromContext(ctx, "user_agent"),
        SessionID:  getFromContext(ctx, "session_id"),
        RequestID:  getFromContext(ctx, "request_id"),
    }

    // Use a new session to avoid triggering callbacks recursively
    db.Session(&gorm.Session{SkipHooks: true}).Create(&auditLog)
}

func getFromContext(ctx context.Context, key string) string {
    if val := ctx.Value(key); val != nil {
        if str, ok := val.(string); ok {
            return str
        }
    }
    return ""
}

Pattern 2: Repository Pattern with Explicit Audit Logging

Concept: Repository layer explicitly logs changes

package repository

import (
    "context"
    "encoding/json"
    "fmt"
    "time"

    "gorm.io/gorm"
)

// OrderRepository handles order data access
type OrderRepository interface {
    Create(ctx context.Context, order *Order) error
    Update(ctx context.Context, order *Order) error
    Delete(ctx context.Context, id uint) error
    FindByID(ctx context.Context, id uint) (*Order, error)
}

type orderRepository struct {
    db *gorm.DB
    auditRepo AuditRepository
}

func NewOrderRepository(db *gorm.DB, auditRepo AuditRepository) OrderRepository {
    return &orderRepository{
        db: db,
        auditRepo: auditRepo,
    }
}

func (r *orderRepository) Create(ctx context.Context, order *Order) error {
    return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // Create order
        if err := tx.Create(order).Error; err != nil {
            return err
        }

        // Log audit
        newValue, _ := json.Marshal(order)
        return r.auditRepo.Log(ctx, AuditEntry{
            TableName: "orders",
            RecordID:  fmt.Sprintf("%d", order.ID),
            Operation: "CREATE",
            NewValue:  newValue,
        })
    })
}

func (r *orderRepository) Update(ctx context.Context, order *Order) error {
    return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // Get old values
        var oldOrder Order
        if err := tx.First(&oldOrder, order.ID).Error; err != nil {
            return err
        }

        // Update order
        if err := tx.Save(order).Error; err != nil {
            return err
        }

        // Log audit
        oldValue, _ := json.Marshal(oldOrder)
        newValue, _ := json.Marshal(order)
        return r.auditRepo.Log(ctx, AuditEntry{
            TableName: "orders",
            RecordID:  fmt.Sprintf("%d", order.ID),
            Operation: "UPDATE",
            OldValue:  oldValue,
            NewValue:  newValue,
        })
    })
}

func (r *orderRepository) Delete(ctx context.Context, id uint) error {
    return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // Get current values before delete
        var order Order
        if err := tx.First(&order, id).Error; err != nil {
            return err
        }

        // Soft delete
        if err := tx.Delete(&order, id).Error; err != nil {
            return err
        }

        // Log audit
        oldValue, _ := json.Marshal(order)
        return r.auditRepo.Log(ctx, AuditEntry{
            TableName: "orders",
            RecordID:  fmt.Sprintf("%d", id),
            Operation: "DELETE",
            OldValue:  oldValue,
        })
    })
}

Pattern 3: State Machine with Audit Trail

Concept: Explicit state transitions with validation and audit

package statemachine

import (
    "context"
    "fmt"
    "time"

    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

// OrderState represents valid order states
type OrderState string

const (
    StateCreated         OrderState = "created"
    StatePaymentPending  OrderState = "payment_pending"
    StatePaid            OrderState = "paid"
    StateProcessing      OrderState = "processing"
    StateShipped         OrderState = "shipped"
    StateDelivered       OrderState = "delivered"
    StateCompleted       OrderState = "completed"
    StateCancelled       OrderState = "cancelled"
    StateRefunded        OrderState = "refunded"
)

// StateTransition defines valid transitions
type StateTransition struct {
    FromState OrderState `gorm:"primaryKey"`
    ToState   OrderState `gorm:"primaryKey"`
}

// OrderStateHistory tracks state changes
type OrderStateHistory struct {
    ID              uint       `gorm:"primaryKey"`
    OrderID         uint       `gorm:"index;not null"`
    FromState       OrderState `gorm:"not null"`
    ToState         OrderState `gorm:"not null"`
    TransitionedAt  time.Time  `gorm:"index;not null"`
    TransitionedBy  string     `gorm:"index;not null"`
    Reason          string
    SortKey         int        `gorm:"not null"`
    IsCurrent       bool       `gorm:"not null;default:true"`
    Metadata        string     `gorm:"type:jsonb"`
}

// OrderStateMachine manages order state transitions
type OrderStateMachine struct {
    db *gorm.DB
}

func NewOrderStateMachine(db *gorm.DB) *OrderStateMachine {
    return &OrderStateMachine{db: db}
}

// TransitionTo attempts to transition order to new state
func (sm *OrderStateMachine) TransitionTo(
    ctx context.Context,
    orderID uint,
    toState OrderState,
    user string,
    reason string,
) error {
    return sm.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
        // Lock order row
        var order Order
        if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
            First(&order, orderID).Error; err != nil {
            return fmt.Errorf("order not found: %w", err)
        }

        currentState := OrderState(order.Status)

        // Validate transition
        if err := sm.validateTransition(tx, currentState, toState); err != nil {
            return err
        }

        // Get next sort key
        var maxSortKey int
        tx.Model(&OrderStateHistory{}).
            Where("order_id = ?", orderID).
            Select("COALESCE(MAX(sort_key), 0)").
            Scan(&maxSortKey)
        nextSortKey := maxSortKey + 1

        // Mark current state as not current
        tx.Model(&OrderStateHistory{}).
            Where("order_id = ? AND is_current = ?", orderID, true).
            Update("is_current", false)

        // Create new state history entry
        history := OrderStateHistory{
            OrderID:        orderID,
            FromState:      currentState,
            ToState:        toState,
            TransitionedAt: time.Now(),
            TransitionedBy: user,
            Reason:         reason,
            SortKey:        nextSortKey,
            IsCurrent:      true,
        }

        if err := tx.Create(&history).Error; err != nil {
            return fmt.Errorf("failed to create state history: %w", err)
        }

        // Update order status
        if err := tx.Model(&order).Update("status", string(toState)).Error; err != nil {
            return fmt.Errorf("failed to update order status: %w", err)
        }

        return nil
    })
}

func (sm *OrderStateMachine) validateTransition(tx *gorm.DB, from, to OrderState) error {
    var count int64
    tx.Model(&StateTransition{}).
        Where("from_state = ? AND to_state = ?", from, to).
        Count(&count)

    if count == 0 {
        return fmt.Errorf("invalid state transition: %s -> %s", from, to)
    }
    return nil
}

// GetStateHistory returns the complete state history for an order
func (sm *OrderStateMachine) GetStateHistory(ctx context.Context, orderID uint) ([]OrderStateHistory, error) {
    var history []OrderStateHistory
    err := sm.db.WithContext(ctx).
        Where("order_id = ?", orderID).
        Order("sort_key ASC").
        Find(&history).Error
    return history, err
}

// GetCurrentState returns the current state of an order
func (sm *OrderStateMachine) GetCurrentState(ctx context.Context, orderID uint) (*OrderStateHistory, error) {
    var history OrderStateHistory
    err := sm.db.WithContext(ctx).
        Where("order_id = ? AND is_current = ?", orderID, true).
        First(&history).Error
    return &history, err
}

Pattern 4: Middleware for API Audit Logging

Concept: HTTP middleware captures API request/response details

package middleware

import (
    "bytes"
    "context"
    "encoding/json"
    "io"
    "log"
    "net/http"
    "time"

    "github.com/gin-gonic/gin"
    "github.com/google/uuid"
    "gorm.io/gorm"
)

// APIAuditLog represents API audit log entries
type APIAuditLog struct {
    ID             uint            `gorm:"primaryKey"`
    RequestID      string          `gorm:"uniqueIndex;not null"`
    CorrelationID  string          `gorm:"index"`
    SessionID      string          `gorm:"index"`
    UserID         string          `gorm:"index"`
    HTTPMethod     string          `gorm:"not null"`
    Endpoint       string          `gorm:"not null"`
    RoutePattern   string          `gorm:"index"`
    QueryParams    json.RawMessage `gorm:"type:jsonb"`
    RequestBody    json.RawMessage `gorm:"type:jsonb"`
    RequestHeaders json.RawMessage `gorm:"type:jsonb"`
    StatusCode     int             `gorm:"index"`
    ResponseTimeMs int
    ResponseBody   json.RawMessage `gorm:"type:jsonb"`
    ErrorMessage   string
    IPAddress      string
    UserAgent      string
    CreatedAt      time.Time       `gorm:"index"`
    Metadata       json.RawMessage `gorm:"type:jsonb"`
}

// APIAuditMiddleware logs all API requests and responses
func APIAuditMiddleware(db *gorm.DB) gin.HandlerFunc {
    return func(c *gin.Context) {
        startTime := time.Now()

        // Generate request ID if not present
        requestID := c.GetHeader("X-Request-ID")
        if requestID == "" {
            requestID = uuid.New().String()
            c.Header("X-Request-ID", requestID)
        }

        // Store request ID in context
        ctx := context.WithValue(c.Request.Context(), "request_id", requestID)
        c.Request = c.Request.WithContext(ctx)

        // Capture request body
        var requestBody []byte
        if c.Request.Body != nil {
            requestBody, _ = io.ReadAll(c.Request.Body)
            // Restore body for handler
            c.Request.Body = io.NopCloser(bytes.NewBuffer(requestBody))
        }

        // Create custom response writer to capture response
        blw := &bodyLogWriter{body: bytes.NewBufferString(""), ResponseWriter: c.Writer}
        c.Writer = blw

        // Process request
        c.Next()

        // Calculate response time
        responseTime := time.Since(startTime).Milliseconds()

        // Prepare audit log (async to not block response)
        go func() {
            auditLog := APIAuditLog{
                RequestID:      requestID,
                CorrelationID:  c.GetHeader("X-Correlation-ID"),
                SessionID:      c.GetString("session_id"), // From auth middleware
                UserID:         c.GetString("user_id"),    // From auth middleware
                HTTPMethod:     c.Request.Method,
                Endpoint:       c.Request.URL.Path,
                RoutePattern:   c.FullPath(),
                QueryParams:    toJSON(c.Request.URL.Query()),
                RequestBody:    sanitizeRequestBody(requestBody),
                RequestHeaders: toJSON(sanitizeHeaders(c.Request.Header)),
                StatusCode:     c.Writer.Status(),
                ResponseTimeMs: int(responseTime),
                ResponseBody:   sanitizeResponseBody(blw.body.Bytes()),
                IPAddress:      c.ClientIP(),
                UserAgent:      c.Request.UserAgent(),
                CreatedAt:      startTime,
            }

            // Save to database
            if err := db.Create(&auditLog).Error; err != nil {
                // Log error but don't fail the request
                log.Printf("Failed to create audit log: %v", err)
            }
        }()
    }
}

type bodyLogWriter struct {
    gin.ResponseWriter
    body *bytes.Buffer
}

func (w bodyLogWriter) Write(b []byte) (int, error) {
    w.body.Write(b)
    return w.ResponseWriter.Write(b)
}

func toJSON(v interface{}) json.RawMessage {
    b, _ := json.Marshal(v)
    return b
}

func sanitizeRequestBody(body []byte) json.RawMessage {
    if len(body) == 0 {
        return nil
    }

    var data map[string]interface{}
    if err := json.Unmarshal(body, &data); err != nil {
        return nil
    }

    // Remove sensitive fields
    sensitiveFields := []string{"password", "token", "api_key", "secret", "credit_card"}
    for _, field := range sensitiveFields {
        if _, exists := data[field]; exists {
            data[field] = "[REDACTED]"
        }
    }

    return toJSON(data)
}

func sanitizeResponseBody(body []byte) json.RawMessage {
    // Similar to request sanitization
    return sanitizeRequestBody(body)
}

func sanitizeHeaders(headers http.Header) http.Header {
    sanitized := make(http.Header)
    for k, v := range headers {
        // Skip authorization headers
        if k == "Authorization" || k == "Cookie" {
            sanitized[k] = []string{"[REDACTED]"}
        } else {
            sanitized[k] = v
        }
    }
    return sanitized
}

8. Complete Code Examples

Example 1: Complete Order Management System with Audit

// main.go
package main

import (
    "log"

    "github.com/gin-gonic/gin"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

func main() {
    // Database connection
    dsn := "host=localhost user=postgres password=postgres dbname=orders port=5432"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        log.Fatal("Failed to connect to database:", err)
    }

    // Auto-migrate tables
    db.AutoMigrate(
        &Order{},
        &OrderStateHistory{},
        &StateTransition{},
        &AuditLog{},
        &APIAuditLog{},
    )

    // Seed valid state transitions
    seedStateTransitions(db)

    // Register audit callbacks
    RegisterAuditCallbacks(db)

    // Initialize repositories
    auditRepo := NewAuditRepository(db)
    orderRepo := NewOrderRepository(db, auditRepo)

    // Initialize state machine
    stateMachine := NewOrderStateMachine(db)

    // Initialize services
    orderService := NewOrderService(orderRepo, stateMachine)

    // Setup router
    router := gin.Default()

    // Global middleware
    router.Use(APIAuditMiddleware(db))
    router.Use(AuthMiddleware()) // Your auth middleware

    // Routes
    v1 := router.Group("/api/v1")
    {
        orders := v1.Group("/orders")
        {
            orders.POST("", orderService.CreateOrder)
            orders.GET("/:id", orderService.GetOrder)
            orders.PUT("/:id", orderService.UpdateOrder)
            orders.DELETE("/:id", orderService.DeleteOrder)
            orders.POST("/:id/transition", orderService.TransitionState)
            orders.GET("/:id/history", orderService.GetStateHistory)
        }
    }

    log.Fatal(router.Run(":8080"))
}

func seedStateTransitions(db *gorm.DB) {
    transitions := []StateTransition{
        {FromState: "created", ToState: "payment_pending"},
        {FromState: "payment_pending", ToState: "paid"},
        {FromState: "payment_pending", ToState: "cancelled"},
        {FromState: "paid", ToState: "processing"},
        {FromState: "processing", ToState: "shipped"},
        {FromState: "shipped", ToState: "delivered"},
        {FromState: "delivered", ToState: "completed"},
        {FromState: "paid", ToState: "refunded"},
        {FromState: "processing", ToState: "cancelled"},
    }

    for _, t := range transitions {
        db.FirstOrCreate(&t, t)
    }
}

Example 2: Service Layer with Context Propagation

// service/order_service.go
package service

import (
    "context"
    "fmt"
    "log"
    "net/http"
    "strconv"
    "time"

    "github.com/gin-gonic/gin"
)

type OrderService struct {
    orderRepo    OrderRepository
    stateMachine *OrderStateMachine
}

func NewOrderService(orderRepo OrderRepository, stateMachine *OrderStateMachine) *OrderService {
    return &OrderService{
        orderRepo:    orderRepo,
        stateMachine: stateMachine,
    }
}

type CreateOrderRequest struct {
    CustomerID  uint    `json:"customer_id" binding:"required"`
    TotalAmount float64 `json:"total_amount" binding:"required"`
}

func (s *OrderService) CreateOrder(c *gin.Context) {
    var req CreateOrderRequest
    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    // Build context with audit information
    ctx := buildAuditContext(c)

    // Generate order number
    orderNumber := fmt.Sprintf("ORD-%d", time.Now().Unix())

    order := &Order{
        OrderNumber: orderNumber,
        CustomerID:  req.CustomerID,
        TotalAmount: req.TotalAmount,
        Status:      string(StateCreated),
    }

    if err := s.orderRepo.Create(ctx, order); err != nil {
        log.Printf("Failed to create order: %v", err)
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to create order"})
        return
    }

    c.JSON(http.StatusCreated, order)
}

func (s *OrderService) TransitionState(c *gin.Context) {
    orderID := c.Param("id")

    var req struct {
        ToState string `json:"to_state" binding:"required"`
        Reason  string `json:"reason"`
    }

    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    ctx := buildAuditContext(c)
    userID := c.GetString("user_id")

    id, _ := strconv.ParseUint(orderID, 10, 64)

    err := s.stateMachine.TransitionTo(
        ctx,
        uint(id),
        OrderState(req.ToState),
        userID,
        req.Reason,
    )

    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    c.JSON(http.StatusOK, gin.H{"message": "State transitioned successfully"})
}

func (s *OrderService) GetStateHistory(c *gin.Context) {
    orderID := c.Param("id")
    id, _ := strconv.ParseUint(orderID, 10, 64)

    ctx := buildAuditContext(c)

    history, err := s.stateMachine.GetStateHistory(ctx, uint(id))
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "Failed to get history"})
        return
    }

    c.JSON(http.StatusOK, history)
}

// buildAuditContext extracts audit info from request and builds context
func buildAuditContext(c *gin.Context) context.Context {
    ctx := c.Request.Context()

    // Add audit fields to context
    ctx = context.WithValue(ctx, "user_id", c.GetString("user_id"))
    ctx = context.WithValue(ctx, "session_id", c.GetString("session_id"))
    ctx = context.WithValue(ctx, "ip_address", c.ClientIP())
    ctx = context.WithValue(ctx, "user_agent", c.Request.UserAgent())
    ctx = context.WithValue(ctx, "request_id", c.GetHeader("X-Request-ID"))

    return ctx
}

Example 3: Querying Audit Logs

// repository/audit_repository.go
package repository

import (
    "context"
    "encoding/json"
    "time"

    "gorm.io/gorm"
)

type AuditRepository interface {
    Log(ctx context.Context, entry AuditEntry) error
    GetByRecordID(ctx context.Context, tableName, recordID string) ([]AuditLog, error)
    GetByUser(ctx context.Context, userID string, limit int) ([]AuditLog, error)
    GetByDateRange(ctx context.Context, start, end time.Time) ([]AuditLog, error)
    GetByOperation(ctx context.Context, operation string, limit int) ([]AuditLog, error)
}

type auditRepository struct {
    db *gorm.DB
}

func NewAuditRepository(db *gorm.DB) AuditRepository {
    return &auditRepository{db: db}
}

type AuditEntry struct {
    TableName string
    RecordID  string
    Operation string
    OldValue  json.RawMessage
    NewValue  json.RawMessage
}

func (r *auditRepository) Log(ctx context.Context, entry AuditEntry) error {
    auditLog := AuditLog{
        TableName:  entry.TableName,
        RecordID:   entry.RecordID,
        Operation:  entry.Operation,
        OldValue:   entry.OldValue,
        NewValue:   entry.NewValue,
        ChangedBy:  getFromContext(ctx, "user_id"),
        ChangedAt:  time.Now(),
        IPAddress:  getFromContext(ctx, "ip_address"),
        UserAgent:  getFromContext(ctx, "user_agent"),
        SessionID:  getFromContext(ctx, "session_id"),
        RequestID:  getFromContext(ctx, "request_id"),
    }

    return r.db.WithContext(ctx).Create(&auditLog).Error
}

func (r *auditRepository) GetByRecordID(ctx context.Context, tableName, recordID string) ([]AuditLog, error) {
    var logs []AuditLog
    err := r.db.WithContext(ctx).
        Where("table_name = ? AND record_id = ?", tableName, recordID).
        Order("changed_at DESC").
        Find(&logs).Error
    return logs, err
}

func (r *auditRepository) GetByUser(ctx context.Context, userID string, limit int) ([]AuditLog, error) {
    var logs []AuditLog
    err := r.db.WithContext(ctx).
        Where("changed_by = ?", userID).
        Order("changed_at DESC").
        Limit(limit).
        Find(&logs).Error
    return logs, err
}

func (r *auditRepository) GetByDateRange(ctx context.Context, start, end time.Time) ([]AuditLog, error) {
    var logs []AuditLog
    err := r.db.WithContext(ctx).
        Where("changed_at BETWEEN ? AND ?", start, end).
        Order("changed_at DESC").
        Find(&logs).Error
    return logs, err
}

func (r *auditRepository) GetByOperation(ctx context.Context, operation string, limit int) ([]AuditLog, error) {
    var logs []AuditLog
    err := r.db.WithContext(ctx).
        Where("operation = ?", operation).
        Order("changed_at DESC").
        Limit(limit).
        Find(&logs).Error
    return logs, err
}

9. Summary & Best Practices

Key Takeaways

Choose the Right Pattern
- Simple audit: Row versioning or shadow tables
- Complex domains: Event sourcing
- API tracking: Middleware-based logging
GORM Integration
- Use callbacks for automatic logging
- Leverage context for audit metadata
- Implement repository pattern for clean separation
State Management
- Use state machines for complex workflows
- Enforce valid transitions at database level
- Maintain complete history with sort keys
API Audit Logging
- Log asynchronously to avoid blocking
- Sanitize sensitive data
- Use correlation IDs for distributed tracing
Performance
- Index strategically (user_id, timestamp, table_name)
- Partition large tables by date
- Archive old logs to cold storage
- Use JSONB for flexible metadata
Compliance
- Encrypt sensitive audit data
- Implement log retention policies
- Support right-to-erasure (GDPR)
- Make logs tamper-proof (write-once)
Testing
- Unit test state transitions
- Integration test audit log creation
- Verify sensitive data is sanitized
- Test concurrent state changes

New Year, New You Portfolio Challenge - Building & Deploying My Portfolio with Google Cloud Run

Akkarapon Phikulsri — Fri, 09 Jan 2026 15:22:58 +0000

New Year New You Portfolio Challenge

This is a submission for the New Year, New You Portfolio Challenge Presented by Google AI

About Me

Akkarapon Phikulsri — Software Engineer specializing in Go, Python, C#, and TypeScript. I build scalable systems with modern web frameworks and microservices.

Portfolio

portfolio.refactorroom.com

✨ Take a look at it on Akkarapon Foliokub

How I Built It

I used Google AI Studio to ideate and implement my portfolio for the New Year, New You challenge. I then refined the project locally using Antigravity Editor, enhancing each section with relevant images and showcasing my certificates.

The portfolio is built using React, TypeScript, and Tailwind CSS, focusing on a clean, responsive design and maintainable component structure.

Antigravity I leverage Antigravity to bring my concepts to life, allowing for rapid prototyping and superior design quality.

What I'm Most Proud Of

Custom Animation System Built five reusable animation components (Aurora, ClickSpark, GlitchText, Bounce, FadeContent) that bring the portfolio to life without compromising performance.

Performance-First Custom useInViewOnce hook optimizes animations using Intersection Observer, triggering only when elements enter the viewport.

Project Showcase Two distinct sections (grid + timeline) highlight 14+ projects across backend APIs, mobile apps, and full-stack applications.

Production Deployment Dockerized with Nginx, deployed to Google Cloud Run with automatic scaling—ready for real-world use.

Interactive Portfolio Experience & Leveling System
The portfolio uses an experience (XP) and leveling system that rewards visitors and unlocks sections.
Features

Five XP categories Scroll, Game, Chat, Activity, and Other
Level progression Total level combines all categories (up to 1000)
Section unlocking New sections unlock as level increases
Persistent progress XP saved in localStorage across sessions
Expandable XP bar Shows total XP and detailed category breakdown with progress bars
Level-up celebration Confetti animation on level up with notifications
Visual feedback Gradient progress bars and real-time XP tracking

3D Interactive Skills Sphere
I built an interactive 3D sphere to showcase my technical skills using React Three Fiber. Skills are distributed evenly using the Fibonacci sphere algorithm, and users can rotate and explore the sphere with smooth hover interactions.

Dark Theme Switcher
A custom Dark theme toggle button that smoothly switches between light and dark modes. It remembers user preferences, respects system settings, and includes smooth animations. The entire site, including the 3D sphere, adapts dynamically to theme changes.
Both features improve the user experience and demonstrate modern web development techniques with 3D graphics and smooth state management.

White

Dark

"Take a Break" Game

To add interactivity and a fun easter egg, I built a full-screen canvas-based game that visitors can play directly on the portfolio.

Key Features

Canvas-based gameplay Built with HTML5 Canvas and requestAnimationFrame for smooth 60fps animations
Mouse-controlled ship Players move their cursor to collect colorful orbs floating across the screen
Multiple themes Three visual themes (Space, Neon, Retro) that can be switched on the fly
XP integration Scores earn XP points that integrate with the portfolio's gamification system Animated background Procedurally generated stars that create a dynamic space environment
Collision detection Real-time collision detection using distance calculations
Full-screen immersion Full-screen overlay with hidden cursor for an immersive experience

🐍 Snake Game (Arcade Zone)

Overview
Classic Snake implemented as a responsive web game. Earn XP while playing.

Features

Responsive board 20x20 grid that adapts to screen size (12–25px cells)
Speed settings Slow, Normal, Fast, Very Fast
Controls
- Keyboard Arrow keys or WASD
- Mobile Touch direction buttons
- Spacebar to pause
Scoring 10 XP per food eaten
Game mechanics
- Grow by eating food (red pulsing dots)
- Game over on wall or self collision
- Pause/Resume
- Reset

🎯 Career Simulator Games

Example

Playgroud

Stats

Interactive role-playing scenarios based on real tech work. Earn XP through problem-solving, design decisions, and code challenges.

All simulators use a chat/log interface where you

Receive scenario briefings
Input solutions, code, or decisions
Get AI feedback from role-played colleagues (Junior/Senior Dev, Tech Lead, etc.)
Earn XP based on solution quality
Track progress with saved stats and achievements

Game List

Senior Engineer Simulator
Backend Developer Simulator
Frontend Developer Simulator
System Analysis Simulator
Project Manager Simulator
Database Admin Simulator

The Local Site-Based Simple Chatbot
Instead of relying on external AI APIs, I built a locally-powered chatbot that runs entirely client-side using a curated knowledge base.
Key Features

Zero API dependencies No external API keys or calls—everything runs locally in the browser Curated knowledge base Pre-built responses covering tech stack, projects, career, and engineering philosophy
Smart keyword matching Fuzzy matching and category-based routing to find relevant answers
Suggested questions Category-based question suggestions to guide conversations XP integration Each interaction rewards XP points, encouraging engagement
Typing indicators Realistic typing delays to simulate natural conversation flow
Privacy-first All conversations stay in the browser—no data is sent to external servers

Crafting software requires artistry, guided by imagination, grounded in logic, endlessly enduring. - Akkarapon Phikulsri

Deploy to Google Cloud Run

I will guide you through deploying a static React portfolio website to Google Cloud Run, a fully managed serverless platform.

What is Google Cloud Run?

Google Cloud Run is a fully managed compute platform that automatically scales your stateless containers. It's perfect for deploying static websites and web applications with minimal configuration. Cloud Run automatically handles

HTTPS/TLS certificates
Load balancing
Auto-scaling (including scaling to zero when not in use)
Pay-per-use pricing

Prerequisites

Before starting, ensure you have:

Google Cloud Account (already registered)
Google Cloud SDK - Will be installed in Step 1 below
Docker (optional, for local testing) Install from docker.com

Step 1 Install Google Cloud SDK

Since your internet connection may be unreliable, this step is broken into parts with checkpoints after each section. Each part can be completed independently and verified before proceeding.

Part A Choose Installation Method

First, choose your operating system and preferred installation method:

macOS

Option 1 Homebrew (Recommended - Better for Interruptions)

Homebrew handles retries and resume automatically
Easier to manage updates
Preferred if you already have Homebrew installed

Option 2 Manual Installation

Direct download from Google Cloud
Works without package managers
Requires manual updates

Linux

Option 1 Package Manager (Recommended - Better for Interruptions)

apt for Debian/Ubuntu
yum for Red Hat/CentOS/Fedora
Package managers handle retries better than manual downloads

Option 2 Manual Installation

Direct download script
Works on any Linux distribution

Windows

Option 1 Installer (Recommended)

Windows installer (.msi file)
Handles installation automatically
Can be resumed if interrupted

Option 2 Package Manager (Chocolatey/Winget)

Requires Chocolatey or Windows Package Manager
Easier updates

Part B Installation by Platform

macOS Installation

Using Homebrew (Recommended)

# Check if Homebrew is installed (if not, install from https://brew.sh)
brew --version

# Install Google Cloud SDK
brew install --cask google-cloud-sdk

# Checkpoint Verify installation started
which gcloud

If installation is interrupted

Homebrew automatically resumes from where it left off
Simply re-run brew install --cask google-cloud-sdk
Homebrew will continue from the last checkpoint

Using Manual Installation

# Step 1 Download the SDK (can be resumed if interrupted)
curl https://sdk.cloud.google.com | bash

# If download is interrupted, remove partial download and retry:
# rm -rf ~/google-cloud-sdk
# Then re-run the curl command above

# Step 2 Restart your shell or source the path
exec -l $SHELL

# Or manually add to PATH in your ~/.zshrc or ~/.bash_profile:
# echo 'source "$HOME/google-cloud-sdk/path.bash.inc"' >> ~/.zshrc
# source ~/.zshrc

Checkpoint Verify Part B Complete

# Check if gcloud is accessible
gcloud version

# If successful, you should see version information
# If command not found, restart your terminal or source your shell config

Linux Installation

Using apt (Debian/Ubuntu)

# Part 1 Add Google Cloud SDK repository
echo "deb [signed-by=/usr/share/keyrings/cloud.google.gpg] https://packages.cloud.google.com/apt cloud-sdk main" | sudo tee -a /etc/apt/sources.list.d/google-cloud-sdk.list

# If interrupted here, you can skip if the repository was already added (check with):
# cat /etc/apt/sources.list.d/google-cloud-sdk.list

# Part 2 Import Google Cloud public key
curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key --keyring /usr/share/keyrings/cloud.google.gpg add -

# If interrupted here, check if key exists:
# sudo apt-key list | grep Google

# Part 3 Update package list
sudo apt-get update

# Part 4 Install Google Cloud SDK
sudo apt-get install google-cloud-sdk

# If installation is interrupted, apt-get will resume automatically:
# sudo apt-get install -f google-cloud-sdk

Checkpoint Verify Part B Complete

gcloud version
# Should display version information if successful

Using yum (Red Hat/CentOS/Fedora)

# Part 1 Add Google Cloud SDK repository
sudo tee -a /etc/yum.repos.d/google-cloud-sdk.repo << EOM
[google-cloud-sdk]
name=Google Cloud SDK
baseurl=https://packages.cloud.google.com/yum/repos/cloud-sdk-el7-x86_64
enabled=1
gpgcheck=1
repo_gpgcheck=1
gpgkey=https://packages.cloud.google.com/yum/doc/yum-key.gpg
       https://packages.cloud.google.com/yum/doc/rpm-package-key.gpg
EOM

# If interrupted here, check if repo file exists:
# cat /etc/yum.repos.d/google-cloud-sdk.repo

# Part 2 Install Google Cloud SDK
sudo yum install google-cloud-sdk

# If interrupted, yum will handle resume automatically

Checkpoint Verify Part B Complete

gcloud version
# Should display version information if successful

Using Manual Installation (Any Linux)

# Step 1 Download the SDK (can be resumed if interrupted)
curl https://sdk.cloud.google.com | bash

# If download is interrupted:
# rm -rf ~/google-cloud-sdk
# Then re-run the curl command

# Step 2 Restart shell or source the path
exec -l $SHELL

# Or add to PATH manually:
# echo 'source "$HOME/google-cloud-sdk/path.bash.inc"' >> ~/.bashrc
# source ~/.bashrc

Checkpoint Verify Part B Complete

gcloud version
# Should display version information if successful

Windows Installation

Using Installer

Download the Google Cloud SDK installer:
- Visit https://cloud.google.com/sdk/docs/install-sdk
- Download the Windows installer (.msi file)
- If download is interrupted, resume or re-download
Run the installer:
- Double-click the downloaded .msi file
- Follow the installation wizard
- If interrupted, re-run the installer (it will resume)
Checkpoint Verify Part B Complete
- Open a new Command Prompt or PowerShell window
- Run gcloud version
- Should display version information if successful

Using Chocolatey (if installed)

# Install Google Cloud SDK
choco install gcloudsdk

# Checkpoint Verify installation
gcloud version

Using Winget (Windows Package Manager)

# Install Google Cloud SDK
winget install Google.CloudSDK

# Checkpoint Verify installation
gcloud version

Part C Initialize and Authenticate

Now that gcloud is installed, initialize and authenticate:

# Step 1 Initialize gcloud
gcloud init

# This will prompt you to
# - Log in with your Google account
# - Choose or create a project
# - Set default region/zone

# If interrupted during initialization:
# Simply re-run gcloud init
# It will resume from where it left off

Alternative Manual Authentication

If gcloud init is interrupted, you can authenticate separately:

# Step 1 Authenticate
gcloud auth login

# This will open a browser for authentication
# If browser doesn't open, use gcloud auth login --no-browser

# Step 2 Set project (after authentication)
gcloud config set project YOUR_PROJECT_ID

# Step 3 Set region (optional, recommended)
gcloud config set run/region asia-southeast1
# or us-central1, europe-west1, etc.

Checkpoint Verify Part C Complete

# Verify authentication
gcloud auth list

# Should show your authenticated account with an asterisk (*)

# Verify current configuration
gcloud config list

# Should show your project, region, etc.

Part D Enable Required APIs

After authentication, enable the APIs needed for Cloud Run

# Enable Cloud Build API
gcloud services enable cloudbuild.googleapis.com

# If interrupted, check which APIs are enabled:
# gcloud services list --enabled

# Enable Cloud Run API
gcloud services enable run.googleapis.com

# Verify both APIs are enabled
gcloud services list --enabled | grep -E "cloudbuild|run"

Checkpoint Verify Part D Complete

# Should show both cloudbuild and run APIs as enabled
gcloud services list --enabled | grep -E "cloudbuild|run"

Installation Complete!

If all checkpoints passed, your Google Cloud SDK is ready. You can verify everything works:

# Full verification
gcloud version
gcloud components list
gcloud config list
gcloud auth list

Step 2 Prepare Your Application

This application uses a multi-stage Docker build:

Build stage Compiles the React application using Node.js
Production stage Serves the static files using Nginx

The Dockerfile is already configured for Cloud Run with:

Port 8080 (Cloud Run default)
Nginx configuration for SPA routing
Optimized multi-stage build

No additional configuration needed. The application is ready to deploy.

Step 3 Deploy to Cloud Run

Method 1 Direct Deployment from Source (Recommended)

This is the easiest method - Cloud Run will build and deploy your application automatically

But it also can do that if you want to build Dockerfile on your locally first before run this command

gcloud run deploy portfolio \
  --source . \
  --platform managed \
  --region asia-southeast1 \
  --allow-unauthenticated \
  --labels dev-tutorial=devnewyear2026 \
  --port 8080

if success it will show like this

Notes

Replace portfolio with your preferred service name
Choose a region closest to your users (e.g., us-central1, asia-southeast1, europe-west1)
The --labels dev-tutorial=devnewyear2026 flag is required if submitting to the Dev.to challenge
--allow-unauthenticated makes your service publicly accessible
--port 8080 matches the port exposed in your Dockerfile

Method 2 Build Docker Image First

If you prefer to build the Docker image locally or using Cloud Build:

# Build the Docker image using Cloud Build
gcloud builds submit --tag gcr.io/$(gcloud config get-value project)/portfolio

# Deploy to Cloud Run
gcloud run deploy portfolio \
  --image gcr.io/$(gcloud config get-value project)/portfolio \
  --platform managed \
  --region asia-southeast1 \
  --allow-unauthenticated \
  --labels dev-tutorial=devnewyear2026 \
  --port 8080

Method 3 Using Cloud Build Config

You can also use the provided cloudbuild.yaml:

gcloud builds submit --config cloudbuild.yaml

Then deploy the built image:

gcloud run deploy portfolio \
  --image gcr.io/$(gcloud config get-value project)/portfolio \
  --platform managed \
  --region asia-southeast1 \
  --allow-unauthenticated \
  --labels dev-tutorial=devnewyear2026 \
  --port 8080

Step 4 Verify Deployment

After deployment, Cloud Run will provide you with a URL

https://portfolio-xxxxx-uc.a.run.app

Visit the URL to verify your portfolio is live! Cloud Run automatically provides:

HTTPS encryption
A custom domain (or you can configure your own)
Global CDN distribution

Step 5 Update and Redeploy

To update your application after making changes

# Simply redeploy (Cloud Run will rebuild if needed)
gcloud run deploy portfolio \
  --source . \
  --platform managed \
  --region asia-southeast1 \
  --allow-unauthenticated \
  --labels dev-tutorial=devnewyear2026 \
  --port 8080

Cost Considerations

Google Cloud Run offers a generous free tier

CPU First 240,000 vCPU-seconds free per month
Memory First 450,000 GiB-seconds free per month
Requests First 2 million requests free per month

For a portfolio website with moderate traffic, you should stay well within the free tier. Cloud Run only charges when requests are being processed (scales to zero).

Useful Commands

# List all Cloud Run services
gcloud run services list

# View service details
gcloud run services describe portfolio --region asia-southeast1

# View logs
gcloud run services logs read portfolio --region asia-southeast1

# Delete service
gcloud run services delete portfolio --region asia-southeast1

# View service URL
gcloud run services describe portfolio --region asia-southeast1 --format='value(status.url)'

Good luck with your deployment! 🚀

Node.js + Fastify + Sequelize + MySQL

Akkarapon Phikulsri — Fri, 09 Jan 2026 03:09:05 +0000

This course is based on this template

akkaraponph / nodejs-ts-fastify-mvc-starter-template

This repo node api crud template - folder-by-type

Node.js TypeScript Fastify MVC Starter Template

A production-ready starter template for building RESTful APIs with Node.js, TypeScript, and Fastify. This template follows the MVC (Model-Routes-Controllers-Services) architecture pattern, providing a clean and scalable foundation for your backend projects.

🚀 Features

Fastify - Fast and low overhead web framework
TypeScript - Type-safe development
Sequelize ORM - Database abstraction with PostgreSQL/MySQL support
JWT Authentication - Secure token-based authentication
Swagger Documentation - Auto-generated API documentation
CORS Support - Cross-origin resource sharing configured
Error Handling - Custom error handling middleware
MVC Architecture - Clean separation of concerns
Environment Configuration - Multi-environment support (dev, test, production)

📁 Project Structure

├── src/
│   ├── app.ts                 # Fastify app configuration
│   ├── config/                # Configuration files
│   │   ├── config.ts          # Environment variables
│   │   ├── db.config.ts       # Database configuration
│   │   └── swagger/           # Swagger documentation setup
│   ├── controllers/           # Request handlers (business logic)
│   ├──

…

View on GitHub

Flag

This YouTube tutorial is in Thai. I’m keeping this post in English so everyone can quickly understand the main concepts alongside the video. For deeper understanding, feel free to ask ChatGPT or your favorite AI.

What you will learn

This course teaches you how to build an API using:

Node.js
Fastify (a web framework)
Sequelize (for working with databases)
MySQL (database)

About MRCS

MRCS means Model-Routes-Controllers-Services. This is a way to organize your code. It helps you keep your code clean and easy to understand.

BillowDev Youtube

Setup Videos

Watch these videos first to install the tools you need:

How to Install Git and using GitHub

How to Install Node.js and VS code

How to Install Laragon

Node js TypeScript Fastify EP 01 Intro

In this episode, you will learn how to start your project and create your first API.

Step 1: Start a new project

First, create a new Node.js project. Open your terminal and type:

npm init -y

This command creates a file called package.json. This file stores information about your project.

Step 2: Using Yarn (Optional)

You can use npm or yarn. Both are package managers. They help you install libraries for your project.

If you want to use yarn, install it first:

npm install -g yarn

Here are the yarn commands you need to know:

npm install is the same as yarn add
npm install -D is the same as yarn add -D
npm run is the same as yarn

Step 3: Install TypeScript tools

You need to install TypeScript and some tools. TypeScript is a language that adds types to JavaScript.

Type this command in your terminal:

npm install --save-dev ts-node @types/node typescript

Or if you use yarn:

yarn add -D ts-node @types/node typescript

Step 4: Create TypeScript config file

Now create a TypeScript configuration file. This file tells TypeScript how to work with your code.

Type this command:

npx tsc --init

This creates a file called tsconfig.json. Copy this code into that file:

tsconfig.json

{
  "compilerOptions": {
    /* Visit https://aka.ms/tsconfig to read more about this file */
    /* Language and Environment */
    "target": "ESNext",                                  /* Set the JavaScript language version for emitted JavaScript and include compatible library declarations. */

    /* Modules */
    "module": "commonjs",                                /* Specify what module code is generated. */
    // "rootDir": "./",                                  /* Specify the root folder within your source files. */
    "moduleResolution": "node",                       /* Specify how TypeScript looks up a file from a given module specifier. */
    "baseUrl": "./src",                                  /* Specify the base directory to resolve non-relative module names. */
    "outDir": "./dist",                                   /* Specify an output folder for all emitted files. */
    "esModuleInterop": true,                             /* Emit additional JavaScript to ease support for importing CommonJS modules. This enables 'allowSyntheticDefaultImports' for type compatibility. */
    "forceConsistentCasingInFileNames": true,            /* Ensure that casing is correct in imports. */

    /* Type Checking */
    "strict": true,                                      /* Enable all strict type-checking options. */
    "skipLibCheck": true                                 /* Skip type checking all .d.ts files. */
  },
  "include": [
    "**/*.ts"
, "src/config/db.config.ts"  ],
  "exclude": [
    "node_modules",
    "**/*.d.ts",
    "**/*.spec.ts"
  ]
}

Step 5: Install Fastify

Fastify is a web framework. It helps you build APIs quickly.

Install it with this command:

npm install fastify

Or if you use yarn:

yarn add fastify

Step 6: Create your first file

Create a new file called index.ts in your project root folder.

Step 7: Write your first API

Now you will write code to create your first API. Follow these steps:

Step 7.1: Import Fastify

import fastify from "fastify";

This line imports the Fastify library.

Step 7.2: Create the app

Next, create your app:

const app = fastify()

Step 7.3: Create your first route

A route is a URL that your API can respond to. Create your first route:

app.get("/", async () => "SERVER");

This creates a route at "/". When someone visits this URL, they will see "SERVER".

Step 7.4: Start your server

Now you need to start your server. Add this code:

    const PORT = 5000
    app.listen({port:Number(PORT)}, (err) => {
        if (err) {
            console.error(err)
            process.exit(1)
        }
        console.log(`SERVE ON ${PORT}`)
    })

This code starts your server on port 5000.

Step 7.5: Run your API

Save your file. Then open your terminal and type:

ts-node index.ts

You should see the message "SERVE ON 5000" in your terminal. This means your API is running!

Step 8: Make your logs look better

The logs help you see what your API is doing. Let's make them look better.

Step 8.1: Add logging options

Change your app code to this:

const app = fastify({
    logger: true
})

This turns on logging.

Step 8.2: Update your listen code

Replace the code from step 7.4 with this:

    const PORT = 5000
    app.listen({port:Number(PORT)}, (err) => {
        if (err) {
            app.log.error(err);
            process.exit(1)
        }
        app.log.info(`SERVE ON ${PORT}`)
    })

This uses the logger instead of console.log.

Step 8.3: See your logs

When you run your API now, you will see logs like this:

{"level":30,"time":1676779096246,"pid":25344,"hostname":"billo","msg":"Server listening at http://[::1]:5000"}
{"level":30,"time":1676779096248,"pid":25344,"hostname":"billo","msg":"Server listening at http://127.0.0.1:5000"}
{"level":30,"time":1676779096248,"pid":25344,"hostname":"billo","msg":"SERVE ON 5000"}

Step 9: Test your API

You can test your API with Thunder Client. It is a tool for testing APIs. You can find it in the Visual Studio Code extensions.

You can also use other tools like Postman or Insomnia. These tools let you test different HTTP methods like GET, POST, PUT, PATCH, and DELETE. If your API uses GET method, you can also test it in your browser.

Node js TypeScript Fastify EP 02 Thunder client

In this episode, you will learn how to test your API using Thunder Client.

What is Thunder Client?

Thunder Client is a tool for testing APIs. It works inside Visual Studio Code. You can use it to send requests to your API and see the responses.

Step 1: Install Thunder Client

Open Visual Studio Code. Go to the Extensions section. Search for "Thunder Client" and install it.

Step 2: How to use Thunder Client

After installing Thunder Client, you will see a new icon in your sidebar. Click on it to open Thunder Client. You can create requests and test your API there.

Your complete index.ts file

After following all the steps in EP 01, your index.ts file should look like this:

import fastify from "fastify";

const app = fastify({
    logger: true
})

app.get("/", async () => "SERVER");

const PORT = 5000
app.listen({port:Number(PORT)}, (err) => {
    if (err) {
        app.log.error(err);
        process.exit(1)
    }
    app.log.info(`SERVE ON ${PORT}`)
})

Node js TypeScript Fastify EP 03 App

In this episode, you will learn how to organize your code better. We will move the app logic to a separate file.

Why organize code?

When your code grows, it is better to organize it. This makes your code easier to read and maintain. We will create a src folder and put our code there.

Step 1: Create the src folder

First, create a new folder called src in your project root.

Step 2: Move app logic to src/app.ts

Create a new file called app.ts inside the src folder. Move the app creation code there.

Code for src/app.ts

import fastify, { FastifyServerOptions } from "fastify";

const App = (options: FastifyServerOptions) => {
    const app = fastify(options)

    app.get("/", async () => "SERVER");
    return app
}
export default App

This code creates a function called App. This function takes options and creates a Fastify app. It returns the app so you can use it later.

Code for index.ts

Now update your index.ts file. Import the App function and use it to create your app:

import App from "./src/app";

const app = App({
    logger: true
})
const PORT = 5000
app.listen({port:Number(PORT)}, (err) => {
    if (err) {
        app.log.error(err);
        process.exit(1)
    }
    app.log.info(`SERVE ON ${PORT}`)
})

What changed?

Before: All your code was in index.ts
After: The app logic is in src/app.ts, and index.ts only starts the server

This makes your code more organized. When you add more features, you will add them to the src folder.

Node js TypeScript Fastify EP 04 Routes

In this episode, you will learn how to create routes. Routes are URLs that your API responds to.

What is a route?

A route is a URL path. When someone visits that URL, your API responds. For example, /api/v1/articles is a route.

Step 1: Create the routes folder

Create a new folder called routes inside the src folder.

Step 2: Create article route file

Create a new file called article.route.ts inside the src/routes folder.

Step 3: Write the route code

import { FastifyInstance } from "fastify";

const articleRouter = async (app: FastifyInstance) => {
    // This is sample data for testing
    const article = {
        id: "1",
        name: "node.js fastify",
        desc: "Fast and easy course for building APIs"
    }

    // Create a GET route at "/"
    app.get(
        "/",
        () => {
            return {
                articles: [
                    article
                ]
            }
        }
    );
};

export default articleRouter;

This code creates a router for articles. It has one route that returns sample data.

Step 4: Create routes index file

Create a new file called index.ts inside the src/routes folder. This file exports all your routes.

import articleRouter from "./article.route";

export { articleRouter };

Step 5: Register the route in app.ts

Now you need to tell your app to use this route. Update your src/app.ts file.

import fastify, { FastifyServerOptions } from "fastify";
import { articleRouter } from "./routes";

const App = (options: FastifyServerOptions) => {
    const app = fastify(options)

    app.get("/", async () => "SERVER");
    // Register the article router with a prefix
    app.register(articleRouter, { prefix: "/api/v1/articles" });
    return app
}
export default App

The prefix means all article routes will start with /api/v1/articles.

Step 6: Test your route

Stop your server by pressing CTRL + C in your terminal
Start it again with: ts-node index.ts
Open Thunder Client and test the route: http://localhost:5000/api/v1/articles

Result

You should see the article data when you visit the route:

Node js TypeScript Fastify EP 05 Controllers

In this episode, you will learn about controllers. Controllers handle the logic for your routes.

What is a controller?

A controller is a function that handles requests. When someone visits a route, the controller decides what to do and what to return.

Step 1: Create controllers folder

Create a new folder called controllers inside the src folder.

Step 2: Create article controller

Create a new file called article.controller.ts inside the src/controllers folder.

export const handleGetArticle = () => {
    // This is sample data for testing
    const article = {
        id: "1",
        name: "node.js fastify",
        desc: "Fast and easy course for building APIs"
    }
    return {
        articles: [
            article
        ]
    }
}

export default {
    handleGetArticle
}

This controller function returns article data. Later, it will get data from a service.

Step 3: Create controllers index file

Create a new file called index.ts inside the src/controllers folder.

import articlesController from "./article.controller";
export { articlesController };

Step 4: Update the route to use the controller

Now update your src/routes/article.route.ts file to use the controller.

import { FastifyInstance } from "fastify";
import articleController from './../controllers/article.controller';

const articleRouter = async (app: FastifyInstance) => {
    // Use the controller function as the handler
    app.get(
        "/",
        articleController.handleGetArticle
    );
};

export default articleRouter;

Now your route uses the controller. This separates the route logic from the business logic.

Node js TypeScript Fastify EP 06 Services

In this episode, you will learn about services. Services contain the business logic of your application.

What is a service?

A service is where you put your business logic. It handles things like getting data or processing information. Controllers call services.

Step 1: Create services folder

Create a new folder called services inside the src folder.

Step 2: Create article service

Create a new file called article.service.ts inside the src/services folder.

export const getArticles = () => {
    const data = {
        id: "1",
        name: "node.js fastify",
        desc: "Fast and easy course for building APIs"
    }

    return { response: data }
}

export default {
    getArticles
}

This service function returns article data. Later, it will get data from a database.

Step 3: Create services index file

Create a new file called index.ts inside the src/services folder.

import articleService from "./article.service";
export { articleService };

Step 4: Update the controller to use the service

Now update your src/controllers/article.controller.ts file to use the service.

import { articleService } from "../services";

export const handleGetArticle = () => {
    return articleService.getArticles()
}

export default {
    handleGetArticle
}

Now your controller calls the service. This separates the request handling from the business logic.

Node js TypeScript Fastify EP 07 MySQL Database

In this episode, you will learn how to set up MySQL database using Laragon.

What is MySQL?

MySQL is a database. It stores your data in tables. You will use it to store articles and other information.

What is Laragon?

Laragon is a tool that helps you run MySQL on your computer. It makes it easy to start and manage MySQL.

Step 1: Install Laragon

First, install Laragon. Follow this video: How to Install Laragon

Step 2: Start Laragon server

Open Laragon and click the "Start All" button to start the MySQL server.

Step 3: Open MySQL

Click on the "Database" button in Laragon to open the database manager.

Step 4: Create a new database

Click on "Database" in the left sidebar
Right-click and select "Create Database"

Step 5: Name your database

Name your database node_fastify_db and click "OK".

Great! Your database is ready. In the next episode, you will learn how to connect your API to this database.

Node js TypeScript Fastify EP 08 Sequelize

In this episode, you will learn how to connect your API to the database using Sequelize.

What is Sequelize?

Sequelize is an ORM (Object-Relational Mapping). It helps you talk to your database. You use models to work with database tables.

What is dotenv?

Dotenv is a package that helps you use environment variables. Environment variables store secret information like database passwords. You put them in a .env file.

What is mysql2?

mysql2 is a driver for MySQL. It allows your code to connect to MySQL database. If you use PostgreSQL, you would use pg instead.

Step 1: Install packages

Install Sequelize, dotenv, and mysql2:

npm install sequelize dotenv mysql2

Or if you use yarn:

yarn add sequelize dotenv mysql2

Step 2: Create .env file

Create a new file called .env in your project root. This file stores your database settings.

Important: The default MySQL username in Laragon is root and the password is empty "".

We separate the settings for different environments: development, production, and test.

NODE_ENV=development

DB_USERNAME_DEV=root
DB_PASSWORD_DEV=""
DB_DATABASE_DEV=node_fastify_db
DB_HOST_DEV=localhost

DB_USERNAME_PROD=root
DB_PASSWORD_PROD=""
DB_DATABASE_PROD=node_fastify_db_production
DB_HOST_PROD=localhost

DB_USERNAME_TEST=root
DB_PASSWORD_TEST=""
DB_DATABASE_TEST=node_fastify_db_test
DB_HOST_TEST=localhost

DB_DIALECT=mysql

Step 3: Create config files

We need to create config files to use the .env file. First, we import dotenv. Then we create a config that reads values from the .env file.

Step 3.1: Create config.ts

Create a new folder called config inside the src folder. Then create a file called config.ts inside src/config.

import dotenv from "dotenv";
dotenv.config();

const config = {
  env: process.env.NODE_ENV || "development",
  port: process.env.PORT || 5000,
  database: {
    dev: {
      username: process.env.DB_USERNAME_DEV,
      password: process.env.DB_PASSWORD_DEV,
      name: process.env.DB_DATABASE_DEV,
      host: process.env.DB_HOST_DEV,
    },
    production:{
      username: process.env.DB_USERNAME_PROD,
      password: process.env.DB_PASSWORD_PROD,
      name: process.env.DB_DATABASE_PROD,
      host: process.env.DB_HOST_PROD,
    },
    test: {
      username: process.env.DB_USERNAME_TEST,
      password: process.env.DB_PASSWORD_TEST,
      name: process.env.DB_DATABASE_TEST,
      host: process.env.DB_HOST_TEST,
    },
    dialect: process.env.DB_DIALECT,
  },
};

export default config;

Step 3.2: Create db.config.ts

Create a new file called db.config.ts inside the src/config folder.

import config from "./config"; // this is important!

module.exports = {
  development: {
    username: config.database.dev.username,
    password: config.database.dev.password,
    database: config.database.dev.name,
    host: config.database.dev.host,
    dialect: config.database.dialect,
  },
  test: {
    username: config.database.test.username,
    password: config.database.test.password,
    database: config.database.test.name,
    host: config.database.test.host,
    dialect: config.database.dialect,
  },
  production: {
    username: config.database.production.username,
    password: config.database.production.password,
    database: config.database.production.name,
    host: config.database.production.host,
    dialect: config.database.dialect,
  },
};

Step 4: Create models

Models represent your database tables. They define what data you can store.

Step 4.1: Create types folder

Create a new folder called types inside the src folder.

Step 4.2: Create article model types

Create a new folder called articles inside the src/types folder. Then create a file called article.model.types.ts inside src/types/articles.

export interface ArticleAttributes {
  id?: string;
  title?: string;
  text?: string;
  type?: string;
  UserId?: string;
  createdAt?: Date;
  updatedAt?: Date;
}

Step 4.3: Create models folder

Create a new folder called models inside the src folder.

Step 4.4: Create models/index.ts

Create a new file called index.ts inside the src/models folder.

Note: The code below is usually generated by sequelize-cli. But for TypeScript, we modified it to read config from ../config/db.config.ts.

"use strict";

const fs = require("fs");
const path = require("path");
const basename = path.basename(__filename);
const Sequelize = require("sequelize");
const env = process.env.NODE_ENV || "development";
const config = require(__dirname + "/../config/db.config.ts")[env];
const db: any = {};

let sequelize: any;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(
    config.database,
    config.username,
    config.password,
    config
  );
}

fs.readdirSync(__dirname)
  .filter((file: string) => {
    return (
      file.indexOf(".") !== 0 && file !== basename && file.slice(-3) === ".ts"
    );
  })
  .forEach((file: any) => {
    const model = require(path.join(__dirname, file))(
      sequelize,
      Sequelize.DataTypes
    );
    db[model.name] = model;
  });

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

export default db;

Step 4.5: Create article model

Create a new file called article.model.ts inside the src/models folder.

This file defines the Article model. The models/index.ts file will automatically read this file and create the model based on what you declare.

"use strict";
import * as Sequelize from "sequelize";
import { Model, UUIDV4 } from "sequelize";
import { ArticleAttributes } from "types/articles/article.model.types";

module.exports = (sequelize: any, DataTypes: any) => {
  class Article
    extends Model<ArticleAttributes>
    implements ArticleAttributes
  {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    id!: string;
    title!: string;
    text!: string;
    type!: string;
    static associate(models: any) {
      // define association here

    }
  }
  Article.init(
    {
      id: {
        type: DataTypes.UUID,
        defaultValue: UUIDV4,
        allowNull: false,
        primaryKey: true,
      },
      title: {
        type: DataTypes.STRING,
        allowNull: false,
      },
      text: {
        type: DataTypes.STRING(500),
      },
    },
    {
      sequelize,
      modelName: "Article",
    }
  );
  return Article;
};

Step 4.6: Update index.ts

Now update your index.ts file to:

Import the database and config files
Get the PORT from the config file
Sync the database with Sequelize (this creates the tables)

import App from "./src/app";
import db from "./src/models"
import config from "./src/config/config"

const app = App({
    logger: true
})
const PORT: string | number = config.port

db.sequelize.sync().then(() => {
app.listen({port:Number(PORT)}, (err) => {
    if (err) {
        app.log.error(err);
        process.exit(1)
    }
    app.log.info(`SERVE ON ${PORT}`)
})
})

Step 5: Use nodemon for development

Nodemon automatically restarts your app when you change the code. This is very helpful during development.

Important: Only use nodemon in development. It will restart your app every time you save a file.

Step 5.1: Install nodemon

npm install -D nodemon

Or if you use yarn:

yarn add -D nodemon

Step 5.2: Add script to package.json

Open your package.json file. Find the "scripts" section and add this line:

"dev": "nodemon index.ts"

Step 5.3: Run your app

Now you can run your app with:

npm run dev

Or if you use yarn:

yarn dev

Your app will automatically restart when you change your code!

Bonus: Complete CRUD API Example

This is a bonus section. You will learn how to create a complete CRUD API. CRUD means Create, Read, Update, and Delete.

Note: This section does not have a video tutorial. Follow the steps below to build a complete Articles API.

What you will build

You will create an Articles API with these operations:

GET - Get all articles
GET - Get one article by ID
POST - Create a new article
PUT - Update an article
DELETE - Delete an article

How it works

The flow is: routes → controllers → services → models (Sequelize) → MySQL database

Step 1: Update routes

We will create 5 routes in the article router. Each route calls a controller function.

import { FastifyInstance } from "fastify";
import articleController from './../controllers/article.controller';

const articleRouter = async (app: FastifyInstance) => {
    // GET all articles
    app.get(
        "/",
        articleController.handleGetArticle
    );

    // GET one article by ID
    app.get(
        "/get/:id",
        articleController.handleGetArticleById
    );

    // POST create a new article
    app.post(
        "/create",
        articleController.handleCreateArticle
    );

    // PUT update an article
    app.put(
        "/update/:id",
        articleController.handleUpdateArticle
    );

    // DELETE delete an article
    app.delete(
        "/delete/:id",
        articleController.handleDeleteArticle
    );
};

export default articleRouter;

Step 2: Create request types

We need to define types for the request data. This helps TypeScript understand what data to expect.

Create a new file called article.controller.types.ts inside src/types/articles.

import { FastifyRequest } from "fastify";
export type RequestWithIdArticle = FastifyRequest<{
    Params: { id: string };
}>;


export type UpdateArticleRequest = FastifyRequest<{
    Params: { id: string };
    Body: {
        title?: string | undefined;
        text?: string | undefined;
        type?: string | undefined;
    };
}>;

export type ArticleCreateRequest = FastifyRequest<{
    Body: {
        title?: string | undefined;
        text?: string | undefined;
        type?: string | undefined;
    };
}>;

Step 3: Update controllers

In the article controller, we will create handler functions. Each function calls a service function.

import { ArticleCreateRequest, RequestWithIdArticle, UpdateArticleRequest } from "types/articles/article.controller.types";
import { ArticleAttributes } from "types/articles/article.model.types";
import { articleService } from "../services";


export const handleGetArticle = async () => {
    return articleService.getArticles()
}

export const handleGetArticleById = async (req: RequestWithIdArticle) => {
    const id = req.params.id;
    return articleService.getOneArticle(id)
}

export const handleCreateArticle = async (req: ArticleCreateRequest) => {
    const { title, text, type } = req.body
    return articleService.createArticle({ title, text, type })
}

export const handleUpdateArticle = async (req: UpdateArticleRequest) => {
    const { title, text, type } = req.body
    return articleService.updateArticle(req.params.id, { title, text, type })
}

export const handleDeleteArticle = async (req: RequestWithIdArticle) => {
    return articleService.deleteArticle(req.params.id)
}

export default {
    handleGetArticle,
    handleGetArticleById,
    handleCreateArticle,
    handleUpdateArticle,
    handleDeleteArticle
}

Step 4: Update services

In the article service, we create functions that use Sequelize to interact with the database.

import { ArticleAttributes } from "types/articles/article.model.types";
import db from "../models";
const ArticleModel = db.Article

export const getArticles = async (): Promise<ArticleAttributes[]> => {
    const response = await ArticleModel.findAll();
    return response
}

export const getOneArticle = async (id: string): Promise<ArticleAttributes> => {
    const response: ArticleAttributes = await ArticleModel.findByPk(id)
    return response
}

export const createArticle = async (body: ArticleAttributes): Promise<ArticleAttributes> => {
    const response: ArticleAttributes = await ArticleModel.create(body)
    return response
}

export const updateArticle = async (id: string, body: ArticleAttributes) => {
    const response = await ArticleModel.update({ ...body }, { where: { id } })
    return response
}

export const deleteArticle = async (id: string) => {
    const response = await ArticleModel.destroy({ where: { id } });
    return response
}

export default {
    getArticles,
    getOneArticle,
    createArticle,
    updateArticle,
    deleteArticle
}

Step 5: Test your API

Now you can test all the routes using Thunder Client. Here are examples of the results:

GET all articles

Visit GET http://localhost:5000/api/v1/articles to get all articles:

GET one article

Visit GET http://localhost:5000/api/v1/articles/get/:id to get one article (replace :id with an article ID):

CREATE article

Send a POST request to http://localhost:5000/api/v1/articles/create with article data in the body:

UPDATE article

Send a PUT request to http://localhost:5000/api/v1/articles/update/:id with updated data in the body:

DELETE article

Send a DELETE request to http://localhost:5000/api/v1/articles/delete/:id to delete an article:

Congratulations! You now have a complete CRUD API working with a database!

Atomic Transaction Patterns C#

Akkarapon Phikulsri — Thu, 08 Jan 2026 05:51:39 +0000

Pattern	Consistency	Scale	Complexity	Suitable For
Pessimistic Locking	Strong	Low	Low	Monoliths, legacy systems
Saga Pattern	Eventual	High	High	Microservices
CQRS	Eventual	High	High	Read-heavy systems, DDD
Mediator	Depends	Med	Medium	Coordinated command handling
Event Sourcing	Eventual	High	High	Auditable systems
Two-Phase Commit	Strong	Low	High	Cross-DB transactional ops
Outbox Pattern	Eventual	High	Medium	DB + messaging consistency

Two powerful transaction patterns for modern applications: AtomicService for ACID transactions and SagaPatternService for distributed transactions with eventual consistency.

🎯 Overview

This library offers two complementary approaches to handle transactions in both monolithic and distributed architectures:

Pattern	Use Case	Consistency	Scope	Recovery
AtomicService	Single service/database	Immediate (ACID)	Local operations	Transaction rollback
SagaPatternService	Multiple services	Eventual	Distributed operations	Compensating actions

✨ Features

🛡️ AtomicService (ACID Transactions)

Hierarchical Transactions - Parent-child transaction support
Enhanced Retry Logic - Configurable retry with exponential backoff
Performance Monitoring - Built-in metrics and correlation tracking
Multiple Isolation Levels - ReadCommitted, RepeatableRead, Serializable
Deadlock Detection - Smart handling of transient database errors
Flexible Configuration - Per-operation timeouts and retry policies

🌐 SagaPatternService (Distributed Transactions)

Compensating Actions - Automatic rollback through compensation
State Persistence - Durable saga state with resume capability
Step Orchestration - Sequential, conditional, and parallel execution
Error Recovery - Comprehensive error handling and retry strategies
Monitoring & Observability - Full saga lifecycle tracking
Integration Ready - Works seamlessly with AtomicService

🚀 Quick Start

Basic Setup

// Program.cs
using Persistence.Database;

var builder = WebApplication.CreateBuilder(args);

// Configure AtomicService
builder.Services.Configure<AtomicServiceOptions>(options =>
{
    options.MaxRetryAttempts = 3;
    options.TransactionTimeout = TimeSpan.FromMinutes(5);
    options.DefaultIsolationLevel = IsolationLevel.ReadCommitted;
});

// Configure SagaPatternService
builder.Services.Configure<SagaPatternOptions>(options =>
{
    options.StepTimeout = TimeSpan.FromMinutes(5);
    options.MaxRetryAttempts = 3;
    options.EnableStateLogging = true;
});

// Register services
builder.Services.AddScoped<IAtomicService, AtomicService>();
builder.Services.AddScoped<ISagaStateStore, InMemorySagaStateStore>();
builder.Services.AddScoped<ISagaPatternService, SagaPatternService>();

var app = builder.Build();

📖 Usage Examples

AtomicService - Local ACID Transactions

public class OrderService
{
    private readonly IAtomicService _atomicService;

    // Simple atomic operation
    public async Task CreateOrderAsync(Order order)
    {
        await _atomicService.ExecuteAtomicallyAsync(async () =>
        {
            await _orderRepository.CreateAsync(order);
            await _auditRepository.LogAsync(order.Id, "OrderCreated");
            await _notificationService.SendConfirmationAsync(order.CustomerId);
        });
    }

    // Parent-child hierarchical transaction
    public async Task ProcessComplexOrderAsync(Order order)
    {
        await _atomicService.ExecuteAsParentAsync(async () =>
        {
            await CreateOrderAsync(order); // Child transaction
            await _paymentService.ProcessPaymentAsync(order.Payment); // Child transaction
            await _inventoryService.ReserveItemsAsync(order.Items); // Child transaction
        });
    }

    // With custom options
    public async Task ProcessCriticalOrderAsync(Order order)
    {
        var options = new AtomicOperationOptions
        {
            OperationName = "CriticalOrderProcessing",
            IsolationLevel = IsolationLevel.Serializable,
            Timeout = TimeSpan.FromMinutes(10),
            MaxRetryAttempts = 5
        };

        await _atomicService.ExecuteAtomicallyAsync(async () =>
        {
            // Critical business logic here
        }, options);
    }
}

SagaPatternService - Distributed Transactions

public class DistributedOrderService
{
    private readonly ISagaPatternService _sagaService;

    public async Task<SagaExecutionResult> ProcessDistributedOrderAsync(OrderRequest request)
    {
        var context = new OrderSagaContext(request);

        var steps = new List<ISagaStep<OrderSagaContext>>
        {
            // Step 1: Process Payment
            SagaStepBuilder.Create<OrderSagaContext>(
                "ProcessPayment", 1,
                executeAction: async (ctx, ct) =>
                {
                    var payment = await _paymentService.ChargeAsync(ctx.OrderRequest.Payment);
                    ctx.PaymentId = payment.Id;
                    return StepResult.Success;
                },
                compensateAction: async (ctx, ct) =>
                {
                    if (!string.IsNullOrEmpty(ctx.PaymentId))
                    {
                        await _paymentService.RefundAsync(ctx.PaymentId);
                    }
                    return StepResult.Success;
                }
            ),

            // Step 2: Reserve Inventory
            SagaStepBuilder.Create<OrderSagaContext>(
                "ReserveInventory", 2,
                executeAction: async (ctx, ct) =>
                {
                    var reservation = await _inventoryService.ReserveAsync(ctx.OrderRequest.Items);
                    ctx.ReservationId = reservation.Id;
                    return StepResult.Success;
                },
                compensateAction: async (ctx, ct) =>
                {
                    if (!string.IsNullOrEmpty(ctx.ReservationId))
                    {
                        await _inventoryService.ReleaseAsync(ctx.ReservationId);
                    }
                    return StepResult.Success;
                }
            ),

            // Step 3: Schedule Shipping
            SagaStepBuilder.Create<OrderSagaContext>(
                "ScheduleShipping", 3,
                executeAction: async (ctx, ct) =>
                {
                    var shipping = await _shippingService.ScheduleAsync(ctx.OrderRequest);
                    ctx.Data["ShippingId"] = shipping.Id;
                    return StepResult.Success;
                },
                compensateAction: async (ctx, ct) =>
                {
                    if (ctx.Data.TryGetValue("ShippingId", out var shippingId))
                    {
                        await _shippingService.CancelAsync(shippingId.ToString()!);
                    }
                    return StepResult.Success;
                }
            )
        };

        return await _sagaService.ExecuteSagaAsync(context, steps);
    }
}

Hybrid Approach - Best of Both Worlds

public class HybridOrderService
{
    public async Task<StepResult> ProcessOrderDataAsync(OrderContext context, CancellationToken ct)
    {
        // Use AtomicService for local consistency within saga steps
        await _atomicService.ExecuteAtomicallyAsync(async () =>
        {
            // Local ACID operations within the distributed saga step
            await _orderRepository.CreateAsync(context.Order);
            await _auditRepository.LogAsync(context.Order.Id, "OrderCreated");
            await _customerRepository.UpdateOrderCountAsync(context.Order.CustomerId);
        }, new AtomicOperationOptions
        {
            OperationName = "SagaStep.LocalOrderData",
            CorrelationData = new Dictionary<string, object>
            {
                ["SagaId"] = context.SagaId,
                ["OrderId"] = context.Order.Id
            }
        });

        return StepResult.Success;
    }
}

🎯 When to Use Which Pattern

Use AtomicService When:

✅ Single service operations - All operations within one service/database
✅ Need immediate consistency - ACID properties required
✅ Traditional database transactions - Relational database operations
✅ Parent-child service relationships - Hierarchical transaction coordination
✅ Short-lived operations - Transactions that complete quickly

Use Saga Pattern When:

✅ Multiple microservices involved - Cross-service coordination needed
✅ External API integrations - Third-party services in the flow
✅ Long-running business processes - Operations that take significant time
✅ Need eventual consistency - Can tolerate temporary inconsistency
✅ Distributed architecture - Services across different databases/systems

Use Both Together When:

🚀 Each saga step needs local ACID guarantees - Best of both worlds
🚀 Distributed coordination with local consistency - Hybrid approach
🚀 Complex business processes - Spanning multiple services with local data integrity

🏗️ Architecture Overview

graph TB
    A[Client Request] --> B{Transaction Type?}

    B -->|Local Operations| C[AtomicService]
    B -->|Distributed Operations| D[SagaPatternService]
    B -->|Hybrid| E[Both Services]

    C --> C1[Transaction Scope]
    C --> C2[Retry Logic]
    C --> C3[Performance Monitoring]

    D --> D1[Saga Steps]
    D --> D2[Compensation Actions]
    D --> D3[State Persistence]

    E --> E1[Saga Orchestration]
    E1 --> E2[Step 1: AtomicService]
    E1 --> E3[Step 2: AtomicService]
    E1 --> E4[Step N: AtomicService]

    C1 --> F[Database]
    D3 --> G[Saga State Store]
    E2 --> F
    E3 --> F
    E4 --> F

📊 Performance Characteristics

Pattern	Latency	Throughput	Consistency	Complexity	Scalability
AtomicService	Low	High	Strong	Low	Vertical
SagaPattern	Medium	Medium	Eventual	Medium	Horizontal
Hybrid	Medium	High	Mixed	High	Both

📚 Documentation

Core Documentation

AtomicService Usage Guide - Comprehensive guide for ACID transactions
Saga Pattern Usage Guide - Complete saga implementation guide
API Reference - Full API documentation
Configuration Guide - Setup and configuration options

Examples & Tutorials

E-commerce Order Processing - Real-world e-commerce example
Banking Transfers - Financial transaction examples
Microservices Integration - Cross-service coordination
Performance Optimization - High-throughput scenarios

Advanced Topics

Error Handling Strategies - Comprehensive error management
Monitoring & Observability - Metrics and logging
Testing Strategies - Unit and integration testing
Production Deployment - Production considerations

🛠️ Configuration Options

AtomicService Configuration

public class AtomicServiceOptions
{
    public int MaxRetryAttempts { get; set; } = 3;
    public TimeSpan BaseRetryDelay { get; set; } = TimeSpan.FromMilliseconds(100);
    public TimeSpan TransactionTimeout { get; set; } = TimeSpan.FromMinutes(5);
    public IsolationLevel DefaultIsolationLevel { get; set; } = IsolationLevel.ReadCommitted;
    public bool EnablePerformanceLogging { get; set; } = true;
    public bool EnableDistributedTransactions { get; set; } = false;
}

SagaPatternService Configuration

public class SagaPatternOptions
{
    public TimeSpan StepTimeout { get; set; } = TimeSpan.FromMinutes(5);
    public TimeSpan CompensationTimeout { get; set; } = TimeSpan.FromMinutes(10);
    public int MaxRetryAttempts { get; set; } = 3;
    public TimeSpan RetryDelay { get; set; } = TimeSpan.FromSeconds(1);
    public bool EnableStateLogging { get; set; } = true;
    public bool EnableCompensationLogging { get; set; } = true;
}

🔍 Monitoring & Observability

Both services provide comprehensive monitoring capabilities:

Built-in Metrics

Transaction success/failure rates
Operation duration and performance
Retry attempt tracking
Compensation execution monitoring
Resource usage statistics

Logging Integration

Structured logging with correlation IDs
Performance logging for optimization
Error tracking with context
Saga state change events

Health Checks

// Built-in health checks
services.AddHealthChecks()
    .AddCheck<AtomicServiceHealthCheck>("atomic-service")
    .AddCheck<SagaPatternHealthCheck>("saga-pattern");

🧪 Testing

Unit Testing

[Test]
public async Task AtomicService_Should_Rollback_On_Exception()
{
    // Arrange
    var mockRepository = new Mock<IRepository>();
    mockRepository.Setup(x => x.SaveAsync(It.IsAny<Entity>()))
              .ThrowsAsync(new InvalidOperationException());

    // Act & Assert
    await Assert.ThrowsAsync<InvalidOperationException>(() =>
        _atomicService.ExecuteAtomicallyAsync(async () =>
        {
            await mockRepository.Object.SaveAsync(new Entity());
        }));

    // Verify rollback occurred
    mockRepository.Verify(x => x.SaveAsync(It.IsAny<Entity>()), Times.Once);
}

Integration Testing

[Test]
public async Task SagaPattern_Should_Compensate_On_Step_Failure()
{
    // Arrange
    var context = new TestSagaContext();
    var steps = CreateTestStepsWithFailure();

    // Act
    var result = await _sagaService.ExecuteSagaAsync(context, steps);

    // Assert
    Assert.That(result.FinalStatus, Is.EqualTo(SagaStatus.Compensated));
    Assert.That(context.CompensatedSteps.Count, Is.GreaterThan(0));
}

Reference

akkaraponph / iamatomic

Atomic Hanlding C# | Consistency within a single transaction scope

🔚 Summary Table Of Data Consistency Handling

Pattern	Consistency	Scale	Complexity	Suitable For
Pessimistic Locking	Strong	Low	Low	Monoliths, legacy systems
Saga Pattern	Eventual	High	High	Microservices
CQRS	Eventual	High	High	Read-heavy systems, DDD
Mediator	Depends	Med	Medium	Coordinated command handling
Event Sourcing	Eventual	High	High	Auditable systems
Two-Phase Commit	Strong	Low	High	Cross-DB transactional ops
Outbox Pattern	Eventual	High	Medium	DB + messaging consistency

🔄 I Am Atomic Transaction Patterns

Two powerful transaction patterns for modern applications: AtomicService for ACID transactions and SagaPatternService for distributed transactions with eventual consistency.

🎯 Overview

This library offers two complementary approaches to handle transactions in both monolithic and distributed architectures:

Pattern	Use Case	Consistency	Scope	Recovery
AtomicService	Single service/database	Immediate (ACID)	Local operations	Transaction rollback
SagaPatternService	Multiple services	Eventual	Distributed operations	Compensating actions

✨ Features

🛡️ AtomicService (ACID Transactions)

Hierarchical Transactions - Parent-child transaction support
Enhanced Retry Logic - Configurable retry with exponential…

View on GitHub

NodeJS 101 — Part 4 🔐 Authorization with JWT

Akkarapon Phikulsri — Thu, 08 Jan 2026 04:13:59 +0000

เนื้อหาดังต่อไปนี้เป็นเนื้อหาเมื่อปี 2023 ถูกปรับปรุงเรียบเรียงใหม่ 2026 ขอให้สนุกกับการเริ่มต้นกับโลก Programming อย่างภาษาง่าย ๆ ด้วย JavaScript กันเน่อ :)

Akkarapon Phikulsri

Jan 8

NodeJS 101 — Part 3 🗄️MySQL + Sequelize

#javascript #mysql #node #tutorial

9 min read

📖 คำนำ

เอกสารฉบับนี้เป็นการพัฒนา web service หรือ RESTful API โดยใช้ภาษา JavaScript Node.js ร่วมกับเฟรมเวิร์กและไลบรารี่ต่าง ๆ ได้แก่:

🌐 Express HTTP Framework - สำหรับสร้าง web server
🗄️ Sequelize - ORM สำหรับจัดการฐานข้อมูล
💾 MySQL - ฐานข้อมูลเชิงสัมพันธ์
🔧 POSTMAN - สำหรับทดสอบ API

Section 4-1 - 🔑 JWT Authentication

📚 JWT คืออะไร?

JWT (JSON Web Token) เป็นมาตรฐานสำหรับสร้าง access token ที่ใช้ในการยืนยันตัวตน (Authentication) และกำหนดสิทธิ์ (Authorization)

🔍 องค์ประกอบของ JWT:

Header - ระบุประเภท token และ algorithm
Payload - ข้อมูลที่ต้องการส่ง (เช่น user ID)
Signature - ใช้สำหรับตรวจสอบความถูกต้องของ token

📦 ติดตั้ง jsonwebtoken

npm install jsonwebtoken

📥 Import jsonwebtoken

const jwt = require('jsonwebtoken');

🔐 สร้าง Login Controller

เพิ่มในไฟล์: controllers/student.controller.js

const jwt = require('jsonwebtoken');

exports.login = async (req, res) => {
    try {
        const id = req.body.stdId;
        const pass = req.body.stdPass;

        // ค้นหานักศึกษาจากฐานข้อมูล
        const response = await studentModel.findOne({
            where: {
                std_id: id,
                std_pass: pass
            },
            raw: true
        })

        if (response) {
            // ⚠️ ในทางปฏิบัติ ควรเก็บ SECRETKEY ไว้ใน .env
            const SECRETKEY = process.env.JWT_SECRET || "secret1234";

            // สร้าง JWT token
            const token = jwt.sign({
                std_id: response.stdId,
            }, SECRETKEY, {
                expiresIn: "1h", // Token หมดอายุใน 1 ชั่วโมง
            });

            // ลบ password ออกจาก response
            delete response.stdPass;

            res.status(200).json({
                message: "login was successfully",
                payload: { ...response, token }
            })
        } else {
            res.status(401).json({
                message: "Invalid credentials"
            })
        }

    } catch (error) {
        res.status(500).json({
            message: error.message || "login student was failed"
        })
    }
}

🔍 อธิบายโค้ด:

jwt.sign() - สร้าง JWT token
expiresIn: "1h" - กำหนดอายุ token (1 ชั่วโมง)
delete response.stdPass - ลบ password ก่อนส่ง response
status(401) - HTTP status code สำหรับ Unauthorized

⚠️ คำเตือน: ในทางปฏิบัติ ควรเก็บ SECRETKEY ไว้ในไฟล์ .env หรือ environment variables เพื่อความปลอดภัยนะครับ

🛣️ เพิ่ม Login Route

เพิ่มในไฟล์: routes/student.route.js

router.post("/login", studentController.login);

🧪 ทดสอบ Login API

POST http://localhost:5000/api/student/login
Content-Type: application/json

{
  "stdId": "12345",
  "stdPass": "password123"
}

✅ Response ที่คาดหวัง:

{
  "message": "login was successfully",
  "payload": {
    "stdId": "12345",
    "stdName": "John Doe",
    "facId": 1,
    "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
  }
}

Section 4-2 - 🛡️ สร้าง Middleware สำหรับ Verify Token

📁 สร้างโฟลเดอร์ Middlewares

mkdir middlewares

🔒 สร้าง Verify Token Middleware

ไฟล์: middlewares/verify-token.js

const jwt = require('jsonwebtoken');

module.exports = {
    verifyToken(req, res, next) {
        let token;

        // ตรวจสอบ token จากหลายแหล่ง
        if (req.headers.authorization && req.headers.authorization.split(' ')[0] === 'Bearer') {
            // Token จาก Authorization header: Bearer <token>
            token = req.headers.authorization.split(' ')[1];
        } else if (req.query && req.query.token) {
            // Token จาก query parameter: ?token=<token>
            token = req.query.token;
        } else if (req.cookies && req.cookies.token) {
            // Token จาก cookie
            token = req.cookies.token;
        }

        // ⚠️ ในทางปฏิบัติ ควรเก็บ SECRETKEY ไว้ใน .env
        const SECRETKEY = process.env.JWT_SECRET || "secret1234";

        if (!token) {
            return res.status(401).json({ 
                auth: false, 
                message: 'No token provided.' 
            });
        }

        jwt.verify(token, SECRETKEY, function (err, decoded) {
            if (err) {
                return res.status(401).json({ 
                    auth: false, 
                    message: 'Failed to authenticate token.' 
                });
            }

            // เก็บข้อมูล decoded token ใน req.user สำหรับใช้ใน routes อื่น
            req.user = decoded;
            next();
        });
    }
}

🔍 อธิบายโค้ด:

verifyToken() - Middleware function สำหรับตรวจสอบ token
jwt.verify() - ตรวจสอบความถูกต้องของ token
req.user - เก็บข้อมูลจาก decoded token
next() - เรียกใช้ middleware หรือ route ถัดไป

⚠️ คำเตือน: ในทางปฏิบัติ ควรเก็บ SECRETKEY ไว้ในไฟล์ .env หรือ environment variables เพื่อความปลอดภัยนะครับ

Section 4-3 - 🔐 การใช้งาน Middleware เพื่อป้องกัน Routes

🛡️ ป้องกัน Faculty Routes

แก้ไขไฟล์: routes/faculty.route.js

const express = require("express");
const router = express.Router();
const facultyController = require("../controllers/faculty.controller");
const VerifyToken = require('../middlewares/verify-token');

// Protected routes - ต้องมี token ถึงจะเข้าถึงได้
router.get("/", VerifyToken.verifyToken, facultyController.findAll);
router.get("/:id", VerifyToken.verifyToken, facultyController.findOne);
router.post("/", VerifyToken.verifyToken, facultyController.createOne);
router.put("/:id", VerifyToken.verifyToken, facultyController.update);
router.delete("/:id", VerifyToken.verifyToken, facultyController.delete);

module.exports = router;

🛡️ ป้องกัน Student Routes

แก้ไขไฟล์: routes/student.route.js

const express = require("express");
const router = express.Router();
const studentController = require("../controllers/student.controller");
const VerifyToken = require('../middlewares/verify-token');

// Public route - ไม่ต้องมี token
router.post("/login", studentController.login);

// Protected routes - ต้องมี token ถึงจะเข้าถึงได้
router.get("/", VerifyToken.verifyToken, studentController.findAll);
router.get("/:id", VerifyToken.verifyToken, studentController.findOne);

module.exports = router;

🧪 ทดสอบ Protected Routes

✅ ทดสอบด้วย Token (สำเร็จ)

GET http://localhost:5000/api/faculty
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

❌ ทดสอบโดยไม่มี Token (ล้มเหลว)

GET http://localhost:5000/api/faculty

Response:

{
  "auth": false,
  "message": "No token provided."
}

Section 4-4 - 📝 ตัวอย่างโค้ดเต็ม

📄 ตัวอย่าง Student Controller (สมบูรณ์)

ไฟล์: controllers/student.controller.js

const db = require('../models')
const studentModel = db.students;
const jwt = require('jsonwebtoken');

// Get all students (Protected)
exports.findAll = async (req, res) => {
    try {
        const response = await studentModel.findAll()
        res.status(200).json({
            message: "get all student was successfully",
            payload: response
        })
    } catch (error) {
        res.status(500).json({
            message: error.message || "get all student was failed"
        })
    }
}

// Get student by id (Protected)
exports.findOne = async (req, res) => {
    try {
        const id = req.params.id
        const response = await studentModel.findOne({
            where: { std_id: id }
        })

        if (response) {
            res.status(200).json({
                message: "get one student was successfully",
                payload: response
            })
        } else {
            res.status(404).json({
                message: "Student not found"
            })
        }
    } catch (error) {
        res.status(500).json({
            message: error.message || "get one student was failed"
        })
    }
}

// Login (Public)
exports.login = async (req, res) => {
    try {
        const id = req.body.stdId;
        const pass = req.body.stdPass;

        const response = await studentModel.findOne({
            where: {
                std_id: id,
                std_pass: pass
            },
            raw: true
        })

        if (response) {
            const SECRETKEY = process.env.JWT_SECRET || "secret1234";

            const token = jwt.sign({
                std_id: response.stdId,
            }, SECRETKEY, {
                expiresIn: "1h",
            });

            delete response.stdPass;

            res.status(200).json({
                message: "login was successfully",
                payload: { ...response, token }
            })
        } else {
            res.status(401).json({
                message: "Invalid credentials"
            })
        }

    } catch (error) {
        res.status(500).json({
            message: error.message || "login student was failed"
        })
    }
}

📄 ตัวอย่าง Student Routes (สมบูรณ์)

ไฟล์: routes/student.route.js

const express = require("express");
const router = express.Router();
const studentController = require("../controllers/student.controller");
const VerifyToken = require('../middlewares/verify-token');

// Public routes
router.post("/login", studentController.login);

// Protected routes
router.get("/", VerifyToken.verifyToken, studentController.findAll);
router.get("/:id", VerifyToken.verifyToken, studentController.findOne);

module.exports = router;

Section 4-5 - 🔧 การใช้ Environment Variables

📦 ติดตั้ง dotenv

npm install dotenv

📄 สร้างไฟล์ `.env`

ไฟล์: .env

JWT_SECRET=your_super_secret_key_here_change_this_in_production
DB_HOST=localhost
DB_USERNAME=root
DB_PASSWORD=
DB_DATABASE=db_std
PORT=5000

⚠️ สำคัญ: อย่าลืมเพิ่ม .env ใน .gitignore เพื่อไม่ให้ commit ไฟล์นี้ขึ้น GitHub นะครับ

🔧 แก้ไข `server.js` เพื่อใช้ dotenv

ไฟล์: server.js

require('dotenv').config(); // เพิ่มบรรทัดนี้ไว้ด้านบนสุด

const express = require('express')
const app = express();
const db = require("./models");

app.use(express.json())
app.use(express.urlencoded({ extended: true }))
const PORT = process.env.PORT || 5000

// ... routes และโค้ดอื่นๆ

🔧 แก้ไข Middleware และ Controller

ไฟล์: middlewares/verify-token.js

const jwt = require('jsonwebtoken');

module.exports = {
    verifyToken(req, res, next) {
        // ... โค้ดเดิม
        const SECRETKEY = process.env.JWT_SECRET; // ใช้จาก .env
        // ... โค้ดเดิม
    }
}

ไฟล์: controllers/student.controller.js

// ... โค้ดเดิม
const SECRETKEY = process.env.JWT_SECRET; // ใช้จาก .env
// ... โค้ดเดิม

🎉 สรุป Module 4

เราได้เรียนรู้การสร้างระบบ Authentication และ Authorization ด้วย JWT ครบทั้ง:

ฟีเจอร์	คำอธิบาย
🔑 JWT Authentication	สร้าง token สำหรับยืนยันตัวตน
🛡️ Middleware Protection	ป้องกัน routes ด้วย middleware
🔒 Token Verification	ตรวจสอบความถูกต้องของ token
🔐 Secure Routes	แยก public และ protected routes
🌍 Environment Variables	เก็บ secret keys อย่างปลอดภัย

📋 สรุป API Endpoints

Public Endpoints (ไม่ต้องมี Token)

Method	Endpoint	Description
`POST`	`/api/student/login`	Login และรับ token

Protected Endpoints (ต้องมี Token)

Method	Endpoint	Description
`GET`	`/api/faculty`	ดึงข้อมูล faculty ทั้งหมด
`GET`	`/api/faculty/:id`	ดึงข้อมูล faculty ตาม id
`POST`	`/api/faculty`	สร้าง faculty ใหม่
`PUT`	`/api/faculty/:id`	อัปเดตข้อมูล faculty
`DELETE`	`/api/faculty/:id`	ลบ faculty
`GET`	`/api/student`	ดึงข้อมูล student ทั้งหมด
`GET`	`/api/student/:id`	ดึงข้อมูล student ตาม id

🧪 ตัวอย่างการใช้งาน

1. Login เพื่อรับ Token

POST http://localhost:5000/api/student/login
Content-Type: application/json

{
  "stdId": "12345",
  "stdPass": "password123"
}

2. ใช้ Token เพื่อเข้าถึง Protected Routes

GET http://localhost:5000/api/faculty
Authorization: Bearer <your_token_here>

Reference

akkaraponph / basic-nodejs-express-sequelize-mysql

สร้าง API โดยใช้ JavaScript Node.js Express

หมายเหตุ Tutorial นี้ได้จัดทำขึ้นเมื่อ (April 4th, 2023) และได้รับการปรับปรุงเมื่อ (January 8th, 2026)

🚀 การสร้าง API โดยใช้ JavaScript Node.js Express

คู่มือการพัฒนา RESTful API แบบครบวงจรด้วย Node.js, Express, Sequelize และ MySQL

⬆ กลับไปด้านบน • 📚 สารบัญ

📖 คำนำ

🌐 Express HTTP Framework - สำหรับสร้าง web server
🗄️ Sequelize - ORM สำหรับจัดการฐานข้อมูล
💾 MySQL - ฐานข้อมูลเชิงสัมพันธ์
🔧 POSTMAN - สำหรับทดสอบ API

DEV Community: Akkarapon Phikulsri

Theory of Humanistic Architecture

Humanistic Architecture Learning to See Problems Differently

Anatomy of a Problem

Another model is the 3 Centers Model

The Anatomy of a Problem

The Anatomy of a Problem

The Anatomy of a Problem

Git - Commit Message Format

PostgreSQL Cheat Sheet: Investigating and Terminating Database Blocking Sessions

Why Can PostgreSQL Sessions Block Each Other?

Step 1: Inspect All Non-Idle Sessions

Step 2: Use a Quick Triage Query

Step 3: Find Idle-in-Transaction Sessions

Step 4: Terminate an Idle Transaction

Step 5: Cancel a Running Query

pg_cancel_backend() vs pg_terminate_backend()

Step 6: Find Who Is Blocking a Query

Finding the Root Blocker

Step 7: Check Which Database Uses the Most Connections

Step 8: Check Connection Headroom

Preventing Idle Transactions

Protect Against Long-Running Queries

Log Lock Problems

Fix the Problem in the Application

Connection Pools Matter Too

My Production Troubleshooting Flow

Useful Commands Summary

Final Thoughts

Fixing "GOPROXY list is not the empty string, but contains no entries" in Go

What This Error Actually Means

Step-by-Step Fix

1. Check for a shell-level override

2. Clear the session override

3. Persist a valid proxy in Go's config

4. Sanity-check related settings

5. Clear Go's caches if the problem persists

6. Retry

Windows/MINGW64 Gotcha

Quick Reference Checklist

Command Cheat Sheet

GitHub Spark - A First Look at GitHub’s New Developer Tool

What is GitHub Spark?

Quick prompt

Referencing Repositories

Comparison with Lovable

Playground

Theme

Data Mockup

Assets

Turning Assets into Action

akkaraponph / income-expense

for fundamental data science

Datasets-Income-Expense

For fundamental data science

Simple Datasets

Repo. Directory Structure

Exsamples

Publish

Is GitHub Spark the Future of Rapid Prototyping?

DeltaView - AI-Driven JSON Diff Viewer Built with GitHub Copilot

DeltaView - AI-Driven Diff Viewer Built with GitHub Copilot

What I Built

Key Features:

Technology Stack:

🎬 Features & Demo

DeltaView - All-in-One Developer Toolkit | JSON Converters, Generators, Editor & Schema Converters

1. 📝 JSON Compare Input Panel

2. 🔍 JSON Comparison Results

3. 📊 JSON Graph Visualization

4. 📋 Table View

5. ✨ JSON Formatter

🌐 Technology & Performance

My Experience with GitHub Copilot CLI

How I Used GitHub Copilot CLI

Impact on Development

Key Learnings

🚀 Get Started with GitHub Copilot - Easy, Fun & Productive!

Why GitHub Copilot?

✨ Key Benefits

`pg_cancel_backend()` vs `pg_terminate_backend()`