How frozsgy/jotsheets works
Project Type and Purpose
• JotSheets is a PHP-based full-stack web application.
• It provides a user interface to configure mappings from JotForm submissions to Google Spreadsheets.
• It persists mapping rules in a MySQL/MariaDB database, listens for JotForm webhooks, and pushes data to Google Sheets via the Google APIs.
File Structure
• Root controllers (index.php, login.php, add.php, append.php, delete.php, hook.php) handle HTTP requests.
• class/ folder contains business-logic adapters:
• db.php – database connection and queries
• user.php – authentication
• google.php & vendor/ – Google API client wrapper
• jotform.php & JotForm.php – JotForm API client wrapper
• spreadsheets.php – sheet read/write operations
• integrationsBase.php & integrations.php – orchestration of integration flows
• .php, append.php, hook.php – CRUD and webhook logic
• curl.php, PushId.php, unique.php, field/forms.php, etc. – utilities
• views/ houses Twig-style templates organized by feature (add, user, main, webhooks, google, jotform, error).
• js/ & css/ provide client-side interactivity (HTML5Sortable) and styling.
• .htaccess configures URL rewriting; db.sql defines the schema.
Extract Architecture from README
• Dependencies: PHP 7+, Apache with mod_rewrite, MySQL/MariaDB, Google API PHP Client, JotForm API PHP Client, HTML5Sortable.
• Installation: import db.sql, configure class/db.php, class/server.php, class/google.php, install external libraries under class/google/vendor and class/jotform.
a. Main Components
• Client Browser / Web UI (HTML/CSS/JS)
• Apache Web Server + PHP Runtime
• Controllers Layer (index.php, add.php, hook.php, etc.)
• Business Logic & Service Adapters (class/…)
• Data Layer (MySQL/MariaDB via class/db.php)
• External Services: JotForm API, Google Sheets API
b. Relationships & Data Flow
1. Browser → HTTP → Apache → PHP Controller
2. Controller → Business Logic classes (auth, CRUD, integration)
3. Business Logic ↔ MySQL for mapping rules
4. Business Logic → JotForm API to fetch forms/fields
5. Business Logic → Google API to list/write spreadsheets
6. JotForm Webhook → hook.php → mapping logic → Google Sheets
7. Responses → Browser or HTTP 200 to JotForm
c. Architectural Patterns & Principles
• MVC‐style separation: controllers, views, models
• Adapter/Strategy for external integrations (IntegrationsBase)
• Event-driven webhook handling
• Single relational database for state
d. Key Technologies & Libraries
• PHP 7+, Apache, MySQL/MariaDB
• Google APIs Client for PHP
• JotForm API Client for PHP
• HTML5Sortable JavaScript
Essential Architectural Elements
• URL-routing via .htaccess → controllers
• Model autoloader paths (class/google/vendor/autoload.php, class/jotform/JotForm.php)
• Mapping data structure stored in DB and used by hook logic