feat(pgs): auto-forms - pico@d69226d

  1@@ -0,0 +1,124 @@
  2+# Auto-Form Feature for pgs.sh
  3+
  4+## Overview
  5+
  6+The auto-form feature allows users to create HTML forms on their pgs.sh sites that automatically submit data to the pgs database. Form submissions can then be retrieved via the pgs CLI in JSON format.
  7+
  8+## How It Works
  9+
 10+1. Form submissions are automatically captured and stored in PostgreSQL
 11+2. Users can retrieve form data via the pgs CLI
 12+
 13+## Database Schema
 14+
 15+```sql
 16+CREATE TABLE form_entries (
 17+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 18+  user_id uuid NOT NULL,
 19+  name VARCHAR(255) NOT NULL,
 20+  data jsonb NOT NULL,
 21+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
 22+  CONSTRAINT form_entries_pkey PRIMARY KEY (id),
 23+  CONSTRAINT fk_form_entries_users
 24+    FOREIGN KEY(user_id)
 25+    REFERENCES app_users(id)
 26+    ON DELETE CASCADE
 27+);
 28+
 29+CREATE INDEX IF NOT EXISTS idx_form_entries_user ON form_entries(user_id);
 30+CREATE INDEX IF NOT EXISTS idx_form_entries_name ON form_entries(name);
 31+```
 32+
 33+### Migration
 34+
 35+Run the migration:
 36+```bash
 37+make latest
 38+```
 39+
 40+Or run all migrations:
 41+```bash
 42+make migrate
 43+```
 44+
 45+## HTML Form Example
 46+
 47+```html
 48+<form method="POST" action="/forms/example" data-pgs="true">
 49+  <p>
 50+    <label>Your Name: <input type="text" name="name" /></label>
 51+  </p>
 52+  <p>
 53+    <label>Your Email: <input type="email" name="email" /></label>
 54+  </p>
 55+  <p>
 56+    <label>Your Role: <select name="role[]" multiple>
 57+      <option value="leader">Leader</option>
 58+      <option value="follower">Follower</option>
 59+    </select></label>
 60+  </p>
 61+  <p>
 62+    <label>Message: <textarea name="message"></textarea></label>
 63+  </p>
 64+  <p>
 65+    <button type="submit">Send</button>
 66+  </p>
 67+</form>
 68+```
 69+
 70+## CLI Commands
 71+
 72+### List all form names for a user
 73+```bash
 74+ssh pgs.sh forms ls
 75+```
 76+
 77+### Get form submissions for a specific form
 78+```bash
 79+ssh pgs.sh forms show example
 80+```
 81+
 82+Output (JSON):
 83+```json
 84+[
 85+  {
 86+    "id": "uuid",
 87+    "name": "contact",
 88+    "data": {
 89+      "name": "John Doe",
 90+      "email": "john@example.com",
 91+      "role": ["leader", "follower"],
 92+      "message": "Hello!"
 93+    },
 94+    "created_at": "2026-03-05T12:00:00Z"
 95+  }
 96+]
 97+```
 98+
 99+### Delete all submissions for a form
100+```bash
101+ssh pgs.sh forms rm example --write
102+```
103+
104+## Data Storage
105+
106+- Form data is stored in PostgreSQL `form_entries` table
107+- Each submission is a JSON object with form field names as keys
108+- Data is associated with the user, not the project
109+- Form data is deleted when the user account is deleted (CASCADE)
110+
111+## Implementation Details
112+
113+### Database Methods
114+
115+```go
116+InsertFormEntry(userID, name string, data map[string]interface{}) error
117+FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error)
118+FindFormNamesByUser(userID string) ([]string, error)
119+RemoveFormEntriesByUserAndName(userID, name string) error
120+```
121+
122+## More features
123+
124+- Form validation and confirmation pages
125+- CSRF token in form and validated in post handler

M Makefile

+2, -1

 1@@ -145,10 +145,11 @@ migrate:
 2 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251217_add_access_logs_table.sql
 3 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20251226_add_pipe_monitoring.sql
 4 	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
 5+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260305_add_forms_table.sql
 6 .PHONY: migrate
 7 
 8 latest:
 9-	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260116_add_analytics_filter_indexes.sql
10+	$(DOCKER_CMD) exec -i $(DB_CONTAINER) psql -U $(PGUSER) -d $(PGDATABASE) < ./sql/migrations/20260305_add_forms_table.sql
11 .PHONY: latest
12 
13 psql:

A pkg/apps/pgs/auto_form.go

+81, -0

 1@@ -0,0 +1,81 @@
 2+package pgs
 3+
 4+import (
 5+	"net/http"
 6+
 7+	"github.com/picosh/pico/pkg/shared/router"
 8+)
 9+
10+func handleAutoForm(w http.ResponseWriter, r *http.Request, cfg *PgsConfig) {
11+	formName := r.PathValue("fname")
12+	if r.Method != http.MethodPost {
13+		http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
14+		return
15+	}
16+
17+	err := r.ParseForm()
18+	if err != nil {
19+		cfg.Logger.Error("failed to parse auto form", "err", err)
20+		http.Error(w, "failed to parse auto form", http.StatusBadRequest)
21+		return
22+	}
23+
24+	formValues := make(map[string]interface{})
25+	for key, values := range r.PostForm {
26+		if len(values) == 1 {
27+			formValues[key] = values[0]
28+		} else if len(values) > 1 {
29+			formValues[key] = values
30+		}
31+	}
32+
33+	subdomain := router.GetSubdomainFromRequest(r, cfg.Domain, cfg.TxtPrefix)
34+	props, err := router.GetProjectFromSubdomain(subdomain)
35+	if err != nil {
36+		cfg.Logger.Error("could not get project from subdomain", "subdomain", subdomain, "err", err)
37+		http.Error(w, "not found", http.StatusNotFound)
38+		return
39+	}
40+
41+	user, err := cfg.DB.FindUserByName(props.Username)
42+	if err != nil {
43+		cfg.Logger.Error("user not found", "username", props.Username)
44+		http.Error(w, "not found", http.StatusNotFound)
45+		return
46+	}
47+
48+	err = cfg.DB.InsertFormEntry(user.ID, formName, formValues)
49+	if err != nil {
50+		cfg.Logger.Error("failed to save form data", "err", err)
51+		http.Error(w, "failed to save form data", http.StatusInternalServerError)
52+		return
53+	}
54+
55+	serveAutoFormSubmitted(w, r, cfg)
56+}
57+
58+type FormData struct {
59+	Error string
60+}
61+
62+func serveAutoFormSubmitted(w http.ResponseWriter, r *http.Request, cfg *PgsConfig) {
63+	errorMsg := r.URL.Query().Get("error")
64+	data := loginFormData{
65+		Error: errorMsg,
66+	}
67+
68+	w.WriteHeader(http.StatusUnprocessableEntity)
69+
70+	ts, err := renderTemplate(cfg, []string{cfg.StaticPath("html/auto_form.page.tmpl")})
71+	if err != nil {
72+		cfg.Logger.Error("could not render auto form template", "err", err.Error())
73+		http.Error(w, "Server error", http.StatusInternalServerError)
74+		return
75+	}
76+
77+	err = ts.Execute(w, data)
78+	if err != nil {
79+		cfg.Logger.Error("could not execute login template", "err", err.Error())
80+		http.Error(w, "Server error", http.StatusInternalServerError)
81+	}
82+}

M pkg/apps/pgs/cli.go

+37, -0

 1@@ -1,6 +1,7 @@
 2 package pgs
 3 
 4 import (
 5+	"encoding/json"
 6 	"errors"
 7 	"fmt"
 8 	"io"
 9@@ -561,3 +562,39 @@ func (c *Cmd) cacheAll() error {
10 	}
11 	return nil
12 }
13+
14+func (c *Cmd) formsLs() error {
15+	forms, err := c.Dbpool.FindFormNamesByUser(c.User.ID)
16+	if err != nil {
17+		return err
18+	}
19+	if len(forms) == 0 {
20+		c.output("no forms found")
21+		return nil
22+	}
23+	for _, name := range forms {
24+		c.output(name)
25+	}
26+	return nil
27+}
28+
29+func (c *Cmd) formData(formName string) error {
30+	formData, err := c.Dbpool.FindFormEntriesByUserAndName(c.User.ID, formName)
31+	if err != nil {
32+		return err
33+	}
34+	data, err := json.Marshal(formData)
35+	if err != nil {
36+		return err
37+	}
38+	c.output(string(data))
39+	return nil
40+}
41+
42+func (c *Cmd) formRm(formName string) error {
43+	c.output(fmt.Sprintf("removing all data associated with form: %s", formName))
44+	if c.Write {
45+		return c.Dbpool.RemoveFormEntriesByUserAndName(c.User.ID, formName)
46+	}
47+	return nil
48+}

M pkg/apps/pgs/cli_middleware.go

+23, -0

 1@@ -212,6 +212,29 @@ func Middleware(handler *UploadAssetHandler) pssh.SSHServerMiddleware {
 2 
 3 				err := opts.cache(projectName)
 4 				opts.notice()
 5+				opts.bail(err)
 6+				return err
 7+			case "forms":
 8+				formName := projectName
 9+				formsCmd, write := flagSet("forms", sesh)
10+				rmForm := formsCmd.Bool("rm", false, "delete form data")
11+				if !flagCheck(formsCmd, formName, cmdArgs) {
12+					return nil
13+				}
14+				opts.Write = *write
15+
16+				var err error
17+				if formName == "ls" {
18+					err = opts.formsLs()
19+				} else {
20+					if *rmForm {
21+						err = opts.formRm(formName)
22+						opts.notice()
23+					} else {
24+						err = opts.formData(formName)
25+					}
26+				}
27+
28 				opts.bail(err)
29 				return err
30 			case "acl":

M pkg/apps/pgs/db/db.go

+5, -0

 1@@ -27,6 +27,11 @@ type PgsDB interface {
 2 	FindProjectsByPrefix(userID, name string) ([]*db.Project, error)
 3 	FindProjects(by string) ([]*db.Project, error)
 4 
 5+	InsertFormEntry(userID, name string, data map[string]interface{}) error
 6+	FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error)
 7+	FindFormNamesByUser(userID string) ([]string, error)
 8+	RemoveFormEntriesByUserAndName(userID, name string) error
 9+
10 	RegisterAdmin(username, pubkey, pubkeyName string) error
11 
12 	Close() error

M pkg/apps/pgs/db/memory.go

+55, -5

 1@@ -11,11 +11,12 @@ import (
 2 )
 3 
 4 type MemoryDB struct {
 5-	Logger   *slog.Logger
 6-	Users    []*db.User
 7-	Projects []*db.Project
 8-	Pubkeys  []*db.PublicKey
 9-	Feature  *db.FeatureFlag
10+	Logger      *slog.Logger
11+	Users       []*db.User
12+	Projects    []*db.Project
13+	Pubkeys     []*db.PublicKey
14+	Feature     *db.FeatureFlag
15+	FormEntries []*db.FormEntry
16 }
17 
18 var _ PgsDB = (*MemoryDB)(nil)
19@@ -195,3 +196,52 @@ func (me *MemoryDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
20 func (me *MemoryDB) InsertAccessLog(*db.AccessLog) error {
21 	return errNotImpl
22 }
23+
24+func (me *MemoryDB) InsertFormEntry(userID, name string, data map[string]interface{}) error {
25+	id := uuid.NewString()
26+	now := time.Now()
27+	entry := &db.FormEntry{
28+		ID:        id,
29+		UserID:    userID,
30+		Name:      name,
31+		Data:      data,
32+		CreatedAt: &now,
33+	}
34+	me.FormEntries = append(me.FormEntries, entry)
35+	return nil
36+}
37+
38+func (me *MemoryDB) FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error) {
39+	entries := []*db.FormEntry{}
40+	for _, entry := range me.FormEntries {
41+		if entry.UserID == userID && entry.Name == name {
42+			entries = append(entries, entry)
43+		}
44+	}
45+	return entries, nil
46+}
47+
48+func (me *MemoryDB) FindFormNamesByUser(userID string) ([]string, error) {
49+	names := make(map[string]bool)
50+	for _, entry := range me.FormEntries {
51+		if entry.UserID == userID {
52+			names[entry.Name] = true
53+		}
54+	}
55+	result := []string{}
56+	for name := range names {
57+		result = append(result, name)
58+	}
59+	return result, nil
60+}
61+
62+func (me *MemoryDB) RemoveFormEntriesByUserAndName(userID, name string) error {
63+	filtered := []*db.FormEntry{}
64+	for _, entry := range me.FormEntries {
65+		if entry.UserID != userID || entry.Name != name {
66+			filtered = append(filtered, entry)
67+		}
68+	}
69+	me.FormEntries = filtered
70+	return nil
71+}

M pkg/apps/pgs/db/postgres.go

+39, -0

 1@@ -1,6 +1,7 @@
 2 package pgsdb
 3 
 4 import (
 5+	"encoding/json"
 6 	"fmt"
 7 	"log/slog"
 8 	"time"
 9@@ -295,3 +296,41 @@ func (me *PgsPsqlDB) RegisterAdmin(username, pubkey, pubkeyName string) error {
10 	_, err = me.Db.Exec("INSERT INTO feature_flags (user_id, name, expires_at) VALUES (1, 'admin', '2100-01-01')", userID)
11 	return err
12 }
13+
14+func (me *PgsPsqlDB) InsertFormEntry(userID, name string, data map[string]interface{}) error {
15+	dataJSON, err := json.Marshal(data)
16+	if err != nil {
17+		return err
18+	}
19+
20+	_, err = me.Db.Exec(
21+		"INSERT INTO form_entries (user_id, name, data) VALUES ($1, $2, $3)",
22+		userID, name, dataJSON,
23+	)
24+	return err
25+}
26+
27+func (me *PgsPsqlDB) FindFormEntriesByUserAndName(userID, name string) ([]*db.FormEntry, error) {
28+	entries := []*db.FormEntry{}
29+	err := me.Db.Select(
30+		&entries,
31+		"SELECT * FROM form_entries WHERE user_id=$1 AND name=$2 ORDER BY created_at DESC",
32+		userID, name,
33+	)
34+	return entries, err
35+}
36+
37+func (me *PgsPsqlDB) FindFormNamesByUser(userID string) ([]string, error) {
38+	names := []string{}
39+	err := me.Db.Select(
40+		&names,
41+		"SELECT DISTINCT name FROM form_entries WHERE user_id=$1 ORDER BY name ASC",
42+		userID,
43+	)
44+	return names, err
45+}
46+
47+func (me *PgsPsqlDB) RemoveFormEntriesByUserAndName(userID, name string) error {
48+	_, err := me.Db.Exec("DELETE FROM form_entries WHERE user_id=$1 AND name=$2", userID, name)
49+	return err
50+}

M pkg/apps/pgs/db/sqlite.go

+12, -0

 1@@ -58,6 +58,18 @@ CREATE TABLE IF NOT EXISTS feature_flags (
 2 		ON DELETE CASCADE
 3 		ON UPDATE CASCADE
 4 );
 5+
 6+CREATE TABLE IF NOT EXISTS form_entries (
 7+	id INTEGER PRIMARY KEY AUTOINCREMENT,
 8+	user_id INTEGER NOT NULL,
 9+	name TEXT NOT NULL,
10+	data BLOB NOT NULL,
11+	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
12+	CONSTRAINT form_entries_user_id_fk
13+		FOREIGN KEY(user_id) REFERENCES app_users(id)
14+		ON DELETE CASCADE
15+		ON UPDATE CASCADE
16+);
17 `
18 
19 var sqliteMigrations = []string{

A pkg/apps/pgs/html/auto_form.page.tmpl

+20, -0

 1@@ -0,0 +1,20 @@
 2+{{define "title"}}Form Submitted{{end}}
 3+
 4+{{define "meta"}}
 5+{{end}}
 6+
 7+{{define "attrs"}}class="container" style="height: 100vh;"{{end}}
 8+
 9+{{define "body"}}
10+<div class="container flex justify-center">
11+	<div style="max-width: 450px;" class="mt-4 border py-4 px-4 flex flex-col gap">
12+  	<h1 class="text-lg">Form Submitted</h1>
13+
14+		{{if .Error}}
15+  	<div style="color: tomato;">{{.Error}}</div>
16+		{{else}}
17+  	<div>Form submitted successfully!</div>
18+  	{{end}}
19+	</div>
20+</div>
21+{{end}}

1@@ -16,7 +16,7 @@
2   	<div style="color: tomato;">{{.Error}}</div>
3   	{{end}}
4 
5-  	<form method="POST" action="/auth/login">
6+  	<form method="POST" action="/pgs/login">
7   		<input type="hidden" name="project" value="{{.ProjectName}}">
8   		<label for="password">Enter password:</label>
9       <div class="flex gap">

M pkg/apps/pgs/web.go

+6, -1

 1@@ -153,7 +153,8 @@ func (web *WebRouter) initRouters() {
 2 
 3 	// subdomain or custom domains
 4 	userRouter := http.NewServeMux()
 5-	userRouter.HandleFunc("POST /auth/login", web.handleLogin)
 6+	userRouter.HandleFunc("POST /pgs/login", web.handleLogin)
 7+	userRouter.HandleFunc("POST /pgs/forms/{fname...}", web.handleAutoForm)
 8 	userRouter.HandleFunc("GET /{fname...}", web.AssetRequest(WebPerm))
 9 	userRouter.HandleFunc("GET /{$}", web.AssetRequest(WebPerm))
10 	web.UserRouter = userRouter
11@@ -570,6 +571,10 @@ func (web *WebRouter) handleLogin(w http.ResponseWriter, r *http.Request) {
12 	handleLogin(w, r, web.Cfg)
13 }
14 
15+func (web *WebRouter) handleAutoForm(w http.ResponseWriter, r *http.Request) {
16+	handleAutoForm(w, r, web.Cfg)
17+}
18+
19 func (web *WebRouter) ServeHTTP(w http.ResponseWriter, r *http.Request) {
20 	subdomain := router.GetSubdomainFromRequest(r, web.Cfg.Domain, web.Cfg.TxtPrefix)
21 	if web.RootRouter == nil || web.UserRouter == nil {

M pkg/db/db.go

+24, -0

 1@@ -249,6 +249,30 @@ type Token struct {
 2 	ExpiresAt *time.Time `json:"expires_at" db:"expires_at"`
 3 }
 4 
 5+type FormEntry struct {
 6+	ID        string        `json:"id" db:"id"`
 7+	UserID    string        `json:"-" db:"user_id"`
 8+	Name      string        `json:"-" db:"name"`
 9+	Data      FormEntryData `json:"data" db:"data"`
10+	CreatedAt *time.Time    `json:"created_at" db:"created_at"`
11+}
12+
13+type FormEntryData map[string]interface{}
14+
15+// Make the FormEntry struct implement the driver.Valuer interface.
16+func (f FormEntryData) Value() (driver.Value, error) {
17+	return json.Marshal(f)
18+}
19+
20+// Make the FormEntry struct implement the sql.Scanner interface.
21+func (f *FormEntryData) Scan(value any) error {
22+	b, err := tcast(value)
23+	if err != nil {
24+		return err
25+	}
26+	return json.Unmarshal(b, &f)
27+}
28+
29 type FeatureFlag struct {
30 	ID               string          `json:"id" db:"id"`
31 	UserID           string          `json:"user_id" db:"user_id"`

M pkg/db/postgres/storage.go

+1, -2

 1@@ -6,11 +6,10 @@ import (
 2 	"fmt"
 3 	"log/slog"
 4 	"math"
 5+	"slices"
 6 	"strings"
 7 	"time"
 8 
 9-	"slices"
10-
11 	"github.com/jmoiron/sqlx"
12 	_ "github.com/lib/pq"
13 	"github.com/picosh/pico/pkg/db"

A sql/migrations/20260305_add_forms_table.sql

+15, -0

 1@@ -0,0 +1,15 @@
 2+CREATE TABLE IF NOT EXISTS form_entries (
 3+  id uuid NOT NULL DEFAULT uuid_generate_v4(),
 4+  user_id uuid NOT NULL,
 5+  name VARCHAR(255) NOT NULL,
 6+  data jsonb NOT NULL,
 7+  created_at timestamp without time zone NOT NULL DEFAULT NOW(),
 8+  CONSTRAINT form_entries_pkey PRIMARY KEY (id),
 9+  CONSTRAINT fk_form_entries_users
10+    FOREIGN KEY(user_id)
11+    REFERENCES app_users(id)
12+    ON DELETE CASCADE
13+);
14+
15+CREATE INDEX IF NOT EXISTS idx_form_entries_user ON form_entries(user_id);
16+CREATE INDEX IF NOT EXISTS idx_form_entries_name ON form_entries(name);