Building a Secure Submission Pipeline with Jekyll, Supabase Edge Function, and Postgres Part 2

4 minute read

This section is Part 2 of the secure submission pipeline breakdown.

Testing Direct Inserts from Notebook

Before wiring the Edge Function, a manual Python payload was inserted through inspect browser tool and psycopg2.

payload = {
    "first_name": "Fari",
    "last_name": "Lindo",
    "submitter_email": "test@example.com",
    "github_username": "dataeden",
    "repo_url": "https://github.com/DataEden/fari-tech-portfolio",
    "project_title": "AI Review Sentiment Labeling Pipeline",
    "project_category": "AI & Decision Systems",
    "project_description": "Testing direct psycopg2 insert workflow before Edge Function integration.",
    "project_tags": ["Python", "OpenAI", "Supabase"],
    "live_url": "",
    "readme_url": "",
    "owns_or_has_permission": True,
    "wants_public_showcase": True,
}

The successful insertion confirmed the database table accepted the intended payload shape.

I was now sure that the form submission was ready for testing.

Setting up Reading Data with SQLAlchemy

A Pandas warning appeared when using a raw psycopg2 connection with pd.read_sql_query().

The cleaner approach used SQLAlchemy:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

def get_supabase_engine():
    database_url = URL.create(
        drivername="postgresql+psycopg2",
        username=os.getenv("SUPABASE_DB_USER"),
        password=os.getenv("SUPABASE_DB_PASSWORD"),
        host=os.getenv("SUPABASE_DB_HOST"),
        port=int(os.getenv("SUPABASE_DB_PORT", "5432")),
        database=os.getenv("SUPABASE_DB_NAME", "postgres"),
        query={"sslmode": os.getenv("SUPABASE_DB_SSLMODE", "require")},
    )

    engine = create_engine(
        database_url,
        pool_pre_ping=True,
        pool_size=1,
        max_overflow=0,
    )

    return engine

Reusable read helper:

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

    try:
        df = pd.read_sql_query(query, engine, params=params)
        return df

    finally:
        engine.dispose()

Running Supabase CLI from a Notebook

The local terminal did not recognize.

supabase --help

because the CLI was not globally installed.

Instead, npx worked:

npx supabase --help

From working notebook:

import subprocess
from pathlib import Path

PROJECT_ROOT = Path.cwd().parent if Path.cwd().name == "notebooks" else Path.cwd()

result = subprocess.run(
    "npx supabase --help",
    cwd=PROJECT_ROOT,
    shell=True,
    capture_output=True,
    text=True
)

print(result.stdout)
print(result.stderr)

This confirmed the Supabase CLI was available through npx.

Supabase Personal Access Token

The project API key was not the same as the CLI access token.

Wrong token type:

sb_publishable_...

Correct CLI token type:

sbp_...

The personal access token came from:

Supabase Dashboard
    → Account / User Settings
    → Access Tokens

Then, inside the notebook:

import os
from getpass import getpass

token = getpass("Paste Supabase personal access token, starts with sbp_: ")
os.environ["SUPABASE_ACCESS_TOKEN"] = token

Then:

subprocess.run(
    "npx supabase projects list",
    cwd=PROJECT_ROOT,
    shell=True,
    check=True
)

Finding the Supabase Project Ref

The project ref can be found in:

https://supabase.com/dashboard/project/THE_PROJECT_REF

or from the project URL:

https://THE_PROJECT_REF.supabase.co

In this build, the function URL followed this pattern:

https://PROJECT_REF.supabase.co/functions/v1/submit-builder-project

Initializing and Linking Supabase Locally

Commands:

npx supabase init
npx supabase link --project-ref PROJECT_REF

Then the function was created:

npx supabase functions new submit-builder-project

This generated:

supabase/
└── functions/
    └── submit-builder-project/
        └── index.ts

The CLI also generated Deno-related VS Code settings.

Function Configuration

In:

supabase/config.toml

The function was configured:

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

Why?

The frontend is using the newer sb_publishable_... key in the apikey header, not a Supabase Auth JWT.

With this setup, the platform JWT check is skipped, and the Edge Function itself performs validation and key-header checking.

Important:

verify_jwt = false means the function code becomes the gatekeeper.

That is why the function includes:

POST-only check
API key header check
CORS allowlist
required-field validation
max-length checks
GitHub URL validation
honeypot field
server-side database insert only

Deno Config Clarification

The generated deno.json looked like:

{
  "imports": {
    "@supabase/functions-js": "jsr:@supabase/functions-js@^2"
  }
}

This file is for Deno imports/tooling.

Quick note: Credentials do not go in deno.json.

Deno.env.get(...) reads Edge Function environment variables/secrets, not deno.json.

Example from index.ts:

const expectedPublicKey = Deno.env.get("DID_PUBLIC_FORM_KEY");
const writeKey = Deno.env.get("DID_SUPABASE_WRITE_KEY");

Setting Supabase Function Secrets

The function expects:

DID_PUBLIC_FORM_KEY
DID_SUPABASE_WRITE_KEY

The public form key matches the browser-facing publishable key:

npx supabase secrets set DID_PUBLIC_FORM_KEY="sb_publishable_PUBLIC_KEY"

The backend write key is a server-side secret/service key:

npx supabase secrets set DID_SUPABASE_WRITE_KEY="SECRET_OR_SERVICE_ROLE_KEY"

Be sure to not put the backend write key in:

submit.md
Jekyll config
_data files
public repository
browser JavaScript

As this may lead to undesirable or catastrophic security outcomes.

Frontend Submit Form

The Jekyll page was:

projects/builder-showcase/submit.md

Some of the collected form fields:

first name
last name
email
GitHub username
repo URL
project title
project category
honeypot field

Example frontend script builds a payload:

const payload = {
  first_name: formData.get("first_name"),
  last_name: formData.get("last_name"),
  submitter_email: formData.get("submitter_email"),
  github_username: formData.get("github_username"),
  repo_url: formData.get("repo_url"),
  project_title: formData.get("project_title"),
  project_category: formData.get("project_category"),
  project_description: formData.get("project_description"),
  project_tags: String(formData.get("project_tags") || "")
    .split(",")
    .map(tag => tag.trim())
    .filter(Boolean),
  live_url: formData.get("live_url"),
  readme_url: formData.get("readme_url"),
  owns_or_has_permission: formData.get("owns_or_has_permission") === "on",
  wants_public_showcase: formData.get("wants_public_showcase") === "on",
  website: formData.get("website") || ""
};

Frontend Fetch Request

The first working test used hardcoded values in submit.md:

const FUNCTION_URL =
  "https://PROJECT_REF.supabase.co/functions/v1/submit-builder-project";

const SUPABASE_PUBLISHABLE_KEY =
  "sb_publishable_PUBLIC_KEY_HERE";

The fetch call:

const response = await fetch(FUNCTION_URL, {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    "apikey": SUPABASE_PUBLISHABLE_KEY
  },
  body: JSON.stringify(payload)
});

This confirmed:

submit.md
    ↓
Supabase Edge Function
    ↓
Postgres insert

Later, the project ref and publishable key will be moved into a Jekyll data/config include to reduce hardcoding.

Minimal Edge Function

The first function version was intentionally insert-only.

It did not fetch GitHub README content yet.

Responsibilities:

POST only
CORS headers
apikey header check
payload validation
tag normalization
honeypot check
insert into builder_project_submissions
return inserted id/status

Key environment variables:

const expectedPublicKey = Deno.env.get("DID_PUBLIC_FORM_KEY");
const writeKey = Deno.env.get("DID_SUPABASE_WRITE_KEY");

The database insert:

const { data, error } = await supabaseAdmin
  .from("builder_project_submissions")
  .insert(submissionRow)
  .select("id, status, readme_fetch_status, created_at")
  .single();

Check out Part 3 here.

Data Inside Data™.

Tech Hands, a Science Mind, and a Heart for Community.