diff options
| -rw-r--r-- | drizzle/0002_married_moondragon.sql | 54 | ||||
| -rw-r--r-- | drizzle/meta/0002_snapshot.json | 837 | ||||
| -rw-r--r-- | drizzle/meta/_journal.json | 7 | ||||
| -rw-r--r-- | src/server/db/schema.ts | 83 | ||||
| -rw-r--r-- | src/server/schemas/index.ts | 107 | ||||
| -rw-r--r-- | src/server/schemas/note.test.ts | 427 |
6 files changed, 1515 insertions, 0 deletions
diff --git a/drizzle/0002_married_moondragon.sql b/drizzle/0002_married_moondragon.sql new file mode 100644 index 0000000..dedd21a --- /dev/null +++ b/drizzle/0002_married_moondragon.sql @@ -0,0 +1,54 @@ +CREATE TABLE "note_field_types" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "note_type_id" uuid NOT NULL, + "name" varchar(255) NOT NULL, + "order" integer NOT NULL, + "field_type" varchar(50) DEFAULT 'text' NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL, + "deleted_at" timestamp with time zone, + "sync_version" integer DEFAULT 0 NOT NULL +); +--> statement-breakpoint +CREATE TABLE "note_field_values" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "note_id" uuid NOT NULL, + "note_field_type_id" uuid NOT NULL, + "value" text NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL, + "sync_version" integer DEFAULT 0 NOT NULL +); +--> statement-breakpoint +CREATE TABLE "note_types" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "user_id" uuid NOT NULL, + "name" varchar(255) NOT NULL, + "front_template" text NOT NULL, + "back_template" text NOT NULL, + "is_reversible" boolean DEFAULT false NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL, + "deleted_at" timestamp with time zone, + "sync_version" integer DEFAULT 0 NOT NULL +); +--> statement-breakpoint +CREATE TABLE "notes" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "deck_id" uuid NOT NULL, + "note_type_id" uuid NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL, + "deleted_at" timestamp with time zone, + "sync_version" integer DEFAULT 0 NOT NULL +); +--> statement-breakpoint +ALTER TABLE "cards" ADD COLUMN "note_id" uuid;--> statement-breakpoint +ALTER TABLE "cards" ADD COLUMN "is_reversed" boolean;--> statement-breakpoint +ALTER TABLE "note_field_types" ADD CONSTRAINT "note_field_types_note_type_id_note_types_id_fk" FOREIGN KEY ("note_type_id") REFERENCES "public"."note_types"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "note_field_values" ADD CONSTRAINT "note_field_values_note_id_notes_id_fk" FOREIGN KEY ("note_id") REFERENCES "public"."notes"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "note_field_values" ADD CONSTRAINT "note_field_values_note_field_type_id_note_field_types_id_fk" FOREIGN KEY ("note_field_type_id") REFERENCES "public"."note_field_types"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "note_types" ADD CONSTRAINT "note_types_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "notes" ADD CONSTRAINT "notes_deck_id_decks_id_fk" FOREIGN KEY ("deck_id") REFERENCES "public"."decks"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "notes" ADD CONSTRAINT "notes_note_type_id_note_types_id_fk" FOREIGN KEY ("note_type_id") REFERENCES "public"."note_types"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "cards" ADD CONSTRAINT "cards_note_id_notes_id_fk" FOREIGN KEY ("note_id") REFERENCES "public"."notes"("id") ON DELETE no action ON UPDATE no action;
\ No newline at end of file diff --git a/drizzle/meta/0002_snapshot.json b/drizzle/meta/0002_snapshot.json new file mode 100644 index 0000000..acf7f37 --- /dev/null +++ b/drizzle/meta/0002_snapshot.json @@ -0,0 +1,837 @@ +{ + "id": "4b30f43f-1827-40c6-9814-d749334da729", + "prevId": "d3d0fb16-7e44-4217-916e-a5edc9ab7a16", + "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 + }, + "note_id": { + "name": "note_id", + "type": "uuid", + "primaryKey": false, + "notNull": false + }, + "is_reversed": { + "name": "is_reversed", + "type": "boolean", + "primaryKey": false, + "notNull": false + }, + "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" + }, + "cards_note_id_notes_id_fk": { + "name": "cards_note_id_notes_id_fk", + "tableFrom": "cards", + "tableTo": "notes", + "columnsFrom": [ + "note_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.note_field_types": { + "name": "note_field_types", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "note_type_id": { + "name": "note_type_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "name": { + "name": "name", + "type": "varchar(255)", + "primaryKey": false, + "notNull": true + }, + "order": { + "name": "order", + "type": "integer", + "primaryKey": false, + "notNull": true + }, + "field_type": { + "name": "field_type", + "type": "varchar(50)", + "primaryKey": false, + "notNull": true, + "default": "'text'" + }, + "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": { + "note_field_types_note_type_id_note_types_id_fk": { + "name": "note_field_types_note_type_id_note_types_id_fk", + "tableFrom": "note_field_types", + "tableTo": "note_types", + "columnsFrom": [ + "note_type_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.note_field_values": { + "name": "note_field_values", + "schema": "", + "columns": { + "id": { + "name": "id", + "type": "uuid", + "primaryKey": true, + "notNull": true, + "default": "gen_random_uuid()" + }, + "note_id": { + "name": "note_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "note_field_type_id": { + "name": "note_field_type_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "value": { + "name": "value", + "type": "text", + "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()" + }, + "sync_version": { + "name": "sync_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + } + }, + "indexes": {}, + "foreignKeys": { + "note_field_values_note_id_notes_id_fk": { + "name": "note_field_values_note_id_notes_id_fk", + "tableFrom": "note_field_values", + "tableTo": "notes", + "columnsFrom": [ + "note_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + }, + "note_field_values_note_field_type_id_note_field_types_id_fk": { + "name": "note_field_values_note_field_type_id_note_field_types_id_fk", + "tableFrom": "note_field_values", + "tableTo": "note_field_types", + "columnsFrom": [ + "note_field_type_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.note_types": { + "name": "note_types", + "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 + }, + "front_template": { + "name": "front_template", + "type": "text", + "primaryKey": false, + "notNull": true + }, + "back_template": { + "name": "back_template", + "type": "text", + "primaryKey": false, + "notNull": true + }, + "is_reversible": { + "name": "is_reversible", + "type": "boolean", + "primaryKey": false, + "notNull": true, + "default": 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": { + "note_types_user_id_users_id_fk": { + "name": "note_types_user_id_users_id_fk", + "tableFrom": "note_types", + "tableTo": "users", + "columnsFrom": [ + "user_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "policies": {}, + "checkConstraints": {}, + "isRLSEnabled": false + }, + "public.notes": { + "name": "notes", + "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 + }, + "note_type_id": { + "name": "note_type_id", + "type": "uuid", + "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()" + }, + "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": { + "notes_deck_id_decks_id_fk": { + "name": "notes_deck_id_decks_id_fk", + "tableFrom": "notes", + "tableTo": "decks", + "columnsFrom": [ + "deck_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "no action", + "onUpdate": "no action" + }, + "notes_note_type_id_note_types_id_fk": { + "name": "notes_note_type_id_note_types_id_fk", + "tableFrom": "notes", + "tableTo": "note_types", + "columnsFrom": [ + "note_type_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/drizzle/meta/_journal.json b/drizzle/meta/_journal.json index f245fa6..5da2908 100644 --- a/drizzle/meta/_journal.json +++ b/drizzle/meta/_journal.json @@ -15,6 +15,13 @@ "when": 1764708169736, "tag": "0001_spotty_jane_foster", "breakpoints": true + }, + { + "idx": 2, + "version": "7", + "when": 1767108756805, + "tag": "0002_married_moondragon", + "breakpoints": true } ] }
\ No newline at end of file diff --git a/src/server/db/schema.ts b/src/server/db/schema.ts index 4b9631f..bd3d396 100644 --- a/src/server/db/schema.ts +++ b/src/server/db/schema.ts @@ -1,4 +1,5 @@ import { + boolean, integer, pgTable, real, @@ -25,6 +26,11 @@ export const Rating = { Easy: 4, } as const; +// Field types for note fields +export const FieldType = { + Text: "text", +} as const; + export const users = pgTable("users", { id: uuid("id").primaryKey().defaultRandom(), username: varchar("username", { length: 255 }).notNull().unique(), @@ -49,6 +55,45 @@ export const refreshTokens = pgTable("refresh_tokens", { .defaultNow(), }); +export const noteTypes = pgTable("note_types", { + id: uuid("id").primaryKey().defaultRandom(), + userId: uuid("user_id") + .notNull() + .references(() => users.id), + name: varchar("name", { length: 255 }).notNull(), + frontTemplate: text("front_template").notNull(), + backTemplate: text("back_template").notNull(), + isReversible: boolean("is_reversible").notNull().default(false), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + deletedAt: timestamp("deleted_at", { withTimezone: true }), + syncVersion: integer("sync_version").notNull().default(0), +}); + +export const noteFieldTypes = pgTable("note_field_types", { + id: uuid("id").primaryKey().defaultRandom(), + noteTypeId: uuid("note_type_id") + .notNull() + .references(() => noteTypes.id), + name: varchar("name", { length: 255 }).notNull(), + order: integer("order").notNull(), + fieldType: varchar("field_type", { length: 50 }) + .notNull() + .default(FieldType.Text), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + deletedAt: timestamp("deleted_at", { withTimezone: true }), + syncVersion: integer("sync_version").notNull().default(0), +}); + export const decks = pgTable("decks", { id: uuid("id").primaryKey().defaultRandom(), userId: uuid("user_id") @@ -67,11 +112,49 @@ export const decks = pgTable("decks", { syncVersion: integer("sync_version").notNull().default(0), }); +export const notes = pgTable("notes", { + id: uuid("id").primaryKey().defaultRandom(), + deckId: uuid("deck_id") + .notNull() + .references(() => decks.id), + noteTypeId: uuid("note_type_id") + .notNull() + .references(() => noteTypes.id), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + deletedAt: timestamp("deleted_at", { withTimezone: true }), + syncVersion: integer("sync_version").notNull().default(0), +}); + +export const noteFieldValues = pgTable("note_field_values", { + id: uuid("id").primaryKey().defaultRandom(), + noteId: uuid("note_id") + .notNull() + .references(() => notes.id), + noteFieldTypeId: uuid("note_field_type_id") + .notNull() + .references(() => noteFieldTypes.id), + value: text("value").notNull(), + createdAt: timestamp("created_at", { withTimezone: true }) + .notNull() + .defaultNow(), + updatedAt: timestamp("updated_at", { withTimezone: true }) + .notNull() + .defaultNow(), + syncVersion: integer("sync_version").notNull().default(0), +}); + export const cards = pgTable("cards", { id: uuid("id").primaryKey().defaultRandom(), deckId: uuid("deck_id") .notNull() .references(() => decks.id), + noteId: uuid("note_id").references(() => notes.id), + isReversed: boolean("is_reversed"), front: text("front").notNull(), back: text("back").notNull(), diff --git a/src/server/schemas/index.ts b/src/server/schemas/index.ts index 0227ebe..d2a8fb0 100644 --- a/src/server/schemas/index.ts +++ b/src/server/schemas/index.ts @@ -105,6 +105,98 @@ export const updateCardSchema = z.object({ back: z.string().min(1).optional(), }); +// Field type schema +export const fieldTypeSchema = z.literal("text"); + +// NoteType schema +export const noteTypeSchema = z.object({ + id: z.uuid(), + userId: z.uuid(), + name: z.string().min(1).max(255), + frontTemplate: z.string().min(1), + backTemplate: z.string().min(1), + isReversible: z.boolean(), + createdAt: z.coerce.date(), + updatedAt: z.coerce.date(), + deletedAt: z.coerce.date().nullable(), + syncVersion: z.number().int().min(0), +}); + +// NoteType creation input schema +export const createNoteTypeSchema = z.object({ + name: z.string().min(1).max(255), + frontTemplate: z.string().min(1), + backTemplate: z.string().min(1), + isReversible: z.boolean().default(false), +}); + +// NoteType update input schema +export const updateNoteTypeSchema = z.object({ + name: z.string().min(1).max(255).optional(), + frontTemplate: z.string().min(1).optional(), + backTemplate: z.string().min(1).optional(), + isReversible: z.boolean().optional(), +}); + +// NoteFieldType schema +export const noteFieldTypeSchema = z.object({ + id: z.uuid(), + noteTypeId: z.uuid(), + name: z.string().min(1).max(255), + order: z.number().int().min(0), + fieldType: fieldTypeSchema, + createdAt: z.coerce.date(), + updatedAt: z.coerce.date(), + deletedAt: z.coerce.date().nullable(), + syncVersion: z.number().int().min(0), +}); + +// NoteFieldType creation input schema +export const createNoteFieldTypeSchema = z.object({ + name: z.string().min(1).max(255), + order: z.number().int().min(0), + fieldType: fieldTypeSchema.default("text"), +}); + +// NoteFieldType update input schema +export const updateNoteFieldTypeSchema = z.object({ + name: z.string().min(1).max(255).optional(), + order: z.number().int().min(0).optional(), +}); + +// Note schema +export const noteSchema = z.object({ + id: z.uuid(), + deckId: z.uuid(), + noteTypeId: z.uuid(), + createdAt: z.coerce.date(), + updatedAt: z.coerce.date(), + deletedAt: z.coerce.date().nullable(), + syncVersion: z.number().int().min(0), +}); + +// Note creation input schema (fields is a map of fieldTypeId -> value) +export const createNoteSchema = z.object({ + noteTypeId: z.uuid(), + fields: z.record(z.uuid(), z.string()), +}); + +// Note update input schema +export const updateNoteSchema = z.object({ + fields: z.record(z.uuid(), z.string()), +}); + +// NoteFieldValue schema +export const noteFieldValueSchema = z.object({ + id: z.uuid(), + noteId: z.uuid(), + noteFieldTypeId: z.uuid(), + value: z.string(), + createdAt: z.coerce.date(), + updatedAt: z.coerce.date(), + syncVersion: z.number().int().min(0), +}); + // ReviewLog schema export const reviewLogSchema = z.object({ id: z.uuid(), @@ -138,3 +230,18 @@ export type CreateCardSchema = z.infer<typeof createCardSchema>; export type UpdateCardSchema = z.infer<typeof updateCardSchema>; export type ReviewLogSchema = z.infer<typeof reviewLogSchema>; export type SubmitReviewSchema = z.infer<typeof submitReviewSchema>; +export type FieldTypeSchema = z.infer<typeof fieldTypeSchema>; +export type NoteTypeSchema = z.infer<typeof noteTypeSchema>; +export type CreateNoteTypeSchema = z.infer<typeof createNoteTypeSchema>; +export type UpdateNoteTypeSchema = z.infer<typeof updateNoteTypeSchema>; +export type NoteFieldTypeSchema = z.infer<typeof noteFieldTypeSchema>; +export type CreateNoteFieldTypeSchema = z.infer< + typeof createNoteFieldTypeSchema +>; +export type UpdateNoteFieldTypeSchema = z.infer< + typeof updateNoteFieldTypeSchema +>; +export type NoteSchema = z.infer<typeof noteSchema>; +export type CreateNoteSchema = z.infer<typeof createNoteSchema>; +export type UpdateNoteSchema = z.infer<typeof updateNoteSchema>; +export type NoteFieldValueSchema = z.infer<typeof noteFieldValueSchema>; diff --git a/src/server/schemas/note.test.ts b/src/server/schemas/note.test.ts new file mode 100644 index 0000000..0a9b84b --- /dev/null +++ b/src/server/schemas/note.test.ts @@ -0,0 +1,427 @@ +import { describe, expect, it } from "vitest"; +import { + createNoteFieldTypeSchema, + createNoteSchema, + createNoteTypeSchema, + noteFieldTypeSchema, + noteFieldValueSchema, + noteSchema, + noteTypeSchema, + updateNoteFieldTypeSchema, + updateNoteSchema, + updateNoteTypeSchema, +} from "./index"; + +describe("NoteType schemas", () => { + describe("noteTypeSchema", () => { + const validNoteType = { + id: "550e8400-e29b-41d4-a716-446655440000", + userId: "550e8400-e29b-41d4-a716-446655440001", + name: "Basic", + frontTemplate: "{{Front}}", + backTemplate: "{{Back}}", + isReversible: false, + createdAt: new Date("2024-01-01"), + updatedAt: new Date("2024-01-01"), + deletedAt: null, + syncVersion: 0, + }; + + it("should parse valid note type", () => { + const result = noteTypeSchema.safeParse(validNoteType); + expect(result.success).toBe(true); + }); + + it("should parse date strings", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + createdAt: "2024-01-01T00:00:00.000Z", + updatedAt: "2024-01-01T00:00:00.000Z", + }); + expect(result.success).toBe(true); + if (result.success) { + expect(result.data.createdAt).toBeInstanceOf(Date); + expect(result.data.updatedAt).toBeInstanceOf(Date); + } + }); + + it("should reject invalid UUID for id", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + id: "not-a-uuid", + }); + expect(result.success).toBe(false); + }); + + it("should reject empty name", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + name: "", + }); + expect(result.success).toBe(false); + }); + + it("should reject name longer than 255 characters", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + name: "a".repeat(256), + }); + expect(result.success).toBe(false); + }); + + it("should reject empty frontTemplate", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + frontTemplate: "", + }); + expect(result.success).toBe(false); + }); + + it("should reject empty backTemplate", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + backTemplate: "", + }); + expect(result.success).toBe(false); + }); + + it("should reject negative syncVersion", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + syncVersion: -1, + }); + expect(result.success).toBe(false); + }); + + it("should accept deletedAt as date", () => { + const result = noteTypeSchema.safeParse({ + ...validNoteType, + deletedAt: new Date("2024-01-02"), + }); + expect(result.success).toBe(true); + if (result.success) { + expect(result.data.deletedAt).toBeInstanceOf(Date); + } + }); + }); + + describe("createNoteTypeSchema", () => { + it("should parse valid create input", () => { + const result = createNoteTypeSchema.safeParse({ + name: "Basic", + frontTemplate: "{{Front}}", + backTemplate: "{{Back}}", + isReversible: true, + }); + expect(result.success).toBe(true); + }); + + it("should default isReversible to false", () => { + const result = createNoteTypeSchema.safeParse({ + name: "Basic", + frontTemplate: "{{Front}}", + backTemplate: "{{Back}}", + }); + expect(result.success).toBe(true); + if (result.success) { + expect(result.data.isReversible).toBe(false); + } + }); + + it("should reject missing name", () => { + const result = createNoteTypeSchema.safeParse({ + frontTemplate: "{{Front}}", + backTemplate: "{{Back}}", + }); + expect(result.success).toBe(false); + }); + + it("should reject missing frontTemplate", () => { + const result = createNoteTypeSchema.safeParse({ + name: "Basic", + backTemplate: "{{Back}}", + }); + expect(result.success).toBe(false); + }); + + it("should reject missing backTemplate", () => { + const result = createNoteTypeSchema.safeParse({ + name: "Basic", + frontTemplate: "{{Front}}", + }); + expect(result.success).toBe(false); + }); + }); + + describe("updateNoteTypeSchema", () => { + it("should parse with all optional fields", () => { + const result = updateNoteTypeSchema.safeParse({}); + expect(result.success).toBe(true); + }); + + it("should parse with partial fields", () => { + const result = updateNoteTypeSchema.safeParse({ + name: "Updated Name", + }); + expect(result.success).toBe(true); + }); + + it("should parse with all fields", () => { + const result = updateNoteTypeSchema.safeParse({ + name: "Updated Name", + frontTemplate: "{{NewFront}}", + backTemplate: "{{NewBack}}", + isReversible: true, + }); + expect(result.success).toBe(true); + }); + + it("should reject empty name if provided", () => { + const result = updateNoteTypeSchema.safeParse({ + name: "", + }); + expect(result.success).toBe(false); + }); + }); +}); + +describe("NoteFieldType schemas", () => { + describe("noteFieldTypeSchema", () => { + const validNoteFieldType = { + id: "550e8400-e29b-41d4-a716-446655440000", + noteTypeId: "550e8400-e29b-41d4-a716-446655440001", + name: "Front", + order: 0, + fieldType: "text" as const, + createdAt: new Date("2024-01-01"), + updatedAt: new Date("2024-01-01"), + deletedAt: null, + syncVersion: 0, + }; + + it("should parse valid note field type", () => { + const result = noteFieldTypeSchema.safeParse(validNoteFieldType); + expect(result.success).toBe(true); + }); + + it("should reject invalid fieldType", () => { + const result = noteFieldTypeSchema.safeParse({ + ...validNoteFieldType, + fieldType: "invalid", + }); + expect(result.success).toBe(false); + }); + + it("should reject negative order", () => { + const result = noteFieldTypeSchema.safeParse({ + ...validNoteFieldType, + order: -1, + }); + expect(result.success).toBe(false); + }); + + it("should reject empty name", () => { + const result = noteFieldTypeSchema.safeParse({ + ...validNoteFieldType, + name: "", + }); + expect(result.success).toBe(false); + }); + }); + + describe("createNoteFieldTypeSchema", () => { + it("should parse valid create input", () => { + const result = createNoteFieldTypeSchema.safeParse({ + name: "Front", + order: 0, + }); + expect(result.success).toBe(true); + if (result.success) { + expect(result.data.fieldType).toBe("text"); + } + }); + + it("should accept explicit fieldType", () => { + const result = createNoteFieldTypeSchema.safeParse({ + name: "Front", + order: 0, + fieldType: "text", + }); + expect(result.success).toBe(true); + }); + + it("should reject missing name", () => { + const result = createNoteFieldTypeSchema.safeParse({ + order: 0, + }); + expect(result.success).toBe(false); + }); + + it("should reject missing order", () => { + const result = createNoteFieldTypeSchema.safeParse({ + name: "Front", + }); + expect(result.success).toBe(false); + }); + }); + + describe("updateNoteFieldTypeSchema", () => { + it("should parse with all optional fields", () => { + const result = updateNoteFieldTypeSchema.safeParse({}); + expect(result.success).toBe(true); + }); + + it("should parse with name only", () => { + const result = updateNoteFieldTypeSchema.safeParse({ + name: "Updated Field", + }); + expect(result.success).toBe(true); + }); + + it("should parse with order only", () => { + const result = updateNoteFieldTypeSchema.safeParse({ + order: 1, + }); + expect(result.success).toBe(true); + }); + }); +}); + +describe("Note schemas", () => { + describe("noteSchema", () => { + const validNote = { + id: "550e8400-e29b-41d4-a716-446655440000", + deckId: "550e8400-e29b-41d4-a716-446655440001", + noteTypeId: "550e8400-e29b-41d4-a716-446655440002", + createdAt: new Date("2024-01-01"), + updatedAt: new Date("2024-01-01"), + deletedAt: null, + syncVersion: 0, + }; + + it("should parse valid note", () => { + const result = noteSchema.safeParse(validNote); + expect(result.success).toBe(true); + }); + + it("should reject invalid deckId", () => { + const result = noteSchema.safeParse({ + ...validNote, + deckId: "not-a-uuid", + }); + expect(result.success).toBe(false); + }); + + it("should reject invalid noteTypeId", () => { + const result = noteSchema.safeParse({ + ...validNote, + noteTypeId: "not-a-uuid", + }); + expect(result.success).toBe(false); + }); + }); + + describe("createNoteSchema", () => { + it("should parse valid create input", () => { + const result = createNoteSchema.safeParse({ + noteTypeId: "550e8400-e29b-41d4-a716-446655440000", + fields: { + "550e8400-e29b-41d4-a716-446655440001": "Front content", + "550e8400-e29b-41d4-a716-446655440002": "Back content", + }, + }); + expect(result.success).toBe(true); + }); + + it("should accept empty fields", () => { + const result = createNoteSchema.safeParse({ + noteTypeId: "550e8400-e29b-41d4-a716-446655440000", + fields: {}, + }); + expect(result.success).toBe(true); + }); + + it("should reject missing noteTypeId", () => { + const result = createNoteSchema.safeParse({ + fields: {}, + }); + expect(result.success).toBe(false); + }); + + it("should reject missing fields", () => { + const result = createNoteSchema.safeParse({ + noteTypeId: "550e8400-e29b-41d4-a716-446655440000", + }); + expect(result.success).toBe(false); + }); + + it("should reject invalid UUID in fields key", () => { + const result = createNoteSchema.safeParse({ + noteTypeId: "550e8400-e29b-41d4-a716-446655440000", + fields: { + "not-a-uuid": "content", + }, + }); + expect(result.success).toBe(false); + }); + }); + + describe("updateNoteSchema", () => { + it("should parse valid update input", () => { + const result = updateNoteSchema.safeParse({ + fields: { + "550e8400-e29b-41d4-a716-446655440001": "Updated content", + }, + }); + expect(result.success).toBe(true); + }); + + it("should reject missing fields", () => { + const result = updateNoteSchema.safeParse({}); + expect(result.success).toBe(false); + }); + }); +}); + +describe("NoteFieldValue schemas", () => { + describe("noteFieldValueSchema", () => { + const validNoteFieldValue = { + id: "550e8400-e29b-41d4-a716-446655440000", + noteId: "550e8400-e29b-41d4-a716-446655440001", + noteFieldTypeId: "550e8400-e29b-41d4-a716-446655440002", + value: "Some content", + createdAt: new Date("2024-01-01"), + updatedAt: new Date("2024-01-01"), + syncVersion: 0, + }; + + it("should parse valid note field value", () => { + const result = noteFieldValueSchema.safeParse(validNoteFieldValue); + expect(result.success).toBe(true); + }); + + it("should accept empty value", () => { + const result = noteFieldValueSchema.safeParse({ + ...validNoteFieldValue, + value: "", + }); + expect(result.success).toBe(true); + }); + + it("should reject invalid noteId", () => { + const result = noteFieldValueSchema.safeParse({ + ...validNoteFieldValue, + noteId: "not-a-uuid", + }); + expect(result.success).toBe(false); + }); + + it("should reject invalid noteFieldTypeId", () => { + const result = noteFieldValueSchema.safeParse({ + ...validNoteFieldValue, + noteFieldTypeId: "not-a-uuid", + }); + expect(result.success).toBe(false); + }); + }); +}); |