Custom Form Generator

A full-stack React + Node + Supabase form builder and submission system with Microsoft Azure AD authentication support. This repository contains a backend API (Express + Supabase) and a frontend single-page app (React + Vite + tailwind CSS). It lets administrators build forms, publish them, collect submissions, and manage approvals.

This application was originally built for OGDCL (Oil and Gas Development Company Limited) to streamline their custom office form generation, submission, and approval workflows. Now it's available as a public tool for anyone who needs a robust form management system.

Contents

• backend/ — Express API using Supabase (postgresSQL) as the database
• frontend/ — React app (Vite) with MSAL (Azure AD) authentication and form builder UI

Quick facts

• Backend: Node.js (ESM), Express, Supabase JS client
• Frontend: React (18) Vite, TailwindCSS (^3.4.1), MSAL for Azure AD
• Database: Supabase (Postgres) — schema provided in backend/database/init-database.sql
• Auth: Microsoft Graph token validation (server-side middleware)

---

Getting started (local development — Windows PowerShell)

Prerequisites

• Node.js (>=18 recommended)
• npm
• A Supabase project (or a PostgreSQL instance compatible with Supabase). You'll need the connection string and anon key. OR Can use local PostgresSQL if installed for development.
• Azure AD app registration (for Azure auth, essential): client id, tenant id, redirect URI

1. Clone the repo

git clone git@github.com:AbdulAhad-2005/OGDCL-Form-Generator.git

2. Backend setup

Open PowerShell and run:

cd "Custom-Form-Generator\backend"
npm install

Create a .env file in backend/ (copy the keys below) and fill in values:

Required environment variables for development:

• DATABASE_URL — your Supabase connection string (Postgres). Example: postgres://postgres:password@db.host:5432/postgres
• SUPABASE_ANON_KEY — Supabase anon/public key (used by server client)
• AZURE_CLIENT_ID — Azure AD App (MSAL) client id used by frontend and server logs
• AZURE_TENANT_ID — Azure AD tenant id
• FRONTEND_URL — URL to allow CORS (defaults to http://localhost:3000)
• PORT — (optional) backend port, default 5000

Example .env (do NOT commit your secrets):

DATABASE_URL=postgres://username:password@your-project.supabase.co:5432/postgres
SUPABASE_ANON_KEY=your_supabase_anon_key_here
AZURE_CLIENT_ID=your_azure_client_id
AZURE_TENANT_ID=your_azure_tenant_id
FRONTEND_URL=http://localhost:3000
PORT=5000

Initialize database schema and optional sample data

The backend provides a helper script that will run the SQL files against your Supabase database. From backend/ run:

# Initialize schema only
npm run setup-db

# Initialize schema + sample data
npm run setup-db -- --sample-data
# or
npm run setup-db -- -s

Notes about setup-database.js:

• It reads backend/database/init-database.sql and (optionally) sample-data.sql and executes them using a Supabase RPC helper.
• This script expects a working Supabase instance and valid DATABASE_URL and SUPABASE_ANON_KEY.

Start backend server

# Development with auto-reload (nodemon)
npm run dev

# Or start normally
npm start

Health check:

• GET http://localhost:5000/api/health
• Test endpoint: GET http://localhost:5000/api/test

3. Frontend setup

Open a new PowerShell window/tab and:

cd "Custom-Form-Generator\frontend"
npm install

Create a .env file in frontend/ (Vite expects variables prefixed with VITE_):

VITE_API_URL=http://localhost:5000/api
VITE_AZURE_CLIENT_ID=your_azure_client_id
VITE_AZURE_TENANT_ID=your_azure_tenant_id
VITE_AZURE_REDIRECT_URI=http://localhost:3000

Start frontend dev server:

npm run dev

By default Vite serves the frontend on http://localhost:5173 (or 3000 if configured). The app expects the API at VITE_API_URL.

---

Architecture & folder overview

• backend/

  • server.js — Express server entry. Registers routes and authentication middleware.
  • config/database.js — Supabase client configuration and connection test.
  • middleware/auth.js — Validates Microsoft Graph access tokens by calling the Graph API; provides validateAzureToken and optionalAuth.
  • controllers/ — Contains controllers for forms, submissions, approvals, users (CRUD + business logic). Each controller uses the Supabase client.
  • routes/ — Router files mapping HTTP endpoints to controllers.
  • database/init-database.sql — DDL to create tables (forms, submissions, approvals, form_templates), indexes, triggers, and permissive RLS policies for development.
  • setup-database.js — Script to run SQL files via Supabase client.

• frontend/

  • src/
    • main.jsx — App entry, ReactDOM render, DnD provider
    • App.jsx — Application routes; integrates MSAL and AuthProvider
    • config/ — api.js and constants.js and authConfig.js (MSAL config). These read Vite env vars (VITE_*)
    • services/api.js — axios client configured with token injection and API helper functions (formsAPI, submissionsAPI, approvalsAPI, usersAPI)
    • contexts/AuthContext.jsx — (auth wrapper) provides user state and token getter used by API
    • components/ — UI for form builder, previews, admin dashboard, submissions, etc.

Key API endpoints (backend)

All endpoints are prefixed with /api (server uses /api base in frontend config).

Authentication: Most endpoints require a Microsoft Graph Bearer token. The middleware validateAzureToken checks Authorization: Bearer <token> by calling Microsoft Graph /me.

Public endpoints:

• GET /api/health — health check
• GET /api/test — test route

Protected endpoints (require token):

• GET /api/forms — list forms (query params: published, created_by, form_type)

• GET /api/forms/published — list published forms

• GET /api/forms/templates — list templates

• GET /api/forms/:id — get single form

• POST /api/forms — create form (JSON body: title, form_config, etc.)

• PUT /api/forms/:id — update form

• DELETE /api/forms/:id — delete form

• PATCH /api/forms/:id/publish — publish

• PATCH /api/forms/:id/unpublish — unpublish

• GET /api/submissions — list submissions (query params: status, submitter_email, form_id)

• POST /api/submissions — create a submission (body: form_id, submission_data, submitter_email)

• GET /api/submissions/form/:formId — submissions for a form

• GET /api/submissions/:id — get single submission

• Approvals endpoints exist under /api/approvals (create approvals on new submission if managers exist; approve/reject flows supported)

• User endpoints under /api/users include email validation and getCurrentUser (wraps Microsoft Graph /me)

Examples (cURL-like, use a real token):

# Get published forms
curl -X GET "http://localhost:5000/api/forms/published"

# Create a form (requires auth token)
curl -X POST "http://localhost:5000/api/forms" -H "Content-Type: application/json" -H "Authorization: Bearer <TOKEN>" -d '{"title":"My Form","form_config":{"fields":[]}}'

Authentication flow

• Frontend uses MSAL (@azure/msal-browser, @azure/msal-react) to sign-in users against Azure AD. The msalConfig object uses env vars from frontend/src/config/constants.js.
• Frontend obtains an access token and attaches it to axios requests via api instance in frontend/src/services/api.js.
• Backend middleware backend/middleware/auth.js validates the token by calling Microsoft Graph https://graph.microsoft.com/v1.0/me. On success it normalizes the user's email and attaches req.user for controllers to use.

Notes: The backend middleware uses a simple in-memory cache for tokens — good for dev, consider a more robust cache for production.

Database notes

• Schema is in backend/database/init-database.sql. Tables: forms, submissions, approvals, form_templates.
• Row Level Security (RLS) is enabled by the SQL script with permissive policies for development. You should tighten these policies for production.
• setup-database.js runs the SQL files via a Supabase RPC helper named exec_sql (ensure your Supabase instance allows running SQL from the client or execute via Supabase SQL editor if RPC is not available).

Troubleshooting

• "Missing DATABASE_URL" error: Make sure backend/.env contains DATABASE_URL and SUPABASE_ANON_KEY.
• Supabase connection issues: Confirm DATABASE_URL format — the backend expects to parse the project ref from the connection string to build the Supabase URL.
• Azure token validation failing: Ensure frontend obtains Microsoft Graph scopes (User.Read) and the token is sent with Authorization: Bearer <token> header to the API.
• If setup script fails due to missing RPC exec_sql, open Supabase SQL editor and paste backend/database/init-database.sql manually.

Suggested improvements / next steps

• Add unit/integration tests for API routes and frontend components.
• Replace in-memory token cache with Redis or similar for horizontal scaling.
• Harden RLS policies in init-database.sql for production.
• Add CI workflows to run linting/tests and build artifacts.

Where to look in the code

• Backend entry: backend/server.js
• DB & supabase client: backend/config/database.js
• Auth middleware: backend/middleware/auth.js
• Controllers: backend/controllers/*Controller.js
• Frontend entry: frontend/src/main.jsx
• App routes & MSAL: frontend/src/App.jsx, frontend/src/config/authConfig.js
• API helpers: frontend/src/services/api.js, frontend/src/config/constants.js

---