repos / pico

AUTO_FORM.md

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