-- API keys for programmatic access to a user's data. Only a SHA-256 hash of the
-- key is stored; the plaintext key is shown to the user exactly once at creation.
CREATE TABLE IF NOT EXISTS `api_keys` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`      BIGINT UNSIGNED NOT NULL,
    `name`         VARCHAR(120)    NOT NULL DEFAULT 'API key',
    `key_prefix`   VARCHAR(16)     NOT NULL,          -- shown for identification
    `key_hash`     CHAR(64)        NOT NULL,          -- sha256(full key)
    `last_used_at` DATETIME        NULL,
    `revoked`      TINYINT(1)      NOT NULL DEFAULT 0,
    `created_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_apikey_hash` (`key_hash`),
    KEY `idx_apikey_user` (`user_id`),
    CONSTRAINT `fk_apikey_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;