This post explores Postgres exclusion constraints through a simple but evolving scenario, exclusion constraints have been in Postgres since 9.0.

## Don't exclude me!

I've recently been introduced to the concept of treating database tables like they are ledgers (where you don't edit the original, you instead create a new record that extends from the old) as an alternative to using history tables to track changes to rows. Schema design with tables acting as ledgers introduced new technical challenges to me. One example (that I'll expand on later) is that you can no longer use a unique index constraint to guarantee that a combination of values are never re-used in a different row (without further normalising).

## Scenario

Let's pretend we're designing a schema for a bus transportation company, called Bus-sy. Bus-sy wants to keep a register of their buses so they have a single place to look up the following information. They've told us that:

- a bus has a unique id
- a bus has a registration number
- a bus optionally has a driver that can change

We aren't given any constraints from Bus-sy yet, other than they want us to treat the register like a ledger.

> **DISCLAIMER**: This is a somewhat contrived scenario to support using a single ledger table in place of instead normalising these tables, this might be considered using exclusion constraints for the wrong reasons, or hacking around the problem. You could avoid this by instead favouring three tables to put the schema into 2NF: `bus_register`, `bus_registration`, and `bus_driver`, all you would need is then some unique constraints. With that design it's likely `bus_driver` is the only table that gets updated as well.

```sql
create table bus_register_ledger(
  -- auto-incrementing id, useful if you ever need to reference a specific ledger entry at a point in time
  id serial primary key,
  -- the "ledger" id, this gets re-used across all updates to this entity
  bus_id text not null,
  registration text not null,
  driver text,
  created_at timestamptz not null default clock_timestamp()
);
```

## When would you use an exclusion constraint?

Simply put, exclusion constraints allow you to perform check(s) across two rows, and if all checks are truthy, then the constraint has failed (I'll demonstrate this manually later). These checks are likely to be equality (`=`), inequality (`<>`), and overlaps (`&&`), however, there is an abundance of operators that can be used on `gist` indexes, see [the builtin gist op classes here](https://www.postgresql.org/docs/15/gist-builtin-opclasses.html).

## Implementing an exclusion constraint

Bus-sy has told us that they'd like us to capture the fact that buses cannot share a registration, nor can it ever be re-used by another bus. In other words, we need a constraint that enforces that new rows cannot use an already used registration value UNLESS it is already associated with the id of the bus we're adding a new row for.

Right now, there's nothing stopping us from inserting rows into our `bus_register_ledger` table that would break the above requirement.

```sql
insert into
  bus_register_ledger(bus_id, registration)
values
  ('bus-id-1', 'bussy1'),
  ('bus-id-2', 'bussy1');
-- INSERT 0 2

select * from bus_register_ledger;
-- id  bus_id    registration  driver  created_at
-- 1   bus-id-1  bussy1        <null>  2023-03-06 19:39:00.353883+00
-- 2   bus-id-2  bussy1        <null>  2023-03-06 19:39:00.354673+00
```

As expected, nothing is stopping new rows from being inserted that break the business requirement. Let's fix that, maybe we could add a unique index here instead?

```sql
create unique index unique_bus_id_registration_pair on bus_register_ledger (bus_id, registration);

insert into
  bus_register_ledger(bus_id, registration)
values
  ('bus-id-1', 'bussy1'),
  ('bus-id-2', 'bussy1');
-- INSERT 0 2
```

Well, that didn't work, makes sense too, that unique index only guarantees that a bus+registration pair are never re-used. What if we tweaked it to also instead have multiple unique indexes?

```sql
create unique index unique_bus_id_registration_pair on bus_register_ledger (bus_id, registration);

create unique index unique_bus_id on bus_register_ledger (bus_id);

create unique index unique_registration on bus_register_ledger (registration);

insert into
  bus_register_ledger(bus_id, registration)
values
  ('bus-id-1', 'bussy1');

insert into
  bus_register_ledger(bus_id, registration)
values
  ('bus-id-2', 'bussy1');

-- duplicate key value violates unique constraint "unique_registration"
-- DETAIL:  Key (registration)=(bussy1) already exists.
```

Now we're getting somewhere... But what if we wanted to update the driver for a given bus? Remember that we're operating with ledgers here, so we need to roll forward with the new value instead of updating it in place. We're probably going to have some issues. Let's try anyway!

```sql
-- same three indexes as before
create unique index unique_bus_id_registration_pair on bus_register_ledger (bus_id, registration);

create unique index unique_bus_id on bus_register_ledger (bus_id);

create unique index unique_registration on bus_register_ledger (registration);

insert into
  bus_register_ledger(bus_id, registration, driver)
values
  ('bus-id-1', 'bussy1', 'jessica');

-- assign new driver to the bus
insert into
  bus_register_ledger(bus_id, registration, driver)
select
  bus_id,
  registration,
  'adam'
from
  bus_register_ledger
where
  bus_id = 'bus-id-1';

-- duplicate key value violates unique constraint "unique_bus_id_registration_pair"
-- DETAIL:  Key (bus_id, registration)=(bus-id-1, bussy1) already exists.
```

The constraints are doing what we told them to do here... But they're stopping us from rolling forward with the old row data. To solve for this, lets introduce an exclusion constraint!

```sql
create extension if not exists btree_gist;

alter table
  bus_register_ledger
add
  constraint unique_bus_id_registration_pair exclude using gist (registration with =, bus_id with <>);
```

Before we test if the above works (hint: it does 🤓), let's break the SQL apart a bit.

The first line adds the `btree_gist` extension, useful exclusion constraints require you to use `gist` indexes (or `sp-gist`). If your exclusion constraint works with a standard `btree` index, it could've been implemented more efficiently as an ordinary unique index. We're using a `gist` index here for two reasons. It gives us access to the `<>` (not equals) operator in the constraint, and its internal data structure is [better suited](https://www.postgresql.org/docs/current/btree-gist.html) to efficiently compare values across multiple rows.

The second line alters the table to add an exclusion constraint. We've specified two expressions in our constraint, one for `bus_id` inequality, and another for `registration` equality. The `with` keyword is baked into the syntax of an exclusion constraint, do not confuse this with the `with` keyword you would see in a CTE.
In plain English, we've told Postgres that if we modify or insert a new row, it must not be using a registration that is otherwise NOT already assigned to the bus. Exclusion constraints compare two rows at a time, let's demonstrate that by emulating these checks manually - Postgres would be doing this a lot more efficiently by using `gist` indexes.

```sql
-- continuing from the above (we've created the exclusion constraint)

insert into
  bus_register_ledger(bus_id, registration, driver)
values
  ('bus-id-1', 'bussy1', 'jessica');
-- INSERT 0 1

select * from bus_register_ledger;
-- id  bus_id    registration  driver   created_at
-- 1   bus-id-1  bussy1        jessica  2023-03-06 19:48:33.662441+00

```

The constraint allows this change because no other rows exist to compare it against. There is no check for us to emulate, let's add another row.

```sql
insert into
  bus_register_ledger(bus_id, registration, driver)
values
  ('bus-id-2', 'bussy2', 'john');
-- INSERT 0 1

select * from bus_register_ledger;
-- id  bus_id    registration  driver   created_at
-- 1   bus-id-1  bussy1        jessica  2023-03-06 19:48:33.662441+00
-- 2   bus-id-2  bussy2        john     2023-03-06 19:48:33.670534+00
```

We've added another row, it was successful because there is no overlap on registration. But what did the check look like? Remember that exclusion constraints run the checks across two rows at a time. There's only one row in the table at this time, so we'll compare our new update with the existing row.

Row with id **`1`**, has

- `bus_id = bus-id-1`
- `registration = bussy1`

The row we've tried to insert has

- `bus_id = bus-id-2`
- `registration = bussy2`

Our constraint has two checks, `bus_id with <>` and `registration with =`.

Evaluating the `bus_id with <>` check looks like: `bus-id-1 !== bus-id-2`, so we have `TRUE`.

Evaluating the `registration with =` check looks like: `bussy1 === bussy2`, so we have `FALSE`.

Inserts/updates are only considered to violate exclusion constraints if ALL expressions in the constraint evaluate to be true. Therefore this check has **not** violated the constraint. This is the only row to compare to, so the insert is allowed!

Let's add another row, this time we'll change the driver of the bus with id `bus-id-1`. In the example below we're carrying forward the `bus_id` and `registration`.

```sql
insert into
  bus_register_ledger(bus_id, registration, driver)
select
  bus_id,
  registration,
  'adam'
from
  bus_register_ledger
where
  bus_id = 'bus-id-1';
-- INSERT 0 1

select * from bus_register_ledger;
-- id  bus_id    registration  driver   created_at
-- 1   bus-id-1  bussy1        jessica  2023-03-06 19:48:33.662441+00
-- 2   bus-id-2  bussy2        john     2023-03-06 19:48:33.670534+00
-- 3   bus-id-1  bussy1        adam     2023-03-06 19:48:33.680098+00
```

Again, the insert was successful, our new row is the third one, but what did the check look like? Now that we've got multiple rows in the table, we'll need to execute the check expressions across both of them.

Row with id **`1`**, has

- `bus_id = bus-id-1`
- `registration = bussy1`
- `driver = jessica`

The row we've tried to insert has

- `bus_id = bus-id-1`
- `registration = bussy1`
- `driver = adam`

Our constraint has two checks, `registration with =` and `bus_id with <>`.

Evaluating the `bus_id with <>` check looks like: `bus-id-1 !== bus-id-1`, so we have `FALSE`.

Evaluating the `registration with =` check looks like: `bussy1 === bussy1`, so we have `TRUE`.

An exclusion constraint is only violated if ALL expressions in the constraint evaluate to be true, so semantically we have `FALSE` AND `TRUE`, which is `FALSE`. Therefore this insert is allowed against **this** row. Let's check the next row.

Row with id **`2`**, has

- `bus_id = bus-id-2`
- `registration = bussy2`
- `driver = john`

The row we've tried to insert has

- `bus_id = bus-id-1`
- `registration = bussy1`
- `driver = adam`

Our constraint has two checks, `registration with =` and `bus_id with <>`.

Evaluating the `bus_id with <>` check looks like: `bus-id-2 !== bus-id-1`, so we have `TRUE`.

Evaluating the `registration with =` check looks like: `bussy2 === bussy1`, so we have `FALSE`.

An exclusion constraint is only violated if ALL expressions in the constraint evaluate to be true, so semantically we have `TRUE` AND `FALSE`, which is `FALSE`. Therefore this insert is allowed against **this** row.

This insert was compared against all available rows and the exclusion constraint wasn't violated, this insert was allowed.

Alright, so far so good. Let's try and break the constraint and then understand why by looking at the truth table. To break the constraint, we'll attempt to register a new bus with id `bus-id-3` using the same registration as `bus-id-2`.

```sql
insert into
  bus_register_ledger(bus_id, registration, driver)
values
  ('bus-id-3', 'bussy2', 'brendan');

-- conflicting key value violates exclusion constraint "unique_bus_id_registration_pair"
-- DETAIL:  Key (registration, bus_id)=(bussy2, bus-id-3) conflicts with existing key (registration, bus_id)=(bussy2, bus-id-2).
```

That insert failed, good stuff! Let's compare our insert against all three existing rows and see which row caused the constraint to fail.

Row with id **`1`**, has

- `bus_id = bus-id-1`
- `registration = bussy1`
- `driver = jessica`

The row we've tried to insert has

- `bus_id = bus-id-3`
- `registration = bussy2`
- `driver = brendan`

Our constraint has two checks, `registration with =` and `bus_id with <>`.

Evaluating the `bus_id with <>` check looks like: `bus-id-1 !== bus-id-3`, so we have `TRUE`.

Evaluating the `registration with =` check looks like: `bussy1 === bussy2`, so we have `FALSE`.

An exclusion constraint is only violated if ALL expressions in the constraint evaluate to be true, so semantically we have `TRUE` AND `FALSE`, which is `FALSE`. Therefore this insert is allowed against **this** row.

Row with id **`2`**, has

- `bus_id = bus-id-2`
- `registration = bussy2`
- `driver = john`

The row we've tried to insert has

- `bus_id = bus-id-3`
- `registration = bussy2`
- `driver = brendan`

Our constraint has two checks, `registration with =` and `bus_id with <>`.

Evaluating the `bus_id with <>` check looks like: `bus-id-2 !== bus-id-3`, so we have `TRUE`.

Evaluating the `registration with =` check looks like: `bussy === bussy2`, so we have `TRUE`.

An exclusion constraint is only violated if ALL expressions in the constraint evaluate to be true, so semantically we have `TRUE` AND `TRUE` here, which is `TRUE`. Therefore this insert has violated the constraint against the second row in our bus register.

So the check against row 1 was allowed as the registrations are different. The check against row 2 was NOT allowed. This is because both expressions returned `TRUE`. The `bus_id`'s are different, but the `registration`'s are the same. I didn't show it here, but the check against the third row would've been allowed as the registrations are different. Wouldn't surprise me however if the query planner is smart enough to realise that the checks are executing on the same values, and its skips them, making this highly efficient in a ledger environment.

Hopefully, this helped you visualise how exclusion constraints work.

Postgres Exclusion Constraints

## Table of contents

## Cloudflare's Free Tier Goes a Long Way

With the (somewhat) recent releases of Cloudflare's blob storage and distributed sql products, [R2](https://blog.cloudflare.com/r2-ga/) and [D1](https://blog.cloudflare.com/introducing-d1/) respectively, they've amassed quite an arsenal of tools on their free tier. You've got compute in the form of Workers, blob storage through R2, key/value storage in Workers KV, and a sql-lite compliant strongly consistent (with some caveats, more later), globally available database through D1 (still in alpha). With that combination you can **build or hack on nearly any idea**!

In this post, I'll be building a small application that uses a handful of the offerings in Cloudflare's free tier:

- [Pages](https://pages.cloudflare.com/)
- [Workers](https://workers.cloudflare.com/)
- [R2](https://developers.cloudflare.com/r2/)
- [D1](https://blog.cloudflare.com/introducing-d1/)

Worth mentioning, is that Cloudflare also offer DNS configuration and CDN services as apart of their free tier. I won't be diving into how they can be used in this series - but they deserve a shout out, I love their DNS configuration tool.

## Let's Build an App!

Coming up with a moderately useful idea that runs on the aforementioned technologies was a bit tricky. Ultimately, it came down to either building a simple analytics service or an interactive resume requesting tool. I landed on resume requester as its something I can template and ideally provides value to others.

## Resume Requester

One of my favourite sites on the internet has to be [Robby Leonardi's interactive resume](http://www.rleonardi.com/interactive-resume/), it's fun, interactive, informative and demonstrates a lot of his skills. Robby has done a great job of introducing his skills through an interactive and gimmicky experience! Now I'm no fancy designer, so I won't even try to replicate that - but I do like the idea of your personal website being your brand, your portfolio, and the home of your resume.

But... My resume has my private email and my mobile number on it, thats not information that I like to give out willy nilly! So to solve for this I'll build an application on top of Cloudflare's free tier that introduces an "approval" step in the resume requesting process process.

### High Level Process





1. Someone "requests" my resume
2. I get a notification on my phone, giving me two options:
   1. _release_ the resume
   2. _reject_ the request
3. If released, the requester will receive my email in their inbox
4. If rejected, the requester will receive an email politely notifying them of the denial

### Architecture

We can look at how the different parts interact in a bit more detail in this architecture diagram below:

<figure>
  <img src="/assets/blog/exploring-cloudflares-free-tier/document-requester-transparent.webp" alt="a diagram drawn using excalidraw that shows an overview of the architecture used in this project">
  <figcaption>A high-level architecture diagram for the resume requester project</figcaption>
</figure>

The Cloudflare worker is at the centre of solving this problem, it will interact with:

- D1 -> to write down requests as they come in, and update them as I manually "release" or "reject" the request. Also used as a cache of sorts over the content in R2 (more on the why behind this soon)
- R2 -> to fetch the PDF from blob storage
- KV store -> to do some basic ip rate limiting
- [ntfy.sh](https://ntfy.sh/) -> to forward push notifications
- [SendGrid](https://sendgrid.com/) -> to forward my resume on to the requester

### Final Product

<video controls muted>
  <source src="/assets/blog/exploring-cloudflares-free-tier/demo.mp4" type="video/mp4" />
  <p>
    Your browser doesn't support HTML video.
  </p>
</video>

If you'd rather just read the code, the implementation for the application can be found [here](https://github.com/korziee/document-requester). I won't be going into detail about how to setup a project on Cloudflare workers, their docs are really good. If you follow [this](https://developers.cloudflare.com/workers/get-started/guide/), you can have a worker running on the edge in minutes.

## Building on the Free Tier

Before I dive into the different offerings on the free tier, I'd like to call out `wrangler`, Cloudflare's CLI for all thing developer(y)! The ease of deployment, development, and configuration thanks to Wrangler is possibly the best I've seen or used to date in my career. It's insanely quick, easy to use, and has clear error messages. Wrangler enables you to spin up workers, setup a dev environment, add secrets, spin up instances of D1, etc.

### Pages

Pages are a no bullshit product by Cloudflare, at its simplest all you do is point it at a git repository, tell it what type of frontend application it is (react, vue, next, etc.), and on each push to the origin, it'll build and deploy your frontend to a public domain backed by SSL. If you push to a branch that isn't main, it'll build and deploy the frontend application to a preview url.

Moving on from the simplest path, Pages offer the ability to deploy serverless functions that your frontend can interact with. Pretty much just workers, but from within the same codebase. These contribute to the free tier limits of Cloudflare workers, quite generously at 100,000 invocations as of right now (2023/01/31).

https://koryporter.com is hosted on Cloudflare Pages. I connected the git repo for this website, followed the UI prompts and when asked what type of frontend I am deploying, I selected Next.js static site, and it did the rest! Only change I've made was wiring up a custom domain to the pages deployment. https://koryporter.com is a CNAME pointing to the pages deployment origin.

### Workers

Workers are just serverless compute... but on the edge. Ok that was a bit reductive, but its mostly true. I say mostly because I believe they differ in two key areas:

1. custom runtime, and
2. the tooling (wrangler)

I'll expand on the custom runtime shortly.

It's pretty common to for serverless providers to be prescriptive in how you structure the entrypoint to your application. E.g, you need to have a file named `x`, with a function named `y` inside it as your entry point. So seeing the following which closely mirrors the cloudflare getting started docs, wasn't a massive surprise:

```typescript
export default {
  async fetch(
    request: Request,
    env: Env,
    ctx: ExecutionContext
  ): Promise<Response> {
    return new Response("G'day friend!", { status: 200 });
  },
};
```

What was a surprise was that the function name `fetch` isn't completely arbitrary. Cloudflare workers are based, at least partly so, on the Web Workers API. Which means you get everything in the [WorkerGlobalScope](https://developer.mozilla.org/en-US/docs/Web/API/WorkerGlobalScope) available to you. So when your worker gets lit up, a `FetchEvent` is passed directly to your fetch handler! More on this [here](https://developers.cloudflare.com/workers/learning/service-worker/). You could also represent the above in standard JS Service Workers syntax:

```javascript
addEventListener("fetch", (event) => {
  event.respondWith(handleRequest(event.request));
});

async function handleRequest(request) {
  return new Response("G'day friend!", { status: 200 });
}
```

I mentioned earlier that the runtime is what differentiates Cloudflare from many of the other serverless providers. One of the reasons Cloudflare can bill based on CPU cycles, and also the reason why there is **no** cold starts - is that each Worker runs on a V8 isolate, they are essentially little sandboxes that run within a single v8 runtime. This means your worker code, and a complete strangers worker code, can be running along side each other in the same v8 process, on the same host! A V8 runtime switches between many (hundreds, thousands) isolates constantly, and memory between isolates is not shared (_isolated_ 🤠). A fantastic explanation, with graphics explaining the concept can be found [here](https://developers.cloudflare.com/workers/learning/how-workers-works/#isolates) on the Cloudflare website.

### KV

I have an irrational fear of releasing anything that relies on compute in the public, without first having some form of rate limiting. I'm not sure how deep we want to analyse this - but it likely comes back to those invasive thoughts that most engineers get when their friend says "oh yeah, it's pretty solid, you couldn't bring it down" - "hehe, watch me"! Well to try and protect this service somewhat from bad actors (and also an excuse to use both KV and D1 in the same project) I introduced some rate-limiting.

KV is an eventually consistent global data store. This means you aren't guaranteed access to the latest data for a key on **read**. Writes aren't distributed, nor are they broadcasted after the event, they go to the central data centre which could be in an entirely different region to the edge. As I said, writes aren't broadcasted after they've been written, if you request a value from KV and its previous value is already cached on the edge, you'll be left waiting potentially up to 60 seconds for that value to expire until you are able to fetch and consume the fresh value.

Another alternative to solve this problem would be to use `Durable Objects` ([link](https://developers.cloudflare.com/workers/learning/using-durable-objects/)). Durable objects support (not sure if they **guarantee**) atomic writes and reads, so you're less likely to get stale data on a read after a write. Durable Objects are no on the free tier though, you need to upgrade to a paid plan to get access to them.

> The eventually consistent nature of KV store isn't really a problem for my use case, I don't need overly accurate rate limits.

Nothing really interesting to report on dev interaction side of KV, it... gets keys, and sets values... The free tier is set at 100,000 reads, and 1,000 writes. Workers KV also ships with the ability to set a TTL on values, which opens it up to more use cases (like time bound caching).

It's as simple as:

```typescript
// reading
const ipHits = await env.KV.get(`limit:${ip}`);

// writing (with a TTL)
await env.KV.put(`limit:${ip}`, `${hits + 1}`, { expirationTtl: 10 * 60 });
```

### R2

If you have used a form of blob/object storage before (AWS S3, GCP Cloud Storage, Azure Blob Storage), then R2 won't come as a shock to you. It stores unstructured data. One differentiator between R2 and it's competitors like S3 or Cloud Storage, is that it **doesn't** charge on data egress. The pricing model is based around storage size, and api requests, not ingress or egress!

My use of R2 was very simple in this project, I used it to list documents, and read documents. I go into more detail on my usage of R2 with D1 below.

<figure>
  <img src="/assets/blog/exploring-cloudflares-free-tier/r2-dashboard.webp" alt="a screenshot of the cloudflare r2 dashboard containing two files, one for my resume, and another for testing">
  <figcaption>Cloudflare R2 Dashboard</figcaption>
</figure>

### D1

I started this article by saying D1 was a "sql-lite compliant strongly consistent (with some caveats, more later), globally available database". This is all true at a certain point in time, but when read replicas are created, D1 is no strongly consistent. When Cloudflare announced D1 (source: [Announcing D1](https://blog.cloudflare.com/introducing-d1/), they mentioned that D1 is built on top of Durable Objects, which guarantees strong consistency, no matter where the object is accessed from in the world. So I assume, for the most part they get the "strong" part of consistency from Durable Objects, but that falls apart if you start creating replicas - there will naturally be some lag propagating the change from the master back to the slaves/replicas. _There isn't a lot of information around this behaviour yet, it's still in alpha._

**D1 is still in early alpha, it's really important to note that it's likely most qualms I experienced with D1 are likely to be ironed out before this gets into an RC state.**

The main issue I experienced with D1, or more specifically, _the combination of D1 with Wrangler_, was that you're unable to run a migration against preview database directly. You need to modify your wrangler configuration to add an extra "environment", and then run the migration. But it seems like the Cloudflare team are [aware of it](https://github.com/cloudflare/wrangler2/issues/2446).

Another point of contention that only arose once I introduced D1 to the project, was the inability to test scheduled events (CRON triggers for the worker), but again, [sounds like its on their radar](https://github.com/cloudflare/wrangler2/issues/2666). This (testing scheduled events) is a really neat feature of the wrangler CLI, so it was a bit of a bummer when it errored out during testing. I should note that D1 + CRON triggers work perfectly -> once the application is deployed. You just can't test it locally - no biggie, it's still in alpha remember!

Within the document-requester app I ended up using D1 for two things, the first I'd consider normal usage of a relational data model, and the second a complete hack to ensure I wasn't pushing passed the 10ms CPU time limits of the free tier.

1. Storing Requests
2. Caching a Base64 content of blobs from R2

Let's dive into those below.

#### Storing Requests

Nothing of note to report here, I used this table to write down and keep track of state for the requests for my resume. It serves a few purposes:

1. writing down requests -> who they're from, and what they're for
2. updating requests -> they're either accepted or rejected.

<figure>
  <img src="/assets/blog/exploring-cloudflares-free-tier/document-requests-schema.webp" alt="an output from carbon.now.sh which contains a textual representation of the database schema for the documents-request sqlite table">
  <figcaption>Schema for the document_requests table</figcaption>
</figure>

#### Caching Base64 Copies of R2 Objects

Just as I was wrapping up the implementation of the `document-requester` project I checked the dashboard in Cloudflare to see how it was performing. I noticed that whenever an email was sent (after accepting/rejecting the request) the average CPU time spiked over 50ms. This is a problem, you're only guaranteed 10ms of CPU time on the free tier, I don't want requests to be dropped. 😱

<figure>
  <img src="/assets/blog/exploring-cloudflares-free-tier/cpu-spike.webp" alt="screenshot of the cloudflare workers dashboard, showing a spike in CPU above 50ms on a purple line">
  <figcaption>Cloudflare Workers dashboard</figcaption>
</figure>

This spike is triggered because of the way SendGrid requires attachments to be formatted. SendGrid expect attachments to be a Base64 representation of a binary file (I wonder if this is a optimisation on their end, or a limitation of email attachments?). So for every document request that gets accepted, I was needing to pull down the file from R2, stream this into an `ArrayBuffer`, iterate through the buffer byte by byte to populate a string containing the binary, and then finally encode the binary string into a Base64 representation of it self. As you can imagine, all this compute adds up and blows (relative to the free tier limits) the CPU time out to around 50ms (given a 500kb PDF document).

The fact that this request and subsequent requests didn't get dropped was pure luck, if the instance of v8 that this isolate was running on was starved for resources, then theres a good chance these requests would've been a prime candidate to be killed. This is because Cloudflare have a rule on the free tier that you are only guaranteed 10ms of CPU time each invocation, anything above that and your worker can be killed. That's not a strict limit though, given the request above did not fail... I imagine that Cloudflare provide some leniency around bursty workloads. Nevertheless, I took this as an excuse to to have a bit fun - by making this process more fault tolerant.

So, to work around this limitation but still make sure people get the document they requested as an email attachment I decided to instead keep a constantly updated cache of the Base64 representation of **ALL** files in my `documents` R2 bucket. I was able to achieve this by using CRON [Triggers](https://developers.cloudflare.com/workers/platform/triggers/cron-triggers/) to periodically check for changes (change being a new file, or a different version of an existing blob) in the bucket, and if there is a change, or if the file is new, then do the above process (fetch, stream, iterate, populate, encode) and **then** write it down inside of D1, to the `documents` table.

Now, when I accept a document request the CPU time is down to a median of **0.8ms**, there is nearly no compute that needs to occur! 🎊

<figure>
  <img src="/assets/blog/exploring-cloudflares-free-tier/documents-schema.webp" alt="an output from carbon.now.sh which contains a textual representation of the database schema for the documents sqlite table">
  <figcaption>Schema for the documents table</figcaption>
</figure>

CRON triggers live right alongside the standard request/response worker code. You just define the entry point slightly different:

```typescript
export default {
  // note that CRON trigger entry-points take a "ScheduledEvent", not a "FetchEvent" like the standard entrypoint for workers!
  async scheduled(_event: ScheduledEvent, env: Env, ctx: ExecutionContext) {
    async function exec() {
      await sync(env);
    }
    ctx.waitUntil(exec());
  },
};
```

## Final Thoughts

I initially found Cloudflare many moons ago when I was looking to replace GoDaddy as my DNS manager for my websites. Cloudflare looked good, had CNAME flattening, and was free. It wasn't long after this when Cloudflare announced Cloudflare Registrar with near wholesale domain name prices, so I started using them as my registrar as well. I very recently revamped my website and the stack it's built on. I went from a Jekyll static site hosted on Netlify, to a NextJS static site which is built and hosted on Cloudflare pages.

I quite enjoyed this expedition on the Cloudflare free tier. I was absolutely blown away with how good the dev tooling is! I don't think it'll be long before Cloudflare is considered part of the "big cloud services providers" and not just a CDN with a bunch of features. I'm especially excited for the release of [Cloudflare Pub/Sub](https://developers.cloudflare.com/pub-sub/)!

All of the code for the Document Requester app is available [here](https://github.com/korziee/document-requester).

Exploring Cloudflare's Free Tier


## What's Advent of Code?

Advent of Code (AoC) is a fun coding challenge that occurs between the 1st to the 25th of December, although you can go back and do the previous years challenges whenever you want! Each day brings a new story, with you trying to solve a problem to help the Elves out. It's not like your typical coding challenges that [Codewars](https://www.codewars.com/) or [LeetCode](https://leetcode.com/) offer, instead a deeper more playful story-driven challenge.

The problems during the month get harder and harder, but not linearly - some days are easier than others, sort of like a "break day". The story each day comes in two parts, both earning you a single star working towards your goal of 50 starts on December 25th. Part 2 is _usually_ an extension of part 1, but later in the series I found that part 2 meant you needed to re-write everything you did in part 1 if you used a brute force approach to the problem.

I've done quite a lot of these types of programming challenges before, especially on [Codewars](https://www.codewars.com/users/korziee/) and one big difference between AoC and the others is that AoC forces you to think through your solution before you submit an answer. If you submit a wrong answer, you'll get locked out temporarily, this sounds scary but each problem provides a sample set of data and the expected answer, so you can test your solution on that first.

> I still haven't gone back and finished all of the challenges. I got up to day 16, and all of my work is over at [korziee@github](https://github.com/korziee/2022-advent-of-code).

## Languages

During the beginning of the month the challenges were fairly trivial, so I spent some time playing around with a few languages that I haven't written in before. Towards the end I fell back to TypeScript as that's the language I'm most proficient in.

### V Lang

I did a couple of challenges in V and I enjoyed it as a language, it was easy to pick up and get going, even though the documentation was scattered and not easy to search. It felt like a nice balance between Go and C. V offers a nice standard library, good control flow, and simple error handling. One downside that I found with V was the lack of editor tooling - the VSCode plugin wasn't **bad**, it had nice syntax highlighting, but was lacking autocomplete.

### Erlang

I've been meaning to check out Erlang for quite a long time... Ever since a tech bro ridiculed me for 5 minutes after I said I use NodeJS primarily in my work, and they wouldn't stop talking about Erlang!

I found Erlang quite tough, I don't have much experience with functional languages, so it was a bit of a paradigm shift. But once I got the hang of pattern matching I started to enjoy it. Although I couldn't stop thinking about just how inefficient this code would be at runtime?

At this point, I thought I'd just been spoilt with TypeScript intellisense and just had high expectations for editor integrations. There are a couple of Erlang VSCode integrations, and the one I used was good enough, but as a newbie to the language I still found some features like autocomplete for the standard library lacking.

### Go

Go has got to be the most boring language I've ever written in, and I love it! The simplicity of Go makes it a enjoyable language to work with. I was pleasantly surprised by the VSCode integration as well, I found debugging to be a breeze, and formatting _just worked_. The Go documentation was great, I did the [Tutorial: Get started with Go](https://go.dev/doc/tutorial/getting-started) and really enjoyed the tour of the language!

### C

C - Bit of a classic! I find opportunities to use C day-to-day are extremely rare in my line of work (web) so I did day 6 in it and had a great time. Theres something so humbling about dropping down into C and doing things from scratch.

### Swift

HOW GOOD IS SWIFT?! I used Swift a fair bit back when I did my stint in the Connected Television Team (CTV) at Nine working on the 9Now Apple TV application. `if let` and named parameters are such nice parts (among many more, but these two come to mind) of the syntax. Swift in VSCode isn't great, but I was trying to avoid whipping out Xcode just for AoC.

### Gleam

Gleam is what Typescript is to JavaScript, but for Erlang. It transpiles to Erlang. It was an extremely enjoyable (borderline bubbly) language to use. The documentation was phenomenal and helped expose me to a lot of concepts that could also be used in Erlang.

In-line error handling was rather enjoyable through the `assert` operator.

```Gleam
assert Ok(file) = file.read(from: "input.txt")
```

From the docs:

> Sometimes we have a function that can technically fail, but in practice, we don't expect it to happen. For example, our program may start by opening a file, if we know that the file is always going to be possible to open, then we don't want to complicate our program with handling an error that should never happen.

## Core Learnings

1. **READ THE FUCKING QUESTION SEVENTEEN TIMES BEFORE YOU TOUCH THE KEYBOARD!**

The further I got into AoC, the more and more frustrated I got (at myself) for not fully understanding the problem before I started solving the problem. I had many teachable moments, as I often jump the gun and just start pseudo-coding a solution to aid my thinking. Usually that's beneficial to what I'm doing, but in my experience, it was just wasted time, as the problem develops throughout the entire story.

2. **Don't spend hours on end trying to solve something, reach for help/hints**

This is tied with core learning #1. AoC is meant to be a fun challenge, not a hair-pulling, aging process. I think I would've gone through to day 25 if I had time-boxed my attempts at some of the challenges, and after that reached for help if I was stuck, in the form of hints or reviewing other people's solutions. The [AoC subreddit](https://www.reddit.com/r/adventofcode/) is a good source of knowledge. You truly don't know, what you don't know. There's no point grinding unnecessarily and getting frustrated. Instead, learn by reviewing and understanding.

1. **Do not eagerly optimise for part 2**

Half of the battle with AoC is groking the problem provided to you in the form of a story, for me, over-optimising early took up a bit too much brain space. I was better off solving the solution with a brute force approach, which gave me the mental capacity to at least understand the problem completely. Rather than spending time optimising and crafting a beautiful algorithm!

## Closing Thoughts

I enjoyed AoC 2022, but knowing what I know now by reflecting on AoC 2022, I'm going to **really enjoy** AoC 2023! You can do the previous years Advent of Code whenever you want, so check it out, get your friends and co-workers on board, and happy programming!


My Advent of Code 2022 Journey


This article pays tribute to a pretty cool protocol I came across whilst browsing a list of TCP/UDP ports on [wikipedia](https://en.wikipedia.org/wiki/List_of_TCP_and_UDP_port_numbers), Gopher.

## I thought this was a tech blog, why are you talking about animals?

The Gopher protocol was created at the University of Minnesota, by a team led by Mark P. McCahill. Minnesota is known is as the [gopher state](http://discussions.mnhs.org/collections/2012/03/why-is-minnesota-the-gopher-state/), so naturally a bunch of smart guys named a tech protocol after an animal.

Gopher is often called the predecessor of the web, and that's a pretty fair comparison given its use and capabilities it provided in the early 90's. The early 90's are a scary place for millennials like myself, there was no concept of "googling" the answer to that burning question you had, or using Stack Overflow to help solve that strange esoteric javascript error that according to your users only occurs when they double click 4 times, refresh, change tabs and then come back to your page.

Gopher provided early internet adopters with a way to share their ideas, and push their content out there on what were and still are known as **GopherHoles**.

Gopher is inherently a very simple protocol that wraps around TCP and uses plain text communication. If you're willing to stray away from what's defined in the RFC you can use gopher for an array of cool things! You could build a JSON viewer in gopher, a [solitaire game](https://www.reddit.com/r/Gopher/comments/c0o3md/solitaire_over_gopher/), a sports score viewer, a tic tac toe game, it really is endless.

However, gopher was not designed with these capabilities in mind, gopher first and foremost was designed to be a distributed document delivery system. Now there's a few buzz words in that, but the core idea of gopher was it was to be used to present users with a hierarchical view of items and directories, much like a file system. Initially this enabled users on campus to share and access documents over a network, later this enabled internet users to share documents!

## So, what does gopher look like?

At its core, a single line of gopher (also known as a Gophermap) looks a little like this: _1News /news gopher.koryporter.com 70 \r\n ._

In the above example we can break those down into a few sections.

- **1** - directory item type

- **News** - display string

- **/news** - selector

- **gopher.koryporter.com** - hostname

- **70** - port

Now there's a few things here that need to be called out. First, the delimiting character between the sections like `1News` and `/news`, are tab characters, or `\t`. Secondly, you'll notice that the line ends with `\r\n`, the `\r\n` is a [carriage return](https://en.wikipedia.org/wiki/Carriage_return) and a [linefeed](https://en.wikipedia.org/wiki/Newline) right after one another, it's more commonly known as a CRLF signal. Gopher browsers use this to delimit each line of gopher, if you had multiple lines you would need a CRLF on the end of each of them. Lastly, and most importantly, the single period `.` at the end of the line, that's the end of message character, it tells gopher clients that you have finished writing.

Check out this excerpt from the RFC for gopher, it goes through a typical connection scenario between client and server. Source: https://datatracker.ietf.org/doc/html/rfc1436

```txt
Client:   {Opens connection to rawBits.micro.umn.edu at port 70}
Server:   {Accepts connection but says nothing}
Client:   <CR><LF> {Sends an empty line: Meaning "list what you have"}
Server:   {Sends a series of lines, each ending with CR LF}
0About internet GopherFStuff:About usFrawBits.micro.umn.eduF70
1Around University of MinnesotaFZ,5692,AUMFunderdog.micro.umn.eduF70
1Microcomputer News & PricesFPrices/Fpserver.bookstore.umn.eduF70
1Courses, Schedules, CalendarsFFevents.ais.umn.eduF9120
1Student-Staff DirectoriesFFuinfo.ais.umn.eduF70
1Departmental PublicationsFStuff:DP:FrawBits.micro.umn.eduF70
          {.....etc.....}
.         {Period on a line by itself}
          {Server closes connection}
```

## What can gopher do?

Let's first investigate what those characters are at the beginning of
every line of text that gopher servers respond with, and why they are
important for gopher clients.

Every single line of gopher usually begins with a character that exists
between 0-9 or a-z, this is the reserved range of "Item type characters".
The original gopher RFC defined just over a dozen item types for clients
to respect, they are as following.

| Item Type | Description                                                |
| --------- | ---------------------------------------------------------- |
| 0         | Text File                                                  |
| 1         | Gopher directory (submenu)                                 |
| 2         | CCSO Nameserver                                            |
| 3         | Error code returned by a Gopher server to indicate failure |
| 4         | BinHex-encoded file (primarily for Macintosh computers)    |
| 5         | DOS file                                                   |
| 6         | UNIX uuencoded file                                        |
| 7         | Gopher full-text search                                    |
| 8         | Text-based Telnet session                                  |
| 9         | Binary file                                                |
| +         | Mirror or alternate server (for load balancing)            |
| g         | GIF file                                                   |
| I         | Image file                                                 |
| T         | Telnet 3270                                                |

These types are used by gopher clients to understand what type of content
it should expect at that directory.

See the following examples:

- "_1News /news gopher.koryporter.com 70_" has an item type of 1,
  which means the gopher client should expect a directory (submenu), and
  that the directory will contain a listing of other gopher items.

- "_0my article /my-article gopher.koryporter.com 70_" has an item
  type of 0, which means the gopher client should expect a text file, and
  that once the client has asked for the file, and the server has responded,
  it should then render out the contents of the text file in the client.

Now that we know gopher has item types, let break down a few key use cases
for gopher.

### Acting as remote file system viewer

As gopher clients can render text
files and download binary files to a users system, we can use a gopher
client as a very primitive way of viewing a directory structure on another
system. This is what gopher was originally designed for really, it gave
users a way to browse and access content remotely.

### Full text document searcher

The gopher item type **7** defines a
full-text search. Common practice among gopher clients is to give users
the opportunity to enter any free text and it will send it to the gopher
server. Full text searching means that the listening gopher server is
meant to implement a search across the contents of every file it had
available to it. The power of this comes into play when you can nest an
item type of 7 deep down into a nested structure. An example might be a
gopher server that displays books available. Instead of doing an expensive
full-text search across all books, the gopher server might have you browse
existing categories, and once you have selected your category, it would
then present you with a full-text search (item type 7) within that
category, which dependent on how many books and categories indexed by the
server, would increase performance exponentially.

## Simple Gopher Server in Node (optional)

If you whipped up a tcp server on port 70 right now, any gopher client
would accept that as valid gopher text, in fact, let's do just that using
Node.js.

```javascript
const net = require("net");

net
  .createServer((socket) => {
    socket.once("data", (buffer) => {
      // sockets receive and stream buffers by default.
      const data = buffer.toString();
      // \r\n === CRLF, this is asking for the "root directory"
      if (dataAsString === "\r\n") {
        socket.write(`1News\t/news\tlocalhost\t70\t\r\n.`, () => socket.end());
      } else if (dataAsString === "/news\r\n") {
        socket.write(`iNice!\t\r\n.`, () => socket.end());
      }
    });
  })
  .listen(70);
```

Once you've run the above code a tcp server will be listening on port 70.

Open up your terminal and install the lynx web browser if you don't have it yet. Mac users can install lynx through brew by running:

```bash
brew install lynx
```

Otherwise checkout https://lynx.invisible-island.net/lynx2.8.9/index.html for more comprehensive instructions.

Now that you've got lynx installed, in the terminal run:

```bash
lynx gopher://localhost:70
```

Woop woop, you've created your first page in gopher! The output on your terminal should look a little like the picture below.

<figure>
  <img src="/assets/blog/gopher-the-father-of-the-world-wide-web/simple-gopher-tcp.webp" alt="A mac OSX terminal output displaying Gopher">
  <figcaption>Woo-hoo, things are happening!</figcaption>
</figure>

Thanks to the power of lynx, we can navigate through our little gopher hole using our arrow keys, because we only have one directory set up so far "News", that will be pre-selected for you. Click the right arrow on your keyboard. Lynx will then ask your gopher server for the contents at "/news", and you should be navigated to a new view that says "Nice!".

If you're interested how in the implementation of gopher using node, checkout [this repo](https://github.com/korziee/gopher) I created. A lot of abstractions were built to hide away the intricacies of gopher, see the core folder for more info.

## So, you say gopher's distributed?

With that in mind, let's go back to the fact that gopher was designed to be a distributed system. In that single line of gopher you have just seen, you'll notice it has a URL and a port in it. You might rightly wonder why each and every line of gopher requires a URL and a port, shouldn't the client just remember that from the first request, seems like a waste to include it every single time?

Because every line of gopher in a listing has a server URL and a server port, we can actually create gopher servers that point users to other systems, without them really feeling like they have navigated away from the initial gopher hole they visited. At the time when gopher was in active use, this enabled campuses to essentially have one or more "root" servers, that held a source of truth of where other gopher servers existed, and what they were used for. Let's say that root server was really simple, when you request a directory listing from it (CRLF), it just returned a list of the different departments within the university (each with a different URL!), and with an empty selector string. From this point, a user could navigate to the department they were interested in, and the gopher server on the receiving end has no idea that the user actually came from a root server, it just sees a connection coming in asking for it's listing (CRLF).

Essentially, the ability to be able to point users to other gopher servers in such a manner meant that server administrators had an easy time creating redundant and highly available gopher servers.

## Gopher + Javascript = 😎

Coming soon! Stay tuned for a detailed explanation on the abstractions that we can build on top of gopher using javascript to create some pretty cool applications! gopher.koryporter.com:70 has a neat JSON viewer in it, all you have to do is give a link to a JSON file, and it will do it's best to display and let your traverse through the contents of the JSON.

## Thanks

If you've made it this far, thanks for reading! Gopher has become a little bit of a pet passion for myself, and if you have any feedback or just want to chat about gopher, feel free to send me an email, you can find it at the bottom of this page.

## Resources Used

- http://discussions.mnhs.org/collections/2012/03/why-is-minnesota-the-gopher-state
- https://www.ietf.org/rfc/rfc1436.txt
- https://en.wikipedia.org/wiki/Gopher_(protocol)


Kory Porter

Tech enthusiast, plant guy, loves a beer.
Some wiffle waffle about me.
Looking for my resume?

Postgres Exclusion Constraints

More Stories

Exploring Cloudflare's Free Tier

My Advent of Code 2022 Journey

Gopher, the Father of the Internet

Kory Porter

Tech enthusiast, plant guy, loves a beer.Some wiffle waffle about me.Looking for my resume?

Postgres Exclusion Constraints

More Stories

Exploring Cloudflare's Free Tier

My Advent of Code 2022 Journey

Gopher, the Father of the Internet

Tech enthusiast, plant guy, loves a beer.
Some wiffle waffle about me.
Looking for my resume?