Bonfire uses the excellent PostgreSQL database for most data storage. PostgreSQL allows us to make a wide range of queries and to make them relatively fast while upholding data integrity guarantees.
Postgres is a relational schema-led database - it expects you to pre-define tables and the fields in each table (represented in tabular form, i.e. as a collection of tables with each table consisting of a set of rows and columns). Fields can contain data or a reference to a row in another table.
This usually means that a field containing a reference has to be pre-defined with a foreign key pointing to a specific field (typically a primary key, like an ID column) *in a specific table*.
A social network, by contrast, is actually a graph of objects. Objects need to be able to refer to other objects by their ID without knowing their type.
A simple example would be likes, you might have a `likes` table with `liked_post_id` field that references the `post` table. But you don't just have posts that can be liked, but also videos, images, polls, etc, each with their own table, but probably do not want to have to add `liked_video_id`, `liked_image_id`, etc?
All referenceable objects in the system have a unique ID (primary key) whose type is the [`ULID`](https://github.com/ulid/spec). It's a lot like a `UUID` in that you can generate unique ones independently of the database. It's also a little different, being made up of two parts:
This means that it naturally sorts by time to the millisecond (close enough for us), giving us a performance advantage on queries ordered by a separate creation datetime field (by contrast, UUIDv4 is randomly distributed).
If you've only worked with integer primary keys before, you are probably used to letting the database dispense an ID for you. With `ULID` (or `UUID`), IDs can be known *before* they are stored, greatly easing the process of storing a graph of data and allowing us to do more of the preparation work outside of a transaction for increased performance.
In PostgreSQL, we actually store `ULID`s as `UUID` columns, thanks to both being the same size (and the lack of a `ULID` column type shipping with postgresql). You mostly will not notice this because it's handled for you, but there are a few places it can come up:
Every object that is stored in the system will have a record in this table. It may also have records in other tables (handy for storing more than 3 fields about the object!).
In this way, they are reusable across different object types. One mixin may (or may not) be used by any number of objects. This is mostly driven by the type of the object we are storing, but can also be driven by user input.
Mixins are just tables too! The only requirement is they have a `ULID` primary key which references `Needle.Pointer`. The developer of the mixin is free to put whatever other fields they want in the table, so long as they have that primary-key-as-reference (which will be automatically added for you by the `mixin_schema` macro).
Multimixins are like mixins, except that where an object may have 0 or 1 of a particular mixins, an object may have any number of a particular multimixin.
For this to work, a multimixin must have a *compound primary key* which must contain an `id` column referencing `Needle.Pointer` and at least one other field which will collectively be unique.
Notice that this looks very similar to defining a mixin. Indeed, the only difference is the `primary_key: true` in this line, which adds a second field to the compound primary key.
This results in ecto recording a compound primary key of `(id, feed_id)` for the schema (the id is added for you as with regular mixins).
The first step to declaring a type is picking a unique table ID in ULID format. You could just generate one at the terminal, but since these IDs are special, we tend to assign a synthetic ULID that are readable as words so they stand out in debug output.
For example, the ID for the `Feed` table is: `1TFEEDS0NTHES0V1S0FM0RTA1S`, which can be read as "It feeds on the souls of mortals". Feel free to have a little fun coming up with them, it makes debug output a little more cheery! The rules are:
To help you with this, the `Needle.ULID.synthesise!/1` method takes an alphanumeric binary and tries to return you it transliterated into a valid ULID. Example usage:
It should look quite similar to a mixin definition, except that we `use``Needle.Virtual` this time (passing an additional `table_id` argument) and we call the `virtual_schema` macro.
The primary limitation of a virtual is that you cannot put extra fields into one. This also means that `belongs_to` is not generally permitted because it results in adding a field. `has_one` and `has_many` work just fine as they do not cause the creation of fields in the schema.
Under the hood, a virtual has a view (in this example, called `bonfire_data_social_block`). It looks like a table with just an id, but it's populated with all the ids of blocks that are not deleted. When the view is inserted into, a record is created in the `pointers` table for you transparently. When you delete from the view, the corresponding `pointers` entry is marked deleted for you.
The other, lesser used, type of object is called the Pointable. The major difference is that unlike the simple case of virtuals, pointables are not backed by views, but by tables.
When a record is inserted into a pointable table, a copy is made in the `pointers` table for you transparently. When you delete from the table, the the corresponding `pointers` entry is marked deleted for you. In these ways, they behave very much like virtuals. By having a table, however, we are free to add new fields.