Files
sharepoint-acc-sync/drizzle/0000_init.sql
2026-10-23 11:32:30 -05:00

76 lines
2.5 KiB
SQL

CREATE TABLE `items` (
`project_id` integer NOT NULL,
`source_id` text NOT NULL,
`parent_source_id` text NOT NULL,
`name` text NOT NULL,
`is_folder` integer NOT NULL,
`content_tag` text,
`source_modified_at` integer NOT NULL,
`size` integer,
`deleted` integer NOT NULL,
`target_id` text,
`synced_parent_source_id` text,
`synced_name` text,
`synced_content_tag` text,
`synced_deleted` integer NOT NULL,
`synced_at` integer,
`status` text NOT NULL,
`attempt_count` integer DEFAULT 0 NOT NULL,
`last_error` text,
`last_attempt_at` integer,
PRIMARY KEY(`project_id`, `source_id`),
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `items_project_status` ON `items` (`project_id`,`status`);--> statement-breakpoint
CREATE TABLE `projects` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`name` text NOT NULL,
`enabled` integer DEFAULT true NOT NULL,
`paused_reason` text,
`sp_site_id` text NOT NULL,
`sp_drive_id` text NOT NULL,
`sp_root_item_id` text NOT NULL,
`sp_display_path` text NOT NULL,
`acc_hub_id` text NOT NULL,
`acc_project_id` text NOT NULL,
`acc_root_folder_id` text NOT NULL,
`acc_display_path` text NOT NULL,
`delta_cursor` text,
`first_synced_at` integer,
`last_run_at` integer,
`created_at` integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE `run_events` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`run_id` integer NOT NULL,
`project_id` integer NOT NULL,
`source_id` text NOT NULL,
`path` text NOT NULL,
`action` text NOT NULL,
`message` text,
`at` integer NOT NULL,
FOREIGN KEY (`run_id`) REFERENCES `runs`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `run_events_run` ON `run_events` (`run_id`);--> statement-breakpoint
CREATE TABLE `runs` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`project_id` integer NOT NULL,
`trigger` text NOT NULL,
`started_at` integer NOT NULL,
`finished_at` integer,
`outcome` text,
`error` text,
`created` integer DEFAULT 0 NOT NULL,
`updated` integer DEFAULT 0 NOT NULL,
`renamed` integer DEFAULT 0 NOT NULL,
`moved` integer DEFAULT 0 NOT NULL,
`deleted` integer DEFAULT 0 NOT NULL,
`restored` integer DEFAULT 0 NOT NULL,
`failed` integer DEFAULT 0 NOT NULL,
FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `runs_project_started` ON `runs` (`project_id`,`started_at`);