ac-ms-url-shortener/sql/schema.mariadb.sql

-- URL shortener — MariaDB schema (SPEC docs/specs/20100611_3_url_shortener.md §9)
CREATE DATABASE IF NOT EXISTS url_shortener
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE url_shortener;

CREATE TABLE IF NOT EXISTS bearer_tokens (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_hash CHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL DEFAULT '',
  trusted TINYINT(1) NOT NULL DEFAULT 0,
  can_temporary TINYINT(1) NOT NULL DEFAULT 1,
  can_permanent TINYINT(1) NOT NULL DEFAULT 0,
  rate_limit_per_hour INT UNSIGNED NOT NULL DEFAULT 1000,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bearer_token_hash (token_hash)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS signer_keys (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  key_hash CHAR(64) NOT NULL,
  label VARCHAR(128) NOT NULL DEFAULT '',
  can_permanent TINYINT(1) NOT NULL DEFAULT 1,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_signer_key_hash (key_hash)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  bearer_id INT UNSIGNED NOT NULL,
  signer_id INT UNSIGNED NULL,
  slug VARCHAR(16) NOT NULL,
  origin TEXT NOT NULL,
  origin_hash CHAR(64) NOT NULL,
  ttl_seconds INT UNSIGNED NOT NULL DEFAULT 86400,
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  accessed_at DATETIME NULL,
  access_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_slug (slug),
  KEY idx_bearer_origin (bearer_id, origin_hash),
  CONSTRAINT fk_links_bearer FOREIGN KEY (bearer_id) REFERENCES bearer_tokens (id),
  CONSTRAINT fk_links_signer FOREIGN KEY (signer_id) REFERENCES signer_keys (id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event VARCHAR(32) NOT NULL,
  bearer_id INT UNSIGNED NULL,
  slug VARCHAR(16) NULL,
  ip VARCHAR(45) NOT NULL DEFAULT '',
  detail TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB;