22 lines
950 B
SQL
22 lines
950 B
SQL
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
|
|
);
|