ai-novel-studio/server/db/migrations/0000_init.sql

CREATE TABLE `agents` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`role` text NOT NULL,
	`name` text NOT NULL,
	`model_id` text,
	`system_prompt` text DEFAULT '' NOT NULL,
	`style_notes` text DEFAULT '' NOT NULL,
	`created_by` text DEFAULT 'user' NOT NULL,
	`enabled` integer DEFAULT true NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`model_id`) REFERENCES `models`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `agents_project_idx` ON `agents` (`project_id`);--> statement-breakpoint
CREATE TABLE `chapter_versions` (
	`id` text PRIMARY KEY NOT NULL,
	`chapter_id` text NOT NULL,
	`version` integer NOT NULL,
	`file_path` text NOT NULL,
	`word_count` integer DEFAULT 0 NOT NULL,
	`writer_agent_id` text,
	`writer_name` text DEFAULT '' NOT NULL,
	`model_label` text DEFAULT '' NOT NULL,
	`feedback` text DEFAULT '' NOT NULL,
	`status` text DEFAULT 'writing' NOT NULL,
	`created_at` integer NOT NULL,
	FOREIGN KEY (`chapter_id`) REFERENCES `chapters`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `versions_chapter_idx` ON `chapter_versions` (`chapter_id`);--> statement-breakpoint
CREATE TABLE `chapters` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`index` integer NOT NULL,
	`title` text DEFAULT '' NOT NULL,
	`synopsis` text DEFAULT '' NOT NULL,
	`brief` text DEFAULT '' NOT NULL,
	`writer_agent_id` text,
	`writer_locked` integer DEFAULT false NOT NULL,
	`editor_notes` text DEFAULT '' NOT NULL,
	`review_notes` text DEFAULT '' NOT NULL,
	`status` text DEFAULT 'planned' NOT NULL,
	`active_version_id` text,
	`summary` text DEFAULT '' NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`writer_agent_id`) REFERENCES `agents`(`id`) ON UPDATE no action ON DELETE set null
);
--> statement-breakpoint
CREATE INDEX `chapters_project_idx` ON `chapters` (`project_id`,`index`);--> statement-breakpoint
CREATE TABLE `characters` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`name` text NOT NULL,
	`identity` text DEFAULT '' NOT NULL,
	`description` text DEFAULT '' NOT NULL,
	`personality` text DEFAULT '' NOT NULL,
	`speech_examples` text DEFAULT '' NOT NULL,
	`notes` text DEFAULT '' NOT NULL,
	`avatar` text DEFAULT '' NOT NULL,
	`source_card` text,
	`sort_order` integer DEFAULT 0 NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `characters_project_idx` ON `characters` (`project_id`);--> statement-breakpoint
CREATE TABLE `jobs` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`chapter_id` text,
	`type` text NOT NULL,
	`status` text DEFAULT 'running' NOT NULL,
	`agent_name` text DEFAULT '' NOT NULL,
	`model_label` text DEFAULT '' NOT NULL,
	`input_tokens` integer DEFAULT 0 NOT NULL,
	`output_tokens` integer DEFAULT 0 NOT NULL,
	`error` text DEFAULT '' NOT NULL,
	`log` text DEFAULT '' NOT NULL,
	`prompt` text,
	`created_at` integer NOT NULL,
	`finished_at` integer,
	FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `jobs_project_idx` ON `jobs` (`project_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `lore_entries` (
	`id` text PRIMARY KEY NOT NULL,
	`project_id` text NOT NULL,
	`title` text DEFAULT '' NOT NULL,
	`keys` text NOT NULL,
	`content` text DEFAULT '' NOT NULL,
	`constant` integer DEFAULT false NOT NULL,
	`enabled` integer DEFAULT true NOT NULL,
	`source` text DEFAULT 'user' NOT NULL,
	`sort_order` integer DEFAULT 100 NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `lore_project_idx` ON `lore_entries` (`project_id`);--> statement-breakpoint
CREATE TABLE `models` (
	`id` text PRIMARY KEY NOT NULL,
	`provider_id` text NOT NULL,
	`label` text NOT NULL,
	`model_id` text NOT NULL,
	`temperature` real,
	`top_p` real,
	`max_output_tokens` integer,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL,
	FOREIGN KEY (`provider_id`) REFERENCES `providers`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE TABLE `projects` (
	`id` text PRIMARY KEY NOT NULL,
	`title` text NOT NULL,
	`description` text DEFAULT '' NOT NULL,
	`world_setting` text DEFAULT '' NOT NULL,
	`story_outline` text DEFAULT '' NOT NULL,
	`requirements` text NOT NULL,
	`settings` text NOT NULL,
	`memory` text NOT NULL,
	`user_name` text DEFAULT '' NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `providers` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`type` text NOT NULL,
	`base_url` text DEFAULT '' NOT NULL,
	`api_key_enc` text DEFAULT '' NOT NULL,
	`created_at` integer NOT NULL,
	`updated_at` integer NOT NULL
);