blog/pi/create_index.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "88c12607",
   "metadata": {},
   "source": [
    "Process docs, save to Supabase.\n",
    "\n",
    "Create a `.env` file and add your `SUPABASE_URL`, `SUPABASE_KEY`, `OPENAI_API_KEY`, and relevant Snowflake credentials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e16a8a37",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "from dotenv import load_dotenv\n",
    "from functools import partial\n",
    "import json\n",
    "import logging\n",
    "from markdownify import markdownify as md\n",
    "import mistune\n",
    "import numpy as np\n",
    "import os\n",
    "import pickle\n",
    "import re\n",
    "import requests\n",
    "import sys\n",
    "from textwrap import dedent\n",
    "import time\n",
    "\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "from langchain.schema import Document\n",
    "from langchain.text_splitter import HTMLHeaderTextSplitter, MarkdownHeaderTextSplitter, CharacterTextSplitter\n",
    "\n",
    "\n",
    "from langchain.embeddings import OpenAIEmbeddings\n",
    "from pathlib import Path\n",
    "import snowflake.connector\n",
    "from supabase import create_client, Client\n",
    "from supabase.lib.client_options import ClientOptions\n",
    "\n",
    "from typing import (\n",
    "    Dict,\n",
    "    Union,\n",
    ")\n",
    "\n",
    "load_dotenv()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d60292ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "url: str = os.environ.get(\"SUPABASE_URL\")\n",
    "key: str = os.environ.get(\"SUPABASE_KEY\")\n",
    "\n",
    "supabase: Client = create_client(url, key, options=ClientOptions(\n",
    "    postgrest_client_timeout=120, \n",
    "    storage_client_timeout=120\n",
    "  )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "933da467",
   "metadata": {},
   "outputs": [],
   "source": [
    "# These are the core lists we use to store content and \"source\" (e.g. the URL to the doc)\n",
    "# We pass this to OpenAI to create embeddings. Later, the Q&A bot searches for sources\n",
    "# that match the query in that embedding.\n",
    "metadatas = []\n",
    "docs = []\n",
    "\n",
    "def split_and_index_source(splitter, text, key, metadata={}):\n",
    "    try:\n",
    "        splits = splitter.split_text(text)\n",
    "        new_metadatas = []\n",
    "        for split in splits:\n",
    "            new_metadata = metadata.copy()\n",
    "            new_metadata['source'] = key\n",
    "            new_metadatas.append(new_metadata)\n",
    "    \n",
    "            # All docs should be LangChain Documents\n",
    "            docs.append(split.page_content)\n",
    "    \n",
    "        metadatas.extend(new_metadatas)\n",
    "    except:\n",
    "        print(f\"Could not split and index docs for {key}\")\n",
    "\n",
    "        \n",
    "## TEXT SPLITTERS ##\n",
    "headers_to_split_on = [\n",
    "    (\"h1\", \"Header 1\"),\n",
    "    (\"h2\", \"Header 2\"),\n",
    "]\n",
    "text_splitter = HTMLHeaderTextSplitter(headers_to_split_on=headers_to_split_on)\n",
    "\n",
    "markdown_headers_to_split_on = [\n",
    "    (\"#\", \"Header 1\"),\n",
    "    (\"##\", \"Header 2\"),\n",
    "    (\"###\", \"Header 3\"),\n",
    "]\n",
    "markdown_splitter = MarkdownHeaderTextSplitter(headers_to_split_on=markdown_headers_to_split_on)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e457b9d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def parse_html_and_get_content(content):\n",
    "    \"\"\" Parses text from HTML, return split text\n",
    "    \"\"\"\n",
    "    soup = BeautifulSoup(content, 'html.parser')\n",
    "\n",
    "    return {\n",
    "        \"text\": content.decode('utf-8'),\n",
    "        \"title\": soup.title.string or \"Untitled\"\n",
    "    }\n",
    "\n",
    "    \n",
    "def fetch_url_and_index_text(url):\n",
    "    \"\"\" Fetches URL, returns list of Documents\n",
    "    \"\"\"\n",
    "    print(f\"Fetching {url}\")\n",
    "    try:\n",
    "        response = requests.get(url)\n",
    "    except:\n",
    "        print(f\"Failed to index {url}\")\n",
    "        return\n",
    "    \n",
    "    # Find the meta tag with the property 'article:published_time'\n",
    "    # Specicic to the blog, but can extend to other sources if needed\n",
    "    soup = BeautifulSoup(response.content, 'html.parser')\n",
    "    meta_tag = soup.find('meta', {'property': 'article:published_time'})\n",
    "    published_time = meta_tag['content'] if meta_tag else None\n",
    "    \n",
    "    # We don't want any content from the v1 version of the builder\n",
    "    if published_time is not None:\n",
    "        cutoff_date = datetime(2022, 5, 1)\n",
    "        date_obj = datetime.strptime(published_time, \"%Y-%m-%dT%H:%M:%S.%fZ\")\n",
    "        if date_obj.date() < cutoff_date.date():\n",
    "            print(\"v1 content, skipping\")\n",
    "            return\n",
    "    \n",
    "    content = parse_html_and_get_content(response.content)\n",
    "    metadata = { \"url\": url, \"title\": content[\"title\"] }\n",
    "    split_and_index_source(text_splitter, content[\"text\"], url)\n",
    "\n",
    "\n",
    "def index_sources(sources, sourceFn):\n",
    "    \"\"\" \n",
    "    sources: Array of source keys (URLs, file paths, etc)\n",
    "    sourceFn: Function to retrieve data from the source via URL, filesystem, etc. The function must accept the source key as input\n",
    "    \"\"\"\n",
    "\n",
    "\n",
    "    for i in sources:\n",
    "        sourceFn(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a118c484",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Index public https://pipedream.com pages\n",
    "urls = [\n",
    "    \"https://pipedream.com/support\",\n",
    "    \"https://pipedream.com/terms\",\n",
    "    \"https://pipedream.com/privacy\",\n",
    "    \"https://pipedream.com/sla\",\n",
    "    \"https://pipedream.com/dpa\",\n",
    "    \"https://pipedream.com/affiliates\"\n",
    "]\n",
    "\n",
    "index_sources(urls, fetch_url_and_index_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d499f57b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# List public docs from the pipedream public repo (change to your path)\n",
    "# Markdown files contain all relevant content\n",
    "\n",
    "# REPLACE THIS with the location of the pipedream public repo\n",
    "root_dir = \"/Users/dylburger/pipedream/docs/docs\"\n",
    "publicDocs = list(Path(root_dir).glob(\"**/*.md\"))\n",
    "\n",
    "# Old content, or content that keeps yielding false positives\n",
    "# Component examples are excluded, since we have a code-specific tool\n",
    "# to handle those questions\n",
    "blacklist = [\n",
    "    \"/components/migrating\",\n",
    "    \"/apps\",\n",
    "    \"/migrate-from-v1\"\n",
    "]\n",
    "\n",
    "for p in publicDocs:\n",
    "    if any(str(p).startswith(f\"{root_dir}{prefix}\") for prefix in blacklist):\n",
    "        continue\n",
    "\n",
    "    with open(p) as f:\n",
    "        content = f.read()\n",
    "        html = mistune.markdown(content)\n",
    "\n",
    "        match = re.search(r'<h1>(.+?)<\\/h1>', html)\n",
    "\n",
    "        title = \"Untitled\"\n",
    "        if match:\n",
    "            title = match.group(1)\n",
    "            \n",
    "        splits = markdown_splitter.split_text(content)\n",
    "        relative_path = str(p.relative_to(root_dir).parent)\n",
    "        docs_url = f\"https://pipedream.com/docs/{'' if relative_path == '.' else relative_path}\"\n",
    "        \n",
    "        docs.extend([split.page_content for split in splits])\n",
    "        \n",
    "        new_metadatas = []\n",
    "        for split in splits:\n",
    "            new_metadata = {}\n",
    "            new_metadata['source'] = docs_url\n",
    "            new_metadatas.append(new_metadata)\n",
    "\n",
    "        metadatas.extend(new_metadatas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5af2a07e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pipedream blog\n",
    "\n",
    "i = 1\n",
    "post_urls = []\n",
    "\n",
    "while True:\n",
    "    url = f\"https://pipedream.com/blog/page/{i}\"\n",
    "    try:\n",
    "        response = requests.get(url)\n",
    "    except:\n",
    "        print(f\"Failed to fetch {url}\")\n",
    "        i +=1\n",
    "        continue\n",
    "        \n",
    "    if response.status_code == 200:\n",
    "        try:\n",
    "            soup = BeautifulSoup(response.content, 'html.parser')\n",
    "            for a in soup.find_all('a', class_='post-card-image-link'):\n",
    "                post_urls.append(a['href'])\n",
    "        except:\n",
    "            print(f\"Failed to parse {url}\")\n",
    "        i += 1\n",
    "    else:\n",
    "        break\n",
    "\n",
    "\n",
    "full_urls = [f\"https://pipedream.com{post_url}\" for post_url in post_urls]\n",
    "index_sources(full_urls, fetch_url_and_index_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c19bef68",
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.connector\n",
    "\n",
    "# Set up connection details\n",
    "account = os.environ[\"SNOWFLAKE_ACCOUNT\"]\n",
    "user = os.environ[\"SNOWFLAKE_USER\"]\n",
    "password = os.environ[\"SNOWFLAKE_PASSWORD\"]\n",
    "warehouse = os.environ[\"SNOWFLAKE_WAREHOUSE\"]\n",
    "database = os.environ[\"SNOWFLAKE_DATABASE\"]\n",
    "schema = os.environ[\"SNOWFLAKE_SCHEMA\"]\n",
    "\n",
    "# Create a connection\n",
    "connection = snowflake.connector.connect(\n",
    "    account=account,\n",
    "    user=user,\n",
    "    password=password,\n",
    "    warehouse=warehouse,\n",
    "    database=database,\n",
    "    schema=schema\n",
    ")\n",
    "\n",
    "# Execute a query and fetch the results\n",
    "query = \"\"\"\n",
    "SELECT \n",
    "  name, \n",
    "  description,\n",
    "  name_slug,\n",
    "  oauth_scopes_csv,\n",
    "  auth_type,\n",
    "  custom_fields_json,\n",
    "  test_request_json,\n",
    "  website_url,\n",
    "  custom_fields_description_md,\n",
    "  component_code_scaffold_raw\n",
    "FROM apps\n",
    "WHERE status IN (3, 4);\n",
    "\"\"\"\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(query)\n",
    "results = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35dddade",
   "metadata": {},
   "outputs": [],
   "source": [
    "for item in results:\n",
    "    app, description, name_slug, oauth_scopes_csv, auth_type, custom_fields_json, test_request_json, website_url, custom_fields_description_md, component_code_scaffold_raw = item\n",
    "    test_request = json.loads(test_request_json)\n",
    "    test_request_url = test_request.get('url')\n",
    "    test_request_verb = test_request.get('http_method')\n",
    "    \n",
    "    if custom_fields_description_md is None:\n",
    "        custom_fields_description_md = ''\n",
    "        \n",
    "    auth_text = f\"Within the run method, the user's {app} credentials are exposed in the object `this.{name_slug}.$auth\\`. For integrations where users provide static API keys / tokens, the $auth object contains properties for each key / token the user enters. For OAuth integrations, this object exposes the OAuth access token in the oauth_access_token property of the $auth object. \";\n",
    "    test_request_code = '';\n",
    "    if component_code_scaffold_raw:\n",
    "        test_request_code = f\"Here's an example Pipedream component that makes a test request against the {app} API:\\n\\n{component_code_scaffold_raw}\";\n",
    "    \n",
    "    if (test_request_url and test_request_verb):\n",
    "        auth_text += f\"The test request below makes a {test_request_verb} request to {test_request_url}. You should use the same base URL in other API requests, and based on the documentation provided / other code on the internet. \";\n",
    "    \n",
    "    if auth_type == \"keys\":\n",
    "        custom_fields = json.loads(custom_fields_json);\n",
    "        custom_fields_text = ', '.join([o['name'] for o in custom_fields])\n",
    "        auth_text += f\"{app} is a key-based app. For integrations where users provide static API keys / tokens, `this.{name_slug}.$auth` contains properties for each key / token the user enters. Users are asked to enter the following custom fields: {custom_fields_text}. These are each exposed as properties in the object `this.{name_slug}.$auth`. When you make the API request, use the format from the {app} docs. Different apps pass credentials in different places in the HTTP request, e.g. headers, url params, etc. Consult the docs\";\n",
    "    elif auth_type == \"oauth\":\n",
    "        auth_text += f\"{app} is an OAuth app. For OAuth integrations, this object exposes the OAuth access token in the variable `this.{name_slug}.$auth.oauth_access_token`. When you make the API request, make sure to use the format from the {app} docs, e.g. you may need to pass the OAuth access token as a Bearer token in the Authorization header. Consult the docs\";\n",
    "    \n",
    "    formatted_oauth_scopes = ''\n",
    "    if oauth_scopes_csv:\n",
    "        formatted_oauth_scopes = '\\n'.join(oauth_scopes_csv.split(','))\n",
    "        \n",
    "    markdown_content = f\"\"\"# {app}\n",
    "\n",
    "{description}\n",
    "\n",
    "{app}'s website is {website_url}\n",
    "\n",
    "## What are the OAuth scopes for {app}?\n",
    "\n",
    "These are the scopes Pipedream has configured for {app} by default, which Pipedream sends in the OAuth authorization request.\n",
    "\n",
    "{formatted_oauth_scopes}\n",
    "\n",
    "## How do I use {app} on Pipedream?\n",
    "\n",
    "{custom_fields_description_md}\n",
    "\n",
    "{auth_text}\n",
    "\n",
    "## How do I make a test request to the {app} API?\n",
    "\n",
    "{test_request_code}\n",
    "\"\"\"\n",
    "    \n",
    "    splits = markdown_splitter.split_text(markdown_content)\n",
    "    docs_url = f\"https://pipedream.com/apps/{name_slug.replace('_', '-')}\"\n",
    "\n",
    "    docs.extend([split.page_content for split in splits])\n",
    "\n",
    "    new_metadatas = []\n",
    "    for i, split in enumerate(splits):\n",
    "        new_metadata = {}\n",
    "        new_metadata['source'] = docs_url\n",
    "        new_metadata['app_slug'] = name_slug\n",
    "        new_metadatas.append(new_metadata)\n",
    "\n",
    "    metadatas.extend(new_metadatas)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6df0cc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "SELECT\n",
    "  c.type,\n",
    "  c.key,\n",
    "  c.name,\n",
    "  c.description,\n",
    "  c.version,\n",
    "  sc.configurable_props_json,\n",
    "  a.name AS app_name,\n",
    "  a.name_slug AS app_name_slug\n",
    "FROM components c\n",
    "JOIN saved_components sc\n",
    "ON c.hid = sc.hid\n",
    "LEFT OUTER JOIN app_saved_components apps_sc\n",
    "ON sc.id = apps_sc.saved_component_id\n",
    "LEFT OUTER JOIN apps a\n",
    "ON apps_sc.app_id = a.id\n",
    "WHERE c.LATEST_VERSION_AND_PUBLISHED_TO_ALL_USERS = TRUE\n",
    "\"\"\"\n",
    "\n",
    "cursor = connection.cursor()\n",
    "cursor.execute(query)\n",
    "registry_components = cursor.fetchall()\n",
    "\n",
    "# Close the connection\n",
    "cursor.close()\n",
    "connection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd363c3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def map_component_type_to_human_readable(prop_type):\n",
    "    human_readable_types = {\n",
    "        \"boolean\": \"a boolean\",\n",
    "        \"boolean[]\": \"an array of booleans\",\n",
    "        \"string\": \"a string\",\n",
    "        \"string[]\": \"an array of strings\",\n",
    "        \"integer\": \"an integer\",\n",
    "        \"integer[]\": \"an array of integers\",\n",
    "        \"object\": \"an object\",\n",
    "    }\n",
    "\n",
    "    return human_readable_types.get(prop_type, prop_type)\n",
    "\n",
    "for component in registry_components:\n",
    "    component_type, key, name, description, version, configurable_props_json, app_name, app_name_slug = component\n",
    "    if key is None or app_name_slug is None:\n",
    "        continue\n",
    "\n",
    "    markdown = f\"# {app_name} — {name}\\n\\n\"\n",
    "    if component_type == \"Source\":\n",
    "        markdown += f\"## How do I use the {name} trigger?\\n\\n\"\n",
    "        markdown += dedent(f\"\"\"1. Search for the {app_name} app from the trigger menu\n",
    "2. Select the {name} trigger\n",
    "3. Connect your {app_name} account\\n\"\"\")\n",
    "        \n",
    "    if component_type == \"Component Action\":\n",
    "        markdown += f\"## How do I use the {name} action?\\n\\n\"\n",
    "        markdown += dedent(f\"\"\"1. Search for the {app_name} app from the step menu\n",
    "2. Select the {name} action\n",
    "3. Connect your {app_name} account\\n\"\"\")\n",
    "\n",
    "    configurable_props = json.loads(configurable_props_json)\n",
    "    # Start numbering from above\n",
    "    i = 4\n",
    "    for prop in configurable_props:\n",
    "        if not 'label' in prop or not 'type' in prop:\n",
    "            continue\n",
    "            \n",
    "        # We don't need to include optional props in the default instructions\n",
    "        if prop.get(\"optional\", False):\n",
    "            continue\n",
    "            \n",
    "        prop_type = prop.get(\"type\")\n",
    "\n",
    "        if prop_type in [\n",
    "            \"boolean\",\n",
    "            \"boolean[]\",\n",
    "            \"string\",\n",
    "            \"string[]\",\n",
    "            \"integer\",\n",
    "            \"integer[]\",\n",
    "            \"object\",\n",
    "            \"any\",\n",
    "        ]:\n",
    "            \n",
    "            selector = \"one or more\" if prop_type.endswith(\"[]\") else \"a\"\n",
    "            human_readable_type = map_component_type_to_human_readable(prop_type) or \"a step export\"\n",
    "            markdown += f\"{i}. Select {selector} {prop['label']} or pass {human_readable_type} from a previous step.\\n\"\n",
    "        \n",
    "        i += 1\n",
    "                \n",
    "    if component_type == \"Source\":\n",
    "        markdown += f\"{i}. Follow the instructions on the trigger configuration screen. You may need to generate a test event from {app_name} to get real data to test your workflow.\"\n",
    "        \n",
    "    if component_type == \"Component Action\":\n",
    "        markdown += f\"{i}. At the bottom of the step, click Test to run the action and test the configuration.\"\n",
    "                \n",
    "    docs_url = ''\n",
    "    parts = key.split('-', 1)\n",
    "\n",
    "    # Get the second part (the component key)\n",
    "    url_key = parts[1].replace('_', '-')\n",
    "    \n",
    "    if component_type == \"Source\":\n",
    "        docs_url = f\"https://pipedream.com/apps/{app_name_slug.replace('_', '-')}/triggers/{url_key}\\n\\n\"\n",
    "    elif component_type == \"Component Action\":\n",
    "        docs_url = f\"https://pipedream.com/apps/{app_name_slug.replace('_', '-')}/actions/{url_key}\\n\\n\"\n",
    "                \n",
    "    splits = markdown_splitter.split_text(markdown)\n",
    "    docs.extend([split.page_content for split in splits])\n",
    "\n",
    "    new_metadatas = []\n",
    "    for i, split in enumerate(splits):\n",
    "        new_metadata = {}\n",
    "        new_metadata['source'] = docs_url\n",
    "        new_metadata['app_slug'] = app_name_slug\n",
    "        new_metadatas.append(new_metadata)\n",
    "\n",
    "    metadatas.extend(new_metadatas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a0e5061",
   "metadata": {},
   "outputs": [],
   "source": [
    "embeddings = OpenAIEmbeddings()\n",
    "embedded_docs = embeddings.embed_documents(docs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e66de3b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# OPTIONAL: Load custom embeddings matrix, get the dot product of that * embeddings\n",
    "# See https://github.com/openai/openai-cookbook/blob/main/examples/Customizing_embeddings.ipynb\n",
    "# Save that as custom_embeddings_matrix.npy and load it here.\n",
    "custom_embeddings_matrix = np.load(\"custom_embeddings_matrix.npy\")\n",
    "modified_embeddings = np.dot(embedded_docs, custom_embeddings_matrix)\n",
    "modified_embeddings_list = modified_embeddings.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7ce4ac2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Submit to Postgres\n",
    "batch_size = 50\n",
    "for i in range(0, len(docs), batch_size):\n",
    "    print(f\"Inserting batch {i}\")\n",
    "    i_end = min(i + batch_size, len(docs))\n",
    "    docs_batch = docs[i:i_end]\n",
    "    # Key on metadata source\n",
    "    urls = [metadata.get(\"source\") for metadata in metadatas[i:i_end]]\n",
    "    app_slug_batch = [metadata.get(\"app_slug\") for metadata in metadatas[i:i_end]]\n",
    "    embeddings_batch = modified_embeddings_list[i:i_end]\n",
    "    to_insert = [{\"url\": url, \"body\": body, \"embedding\": embedding, \"app_slug\": app_slug} for url, body, embedding, app_slug in zip(urls, docs_batch, embeddings_batch, app_slug_batch)]\n",
    "    supabase.table('docs_stg').insert(to_insert).execute()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51ced01f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}