Ch. 3: Persist everything

Linkly’s links live in iii-state, which you set to in-memory back in Chapter 1. Restart the engine and everything is gone. In this chapter you add a database worker (SQLite) that holds the durable record of links and a timestamped row for every click on a short code. iii-state stays in the picture as a fast read cache in front of the database.

iii-state can also persist on its own (store_method: file_based with a file_path). This chapter uses a dedicated database worker instead, which gives you durable storage plus SQL to query it.

Add the database worker

State is a fast cache, but you also want a durable record you can run SQL over: every link, and a timestamped row each time someone follows one. Add the database worker:

iii worker add database
mkdir -p data

The engine has been running since Chapter 1, so it starts the database worker as soon as it is added, and the worker’s settings are managed in ./config/database.yaml from that point on (see Configuration). Adjust the fields under value: in that file so it looks like the below and save; the change applies without a restart.

The database worker will automatically create ./data/iii.db on first run.

The database worker supports more than SQLite, refer to the database worker docs for all supported databases.

config/database.yaml

id: database
# ...
value:
  databases:
    primary:
      pool:
        acquire_timeout_ms: 5000
        idle_timeout_ms: 30000
        max: 10
      url: sqlite:./data/iii.db

The worker will be in charge of defining its own schema. We’ll build up the necessary changes to link/src/index.ts in pieces.

Define the database

First add the DB constant near the top of link/src/index.ts:

src/index.ts

import { registerWorker } from "iii-sdk";
import { Logger } from "@iii-dev/helpers/observability";

const DB = "primary"; // Matches db name in config.yaml

Make link storage persistent

Now we’re going to adapt the existing link::create and link::resolve functions so that they write and read from our new database while using our state worker as a hot cache.

Create a schema

Add an ensureSchema() function at the end of link/src/index.ts that creates both tables on startup. The database worker accepts SQL through its database::execute function:

src/index.ts

async function ensureSchema(): Promise<void> {
  await worker.trigger({
    function_id: "database::execute",
    payload: {
      db: DB,
      sql: "CREATE TABLE IF NOT EXISTS links (code TEXT PRIMARY KEY, url TEXT NOT NULL, created_at TEXT NOT NULL)",
    },
  });
  await worker.trigger({
    function_id: "database::execute",
    payload: {
      db: DB,
      sql: "CREATE TABLE IF NOT EXISTS clicks (id INTEGER PRIMARY KEY AUTOINCREMENT, code TEXT NOT NULL, clicked_at TEXT NOT NULL)",
    },
  });
}

ensureSchema()
  .then(() => logger.info("database: ready"))
  .catch((err) => logger.error("database: schema init failed", { error: String(err) }));

Setup database writing

Modify link::create to write to both the database (durable record) and iii-state (hot cache):

src/index.ts

worker.registerFunction("link::create", async (payload: { url: string; code?: string }) => {
  const code = payload.code ?? makeCode();
  const url = /^https?:\/\//i.test(payload.url) ? payload.url : `https://${payload.url}`;
  await worker.trigger({
    function_id: "database::execute",
    payload: {
      db: DB,
      sql: "INSERT INTO links (code, url, created_at) VALUES (?, ?, ?)",
      params: [code, url, new Date().toISOString()],
    },
  });
  await worker.trigger({
    function_id: "state::set",
    payload: { scope: "links", key: code, value: { url } },
  });
  logger.info("link created", { code, url });
  return { code, url };
});

Setup database retrieval

Modify link::resolve to check the cache first; on a miss, fall back to the database and warm the cache for the next read. It’s easiest to replace the existing link::resolve function with our new version:

src/index.ts

worker.registerFunction("link::resolve", async (payload: { code: string }) => {
  const cached = await worker.trigger<{ scope: string; key: string }, { url: string } | null>({
    function_id: "state::get",
    payload: { scope: "links", key: payload.code },
  });
  if (cached) {
    logger.info("link resolved", { code: payload.code, found: true });
    return { url: cached.url };
  }
  const { rows } = await worker.trigger<
    { db: string; sql: string; params: string[] },
    { rows: Array<{ url: string }> }
  >({
    function_id: "database::query",
    payload: { db: DB, sql: "SELECT url FROM links WHERE code = ?", params: [payload.code] },
  });
  const url = rows[0]?.url ?? null;
  if (url) {
    await worker.trigger({
      function_id: "state::set",
      payload: { scope: "links", key: payload.code, value: { url } },
    });
  }
  logger.info("link resolved", { code: payload.code, found: !!url });
  return { url };
});

Add click tracking

Since we have a database now, you can start click tracking. Make a new function (link::record_click) to do that and save it to the database. The next chapter will move this work onto a queue so that it can run without touching the redirect’s logic. Add it below link::resolve:

src/index.ts

worker.registerFunction(
  "link::record_click",
  async (payload: { code: string; clicked_at: string }) => {
    await worker.trigger({
      function_id: "database::execute",
      payload: {
        db: DB,
        sql: "INSERT INTO clicks (code, clicked_at) VALUES (?, ?)",
        params: [payload.code, payload.clicked_at],
      },
    });
    return { recorded: true };
  },
);

Update `http::redirect` to call `link::record_click`

Now update http::redirect to trigger it directly, right before returning the redirect:

src/index.ts

worker.registerFunction("http::redirect", async (req) => {
  const code = req.path_params.code;
  const { url } = await worker.trigger<{ code: string }, { url: string | null }>({
    function_id: "link::resolve",
    payload: { code },
  });
  if (!url) {
    return {
      status_code: 404,
      body: { error: "link not found" },
      headers: { "Content-Type": "application/json" },
    };
  }
  // This Trigger is slow because it waits on link::record_click's completion, we'll move its work to a queue soon
  await worker.trigger({
    function_id: "link::record_click",
    payload: { code, clicked_at: new Date().toISOString() },
  });
  return { status_code: 302, headers: { Location: url } };
});

The database write for clicks adds latency to every redirect. The next chapter moves it onto a durable queue that removes the latency while also adding recovery from database failures.

Try the click tracking

Now let’s see the click tracking in action. Save the file, create a link, and simulate clicking it a few times:

curl -s -X POST http://127.0.0.1:3111/links \
  -H 'Content-Type: application/json' -d '{"url":"https://iii.dev","code":"iii"}'
for n in $(seq 1 3); do curl -s -o /dev/null http://127.0.0.1:3111/s/iii; done

The durable history is now queryable with SQL:

iii trigger database::query db=primary sql="SELECT COUNT(*) AS clicks FROM clicks WHERE code = 'iii'"

{
  "columns": [
    {
      "name": "clicks",
      "type": ""
    }
  ],
  "row_count": 1,
  "rows": [
    {
      "clicks": 3
    }
  ]
}

Conclusion

Did you know that --help works with function id’s as well? Try running: iii trigger database::query --help to see what arguments database::query accepts.

Linkly’s links are now durable: the database is the source of truth, iii-state keeps lookups fast, and every redirect appends a timestamped row to the clicks table. But that row is written on the redirect’s hot path, so a slow database write slows the redirect. Next, in Ch. 4: Make it durable, you move that write onto a queue so redirects stay fast.

​Add the database worker

​Define the database

​Make link storage persistent

​Create a schema

​Setup database writing

​Setup database retrieval

​Add click tracking

​Update http::redirect to call link::record_click

​Try the click tracking

​Conclusion

Add the database worker

Define the database

Make link storage persistent

Create a schema

Setup database writing

Setup database retrieval

Add click tracking

Update `http::redirect` to call `link::record_click`

Try the click tracking

Conclusion