Fixing a Supabase Builder Intake Pipeline: Deno, Jekyll Cache, Duplicate Submit Pages, and SQLAlchemy Params

This fix note documents a debugging session from the DataInsideData™ Builder Showcase / ProofMe build.

The system being tested was:

Jekyll submit form
    ↓
Supabase Edge Function
    ↓
Postgres insert
    ↓
builder_project_submissions
    ↓
review / promote / publish workflow

The pipeline was already working, but during branch switching, cleanup, and testing, several issues appeared:

  • VS Code could not resolve Deno.
  • The submit form showed an old test-mode success message.
  • A stale submit page competed with the newer canonical form.
  • Supabase panel testing returned 401.
  • SQLAlchemy scripts failed when using the wrong parameter style.
  • Python cache folders and Supabase temp files needed cleanup.
  • Generated Builder Showcase pages needed better README rendering support.

1. VS Code Could Not Resolve the Deno Executable

Symptom

Could not resolve Deno executable. Please ensure it is available on the PATH used by VS Code or set an explicit "deno.path" setting.

The Deno extension log also showed:

spawn C:\Users\oneps\.deno\bin\deno.exe ENOENT

Assessment

ENOENT means VS Code tried to launch a file path that did not exist. Deno worked in the terminal, but VS Code was pointed to the wrong executable path.

Fix

Find the real Deno path:

where.exe deno

or:

(Get-Command deno).Source

Put the machine-specific path in VS Code User Settings JSON, not the repo settings:

{
  "deno.path": "C:/Users/oneps/AppData/Local/Microsoft/WinGet/Packages/DenoLand.Deno_Microsoft.Winget.Source_.../deno.exe"
}

Keep only shared project Deno settings in repo .vscode/settings.json:

{
  "deno.enable": true,
  "deno.enablePaths": [
    "supabase/functions"
  ]
}

Prevention

Use this split:

User Settings
    → deno.path
    → machine-specific

Repo .vscode/settings.json
    → deno.enable
    → deno.enablePaths
    → project-specific and safe to commit

2. VS Code Crashed with an Out-of-Memory Error

Symptom

The window terminated unexpectedly (reason: 'oom', code: '-536870904')

Assessment

This was an editor/runtime memory issue, not an application bug.

Possible causes:

  • Too many editors restored at once.
  • Deno language server repeatedly resolving dependencies.
  • Large workspace with generated folders.
  • Jekyll _site or .jekyll-cache being watched.

Fix

When reopening VS Code, choose:

Don't restore editors

Then reopen only the files needed.

Make sure generated folders are ignored:

_site/
.jekyll-cache/
.sass-cache/
__pycache__/
*.py[cod]
supabase/.temp/

3. Running deno check index.ts Created deno.lock

Symptom

After running:

cd supabase/functions/submit-builder-project
deno check index.ts

a deno.lock file appeared.

Assessment

This is normal. Deno resolved the function imports and wrote a lockfile.

Fix

Keep deno.lock if it is inside your Supabase function/project area and does not contain secrets.

git add supabase/functions/submit-builder-project/deno.lock

4. Supabase Web Panel Returned 401

Symptom

Testing the Edge Function from Supabase returned:

401 Unauthorized

Assessment

The Edge Function intentionally checks for an apikey header:

const expectedPublicKey = Deno.env.get("DID_PUBLIC_FORM_KEY");
const requestApiKey = req.headers.get("apikey");

If the dashboard test request does not include the expected apikey, 401 is expected.

Fix

Include the required test headers:

{
  "Content-Type": "application/json",
  "apikey": "sb_publishable_YOUR_PUBLIC_KEY"
}

Confirm function config:

[functions.submit-builder-project]
verify_jwt = false

Redeploy if needed:

npx supabase functions deploy submit-builder-project

5. Submit Form Displayed Old Test-Mode Message

Symptom

The page showed:

Test payload created successfully. Check the browser console.
Database insert will be enabled after the Supabase Edge Function is connected.

Assessment

The old message still existed in a different source page.

Search found:

projects\submit-a-builder-project\index.md
_site\projects\submit-a-builder-project\index.html
.jekyll-cache\...

The real source problem was:

projects/submit-a-builder-project/index.md

There were two competing submit pages:

Old page:
projects/submit-a-builder-project/index.md

New canonical page:
projects/builder-showcase/submit.md

Fix

Remove, archive, or redirect the old test page. Then clean and rebuild:

bundle exec jekyll clean
bundle exec jekyll serve --livereload --trace

Hard refresh the browser:

Ctrl + Shift + R

Prevention

Search for old prototype text:

Get-ChildItem -Recurse -File | Select-String "Database insert will be enabled"

Search for duplicate submit routes:

Get-ChildItem -Recurse -File | Select-String "submit-a-builder-project"

6. Jekyll Cache and _site Kept Showing Old Output

Symptom

Old form behavior appeared even after editing the newer submit page.

Assessment

Jekyll generated output and cache can preserve older rendered files. _site is generated output and should not be edited directly.

Fix

bundle exec jekyll clean
bundle exec jekyll serve --livereload --trace

Then hard refresh the browser.

7. Branch Switching Brought Back Older Files

Symptom

After switching branches, the form appeared to revert to earlier behavior.

Assessment

The older branch still had prototype form files. When switching branches, the workspace ended up with an older test page and the newer canonical form.

Fix

Check branch and status:

git branch --show-current
git status

Search for old form text:

Get-ChildItem -Recurse -File | Select-String "Test payload created successfully"

Remove, archive, or redirect old pages.

8. SQLAlchemy Failed on :status Parameters

Symptom

psycopg2.errors.SyntaxError: syntax error at or near ":"
LINE 16: where (:status is null or status = :status)

Assessment

read_supabase_df() passed the query to Pandas without wrapping it in SQLAlchemy text(), so psycopg2 saw raw :status.

Fix

In src/builder_showcase/db.py:

from sqlalchemy import text

def read_supabase_df(query: str, params: dict | None = None) -> pd.DataFrame:
    engine = get_supabase_engine()

    try:
        return pd.read_sql_query(
            sql=text(query),
            con=engine,
            params=params or {},
        )

    finally:
        engine.dispose()

Prevention

Use SQLAlchemy-style bind params with SQLAlchemy helpers:

where status = :status
limit :limit

Use psycopg2-style params only with raw psycopg2 cursor execution:

where status = %(status)s

9. Promote Script Used psycopg2 Param Style with SQLAlchemy Helper

Symptom

psycopg2.errors.SyntaxError: syntax error at or near "%"
LINE 14: where id = %(submission_id)s;

Assessment

The script used psycopg2-style params but called a SQLAlchemy helper.

Fix

Use SQLAlchemy bind style:

where id = :submission_id

Move reusable promotion logic into:

src/builder_showcase/workflow.py

Keep the terminal wrapper thin:

scripts/promote_builder_project.py

10. Generated Builder Showcase Pages Did Not Render README

Symptom

Generated project page loaded, but README stayed stuck at:

Loading project documentation...

Assessment

The generated include was missing fields expected by github-project.html:

github_repo
repo_base
repo_blob_base

The form collected only:

repo_url
readme_url

but the renderer needed derived GitHub fields.

Fix

Add a helper:

parse_github_sources(repo_url, readme_url)

Derive:

repo_owner
repo_name
repo_branch
repo_path
repo_base
repo_blob_base
normalized_readme_url

Then generate the include with those fields:

<div class="project-shell" id="project-shell-main">
  <div class="project-meta-card">
    <h2>Project Overview</h2>
    <p></p>

    <ul class="project-meta-list">
     

    
    </ul>

  </div>

  <div id="readme-container-main">
    Loading project documentation...
  </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/marked/marked.min.js"></script>

<script>
(function() {
  const id = "main";
  const readmeContainer = document.getElementById(`readme-container-${id}`);
  const statsContainer = document.getElementById(`repo-stats-${id}`);

  const githubUser = "DataEden";
  const githubRepo = "fari-tech-portfolio";
  const readmeUrl = "https://raw.githubusercontent.com/.../README.md";
  const langOverride = "";

  const repoRawBase = "https://raw.githubusercontent.com/...";
  const repoBlobBase = "https://github.com/...";
  const nbviewerBase = "";

  function isRelativePath(path) {
    return path &&
      !path.startsWith("http://") &&
      !path.startsWith("https://") &&
      !path.startsWith("mailto:") &&
      !path.startsWith("#") &&
      !path.startsWith("/");
  }

  function normalizePath(base, path) {
    if (!base || !path) return path;
    return `${base.replace(/\/$/, "")}/${path.replace(/^\.\//, "").replace(/^\//, "")}`;
  }

  function rewriteLinks(container) {
    // Rewrite image paths to raw GitHub
    container.querySelectorAll("img").forEach((img) => {
      const src = img.getAttribute("src");
      if (isRelativePath(src)) {
        img.setAttribute("src", normalizePath(repoRawBase, src));
      }
    });

    // Rewrite README links
    container.querySelectorAll("a").forEach((a) => {
      const href = a.getAttribute("href");
      if (!isRelativePath(href)) return;

      const cleanedHref = href.replace(/^\.\//, "");

      // Jupyter notebooks → NBViewer
      if (cleanedHref.endsWith(".ipynb")) {
        if (nbviewerBase) {
          a.setAttribute("href", normalizePath(nbviewerBase, cleanedHref));
        } else if (repoBlobBase) {
          a.setAttribute("href", normalizePath(repoBlobBase, cleanedHref));
        }
        a.setAttribute("target", "_blank");
        a.setAttribute("rel", "noopener");
        return;
      }

      // Docs/text files → GitHub blob
      if (
        cleanedHref.endsWith(".md") ||
        cleanedHref.endsWith(".txt") ||
        cleanedHref.endsWith(".pdf")
      ) {
        if (repoBlobBase) {
          a.setAttribute("href", normalizePath(repoBlobBase, cleanedHref));
        } else {
          a.setAttribute("href", normalizePath(repoRawBase, cleanedHref));
        }
        a.setAttribute("target", "_blank");
        a.setAttribute("rel", "noopener");
        return;
      }

      // Default relative links
      const base = repoBlobBase || repoRawBase;
      a.setAttribute("href", normalizePath(base, cleanedHref));
      a.setAttribute("target", "_blank");
      a.setAttribute("rel", "noopener");
    });
  }

  if (!githubUser || !githubRepo) {
    statsContainer.innerHTML = "GitHub repository metadata not configured.";
  } else {
    fetch(`https://api.github.com/repos/${githubUser}/${githubRepo}`)
      .then(response => {
        if (!response.ok) {
          throw new Error("Failed to load GitHub repo metadata.");
        }
        return response.json();
      })
      .then(data => {
        const pushedAt = data.pushed_at
          ? new Date(data.pushed_at).toLocaleDateString()
          : "Unknown";

        const language = langOverride || data.language || "Not specified";

        statsContainer.innerHTML = `
          <ul class="project-meta-list">
            <li><strong>Primary Language:</strong> ${language}</li>
            <li><strong>Last Updated:</strong> ${pushedAt}</li>
          </ul>
        `;
      })
      .catch(error => {
        statsContainer.innerHTML = "Unable to load GitHub metadata.";
        console.error("GitHub metadata error:", error);
      });
  }

  if (!readmeUrl) {
    readmeContainer.innerHTML = "README URL not provided.";
    return;
  }

  fetch(readmeUrl)
    .then(response => {
      if (!response.ok) {
        throw new Error("Failed to load README.");
      }
      return response.text();
    })
    .then(markdown => {
      const html = marked.parse(markdown);
      readmeContainer.innerHTML = html;
      rewriteLinks(readmeContainer);
    })
    .catch(error => {
      readmeContainer.innerHTML = "Error loading project documentation.";
      console.error("README load error:", error);
    });
})();
</script>

Prevention

Keep the form simple for users. Let the system derive technical rendering fields.

11. Generated index.md Front Matter Was Indented

Symptom

Generated detail pages did not behave correctly in Jekyll.

Assessment

Jekyll front matter must start at the first character of the file:

---
title: "..."
---

not:

    ---
    title: "..."
    ---

Fix

Use textwrap.dedent():

from textwrap import dedent

page_content = dedent(f'''\
---
title: "{title}"
permalink: /projects/builder-showcase/{slug}/
layout: single
classes: wide
author_profile: false
---
''').strip() + "\n"

12. __pycache__ Appeared in src and scripts

Symptom

Python generated cache folders:

src/builder_showcase/__pycache__/
scripts/__pycache__/

Fix

Add to .gitignore:

__pycache__/
*.py[cod]
*$py.class
.ipynb_checkpoints/

If already tracked:

git rm -r --cached **/__pycache__

PowerShell option:

Get-ChildItem -Recurse -Directory -Name __pycache__ | ForEach-Object {
    git rm -r --cached $_
}

13. Supabase .temp Was Tracked by Git

Symptom

Supabase CLI generated:

supabase/.temp/

and Git was tracking it.

Fix

git rm -r --cached supabase/.temp/

Add to .gitignore:

supabase/.temp/
supabase/.env
supabase/.env.*

Then commit:

git add .gitignore
git commit -m "Stop tracking Supabase temp files"

14. .vscode Needed a Clean Commit Strategy

Assessment

Some .vscode settings are project-level and useful. Others are local machine-specific.

Commit shared project settings only:

{
  "deno.enable": true,
  "deno.enablePaths": [
    "supabase/functions"
  ]
}

Do not commit machine paths:

"deno.path": "C:/Users/oneps/..."

Suggested .gitignore

.vscode/*
!.vscode/settings.json
!.vscode/extensions.json

15. Form Checkbox Acknowledgements Needed Validation Planning

Assessment

Unchecked HTML checkboxes may not appear in FormData, so frontend code may send them as false.

Fix Plan

Validate in two places:

submit.md
    → user-friendly browser validation

Edge Function index.ts
    → source-of-truth backend validation

Frontend:

<input type="checkbox" name="owns_or_has_permission" required>
<input type="checkbox" name="wants_public_showcase" required>

Backend:

if (payload.owns_or_has_permission !== true) {
  errors.push("You must confirm ownership or permission.");
}

if (payload.wants_public_showcase !== true) {
  errors.push("You must acknowledge public review.");
}

16. Input Normalization Needed Cleanup

first_name / last_name
    → trim spaces
    → preserve user casing

submitter_email
    → trim
    → lowercase

github_username
    → trim
    → optionally lowercase for matching

repo_url / readme_url / live_url
    → trim

project_title
    → trim
    → preserve casing

project_category
    → use controlled dropdown value

project_tags
    → split by comma
    → trim each tag
    → remove blanks

Final Phase 2 Cleanup Checklist

[ ] Remove or redirect old submit page.
[ ] Keep one canonical submit URL.
[ ] Search for old test-mode messages.
[ ] Clean Jekyll cache and rebuild.
[ ] Confirm submit form reaches Edge Function.
[ ] Confirm row lands in builder_project_submissions.
[ ] Confirm checkbox validation behavior.
[ ] Confirm text input normalization behavior.
[ ] Confirm Deno path works in VS Code.
[ ] Confirm verify_jwt = false in config.toml.
[ ] Confirm Edge Function rejects missing apikey.
[ ] Confirm Supabase panel test includes apikey header.
[ ] Confirm Python scripts use SQLAlchemy :param style.
[ ] Confirm __pycache__ and supabase/.temp are ignored.
[ ] Confirm .vscode only contains safe shared settings.
[ ] Confirm promote/export/view scripts still work.

Final Lesson

This debugging session reinforced a systems lesson:

When a full-stack workflow breaks, the issue may not be where the symptom appears.

In this case:

Old form message
    looked like an Edge Function problem
    but was actually a duplicate stale Jekyll page.

Deno linting
    looked scary
    but was mostly VS Code path/config.

SQL errors
    looked like Postgres issues
    but were parameter-style mismatches.

README not rendering
    looked like a frontend issue
    but was missing derived GitHub metadata.

The real fix was a cleanup pass across:

frontend source files
Jekyll generated output
Supabase Edge Function config
Deno editor tooling
Python database helpers
Git hygiene
publishing scripts

That is exactly what Phase 2 cleanup is for.