# frozsgy/jotsheets architecture

> Architecture diagram of the GitHub repository frozsgy/jotsheets, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-05-24). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/frozsgy/jotsheets
- Repository: https://github.com/frozsgy/jotsheets

## Overview

##### 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

## Diagram (Mermaid)

```mermaid
flowchart TB
  subgraph "Presentation Layer"
    direction TB
    Browser["Client Browser"]:::client
    CSS["CSS Files"]:::client
    JS["JS Files"]:::client
    Views["Twig Templates"]:::client
  end

  Browser -->|load| CSS
  Browser -->|load| JS
  Browser -->|render| Views

  subgraph "Application Layer"
    direction TB
    .HT[".htaccess"]:::routing

    subgraph "Controllers"
      direction TB
      Index["index.php"]:::controller
      Login["login.php / logout.php"]:::controller
      CRUDCtr["add.php / append.php / delete.php"]:::controller
      HookCtr["hook.php"]:::controller
    end

    subgraph "Business Logic & Services"
      direction TB
      DBAdapter["class/db.php"]:::service
      Auth["class/user.php & class/server.php"]:::service
      GoogleClient["class/google.php & vendor/"]:::service
      JotFormClient["class/jotform.php & JotForm/"]:::service
      Sheets["class/spreadsheets.php"]:::service
      IntegrBase["class/integrationsBase.php"]:::service
      Integr["class/integrations.php"]:::service
      CRUDLogic["class/add.php, append.php, addhook.php, hook.php"]:::service
      Utils["class/curl.php, PushId.php, unique.php, field.php, forms.php"]:::service
    end
  end

  Browser -->|HTTP| .HT
  .HT --> Index
  .HT --> Login
  .HT --> CRUDCtr
  .HT --> HookCtr

  Index --> Auth
  Index --> DBAdapter
  Index --> JotFormClient
  Index --> Sheets
  Index -->|response| Browser

  Login --> Auth
  CRUDCtr --> Auth
  CRUDCtr --> DBAdapter
  CRUDCtr --> IntegrBase
  CRUDCtr --> Integr
  CRUDCtr --> Sheets
  CRUDCtr --> JotFormClient

  HookCtr --> DBAdapter
  HookCtr --> IntegrBase
  HookCtr --> Integr
  HookCtr --> Sheets

  DBAdapter --> DB["MySQL/MariaDB"]:::database

  JotFormClient -->|API call| JFormExt["JotForm API"]:::external
  Sheets -->|API call| GSheetsExt["Google Sheets API"]:::external

  click CSS "https://github.com/frozsgy/jotsheets/tree/master/css/"
  click JS "https://github.com/frozsgy/jotsheets/tree/master/js/"
  click Views "https://github.com/frozsgy/jotsheets/tree/master/views/"
  click .HT "https://github.com/frozsgy/jotsheets/blob/master/.htaccess"
  click Index "https://github.com/frozsgy/jotsheets/blob/master/index.php"
  click Login "https://github.com/frozsgy/jotsheets/blob/master/login.php"
  click CRUDCtr "https://github.com/frozsgy/jotsheets/blob/master/add.php"
  click HookCtr "https://github.com/frozsgy/jotsheets/blob/master/hook.php"
  click DBAdapter "https://github.com/frozsgy/jotsheets/blob/master/class/db.php"
  click DB "https://github.com/frozsgy/jotsheets/blob/master/db.sql"
  click Auth "https://github.com/frozsgy/jotsheets/blob/master/class/user.php"
  click GoogleClient "https://github.com/frozsgy/jotsheets/blob/master/class/google.php"
  click JotFormClient "https://github.com/frozsgy/jotsheets/blob/master/class/jotform.php"
  click Sheets "https://github.com/frozsgy/jotsheets/blob/master/class/spreadsheets.php"
  click IntegrBase "https://github.com/frozsgy/jotsheets/blob/master/class/integrationsBase.php"
  click Integr "https://github.com/frozsgy/jotsheets/blob/master/class/integrations.php"
  click CRUDLogic "https://github.com/frozsgy/jotsheets/blob/master/class/add.php"
  click Utils "https://github.com/frozsgy/jotsheets/blob/master/class/curl.php"

  classDef client fill:#fff2cc,stroke:#f1c232,color:#000
  classDef routing fill:#cfe2f3,stroke:#6fa8dc,color:#000
  classDef controller fill:#9fc5e8,stroke:#6d9eeb,color:#000
  classDef service fill:#d9ead3,stroke:#93c47d,color:#000
  classDef database fill:#b6d7a8,stroke:#38761d,color:#000
  classDef external fill:#f4cccc,stroke:#e06666,color:#000
```
