From 0763153865e2157e0d06c946993dd8b235b06c83 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Wed, 3 Dec 2025 05:45:41 +0900 Subject: feat(auth): add refresh token endpoint MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Implement refresh token functionality for authentication: - Add refresh_tokens table to database schema with user reference - Generate migration for the new table - Login endpoint now returns both access token and refresh token - Add POST /api/auth/refresh endpoint with token rotation - Refresh tokens are hashed (SHA256) before storage for security - Tokens expire after 7 days, access tokens after 15 minutes - Update tests to cover new functionality 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude --- pkgs/server/drizzle/0001_spotty_jane_foster.sql | 9 + pkgs/server/drizzle/meta/0001_snapshot.json | 462 ++++++++++++++++++++++++ pkgs/server/drizzle/meta/_journal.json | 7 + 3 files changed, 478 insertions(+) create mode 100644 pkgs/server/drizzle/0001_spotty_jane_foster.sql create mode 100644 pkgs/server/drizzle/meta/0001_snapshot.json (limited to 'pkgs/server/drizzle') diff --git a/pkgs/server/drizzle/0001_spotty_jane_foster.sql b/pkgs/server/drizzle/0001_spotty_jane_foster.sql new file mode 100644 index 0000000..417408f --- /dev/null +++ b/pkgs/server/drizzle/0001_spotty_jane_foster.sql @@ -0,0 +1,9 @@ +CREATE TABLE "refresh_tokens" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "user_id" uuid NOT NULL, + "token_hash" varchar(255) NOT NULL, + "expires_at" timestamp with time zone NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "refresh_tokens" ADD CONSTRAINT "refresh_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action; \ No newline at end of file diff --git a/pkgs/server/drizzle/meta/0001_snapshot.json b/pkgs/server/drizzle/meta/0001_snapshot.json new file mode 100644 index 0000000..55c3999 --- /dev/null +++ b/pkgs/server/drizzle/meta/0001_snapshot.json @@ -0,0 +1,462 @@ +{ + "id": "d3d0fb16-7e44-4217-916e-a5edc9ab7a16", + "prevId": "d2f779a2-d302-4fe3-91bb-a541025dbe4a", + "version": "7", + "dialect": "postgresql", + "tables": { + "public.cards": { + "name": "cards", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "deck_id": { + "name": "deck_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "front": { + "name": "front", + "type": "text", + "primaryKey": false, + "notNull": true + }, + "back": { + "name": "back", + "type": "text", + "primaryKey": false, + "notNull": true + }, + "state": { + "name": "state", + "type": "smallint", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "due": { + "name": "due", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "stability": { + "name": "stability", + "type": "real", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "difficulty": { + "name": "difficulty", + "type": "real", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "elapsed_days": { + "name": "elapsed_days", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "scheduled_days": { + "name": "scheduled_days", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "reps": { + "name": "reps", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "lapses": { + "name": "lapses", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "last_review": { + "name": "last_review", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": false + }, + "created_at": { + "name": "created_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "updated_at": { + "name": "updated_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "deleted_at": { + "name": "deleted_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": false + }, + "sync_version": { + "name": "sync_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + } + }, + "indexes": {}, + "foreignKeys": { + "cards_deck_id_decks_id_fk": { + "name": "cards_deck_id_decks_id_fk", + "tableFrom": "cards", + "tableTo": "decks", + "columnsFrom": [ + "deck_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.decks": { + "name": "decks", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "user_id": { + "name": "user_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "name": { + "name": "name", + "type": "varchar(255)", + "primaryKey": false, + "notNull": true + }, + "description": { + "name": "description", + "type": "text", + "primaryKey": false, + "notNull": false + }, + "new_cards_per_day": { + "name": "new_cards_per_day", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 20 + }, + "created_at": { + "name": "created_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "updated_at": { + "name": "updated_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "deleted_at": { + "name": "deleted_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": false + }, + "sync_version": { + "name": "sync_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + } + }, + "indexes": {}, + "foreignKeys": { + "decks_user_id_users_id_fk": { + "name": "decks_user_id_users_id_fk", + "tableFrom": "decks", + "tableTo": "users", + "columnsFrom": [ + "user_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.refresh_tokens": { + "name": "refresh_tokens", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "user_id": { + "name": "user_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "token_hash": { + "name": "token_hash", + "type": "varchar(255)", + "primaryKey": false, + "notNull": true + }, + "expires_at": { + "name": "expires_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true + }, + "created_at": { + "name": "created_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + } + }, + "indexes": {}, + "foreignKeys": { + "refresh_tokens_user_id_users_id_fk": { + "name": "refresh_tokens_user_id_users_id_fk", + "tableFrom": "refresh_tokens", + "tableTo": "users", + "columnsFrom": [ + "user_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.review_logs": { + "name": "review_logs", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "card_id": { + "name": "card_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "user_id": { + "name": "user_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "rating": { + "name": "rating", + "type": "smallint", + "primaryKey": false, + "notNull": true + }, + "state": { + "name": "state", + "type": "smallint", + "primaryKey": false, + "notNull": true + }, + "scheduled_days": { + "name": "scheduled_days", + "type": "integer", + "primaryKey": false, + "notNull": true + }, + "elapsed_days": { + "name": "elapsed_days", + "type": "integer", + "primaryKey": false, + "notNull": true + }, + "reviewed_at": { + "name": "reviewed_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "duration_ms": { + "name": "duration_ms", + "type": "integer", + "primaryKey": false, + "notNull": false + }, + "sync_version": { + "name": "sync_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + } + }, + "indexes": {}, + "foreignKeys": { + "review_logs_card_id_cards_id_fk": { + "name": "review_logs_card_id_cards_id_fk", + "tableFrom": "review_logs", + "tableTo": "cards", + "columnsFrom": [ + "card_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + }, + "review_logs_user_id_users_id_fk": { + "name": "review_logs_user_id_users_id_fk", + "tableFrom": "review_logs", + "tableTo": "users", + "columnsFrom": [ + "user_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.users": { + "name": "users", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "username": { + "name": "username", + "type": "varchar(255)", + "primaryKey": false, + "notNull": true + }, + "password_hash": { + "name": "password_hash", + "type": "varchar(255)", + "primaryKey": false, + "notNull": true + }, + "created_at": { + "name": "created_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + }, + "updated_at": { + "name": "updated_at", + "type": "timestamp with time zone", + "primaryKey": false, + "notNull": true, + "default": "now()" + } + }, + "indexes": {}, + "foreignKeys": {}, + "compositePrimaryKeys": {}, + "uniqueConstraints": { + "users_username_unique": { + "name": "users_username_unique", + "nullsNotDistinct": false, + "columns": [ + "username" + ] + } + }, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + } + }, + "enums": {}, + "schemas": {}, + "sequences": {}, + "roles": {}, + "policies": {}, + "views": {}, + "_meta": { + "columns": {}, + "schemas": {}, + "tables": {} + } +} \ No newline at end of file diff --git a/pkgs/server/drizzle/meta/_journal.json b/pkgs/server/drizzle/meta/_journal.json index 6448a8c..f245fa6 100644 --- a/pkgs/server/drizzle/meta/_journal.json +++ b/pkgs/server/drizzle/meta/_journal.json @@ -8,6 +8,13 @@ "when": 1764706245996, "tag": "0000_cynical_zeigeist", "breakpoints": true + }, + { + "idx": 1, + "version": "7", + "when": 1764708169736, + "tag": "0001_spotty_jane_foster", + "breakpoints": true } ] } \ No newline at end of file -- cgit v1.2.3-70-g09d2