Initial Postgres (+PostGIS ) Schema for Journey

schemav2.sql

CREATE TABLE poi (
  id SERIAL PRIMARY KEY,
  location GEOGRAPHY(POINT, 4326),
  address TEXT
);

CREATE INDEX poi_location_index ON poi USING gist (location);

CREATE TABLE "user" (
  id SERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  image_url TEXT
);

CREATE TABLE poi_data (
  poi_id INTEGER REFERENCES poi(id),
  timestamp INTEGER,
  data TEXT NOT NULL // points to S3 URLs with (JSON)
);

CREATE TABLE interaction (
  uuid TEXT NOT NULL,
  timestamp INTEGER,
  associated_uid INTEGER, // nullable
  data TEXT NOT NULL
);

For the poi_data table we will need some how a way of adding things that are not text.
In the mocks we had things like audio, video, or extra images coming from different places.

FLYBY: Should we stick to user_id. Usernames could change.

poi_data is quite vague. What is that exactly? Is it media linked to a POI?

BIKESHED: What is interaction, is it a list of events? If so I think events is a better name as not all of the items will be directly related to a users interaction. So example if a user gets close to POI we may want to record that as an event, but it's not necessarily an 'interaction'.

Sorry, am I being pedantic?

@wilsonpage

Nope, I like pedantry in code and architecture.

events is a better term, to stick with convention, I'll use singular event.

poi_data is data linked to a poi. Could be content_attachment? That's what I had in a previous iteration.

user.id will never change (the underlying uid for a user), user.username can change, but must also be unique, just not the PK.