db_setup.sql

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE NOT NULL,
  hashed_password TEXT,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE urls (
  url_id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  short_code VARCHAR(15) UNIQUE NOT NULL,
  times_visited INT DEFAULT 0,
  last_visited TIMESTAMPTZ DEFAULT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE visits (
  visit_id SERIAL PRIMARY KEY,
  short_code VARCHAR(15) NOT NULL REFERENCES urls(short_code) ON DELETE CASCADE,
  visited_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE logs (
  log_id SERIAL PRIMARY KEY,
  ip VARCHAR(45),
  visited_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);