Database Schema

GoApp uses PostgreSQL as its persistent datastore. The complete schema is defined in the schemas/ directory of the repository. This page provides an overview of the tables and functions.

Database Trigger Function

The application uses a generic PostgreSQL function to automatically update the updated_at timestamp on any row that is modified. This ensures that we always have an accurate record of the last modification time without needing to manage it in the application code.

File: schemas/functions.sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.updated_at = now(); 
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

This function is applied as a BEFORE UPDATE trigger to the users and user_notes tables.

Users Table

This table stores the primary user information.

File: schemas/users.sql

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY,
    email TEXT UNIQUE,
    full_name TEXT,
    phone TEXT,
    contact_address TEXT,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER tr_users_bu BEFORE UPDATE on users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Columns

• id (UUID, Primary Key): A unique identifier for the user, generated by the application.
• email (TEXT, Unique): The user's email address. A unique constraint ensures no two users can share the same email.
• full_name (TEXT): The user's full name.
• phone (TEXT): An optional phone number.
• contact_address (TEXT): An optional contact address.
• created_at (TIMESTAMPTZ): The timestamp when the user record was created. Defaults to the current time.
• updated_at (TIMESTAMPTZ): The timestamp of the last update to the user record. Automatically managed by the update_updated_at_column trigger.

User Notes Table

This table stores notes created by users.

File: schemas/user_notes.sql

CREATE TABLE IF NOT EXISTS user_notes (
    id UUID PRIMARY KEY,
    title TEXT,
    content TEXT,
    user_id UUID references users(id),
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

CREATE TRIGGER tr_users_bu BEFORE UPDATE on user_notes
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

Columns

• id (UUID, Primary Key): A unique identifier for the note.
• title (TEXT): The title of the note.
• content (TEXT): The body content of the note.
• user_id (UUID, Foreign Key): A reference to the id in the users table, linking the note to its creator.
• created_at (TIMESTAMPTZ): The timestamp when the note was created.
• updated_at (TIMESTAMPTZ): The timestamp of the last update to the note, managed by the trigger.