CREATE TABLE `conversations` (
	`id` text NOT NULL,
	`did` text NOT NULL,
	`post_uri` text NOT NULL,
	`revision` text NOT NULL,
	`created_at` integer DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`last_active` integer DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `conversations_id_unique` ON `conversations` (`id`);--> statement-breakpoint
CREATE UNIQUE INDEX `conversations_did_unique` ON `conversations` (`did`);--> statement-breakpoint
CREATE TABLE `messages` (
	`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	`conversation_id` text NOT NULL,
	`revision` text NOT NULL,
	`did` text NOT NULL,
	`post_uri` text NOT NULL,
	`text` text NOT NULL,
	`created_at` integer DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`conversation_id`) REFERENCES `conversations`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`revision`) REFERENCES `conversations`(`revision`) ON UPDATE no action ON DELETE no action
);
