feat: Account-free outlet submissions using a GitHub App

[jlucus]

feat: Account-free outlet submissions using a GitHub App

Summary

Allow visitors to submit public charging-outlet locations through the form on plug.vln.gg without requiring a GitHub account.

Use a stateless email-verification service and a narrowly permissioned GitHub App to convert verified submissions into GitHub Issues. GitHub Actions will validate accepted submissions, insert them into our existing SQLite database, regenerate the public map data, open a pull request, and automatically merge it after all required checks pass.

Problem

PLUG is hosted on GitHub Pages, which only serves static HTML, CSS, and JavaScript. It cannot securely:

• Store GitHub credentials.
• Sign GitHub App requests.
• Send verification emails.
• Write directly to the repository.
• Insert records into SQLite.

Requiring contributors to create a GitHub account would also create unnecessary friction for the people PLUG is intended to serve.

Proposed Architecture

flowchart TD
    FORM["User submits outlet form"]
    RELAY["Stateless verification function"]
    TOKEN["Create encrypted submission token"]
    EMAIL["Send verification email"]
    CONFIRM["User confirms submission"]
    VERIFY{"Token valid?"}
    APP["PLUG GitHub App"]
    ISSUE["Create submission issue"]
    ACTION["Process submission Action"]
    VALIDATE{"Submission valid?"}
    DATABASE["Insert record into data/locations.db"]
    EXPORT["Regenerate public/data/locations.json"]
    PR["Create pull request"]
    CHECKS{"Required checks pass?"}
    MERGE["Automatically merge"]
    DEPLOY["Deploy GitHub Pages"]
    REJECT["Reject or request corrections"]

    FORM --> RELAY
    RELAY --> TOKEN
    TOKEN --> EMAIL
    EMAIL --> CONFIRM
    CONFIRM --> VERIFY

    VERIFY -->|No| REJECT
    VERIFY -->|Yes| APP
    APP --> ISSUE
    ISSUE --> ACTION
    ACTION --> VALIDATE

    VALIDATE -->|No| REJECT
    VALIDATE -->|Yes| DATABASE
    DATABASE --> EXPORT
    EXPORT --> PR
    PR --> CHECKS

    CHECKS -->|No| REJECT
    CHECKS -->|Yes| MERGE
    MERGE --> DEPLOY

Loading

Submission Flow

1. A visitor completes the outlet-submission form on plug.vln.gg.
2. The browser performs basic field validation.
3. The normalized submission is sent to a stateless edge function.
4. The function applies rate limits.
5. The function encrypts and signs a short-lived verification token.
6. A verification link is emailed to the submitter.
7. The link opens a confirmation page on plug.vln.gg.
8. The visitor explicitly confirms the submission with a POST request.
9. The function verifies the signature, expiration and submission identifier.
10. The PLUG GitHub App creates a labeled submission issue.
11. GitHub Actions validates and sanitizes the issue.
12. A parameterized SQLite statement inserts the new location into the existing venues table in data/locations.db.
13. The Action regenerates public/data/locations.json.
14. The Action opens a pull request containing the proposed change.
15. Required integrity, schema, duplicate and security checks run.
16. The pull request is automatically merged after all checks pass.
17. GitHub Pages deploys the updated map via the existing deploy.yml workflow.
18. The original submission issue is closed with the result.

GitHub App Permissions

Install the GitHub App only on Fused-Gaming/plug.

Permission	Access	Purpose
Metadata	Read	Basic repository identification
Issues	Read and write	Create and update submission issues
Contents	None	Prevent direct repository modification
Pull requests	None	GitHub Actions creates pull requests
Actions	None	Prevent workflow modification or execution
Administration	None	Prevent repository configuration changes

The GitHub App must not receive permission to modify source code, workflows, SQLite files, branches or pull requests.

Verification Token

The encrypted token may contain:

{
  "submissionId": "sub_01K...",
  "emailHash": "sha256...",
  "outlet": {
    "name": "Oakland Main Library",
    "description": "Outlets available near the public computer area.",
    "address": "125 14th Street, Oakland, CA",
    "latitude": 37.8002,
    "longitude": -122.2631,
    "locationType": "library",
    "accessHours": "Monday–Saturday, 10:00 AM–5:30 PM",
    "accessible": true,
    "indoor": true,
    "requiresPurchase": false
  },
  "issuedAt": 1785110000,
  "expiresAt": 1785111800
}

Requirements:

• Encrypt and authenticate the token.
• Expire tokens after 15–30 minutes.
• Do not expose the submitter's email address in the URL.
• Do not store the email address in GitHub.
• Require a confirmation POST after opening the link.
• Generate a deterministic or unique submission identifier.
• Reject expired, modified or malformed tokens.

SQLite Schema

Correction: our canonical database is data/locations.db, and it already has a venues table (schema defined in scripts/etl/db.js, not a standalone .sql file) that this feature's rows belong in — the same table scripts/etl/sync-locations.mjs and scripts/etl/ingest-submissions.mjs already read and write. This feature needs three new tracking columns on that existing table, not a separate outlets table:

-- New submission-tracking columns on the existing `venues` table in data/locations.db
ALTER TABLE venues ADD COLUMN source_issue INTEGER UNIQUE;
ALTER TABLE venues ADD COLUMN submission_id TEXT UNIQUE;
ALTER TABLE venues ADD COLUMN submitted_by_hash TEXT;

The rest of this feature's fields already map onto existing venues columns:

This feature's field	Existing venues column
name	name
description	notes
address	address
latitude, longitude	lat, lon
locationType	category
accessHours	hours
indoor	indoor
accessible	appended to amenities (e.g. "Accessible", matching how public/scripts/landing.js already reads this flag)
requiresPurchase	reflected in access ("Open to everyone" vs "Customers only", matching .github/ISSUE_TEMPLATE/location.yml)
verification/review state	tier (e.g. "community") and verification_source, both already present
submission timestamp	first_seen, already present

All inserts must use parameterized statements. Never interpolate issue content into SQL or shell commands.

Repository Changes

.github/
└── workflows/
    ├── process-location-submissions.yml
    └── validate-location-data.yml
data/
└── change-summary.json          # new: human-readable PR summary
public/
└── data/
    └── locations.json            # existing file — regenerated by scripts/etl/sync-locations.mjs, same as today
scripts/
├── parse-submission-issue.mjs
├── validate-submission.mjs
├── insert-venue.mjs
└── export-locations.mjs
src/
├── components/
│   ├── OutletSubmissionForm.*
│   └── VerifySubmission.*
└── pages/
    ├── SubmitOutlet.*
    └── VerifySubmission.*

data/locations.db and public/data/locations.json are not listed above as new files — they already exist and are already maintained by scripts/etl/sync-locations.mjs and scripts/etl/ingest-submissions.mjs; this feature extends them rather than introducing parallel plug.sqlite / outlets.json files. GitHub Pages deployment also doesn't need a new deploy-pages.yml — merges to main already trigger the existing .github/workflows/deploy.yml.

Validation Requirements

Before creating or merging a pull request, verify:

• Required fields are present.
• Field lengths are within documented limits.
• Latitude is between -90 and 90.
• Longitude is between -180 and 180.
• Coordinates are within the supported service region.
• Location type is an allowed enum value.
• URLs use an allowed protocol.
• User content contains no executable HTML.
• The submission ID has not been processed.
• The source issue has not been processed.
• The location is not an obvious duplicate.
• The venues table schema in data/locations.db has not changed unexpectedly.
• PRAGMA integrity_check returns ok.
• Existing records were not unintentionally removed.
• public/data/locations.json matches data/locations.db.
• No email address or sensitive verification data is exported.

Abuse Prevention

• Apply IP-based and email-hash rate limits.
• Limit request-body size.
• Limit field lengths.
• Reject control characters and unexpected fields.
• Normalize coordinates and addresses.
• Do not execute content from the GitHub Issue.
• Do not permit arbitrary labels from the form.
• Do not accept direct database commands.
• Flag suspicious or duplicate submissions for human review.
• Automatically reject repeated invalid submissions.
• Keep edge-function and GitHub App secrets outside the frontend bundle.

Automatic Merge Policy

A generated pull request may be automatically merged only when:

• The submission passed validation.
• SQLite integrity checks passed.
• No duplicate location was detected.
• public/data/locations.json matches data/locations.db.
• No existing records were deleted.
• Only approved data files changed.
• All required GitHub Actions checks passed.

Submissions that fail or produce uncertain results must remain open for human review.

Acceptance Criteria

• Visitors can submit an outlet without a GitHub account.
• The form validates required fields before submission.
• A verification email is delivered successfully.
• Verification tokens are encrypted, signed and short-lived.
• Email scanners cannot finalize submissions with a GET request.
• Confirmed submissions create a GitHub Issue through the GitHub App.
• The GitHub App has only Metadata read and Issues write permissions.
• Submitter email addresses do not appear in public issues or repository data.
• GitHub Actions parses the structured submission safely.
• Valid submissions create a new row in the venues table of data/locations.db.
• Duplicate submissions do not create duplicate database records.
• public/data/locations.json is regenerated automatically.
• A human-readable change summary is included in the pull request.
• Required checks prevent invalid database changes from merging.
• Qualified pull requests merge automatically.
• GitHub Pages deploys the updated map.
• The submission issue receives a success or rejection comment.
• No GitHub credentials are exposed to the browser.

Implementation Tasks

Frontend

• Build the outlet-submission form.
• Add client-side validation.
• Add submission-pending state.
• Add email-verification confirmation page.
• Add successful-submission receipt page.
• Add accessible error handling and status announcements.

Verification Function

• Normalize and validate submission fields.
• Encrypt and sign verification tokens.
• Send verification emails.
• Validate confirmation POST requests.
• Generate short-lived GitHub App installation tokens.
• Create labeled GitHub submission issues.
• Add rate limiting and request-size restrictions.

GitHub App

• Register the PLUG GitHub App.
• Grant Metadata read permission.
• Grant Issues read/write permission.
• Install the App only on Fused-Gaming/plug.
• Store the App ID, installation ID and private key securely.
• Confirm that the App cannot modify repository contents.

GitHub Actions and Data

• Create the issue-processing workflow.
• Implement structured issue parsing.
• Implement submission validation.
• Add the new source_issue, submission_id, and submitted_by_hash columns to the existing venues table in data/locations.db.
• Insert records with parameterized SQL.
• Regenerate public/data/locations.json.
• Generate a human-readable change summary.
• Create the submission pull request.
• Configure required checks.
• Configure qualified automatic merging.
• Close or update processed issues.
• Confirm the existing deploy.yml workflow picks up the merge (no new deploy workflow needed).

Non-Goals

• Building a traditional user-account system.
• Storing passwords.
• Giving public users GitHub repository permissions.
• Allowing the GitHub App to modify repository contents.
• Real-time database writes from the browser.
• Publishing contributor email addresses.
• Automatically accepting submissions that fail geographic or duplicate checks.

Definition of Done

A visitor without a GitHub account can submit a public charging location, verify control of an email address, and receive confirmation. The verified submission is converted into a GitHub Issue by the narrowly scoped PLUG GitHub App, processed into data/locations.db by GitHub Actions, reviewed through an automatically generated pull request, merged after all required checks pass, and published on plug.vln.gg without maintaining a traditional application database.