Basic Address Book Example/Explanation

[nelsonic]

At present this tutorial does a good job of diving straight into the code example. ✅ We feel that explaining the context of the example would help people understand it.

Most basic address books, like the one you have on your mobile phone, do not preserve history. This makes sense because most people only want the latest (up-to-date) version of a person's address.

But using our imagination for a bit we can easily demonstrate that having history in addresses can be highly useful.

Intro

In a "normal" Phoenix App, when a schema is generated using phx.gen ("generator") command e.g:

mix phx.gen.schema Address addresses name:string address_line_1:string 
address_line_2:string city:string postcode:string tel:string

or

mix phx.gen.html Accounts Address addresses name:string address_line_1:string 
address_line_2:string city:string postcode:string tel:string

A standard PostgreSQL Table called addresses is created with the following schema:

A standard PostgreSQL Table does not store the history of a record so when the record/row gets updated, we have no way of "undoing" the update. Let's consider a basic example.

Basic Example

We have a basic Address Book app for storing the addresses of our friends & family.

If we insert a record into the addresses table (see https://github.com/dwyl/phoenix-ecto-append-only-log-example/issues/17#issuecomment-486610753) we get the following row:

id	name	address_line_1	address_line_2	city	postcode	tel	inserted_at
1	Thor	The Hall	Valhalla	Asgard	AS1 3DG	123123	2019-04-25 10:01:42

This is very much "traditional CRUD" approach; the primary key (unique identifier) of the record is 1 and if we were to update this record, it would overwrite the previous version (and any history would be lost).

id	name	address_line_1	address_line_2	city	postcode	tel	inserted_at	updated_at
1	Thor	177A Bleecker Street	c/o Dr. Strange	New York	NY 10012	98765	2019-03-14 10:01:42	2019-04-17 01:03:13

In our scenario above, we start out with our friend Thor's "home" address in Asgard. Thor moves to Earth and is temporarily staying with his buddy Dr. Strange in New York.

After completing finishing his "job" on Earth, Thor moves back to Asgard to take a break from the chaos of NY. Thor forgets to leave his forwarding address assuming that everyone just knows where to send him mail.

Sadly, because we lost Thor's previous address when we updated the record, we have no idea how to contact him. Without record history, we lose contact with our friends. 😞

Note: I have attempted to give this example in https://github.com/dwyl/phoenix-ecto-append-only-log-example/issues/5#issuecomment-425686791 but it appears to have been lost in that thread. My intention is to include this example in the "What?" section of the main README.md to help people understand the benefit of immutable data with a simple example.

Real World Example

If you have ever used an E-commerce shopping website, most of them allow you to have multiple addresses which are effectively an address "history".

When you update your address on Amazon, you are actually inserting a new version of you address. The way you can check this is that your previous orders that went to the previous address have not been altered.

As an end-user you have no visibility of the underlying data structure, but the reality is that all changes to your address are carefully recorded by Amazon to ensure full accountability and prevent fraud.

If a criminal was to gain access to your Amazon account, add their own address, send parcels to themself and then attempt to delete their address, it's not going to help them, their address is very much recorded in the account history and will be passed to the fraud investigation team.

Try it yourself, temporarily change your address to your Work or a Friend's address send an order to them. Then delete the address and go "Order Reports", the address is still there.

You might not think about address history as a "consumer", but if your account was ever hacked, you would be very grateful for the history.

[nelsonic]

Three Options for Preserving History

1. A single table with a master column

We simply need to add a master column to the address table. This allows us to traverse the history of the record.

id	master	name	address_line_1	address_line_2	city	postcode	tel	inserted_at
1	null	Thor	The Hall	Valhalla	Asgard	AS1 3DG	123123	2019-02-25 10:01:42
2	1	Thor	177A Bleecker Street	c/o Dr. Strange	New York	NY 10012	98765	2019-03-14 10:01:42
3	1	Thor	1 Sunset Blvd	3rd Floor	LA	90210	98765	2019-03-28 22:01:42

Sadly, this approach does not allow us to use the standard Ecto "CRUD" ("out of the box") because the second row will not be created Ecto will always/only overwrite the first row ... We can easily add a custom update function. (this is what we are doing in a couple of projects already and it's turning out to be "confusing" for people ...)

The problem with this approach is that we cannot have/enforce a unique constraint on any of the columns. Right now our address schema does not have any unique columns, but it could very well have a unique constraint on tel (telephone number), which makes sense to be unique unless you only have a land line for the person ... ☎️💭

2. The way we are currently doing ALOG with no unique columns

See: https://github.com/dwyl/alog

3. address_history stores a copy of record as they are inserted/updated

Imagine we create a schema using the mix phx.gen schema command (see above): The resulting schema would look something like this:

defmodule Append.Address do
  use Ecto.Schema

  @timestamps_opts [type: :naive_datetime_usec]
  schema "addresses" do
    field(:address_line_1, :string)
    field(:address_line_2, :string)
    field(:city, :string)
    field(:name, :string)
    field(:postcode, :string)
    field(:tel, :string)

    timestamps()
  end
end

We would simply have a function call to history() in the body of the schema:

defmodule Append.Address do
  use Ecto.Schema
  import Alog.History

  @timestamps_opts [type: :naive_datetime_usec]
  schema "addresses" do
    field(:address_line_1, :string)
    field(:address_line_2, :string)
    field(:city, :string)
    field(:name, :string)
    field(:postcode, :string)
    field(:tel, :string)

    timestamps()
    history()
  end
end

When history() is invoked in a schema, it would create a duplicate table called address_history that would strip any unique constraints (because they are already being enforced in the main table).

Such that if the address table is:

id	name	address_line_1	address_line_2	city	postcode	tel	inserted_at	updated_at
1	Thor	1 Sunset Blvd	3rd Floor	LA	90210	98765	2019-03-28 22:01:42	2019-03-28 22:01:42

address_history would be:

_id	id	name	address_line_1	address_line_2	city	postcode	tel	inserted_at
1	1	Thor	The Hall	Valhalla	Asgard	AS1 3DG	123123	2019-02-25 10:01:42
2	1	Thor	177A Bleecker Street	c/o Dr. Strange	New York	NY 10012	98765	2019-03-14 10:01:42
3	1	Thor	1 Sunset Blvd	3rd Floor	LA	90210	98765	2019-03-28 22:01:42

Where _id is your auto-incrementing counter and id is the original id.

Having the address_history table means we can still get the full history for the record.

Next:

Investigate using LISTEN and NOTIFY in PostgreSQL in Elixir reading this post by @KamilLelonek https://blog.lelonek.me/listen-and-notify-postgresql-commands-in-elixir-187c49597851