Andrew S Erwin
4e421f6ea2
release gate / check (push) Failing after 6m43s
High severity: - wire TRUSTED_PROXY through Compose/deploy/docs and warn at web startup - bucket public rate limits on effective client IP, add per-bookmark budget, and forward the signed browser identity from the share page - disable first-run bootstrap in server deployments and document seeding - serialize last-admin demotion in one advisory-locked transaction Medium/low: - cap and batch share recipient inserts - add a shared outbound budget for preview/CardDAV/IMAP/signal work - pin CardDAV dials to 443 and bound IMAP logout - fix rightward kanban column drags and normalize cards on column delete - classify backend outages in auth guards, add request timeouts and an error boundary, remove the dead login route, surface throttling - enforce pinned/unpinned page reorder grouping server-side - avoid preview work for stale bookmark versions; drop duplicate dialog fetch - sequence-guard collection/tag/calendar/quick-find loads - validate property config and fix MULTI_SELECT/DATE filter inputs - document index maintenance; apply same-origin check to sign-out - trim signup names, unique shared dates and kanban keys (migration 010), reset reference cache on identity changes, hide OIDC issuer, return 413 for oversized direct bodies, escape Markdown link destinations - remove dead template files and duplicate test helper Verified with go build/vet, full PostgreSQL race suite, db convergence, tsc, both ESLint configs and a Next production build.
437 lines
18 KiB
PL/PgSQL
437 lines
18 KiB
PL/PgSQL
-- computer database schema
|
|
-- Derived from computer_porting_guide.md section 2 and prisma/schema.prisma.
|
|
-- IDs are text (CUID/UUID). CASCADE / SetNull per the guide.
|
|
|
|
-- Run against an EMPTY database. Never drops or recreates a database.
|
|
BEGIN;
|
|
|
|
-- Trigram indexes back the page search substring predicate (review PERF-2).
|
|
-- pg_trgm is a trusted extension, so the database owner can enable it.
|
|
CREATE EXTENSION IF NOT EXISTS pg_trgm;
|
|
|
|
-- 2.1 legacy enum
|
|
CREATE TYPE "KanbanStatus" AS ENUM ('INBOX','TODO','IN_PROGRESS','DONE','ARCHIVED');
|
|
|
|
-- 2.2 User
|
|
CREATE TABLE "User" (
|
|
id text PRIMARY KEY,
|
|
name text,
|
|
email text UNIQUE,
|
|
"emailVerified" timestamptz,
|
|
image text,
|
|
password text,
|
|
role text NOT NULL DEFAULT 'USER' CHECK (role IN ('USER','ADMIN')),
|
|
"tokenVersion" integer NOT NULL DEFAULT 1,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- 2.3 Account, Session, VerificationToken
|
|
CREATE TABLE "Account" (
|
|
id text PRIMARY KEY,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
type text NOT NULL,
|
|
provider text NOT NULL,
|
|
"providerAccountId" text NOT NULL,
|
|
"refresh_token" text,
|
|
"access_token" text,
|
|
"expires_at" int,
|
|
"token_type" text,
|
|
scope text,
|
|
"id_token" text,
|
|
"session_state" text,
|
|
UNIQUE(provider, "providerAccountId")
|
|
);
|
|
CREATE INDEX "Account_userId_idx" ON "Account"("userId");
|
|
|
|
CREATE TABLE "Session" (
|
|
id text PRIMARY KEY,
|
|
"sessionToken" text NOT NULL UNIQUE,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
expires timestamptz NOT NULL
|
|
);
|
|
CREATE INDEX "Session_userId_idx" ON "Session"("userId");
|
|
|
|
CREATE TABLE "VerificationToken" (
|
|
identifier text NOT NULL,
|
|
token text NOT NULL UNIQUE,
|
|
expires timestamptz NOT NULL,
|
|
UNIQUE(identifier, token)
|
|
);
|
|
|
|
-- 2.4 Bookmark
|
|
CREATE TABLE "Bookmark" (
|
|
id text PRIMARY KEY,
|
|
url text NOT NULL,
|
|
title text,
|
|
description text,
|
|
favicon text,
|
|
"ogImage" text,
|
|
"ogTitle" text,
|
|
"ogDescription" text,
|
|
notes text,
|
|
"dueDate" timestamptz,
|
|
"alertAt" timestamptz,
|
|
"alertSent" boolean NOT NULL DEFAULT false,
|
|
"kanbanStatus" "KanbanStatus" NOT NULL DEFAULT 'INBOX',
|
|
"kanbanColumnId" text,
|
|
-- Board ordering within a column. Server-normalized on every card move;
|
|
-- legacy rows default to 0 and fall back to updatedAt-desc ordering.
|
|
"kanbanPosition" integer NOT NULL DEFAULT 0,
|
|
"imapWatchEnabled" boolean NOT NULL DEFAULT false,
|
|
"imapQuery" text,
|
|
"isPublic" boolean NOT NULL DEFAULT false,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"contactId" text,
|
|
-- Optimistic concurrency revision (review CR-04): every owner-driven PUT
|
|
-- (fields and tag replacement) requires the expected version and bumps it;
|
|
-- the column-deletion move bumps it too. Sharing and server-driven flags
|
|
-- (alertSent) are outside the contract — see API.md.
|
|
version integer NOT NULL DEFAULT 0,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "Bookmark_ownerId_idx" ON "Bookmark"("ownerId");
|
|
CREATE INDEX "Bookmark_ownerId_updatedAt_id_idx" ON "Bookmark"("ownerId","updatedAt" DESC,id DESC);
|
|
CREATE INDEX "Bookmark_contactId_idx" ON "Bookmark"("contactId");
|
|
CREATE INDEX "Bookmark_kanbanStatus_idx" ON "Bookmark"("kanbanStatus");
|
|
CREATE INDEX "Bookmark_kanbanColumnId_idx" ON "Bookmark"("kanbanColumnId");
|
|
CREATE INDEX "Bookmark_ownerId_kanbanColumnId_kanbanPosition_idx" ON "Bookmark"("ownerId","kanbanColumnId","kanbanPosition",id);
|
|
CREATE INDEX "Bookmark_dueDate_idx" ON "Bookmark"("dueDate");
|
|
CREATE INDEX "Bookmark_alertAt_idx" ON "Bookmark"("alertAt");
|
|
|
|
-- 2.5 Tag
|
|
CREATE TABLE "Tag" (
|
|
id text PRIMARY KEY,
|
|
name text NOT NULL,
|
|
color text NOT NULL DEFAULT '#6366f1',
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE(name, "ownerId")
|
|
);
|
|
CREATE INDEX "Tag_ownerId_idx" ON "Tag"("ownerId");
|
|
|
|
-- 2.6 BookmarkTag
|
|
CREATE TABLE "BookmarkTag" (
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
"tagId" text NOT NULL REFERENCES "Tag"(id) ON DELETE CASCADE,
|
|
PRIMARY KEY ("bookmarkId", "tagId")
|
|
);
|
|
CREATE INDEX "BookmarkTag_tagId_idx" ON "BookmarkTag"("tagId");
|
|
|
|
-- 2.7 BookmarkShare
|
|
CREATE TABLE "BookmarkShare" (
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("bookmarkId", "userId")
|
|
);
|
|
CREATE INDEX "BookmarkShare_userId_idx" ON "BookmarkShare"("userId");
|
|
|
|
-- 2.8 TagShare
|
|
CREATE TABLE "TagShare" (
|
|
"tagId" text NOT NULL REFERENCES "Tag"(id) ON DELETE CASCADE,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("tagId", "userId")
|
|
);
|
|
CREATE INDEX "TagShare_userId_idx" ON "TagShare"("userId");
|
|
|
|
-- 2.9 Contact
|
|
CREATE TABLE "Contact" (
|
|
id text PRIMARY KEY,
|
|
uid text NOT NULL,
|
|
"displayName" text NOT NULL,
|
|
email text,
|
|
phone text,
|
|
"cardDavUrl" text,
|
|
-- CardDAV provenance (review CR-07): the address-book URL this contact was
|
|
-- imported from. Deletion reconciliation is scoped to it, so manually
|
|
-- created contacts (NULL) and other address books' imports are never
|
|
-- touched by a sync.
|
|
source text,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE(uid, "userId")
|
|
);
|
|
CREATE INDEX "Contact_userId_idx" ON "Contact"("userId");
|
|
ALTER TABLE "Bookmark" ADD CONSTRAINT "Bookmark_contactId_fkey"
|
|
FOREIGN KEY ("contactId") REFERENCES "Contact"(id) ON DELETE SET NULL;
|
|
|
|
-- 2.10 ImapConfig
|
|
CREATE TABLE "ImapConfig" (
|
|
id text PRIMARY KEY,
|
|
host text NOT NULL,
|
|
port int NOT NULL DEFAULT 993,
|
|
tls boolean NOT NULL DEFAULT true,
|
|
username text NOT NULL,
|
|
password text NOT NULL,
|
|
folder text NOT NULL DEFAULT 'INBOX',
|
|
"userId" text UNIQUE NOT NULL REFERENCES "User"(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- 2.11 KanbanColumn
|
|
CREATE TABLE "KanbanColumn" (
|
|
id text PRIMARY KEY,
|
|
label text NOT NULL,
|
|
color text NOT NULL DEFAULT '#64748b',
|
|
position int NOT NULL DEFAULT 0,
|
|
"key" text,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "KanbanColumn_userId_position_idx" ON "KanbanColumn"("userId", position);
|
|
-- Legacy status keys are unique per user (review L-12): ResolveColumnID picks
|
|
-- the first match, so duplicates would make the legacy fallback ambiguous.
|
|
CREATE UNIQUE INDEX "KanbanColumn_userId_key_idx" ON "KanbanColumn"("userId", "key") WHERE "key" IS NOT NULL;
|
|
|
|
-- Bookmark.kanbanColumnId -> KanbanColumn (SetNull)
|
|
ALTER TABLE "Bookmark" ADD CONSTRAINT "Bookmark_kanbanColumnId_fkey"
|
|
FOREIGN KEY ("kanbanColumnId") REFERENCES "KanbanColumn"(id) ON DELETE SET NULL;
|
|
|
|
-- 2.12 Page
|
|
CREATE TABLE "Page" (
|
|
id text PRIMARY KEY,
|
|
title text NOT NULL,
|
|
content text NOT NULL DEFAULT '',
|
|
icon text,
|
|
pinned boolean NOT NULL DEFAULT false,
|
|
position int NOT NULL DEFAULT 0,
|
|
-- Connected pages (phase 3): a page may live under another page. Deleting
|
|
-- the parent promotes its children to the top level instead of deleting
|
|
-- them; cycle rejection is enforced by the API.
|
|
"parentId" text REFERENCES "Page"(id) ON DELETE SET NULL,
|
|
-- Optimistic concurrency revision (review DATA-01): content saves bump it;
|
|
-- a stale base version in PATCH is rejected with 409.
|
|
version integer NOT NULL DEFAULT 1,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "Page_ownerId_position_idx" ON "Page"("ownerId", position);
|
|
CREATE INDEX "Page_parentId_idx" ON "Page"("parentId");
|
|
-- Index-backed substring search over the two searchable fields.
|
|
CREATE INDEX "Page_title_trgm_idx" ON "Page" USING gin (title gin_trgm_ops);
|
|
CREATE INDEX "Page_content_trgm_idx" ON "Page" USING gin (content gin_trgm_ops);
|
|
|
|
-- 2.13 PageBookmark
|
|
CREATE TABLE "PageBookmark" (
|
|
"pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
position int NOT NULL DEFAULT 0,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("pageId", "bookmarkId")
|
|
);
|
|
CREATE INDEX "PageBookmark_bookmarkId_idx" ON "PageBookmark"("bookmarkId");
|
|
|
|
-- 2.13b PageLink: resolved internal page links, rebuilt from page content on
|
|
-- every content save. Targets are only stored when the author owns the target
|
|
-- page, so a backlink can never reveal an inaccessible title. Self-links are
|
|
-- ignored by the writer and refused here as defense in depth.
|
|
CREATE TABLE "PageLink" (
|
|
"sourcePageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
|
|
"targetPageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("sourcePageId", "targetPageId"),
|
|
CONSTRAINT "PageLink_no_self" CHECK ("sourcePageId" <> "targetPageId")
|
|
);
|
|
CREATE INDEX "PageLink_targetPageId_idx" ON "PageLink"("targetPageId");
|
|
|
|
-- 2.13c PageRevision: server-side content history. A snapshot is recorded for
|
|
-- every committed content version (including restore, which is a new version)
|
|
-- so an old draft can be inspected and restored without ever rewriting the
|
|
-- past. Revision rows cascade with their page.
|
|
CREATE TABLE "PageRevision" (
|
|
id text PRIMARY KEY,
|
|
"pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
|
|
version integer NOT NULL,
|
|
title text NOT NULL,
|
|
content text NOT NULL,
|
|
icon text,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE ("pageId", version)
|
|
);
|
|
CREATE INDEX "PageRevision_pageId_version_idx" ON "PageRevision"("pageId", version DESC);
|
|
|
|
-- 2.14 SignalSource
|
|
CREATE TABLE "SignalSource" (
|
|
id text PRIMARY KEY,
|
|
type text NOT NULL,
|
|
config jsonb NOT NULL DEFAULT '{}',
|
|
enabled boolean NOT NULL DEFAULT true,
|
|
"lastCheckedAt" timestamptz,
|
|
"lastStatus" text,
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "SignalSource_bookmarkId_idx" ON "SignalSource"("bookmarkId");
|
|
CREATE INDEX "SignalSource_ownerId_idx" ON "SignalSource"("ownerId");
|
|
|
|
-- 2.14b SignalCheckpoint (review SYNC-02): durable per-source mailbox
|
|
-- ingestion state. A poll processes only UIDs newer than lastUid while
|
|
-- uidValidity matches; the checkpoint advances only after the batch's
|
|
-- signals are durably inserted. A uidValidity change resets ingestion.
|
|
CREATE TABLE "SignalCheckpoint" (
|
|
"sourceId" text PRIMARY KEY REFERENCES "SignalSource"(id) ON DELETE CASCADE,
|
|
"uidValidity" bigint NOT NULL,
|
|
"lastUid" bigint NOT NULL,
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- 2.15 Signal
|
|
CREATE TABLE "Signal" (
|
|
id text PRIMARY KEY,
|
|
"sourceType" text NOT NULL,
|
|
title text NOT NULL,
|
|
summary text,
|
|
url text,
|
|
"externalId" text,
|
|
"occurredAt" timestamptz NOT NULL DEFAULT now(),
|
|
read boolean NOT NULL DEFAULT false,
|
|
"sourceId" text,
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE("sourceId", "externalId")
|
|
);
|
|
CREATE INDEX "Signal_bookmarkId_occurredAt_idx" ON "Signal"("bookmarkId", "occurredAt");
|
|
CREATE INDEX "Signal_ownerId_occurredAt_idx" ON "Signal"("ownerId", "occurredAt");
|
|
ALTER TABLE "Signal" ADD CONSTRAINT "Signal_sourceId_fkey"
|
|
FOREIGN KEY ("sourceId") REFERENCES "SignalSource"(id) ON DELETE SET NULL;
|
|
|
|
-- 2.16 AppSetting: server-owned singleton settings rows. 'adminBootstrap'
|
|
-- records that initial provisioning completed (review AUTH-06), so the
|
|
-- absence of a qualifying administrator never re-opens first-signup
|
|
-- promotion after a demotion or removal.
|
|
CREATE TABLE "AppSetting" (
|
|
key text PRIMARY KEY,
|
|
value jsonb NOT NULL DEFAULT '{}',
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- 2.16b Trash: recoverable deletions (trust phase). The row is a private
|
|
-- per-owner snapshot taken in the same transaction as the hard delete. It
|
|
-- keeps intrinsic data and owner-scoped relationships, but deliberately not
|
|
-- access grants (bookmark shares, public flags, collection shares): restoring
|
|
-- an item never resurrects a grant that was revoked while it sat in the bin.
|
|
CREATE TABLE "Trash" (
|
|
id text PRIMARY KEY,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
type text NOT NULL CHECK (type IN ('bookmark','page','collection')),
|
|
"originalId" text NOT NULL,
|
|
data jsonb NOT NULL,
|
|
"deletedAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE (type, "originalId")
|
|
);
|
|
CREATE INDEX "Trash_ownerId_deletedAt_idx" ON "Trash"("ownerId", "deletedAt" DESC);
|
|
|
|
-- 2.16 SharedDate
|
|
CREATE TABLE "SharedDate" (
|
|
id text PRIMARY KEY,
|
|
date date NOT NULL,
|
|
note text,
|
|
"creatorId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"recipientId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "SharedDate_recipientId_idx" ON "SharedDate"("recipientId");
|
|
CREATE INDEX "SharedDate_creatorId_idx" ON "SharedDate"("creatorId");
|
|
CREATE INDEX "SharedDate_date_idx" ON "SharedDate"(date);
|
|
-- One row per creator/recipient/date (review L-3): re-sharing the same date
|
|
-- updates the note instead of duplicating calendar rows and notifications.
|
|
CREATE UNIQUE INDEX "SharedDate_creatorId_recipientId_date_idx" ON "SharedDate"("creatorId","recipientId",date);
|
|
CREATE INDEX "Bookmark_ownerId_alertAt_unsent_idx" ON "Bookmark"("ownerId","alertAt") WHERE NOT "alertSent";
|
|
|
|
-- 2.17 Collections (Notion-like upgrades phase 2; see 002_collections.sql for
|
|
-- the design decisions). Contextual workflow state: the same bookmark can
|
|
-- live in many collections with independent property values; membership
|
|
-- never broadens access and never owns the bookmark.
|
|
CREATE TABLE "Collection" (
|
|
id text PRIMARY KEY,
|
|
name text NOT NULL,
|
|
description text,
|
|
icon text,
|
|
"ownerId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
version integer NOT NULL DEFAULT 1,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "Collection_ownerId_idx" ON "Collection"("ownerId");
|
|
|
|
-- 2.17b CollectionShare: explicit, revocable read access to a collection.
|
|
-- Recipients get the narrowed bookmark projection for member bookmarks (via
|
|
-- BookmarkVisible); sharing therefore widens access only by the owner's
|
|
-- deliberate action, is disclosed in the share UI, and revocation removes
|
|
-- access immediately. Mutations remain owner-only.
|
|
CREATE TABLE "CollectionShare" (
|
|
"collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
|
|
"userId" text NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("collectionId", "userId")
|
|
);
|
|
CREATE INDEX "CollectionShare_userId_idx" ON "CollectionShare"("userId");
|
|
|
|
CREATE TABLE "CollectionProperty" (
|
|
id text PRIMARY KEY,
|
|
"collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
type text NOT NULL CHECK (type IN ('TEXT','NUMBER','SELECT','MULTI_SELECT','DATE','CHECKBOX','URL','RELATION')),
|
|
position integer NOT NULL DEFAULT 0,
|
|
config jsonb NOT NULL DEFAULT '{}',
|
|
version integer NOT NULL DEFAULT 1,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE ("collectionId", name)
|
|
);
|
|
|
|
CREATE TABLE "CollectionItem" (
|
|
id text PRIMARY KEY,
|
|
"collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
|
|
"bookmarkId" text NOT NULL REFERENCES "Bookmark"(id) ON DELETE CASCADE,
|
|
"propertyValues" jsonb NOT NULL DEFAULT '{}',
|
|
position integer NOT NULL DEFAULT 0,
|
|
version integer NOT NULL DEFAULT 1,
|
|
"addedAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE ("collectionId", "bookmarkId")
|
|
);
|
|
CREATE INDEX "CollectionItem_bookmarkId_idx" ON "CollectionItem"("bookmarkId");
|
|
|
|
CREATE TABLE "CollectionView" (
|
|
id text PRIMARY KEY,
|
|
"collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
|
|
name text NOT NULL,
|
|
type text NOT NULL DEFAULT 'TABLE' CHECK (type IN ('TABLE','BOARD','CALENDAR','LIST')),
|
|
position integer NOT NULL DEFAULT 0,
|
|
filters jsonb NOT NULL DEFAULT '{"operator":"AND","clauses":[]}',
|
|
sorts jsonb NOT NULL DEFAULT '[]',
|
|
"groupByPropertyId" text REFERENCES "CollectionProperty"(id) ON DELETE SET NULL,
|
|
"displaySettings" jsonb NOT NULL DEFAULT '{}',
|
|
version integer NOT NULL DEFAULT 1,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
"updatedAt" timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX "CollectionView_collectionId_idx" ON "CollectionView"("collectionId");
|
|
|
|
-- 2.18 PageEmbed (Notion-like upgrades phase 3): a live collection view
|
|
-- embedded in a page. The reference is resolved from the page's Markdown
|
|
-- (`{{collection:<id>|view=<id>}}`) on every content save. Only collections
|
|
-- owned by the page's author are stored; embedding displays data and never
|
|
-- publishes it or widens bookmark access. Deleting the collection or the
|
|
-- chosen view removes/falls back rather than leaving a dangling embed.
|
|
CREATE TABLE "PageEmbed" (
|
|
"pageId" text NOT NULL REFERENCES "Page"(id) ON DELETE CASCADE,
|
|
"collectionId" text NOT NULL REFERENCES "Collection"(id) ON DELETE CASCADE,
|
|
"viewId" text REFERENCES "CollectionView"(id) ON DELETE SET NULL,
|
|
position integer NOT NULL DEFAULT 0,
|
|
"createdAt" timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY ("pageId", "collectionId")
|
|
);
|
|
CREATE INDEX "PageEmbed_collectionId_idx" ON "PageEmbed"("collectionId");
|
|
CREATE INDEX "PageEmbed_viewId_idx" ON "PageEmbed"("viewId");
|
|
COMMIT;
|