Andrew S Erwin
9db69efe4b
release gate / check (push) Failing after 7m7s
Implement Notion-like upgrade phase 3: owner-scoped nested pages with cycle rejection and child promotion on delete; Markdown-derived internal links and backlinks; live collection embeds resolved in the content-save transaction. Add migration 008, API/docs, integration coverage, and page/sidebar UI.
58 lines
2.6 KiB
SQL
58 lines
2.6 KiB
SQL
-- 008_connected_pages.sql — Notion-like upgrades phase 3.
|
|
--
|
|
-- Nested pages, resolved internal page links (backlinks), and live collection
|
|
-- views embedded in a page. Links and embeds are derived from Markdown on
|
|
-- every content save: the page body stays the single source of truth.
|
|
--
|
|
-- Access rules:
|
|
-- * Pages remain owner-scoped. A link to a page the author does not own is
|
|
-- ignored, so backlinks can never reveal a title across accounts.
|
|
-- * An embed is stored only when the author owns the collection. The live
|
|
-- view still re-checks bookmark visibility through the collection item
|
|
-- endpoints (N-08); embedding never publishes anything.
|
|
--
|
|
-- Deleting:
|
|
-- * Deleting a parent page promotes its children (SET NULL), never deletes
|
|
-- them silently.
|
|
-- * Deleting a page cascades its outgoing/incoming links and embeds.
|
|
-- * Deleting a collection cascades its embeds; deleting a view clears the
|
|
-- embed's viewId so it falls back to the collection's default view.
|
|
|
|
ALTER TABLE "Page" ADD COLUMN IF NOT EXISTS "parentId" text;
|
|
|
|
-- The named constraint matches schema.sql so the fresh and upgraded paths
|
|
-- converge. The guard makes a re-run against an already-current schema a
|
|
-- no-op (without it, schema.sql's inline FK would collide).
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint
|
|
WHERE conname = 'Page_parentId_fkey' AND conrelid = '"Page"'::regclass
|
|
) THEN
|
|
ALTER TABLE "Page" ADD CONSTRAINT "Page_parentId_fkey"
|
|
FOREIGN KEY ("parentId") REFERENCES "Page"(id) ON DELETE SET NULL;
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE INDEX IF NOT EXISTS "Page_parentId_idx" ON "Page"("parentId");
|
|
|
|
CREATE TABLE IF NOT EXISTS "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 IF NOT EXISTS "PageLink_targetPageId_idx" ON "PageLink"("targetPageId");
|
|
|
|
CREATE TABLE IF NOT EXISTS "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 IF NOT EXISTS "PageEmbed_collectionId_idx" ON "PageEmbed"("collectionId");
|
|
CREATE INDEX IF NOT EXISTS "PageEmbed_viewId_idx" ON "PageEmbed"("viewId");
|