[{"_1":2,"_170":-5,"_171":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_37":38,"_39":40,"_41":42,"_43":44,"_45":46,"_47":48,"_49":50,"_51":52,"_53":54,"_55":56,"_57":58,"_59":60,"_61":62,"_63":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","4207e8ad4ddbe7f6e8b804339ca1b2ec4ab76c50","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_148":149,"_167":168,"_169":-5},"handle","tylermyeo","name","notion-backfill-v1","path","main.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":-5,"_114":20,"_115":-5,"_116":-5,"_117":32,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","2a70e382-f53a-11f0-ae2f-42dde27851f2","createdAt",["D",1768829061741],"deletedAt","legacyEnvVars","privacy","public","description","imageUrl","019bc224-5e41-7430-8a11-f9ef2a021060","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","019bc224-5e42-70fc-944f-5712b27516c9","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_129":130},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvb2F1dGhfZ2l0aHViL2ltZ18zOElZbGFhMzZWVEtwQUMwQXVVamtoWVp5V24ifQ","org",{"_94":95,"_133":130,"_12":112},"avatar","branch",{"_136":137,"_96":44,"_102":138,"_139":103,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":97,"_146":95},"version",12,"2a711992-f53a-11f0-ae2f-42dde27851f2","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":150,"_96":99,"_151":152,"_153":-5,"_154":138,"_155":137,"_156":157,"_104":158,"_136":159,"_160":161,"_162":163,"_164":20,"_165":-5,"_98":166},"b4fd0640-f53d-11f0-9deb-42dde27851f2","fileId","2a7285c0-f53a-11f0-ae2f-42dde27851f2","parentId","branchId","branchVersion","latest",true,["D",1768830582667],11,"fileType","interval","content","import { sqlite } from \"https://esm.town/v/std/sqlite\";\n\n// Configuration\nconst BATCH_SIZE = 45; // Process 50 records per run (free tier: 1 min timeout)\nconst NOTION_API_BASE = \"https://api.notion.com/v1\";\nconst NOTION_VERSION = \"2022-06-28\";\nconst DRY_RUN = true; // Set to true to log without tagging\n\ninterface NotionPage {\n id: string;\n properties: {\n Name?: {\n title: Array<{ plain_text: string }>;\n };\n Tags?: {\n multi_select: Array<{ name: string }>;\n };\n };\n}\n\ninterface NotionQueryResponse {\n results: NotionPage[];\n next_cursor: string | null;\n has_more: boolean;\n}\n\ninterface Progress {\n last_cursor: string | null;\n total_processed: number;\n total_tagged: number;\n completed: boolean;\n}\n\n/**\n * Normalize name for comparison (trim and lowercase)\n */\nfunction normalizeName(name: string): string {\n return name.trim().toLowerCase();\n}\n\n/**\n * Get Notion API headers\n */\nfunction getNotionHeaders(): HeadersInit {\n const token = Deno.env.get(\"NOTION_TOKEN\");\n if (!token) {\n throw new Error(\"NOTION_TOKEN environment variable is not set\");\n }\n\n return {\n \"Authorization\": `Bearer ${token}`,\n \"Notion-Version\": NOTION_VERSION,\n \"Content-Type\": \"application/json\",\n };\n}\n\n/**\n * Retry a function with exponential backoff\n */\nasync function retryWithBackoff(\n fn: () => Promise,\n maxRetries: number = 3,\n initialDelay: number = 1000,\n): Promise {\n let lastError: any;\n\n for (let i = 0; i < maxRetries; i++) {\n try {\n return await fn();\n } catch (error: any) {\n lastError = error;\n\n // Check if it's a rate limit error\n if (\n error.message?.includes(\"429\") ||\n error.message?.includes(\"Rate limit\")\n ) {\n const delay = initialDelay * Math.pow(2, i);\n console.log(\n `Rate limit hit, retrying in ${delay}ms... (attempt ${\n i + 1\n }/${maxRetries})`,\n );\n await new Promise((resolve) => setTimeout(resolve, delay));\n continue;\n }\n\n // If it's not a rate limit error, throw immediately\n throw error;\n }\n }\n\n throw lastError;\n}\n\n/**\n * Initialize database tables\n */\nasync function initializeTables(): Promise {\n // Create name_index table (same as webhook)\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS name_index (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL COLLATE NOCASE,\n notion_page_id TEXT NOT NULL UNIQUE,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n // Create index for fast name lookups\n await sqlite.execute(`\n CREATE INDEX IF NOT EXISTS idx_name ON name_index(name)\n `);\n\n // Create progress tracking table\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS backfill_progress (\n id INTEGER PRIMARY KEY CHECK (id = 1),\n last_cursor TEXT,\n total_processed INTEGER DEFAULT 0,\n total_tagged INTEGER DEFAULT 0,\n started_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n last_run_at DATETIME,\n completed BOOLEAN DEFAULT FALSE\n )\n `);\n}\n\n/**\n * Get current progress from database\n */\nasync function getProgress(): Promise {\n const result = await retryWithBackoff(() =>\n sqlite.execute({\n sql:\n \"SELECT last_cursor, total_processed, total_tagged, completed FROM backfill_progress WHERE id = 1\",\n args: [],\n })\n );\n\n if (result.rows.length === 0) {\n // First run, initialize progress\n await retryWithBackoff(() =>\n sqlite.execute({\n sql:\n \"INSERT INTO backfill_progress (id, last_cursor, total_processed, total_tagged, completed) VALUES (1, NULL, 0, 0, FALSE)\",\n args: [],\n })\n );\n return {\n last_cursor: null,\n total_processed: 0,\n total_tagged: 0,\n completed: false,\n };\n }\n\n const row = result.rows[0];\n return {\n last_cursor: row[0] as string | null,\n total_processed: row[1] as number,\n total_tagged: row[2] as number,\n completed: row[3] as boolean,\n };\n}\n\n/**\n * Update progress in database\n */\nasync function updateProgress(data: {\n last_cursor: string | null;\n total_processed: number;\n total_tagged: number;\n completed: boolean;\n}): Promise {\n await retryWithBackoff(() =>\n sqlite.execute({\n sql: `\n UPDATE backfill_progress \n SET last_cursor = ?, \n total_processed = ?, \n total_tagged = ?, \n last_run_at = datetime('now'), \n completed = ?\n WHERE id = 1\n `,\n args: [\n data.last_cursor,\n data.total_processed,\n data.total_tagged,\n data.completed ? 1 : 0,\n ],\n })\n );\n}\n\n/**\n * Query Notion database with pagination\n */\nasync function queryNotionDatabase(\n databaseId: string,\n startCursor: string | null,\n pageSize: number,\n): Promise {\n const body: any = {\n page_size: pageSize,\n };\n\n if (startCursor) {\n body.start_cursor = startCursor;\n }\n\n const response = await fetch(\n `${NOTION_API_BASE}/databases/${databaseId}/query`,\n {\n method: \"POST\",\n headers: getNotionHeaders(),\n body: JSON.stringify(body),\n },\n );\n\n if (!response.ok) {\n const errorText = await response.text();\n throw new Error(\n `Failed to query Notion database: ${response.status} ${errorText}`,\n );\n }\n\n return await response.json();\n}\n\n/**\n * Fetch a page from Notion API\n */\nasync function fetchNotionPage(pageId: string): Promise {\n const response = await fetch(`${NOTION_API_BASE}/pages/${pageId}`, {\n method: \"GET\",\n headers: getNotionHeaders(),\n });\n\n if (!response.ok) {\n const errorText = await response.text();\n throw new Error(\n `Failed to fetch Notion page ${pageId}: ${response.status} ${errorText}`,\n );\n }\n\n return await response.json();\n}\n\n/**\n * Update Tags property on a Notion page\n */\nasync function updateNotionTags(\n pageId: string,\n existingTags: Array<{ name: string }>,\n): Promise {\n return retryWithBackoff(async () => {\n // Check if \"Duplicate\" tag already exists\n const hasDuplicateTag = existingTags.some(\n (tag) => tag.name.toLowerCase() === \"duplicate\",\n );\n\n if (hasDuplicateTag) {\n console.log(\n ` Page ${pageId} already has \"Duplicate\" tag, skipping`,\n );\n return;\n }\n\n if (DRY_RUN) {\n console.log(` [DRY RUN] Would tag page ${pageId} as \"Duplicate\"`);\n return;\n }\n\n // Add \"Duplicate\" tag to existing tags\n const updatedTags = [\n ...existingTags,\n { name: \"Duplicate\" },\n ];\n\n const response = await fetch(`${NOTION_API_BASE}/pages/${pageId}`, {\n method: \"PATCH\",\n headers: getNotionHeaders(),\n body: JSON.stringify({\n properties: {\n Tags: {\n multi_select: updatedTags,\n },\n },\n }),\n });\n\n if (!response.ok) {\n const errorText = await response.text();\n throw new Error(\n `Failed to update Notion page ${pageId}: ${response.status} ${errorText}`,\n );\n }\n\n console.log(` Tagged page ${pageId} as \"Duplicate\"`);\n });\n}\n\n/**\n * Find all duplicate pages for a given name\n */\nasync function findDuplicatePages(normalizedName: string): Promise {\n return retryWithBackoff(async () => {\n const result = await sqlite.execute({\n sql: \"SELECT notion_page_id FROM name_index WHERE name = ?\",\n args: [normalizedName],\n });\n\n return result.rows.map((row: any) => row[0] as string);\n });\n}\n\n/**\n * Insert a new name into the index\n */\nasync function insertNameIndex(name: string, pageId: string): Promise {\n const normalizedName = normalizeName(name);\n\n return retryWithBackoff(async () => {\n try {\n await sqlite.execute({\n sql: \"INSERT INTO name_index (name, notion_page_id) VALUES (?, ?)\",\n args: [normalizedName, pageId],\n });\n } catch (error: any) {\n // If this specific page is already indexed, skip it\n if (error.message?.includes(\"UNIQUE constraint\")) {\n // Silent skip - this is expected for idempotency\n } else {\n throw error;\n }\n }\n });\n}\n\n/**\n * Process a batch of pages: detect duplicates, tag, and index\n */\nasync function processBatch(\n pages: NotionPage[],\n): Promise<{ tagged: number; indexed: number }> {\n let tagged = 0;\n let indexed = 0;\n\n for (const page of pages) {\n const name = page.properties.Name?.title?.[0]?.plain_text;\n if (!name) {\n console.log(` Skipping page ${page.id} - no Name property`);\n continue;\n }\n\n const normalizedName = normalizeName(name);\n\n // Check existing index for duplicates\n const existingDuplicates = await findDuplicatePages(normalizedName);\n\n if (existingDuplicates.length > 0) {\n console.log(\n ` Found ${existingDuplicates.length} existing duplicate(s) for \"${name}\"`,\n );\n\n // Tag the current page\n const currentPage = page; // We already have the page data from the query\n const currentTags = currentPage.properties.Tags?.multi_select || [];\n await updateNotionTags(page.id, currentTags);\n\n // Tag all existing duplicate pages\n for (const duplicatePageId of existingDuplicates) {\n try {\n const duplicatePage = await fetchNotionPage(duplicatePageId);\n const duplicateTags = duplicatePage.properties.Tags?.multi_select ||\n [];\n await updateNotionTags(duplicatePageId, duplicateTags);\n } catch (error: any) {\n console.error(\n ` Failed to tag duplicate page ${duplicatePageId}:`,\n error.message,\n );\n }\n }\n\n tagged += existingDuplicates.length + 1; // Count all tagged pages\n }\n\n // Always index the page (UNIQUE constraint prevents duplicates)\n await insertNameIndex(name, page.id);\n indexed++;\n\n // Small delay to avoid rate limiting\n await new Promise((resolve) => setTimeout(resolve, 50));\n }\n\n return { tagged, indexed };\n}\n\n/**\n * Main scheduled function (runs every 15 minutes)\n */\nexport default async function scheduledBackfill(): Promise {\n const startTime = Date.now();\n console.log(\"=== Backfill Run Started ===\");\n console.log(`Time: ${new Date().toISOString()}`);\n console.log(`Batch size: ${BATCH_SIZE}`);\n console.log(`Dry run: ${DRY_RUN}`);\n\n try {\n // 1. Initialize tables\n await initializeTables();\n\n // 2. Check if already completed\n const progress = await getProgress();\n if (progress.completed) {\n console.log(\"✅ Backfill already completed!\");\n console.log(\n `Total processed: ${progress.total_processed} | Total tagged: ${progress.total_tagged}`,\n );\n return;\n }\n\n console.log(\n `Previous progress: ${progress.total_processed} pages processed`,\n );\n if (progress.last_cursor) {\n console.log(\n `Resuming from cursor: ${progress.last_cursor.substring(0, 20)}...`,\n );\n }\n\n // 3. Get database ID\n const databaseId = Deno.env.get(\"NOTION_DATABASE_ID\");\n if (!databaseId) {\n throw new Error(\"NOTION_DATABASE_ID environment variable is not set\");\n }\n\n // 4. Fetch next batch from Notion\n console.log(`Fetching batch of ${BATCH_SIZE} pages...`);\n const response = await queryNotionDatabase(\n databaseId,\n progress.last_cursor,\n BATCH_SIZE,\n );\n\n const pages = response.results;\n const nextCursor = response.next_cursor;\n const hasMore = response.has_more;\n\n console.log(`Fetched ${pages.length} pages`);\n\n if (pages.length === 0) {\n console.log(\"No pages to process, marking as complete\");\n await updateProgress({\n last_cursor: null,\n total_processed: progress.total_processed,\n total_tagged: progress.total_tagged,\n completed: true,\n });\n console.log(\"🎉 BACKFILL COMPLETE!\");\n return;\n }\n\n // 5. Process batch (detect duplicates, tag, index)\n console.log(\"Processing batch...\");\n const { tagged, indexed } = await processBatch(pages);\n\n // 6. Update progress\n const newTotalProcessed = progress.total_processed + pages.length;\n const newTotalTagged = progress.total_tagged + tagged;\n const completed = !hasMore;\n\n await updateProgress({\n last_cursor: nextCursor,\n total_processed: newTotalProcessed,\n total_tagged: newTotalTagged,\n completed,\n });\n\n // 7. Log summary\n const duration = ((Date.now() - startTime) / 1000).toFixed(2);\n console.log(\"\\n=== Batch Complete ===\");\n console.log(`Processed: ${pages.length} pages`);\n console.log(`Indexed: ${indexed} pages`);\n console.log(`Tagged: ${tagged} pages (including existing duplicates)`);\n console.log(`Total processed: ${newTotalProcessed}`);\n console.log(`Total tagged: ${newTotalTagged}`);\n console.log(`Duration: ${duration}s`);\n\n if (completed) {\n console.log(\"\\n🎉 BACKFILL COMPLETE! All pages processed.\");\n console.log(\"You can now disable this scheduled Val.\");\n } else {\n console.log(\n `\\n⏳ More pages remaining. Next run in 15 minutes...`,\n );\n }\n } catch (error: any) {\n console.error(\"\\n❌ Error during backfill:\");\n console.error(error.message);\n console.error(error.stack);\n throw error;\n }\n}","deleted","label",[99],"permalink","https://www.val.town/x/tylermyeo/notion-backfill-v1/code/main.ts","extension","actionData","errors"]