diff options
| author | nsfisis <nsfisis@gmail.com> | 2025-12-31 16:07:00 +0900 |
|---|---|---|
| committer | nsfisis <nsfisis@gmail.com> | 2025-12-31 16:07:46 +0900 |
| commit | e4aeded6c105de6c8af6a931d5c24a659dcbd138 (patch) | |
| tree | 7d04ec5e875c1e4d0e0ae8e8c31b02235267b4e5 /drizzle | |
| parent | 3f9165e4fcbd83b7f98875a4a3de4036b67dde37 (diff) | |
| download | kioku-e4aeded6c105de6c8af6a931d5c24a659dcbd138.tar.gz kioku-e4aeded6c105de6c8af6a931d5c24a659dcbd138.tar.zst kioku-e4aeded6c105de6c8af6a931d5c24a659dcbd138.zip | |
feat(crdt): add database migration for crdt_documents table
Add Drizzle migration to create the crdt_documents table with:
- UUID primary key with auto-generation
- Foreign key to users table with cascade delete
- Unique index on (user_id, entity_type, entity_id)
- Indexes for entity_type and sync_version queries
🤖 Generated with [Claude Code](https://claude.com/claude-code)
Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
Diffstat (limited to 'drizzle')
| -rw-r--r-- | drizzle/0004_clean_leopardon.sql | 15 | ||||
| -rw-r--r-- | drizzle/meta/0004_snapshot.json | 980 | ||||
| -rw-r--r-- | drizzle/meta/_journal.json | 7 |
3 files changed, 1002 insertions, 0 deletions
diff --git a/drizzle/0004_clean_leopardon.sql b/drizzle/0004_clean_leopardon.sql new file mode 100644 index 0000000..95e203d --- /dev/null +++ b/drizzle/0004_clean_leopardon.sql @@ -0,0 +1,15 @@ +CREATE TABLE "crdt_documents" ( + "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, + "user_id" uuid NOT NULL, + "entity_type" varchar(50) NOT NULL, + "entity_id" uuid NOT NULL, + "binary" varchar(1048576) NOT NULL, + "sync_version" integer DEFAULT 0 NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "crdt_documents" ADD CONSTRAINT "crdt_documents_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +CREATE UNIQUE INDEX "crdt_documents_user_entity_idx" ON "crdt_documents" USING btree ("user_id","entity_type","entity_id");--> statement-breakpoint +CREATE INDEX "crdt_documents_entity_type_idx" ON "crdt_documents" USING btree ("entity_type");--> statement-breakpoint +CREATE INDEX "crdt_documents_sync_version_idx" ON "crdt_documents" USING btree ("user_id","sync_version");
\ No newline at end of file diff --git a/drizzle/meta/0004_snapshot.json b/drizzle/meta/0004_snapshot.json new file mode 100644 index 0000000..e0b6fce --- /dev/null +++ b/drizzle/meta/0004_snapshot.json @@ -0,0 +1,980 @@ +{ + "id": "4978370b-924e-40b1-9fd4-770994e1a90a", + "prevId": "64c4ab9c-4036-44de-99cb-c4b14f494a4d", + "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": true + }, + "is_reversed": { + "name": "is_reversed", + "type": "boolean", + "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" + }, + "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 + }, + "public.crdt_documents": { + "name": "crdt_documents", + "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 + }, + "entity_type": { + "name": "entity_type", + "type": "varchar(50)", + "primaryKey": false, + "notNull": true + }, + "entity_id": { + "name": "entity_id", + "type": "uuid", + "primaryKey": false, + "notNull": true + }, + "binary": { + "name": "binary", + "type": "varchar(1048576)", + "primaryKey": false, + "notNull": true + }, + "sync_version": { + "name": "sync_version", + "type": "integer", + "primaryKey": false, + "notNull": true, + "default": 0 + }, + "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": { + "crdt_documents_user_entity_idx": { + "name": "crdt_documents_user_entity_idx", + "columns": [ + { + "expression": "user_id", + "isExpression": false, + "asc": true, + "nulls": "last" + }, + { + "expression": "entity_type", + "isExpression": false, + "asc": true, + "nulls": "last" + }, + { + "expression": "entity_id", + "isExpression": false, + "asc": true, + "nulls": "last" + } + ], + "isUnique": true, + "concurrently": false, + "method": "btree", + "with": {} + }, + "crdt_documents_entity_type_idx": { + "name": "crdt_documents_entity_type_idx", + "columns": [ + { + "expression": "entity_type", + "isExpression": false, + "asc": true, + "nulls": "last" + } + ], + "isUnique": false, + "concurrently": false, + "method": "btree", + "with": {} + }, + "crdt_documents_sync_version_idx": { + "name": "crdt_documents_sync_version_idx", + "columns": [ + { + "expression": "user_id", + "isExpression": false, + "asc": true, + "nulls": "last" + }, + { + "expression": "sync_version", + "isExpression": false, + "asc": true, + "nulls": "last" + } + ], + "isUnique": false, + "concurrently": false, + "method": "btree", + "with": {} + } + }, + "foreignKeys": { + "crdt_documents_user_id_users_id_fk": { + "name": "crdt_documents_user_id_users_id_fk", + "tableFrom": "crdt_documents", + "tableTo": "users", + "columnsFrom": [ + "user_id" + ], + "columnsTo": [ + "id" + ], + "onDelete": "cascade", + "onUpdate": "no action" + } + }, + "compositePrimaryKeys": {}, + "uniqueConstraints": {}, + "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 cdfca60..fe8279e 100644 --- a/drizzle/meta/_journal.json +++ b/drizzle/meta/_journal.json @@ -29,6 +29,13 @@ "when": 1767156818092, "tag": "0003_brave_blacklash", "breakpoints": true + }, + { + "idx": 4, + "version": "7", + "when": 1767164715079, + "tag": "0004_clean_leopardon", + "breakpoints": true } ] }
\ No newline at end of file |
