340 lines
12 KiB
SQL
340 lines
12 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "NodeType" AS ENUM ('task', 'event', 'note', 'project', 'goal');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "NodeStatus" AS ENUM ('active', 'archived', 'deleted');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "LinkType" AS ENUM ('references', 'blocks', 'relates_to', 'parent_of', 'child_of', 'scheduled_as', 'prepared_for');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "WorkspaceRole" AS ENUM ('owner', 'admin', 'editor', 'viewer');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ReminderStatus" AS ENUM ('pending', 'sent', 'dismissed', 'snoozed');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "NotificationType" AS ENUM ('push', 'email', 'sms', 'in_app');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ActionType" AS ENUM ('created', 'updated', 'deleted', 'viewed', 'shared', 'linked', 'completed', 'ai_assisted');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "users" (
|
|
"id" UUID NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password_hash" TEXT NOT NULL,
|
|
"display_name" TEXT NOT NULL,
|
|
"avatar_url" VARCHAR,
|
|
"preferences" JSONB NOT NULL DEFAULT '{}',
|
|
"encryption_key" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
"last_active_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "refresh_tokens" (
|
|
"id" UUID NOT NULL,
|
|
"token_hash" TEXT NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "refresh_tokens_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "workspaces" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"settings" JSONB NOT NULL DEFAULT '{}',
|
|
"owner_id" UUID NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "workspaces_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "nodes" (
|
|
"id" UUID NOT NULL,
|
|
"type" "NodeType" NOT NULL,
|
|
"workspace_id" UUID NOT NULL,
|
|
"owner_id" UUID NOT NULL,
|
|
"parent_id" UUID,
|
|
"title" TEXT NOT NULL,
|
|
"content" JSONB NOT NULL DEFAULT '{}',
|
|
"plain_text" TEXT NOT NULL DEFAULT '',
|
|
"status" "NodeStatus" NOT NULL DEFAULT 'active',
|
|
"priority" INTEGER,
|
|
"start_time" TIMESTAMP(3),
|
|
"end_time" TIMESTAMP(3),
|
|
"duration_minutes" INTEGER,
|
|
"recurrence_rule" TEXT,
|
|
"completion_rate" DOUBLE PRECISION,
|
|
"metadata" JSONB NOT NULL DEFAULT '{}',
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
"is_encrypted" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
"completed_at" TIMESTAMP(3),
|
|
"archived_at" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "nodes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "node_links" (
|
|
"id" UUID NOT NULL,
|
|
"source_id" UUID NOT NULL,
|
|
"target_id" UUID NOT NULL,
|
|
"link_type" "LinkType" NOT NULL,
|
|
"strength" DOUBLE PRECISION NOT NULL DEFAULT 0.5,
|
|
"is_auto" BOOLEAN NOT NULL DEFAULT false,
|
|
"context" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "node_links_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "tags" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"color" VARCHAR(7) NOT NULL,
|
|
"workspace_id" UUID NOT NULL,
|
|
"is_system" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "tags_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "node_tags" (
|
|
"node_id" UUID NOT NULL,
|
|
"tag_id" UUID NOT NULL,
|
|
|
|
CONSTRAINT "node_tags_pkey" PRIMARY KEY ("node_id","tag_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "folders" (
|
|
"id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"workspace_id" UUID NOT NULL,
|
|
"parent_id" UUID,
|
|
"view_type" TEXT NOT NULL DEFAULT 'list',
|
|
"filter_config" JSONB NOT NULL DEFAULT '{}',
|
|
"sort_config" JSONB NOT NULL DEFAULT '{}',
|
|
"order_index" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "folders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "reminders" (
|
|
"id" UUID NOT NULL,
|
|
"node_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"remind_at" TIMESTAMP(3) NOT NULL,
|
|
"notification_type" "NotificationType" NOT NULL,
|
|
"status" "ReminderStatus" NOT NULL DEFAULT 'pending',
|
|
"snooze_until" TIMESTAMP(3),
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "reminders_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "embeddings" (
|
|
"id" UUID NOT NULL,
|
|
"node_id" UUID NOT NULL,
|
|
"chunk_index" INTEGER NOT NULL,
|
|
"chunk_text" TEXT NOT NULL,
|
|
"vector" vector(1536) NOT NULL,
|
|
"model_version" TEXT NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "embeddings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "activities" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"node_id" UUID,
|
|
"workspace_id" UUID NOT NULL,
|
|
"action_type" "ActionType" NOT NULL,
|
|
"metadata" JSONB NOT NULL DEFAULT '{}',
|
|
"session_id" TEXT,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "activities_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "attachments" (
|
|
"id" UUID NOT NULL,
|
|
"node_id" UUID NOT NULL,
|
|
"filename" TEXT NOT NULL,
|
|
"mime_type" TEXT NOT NULL,
|
|
"size_bytes" INTEGER NOT NULL,
|
|
"storage_key" TEXT NOT NULL,
|
|
"thumbnail_key" TEXT,
|
|
"uploaded_by" UUID NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "attachments_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "workspace_members" (
|
|
"id" UUID NOT NULL,
|
|
"workspace_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"role" "WorkspaceRole" NOT NULL,
|
|
"permissions" JSONB NOT NULL DEFAULT '{}',
|
|
"joined_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"last_accessed_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "workspace_members_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "sync_checkpoints" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"device_id" TEXT NOT NULL,
|
|
"last_sync_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"sync_token" TEXT,
|
|
"device_info" JSONB NOT NULL DEFAULT '{}',
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "sync_checkpoints_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "password_reset_tokens" (
|
|
"id" UUID NOT NULL,
|
|
"token_hash" TEXT NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"expires_at" TIMESTAMP(3) NOT NULL,
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "password_reset_tokens_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "refresh_tokens_user_id_idx" ON "refresh_tokens"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "refresh_tokens_token_hash_idx" ON "refresh_tokens"("token_hash");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "workspaces_slug_owner_id_key" ON "workspaces"("slug", "owner_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "nodes_type_status_workspace_id_owner_id_idx" ON "nodes"("type", "status", "workspace_id", "owner_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "nodes_created_at_idx" ON "nodes"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "node_links_source_id_idx" ON "node_links"("source_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "node_links_target_id_idx" ON "node_links"("target_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "node_links_source_id_target_id_link_type_key" ON "node_links"("source_id", "target_id", "link_type");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "embeddings_node_id_idx" ON "embeddings"("node_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "activities_created_at_idx" ON "activities"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "activities_user_id_workspace_id_action_type_idx" ON "activities"("user_id", "workspace_id", "action_type");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "workspace_members_workspace_id_user_id_key" ON "workspace_members"("workspace_id", "user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "sync_checkpoints_user_id_device_id_key" ON "sync_checkpoints"("user_id", "device_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "password_reset_tokens_token_hash_idx" ON "password_reset_tokens"("token_hash");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "refresh_tokens" ADD CONSTRAINT "refresh_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "workspaces" ADD CONSTRAINT "workspaces_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "nodes" ADD CONSTRAINT "nodes_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "nodes" ADD CONSTRAINT "nodes_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "nodes" ADD CONSTRAINT "nodes_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "nodes"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "node_links" ADD CONSTRAINT "node_links_source_id_fkey" FOREIGN KEY ("source_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "node_links" ADD CONSTRAINT "node_links_target_id_fkey" FOREIGN KEY ("target_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "tags" ADD CONSTRAINT "tags_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "node_tags" ADD CONSTRAINT "node_tags_node_id_fkey" FOREIGN KEY ("node_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "node_tags" ADD CONSTRAINT "node_tags_tag_id_fkey" FOREIGN KEY ("tag_id") REFERENCES "tags"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "folders" ADD CONSTRAINT "folders_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "folders" ADD CONSTRAINT "folders_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "folders"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "reminders" ADD CONSTRAINT "reminders_node_id_fkey" FOREIGN KEY ("node_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "reminders" ADD CONSTRAINT "reminders_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "embeddings" ADD CONSTRAINT "embeddings_node_id_fkey" FOREIGN KEY ("node_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "activities" ADD CONSTRAINT "activities_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "activities" ADD CONSTRAINT "activities_node_id_fkey" FOREIGN KEY ("node_id") REFERENCES "nodes"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_node_id_fkey" FOREIGN KEY ("node_id") REFERENCES "nodes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "workspace_members" ADD CONSTRAINT "workspace_members_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "sync_checkpoints" ADD CONSTRAINT "sync_checkpoints_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|