CREATE TABLE list (
  id INT GENERATED ALWAYS AS IDENTITY,
  owner_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY(id),
  CONSTRAINT fk_owner
    FOREIGN KEY(owner_id)
    REFERENCES users(id)
);

CREATE TABLE note (
  id INT GENERATED ALWAYS AS IDENTITY,
  list_id INT NOT NULL, -- references a list
  writer_id INT NOT NULL, -- references a user
  content VARCHAR(255) NOT NULL, -- about one twitter post worth
  modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- the last time this item was modified
  due_at TIMESTAMPTZ, -- a nullable time at which the task needs to be complete
  PRIMARY KEY(id),
  CONSTRAINT fk_writer
    FOREIGN KEY(writer_id)
    REFERENCES users(id),
  CONSTRAINT fk_list
    FOREIGN KEY(list_id)
    REFERENCES list(id)
);

-- The following section automatically updates the "modified" column when a record is updated
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified_at = NOW();
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON note
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();