🏷️ Cloud-Native, Metadata-Driven & Schema-Driven Master Data Management (MDM) Engine (FastAPI + Python + Prisma + PostgreSQL + Next.js 16 + TypeScript)

Enterprise-grade master data management UI and API layer over 29 PostgreSQL master tables, implemented as a generic CRUD pipeline using FastAPI, Prisma (Python), and Next.js 16.

🧾 Executive Summary

The Master Table Management Platform is a full-stack master data management solution designed to centralize and standardize CRUD operations across 29 PostgreSQL “master” tables such as mast_country, mast_state, mast_region, mast_pincode, mast_place, mast_sector, mast_skills, mast_user, mast_zone, and user_template. Instead of per-table API implementations and screens, the system exposes a single, parameterised REST endpoint on the backend and a schema-driven admin UI on the frontend.

The backend, implemented with FastAPI and the Prisma Python client, connects to a PostgreSQL database (Supabase in development). It performs dynamic, safe CRUD using query_raw with fully parameterised SQL and runtime primary-key discovery from PostgreSQL catalogs. All operations are restricted to an explicit allowlist of tables (ALLOWED_TABLES) to prevent arbitrary table access.

The frontend is a Next.js 16 + TypeScript single-page admin UI. It does not contain table-specific business logic. Instead, it:

• Loads table rows and associated metadata from the backend.
• Infers column structure from returned JSON.
• Renders a reusable data grid with search, pagination, Add/Edit/Delete, and modal-based forms.

Configuration is environment-driven via DATABASE_URL (backend) and NEXT_PUBLIC_API_URL (frontend), enabling split deployment: the UI can be hosted on Vercel while the API runs on a VPS behind a reverse proxy.

🧩 Business Context & Scope

Business Problem

Multiple master tables (geographical, classification, user traits, skills, etc.) are required across products and internal workflows. Historically, each table often comes with:

• Individual CRUD forms.
• Per-table database scripts.
• Duplicated logic and inconsistent validation.

This increases maintenance cost, onboarding time, and risk of data inconsistency.

Solution Scope

• Provide a single, generic admin interface for all master tables in PostgreSQL.
• Provide a single, generic CRUD API powered by FastAPI and Prisma.
• Restrict operations to a curated list of master tables (ALLOWED_TABLES).
• Support flexible deployment with clear separation of frontend and backend concerns.

🎯 Objectives & Goals

Functional Objectives

• Expose CRUD operations over 29 master tables via /api/data.
• Support pagination, free-text search, and dynamic schema handling.
• Allow quick inclusion of new master tables with minimal configuration.

Non-Functional Objectives

• Ensure safe database access with parameterised SQL and table allowlisting.
• Achieve clean, composable architecture that supports split deployment (VPS API + Vercel UI).
• Provide a developer-friendly experience with predictable behaviour and standard tooling.

✅ Acceptance Criteria

Area	Criteria
CRUD Behaviour	GET /api/data?table=mast_country returns rows, total, limit, offset, primaryKey. POST /api/data inserts a row and returns the inserted entity. PUT /api/data?id=<PK> updates exactly one row or returns HTTP 404. DELETE /api/data?id=<PK> deletes exactly one row for the resolved primary key.
Table Safety	Requests to tables not in ALLOWED_TABLES must return HTTP 400 with an explanatory message.
Primary Key Handling	Primary key discovery from PostgreSQL catalogs must work for numeric and non-numeric keys.
UI Behaviour	Selecting any allowed table loads a DataTable view with dynamic columns. Search, pagination, Add, Edit, Delete work without page reload. Backend validation errors are surfaced via toast notifications.
Integration & CORS	Cross-origin requests from http://localhost:3000 and http://127.0.0.1:3000 succeed without CORS errors.

🧮 System Overview & High-Level Architecture

API FastAPI + Prisma (Python) exposing / and /api/data.

UI Next.js 16 SPA using a generic DataTable component.

DB PostgreSQL (Supabase dev instance) with all mast_* and user_template tables.

Data Flow

1. User selects a table from the Next.js dashboard.
2. Next.js calls GET /api/data?table=<TABLE>&limit=<L>&offset=<O>&search=<S>.
3. FastAPI validates the table, queries PostgreSQL with Prisma query_raw, and discovers the primary key.
4. The response { data, total, limit, offset, primaryKey } is used to render the grid and infer columns.
5. On Create/Update/Delete, the frontend calls the same endpoint with appropriate method and payload.

🧱 Technical Stack

Layer	Technology	Role
Backend Framework	FastAPI	HTTP routing, request/response lifecycle, automatic OpenAPI docs.
DB Client	Prisma (Python)	Typed access to PostgreSQL, executing query_raw for dynamic SQL.
Database	PostgreSQL / Supabase	Source of truth for all master data tables.
Frontend Framework	Next.js 16 (App Router)	React-based SPA with server-side and client-side rendering.
Language	TypeScript	Static types for UI code and data contracts.
Styling	Tailwind CSS	Utility-first styling for DataTable and layout.
UX Utilities	react-hot-toast, lucide-react	Notifications and iconography.

🧩 Data Model & Master Tables

The platform manages 29 master tables, including but not limited to:

• Location hierarchy: mast_country, mast_state, mast_district, mast_region, mast_pincode, mast_place, mast_zone
• Human attributes & skills: mast_ability, mast_activity, mast_aptitude, mast_knowledge, mast_lang, mast_skills, mast_trait
• Business-related classifications: mast_industry, mast_leadtype, mast_outlook, mast_pathway, mast_preference, mast_sector, mast_status, mast_stem, mast_task, mast_technology, mast_tools
• User & template: mast_user, user_template, plus supporting tables like mast_contact, mast_data.

Primary keys vary by table and may be numeric (for example regionid, stateid, districtid, pinid) or textual (for example country_code). A key design decision is not to hardcode these names in the backend; instead, they are discovered dynamically from PostgreSQL catalogs for every table in ALLOWED_TABLES.

🔗 API Contract & Behaviour

Base URL

Development base URL: http://127.0.0.1:8000.

FastAPI automatically exposes Swagger UI at /docs, showing / and /api/data.

Endpoint Summary

Method	Path	Description
GET	/	Healthcheck: confirms API is running and DB connectivity is available.
GET	/api/data	Read table rows with optional search and pagination.
POST	/api/data	Create a new row in a specified table.
PUT	/api/data	Update a row identified by primary key.
DELETE	/api/data	Delete a row identified by primary key.

GET /api/data

• Query parameters:
  • table (required) – target table name, must exist in ALLOWED_TABLES.
  • limit (optional, default 10) – maximum number of rows.
  • offset (optional, default 0) – starting offset for pagination.
  • search (optional) – case-insensitive search string.
• Behaviour:
  1. Validate that table belongs to the allowlist.
  2. Compute total using SELECT COUNT(*)::int FROM "table".
  3. If search is not empty, filter using: CAST(row_to_json(t) AS TEXT) ILIKE $1 with placeholders for value.
  4. Otherwise, fetch unfiltered rows using LIMIT and OFFSET.
  5. Discover primary key column name via get_primary_key_column(table).
  6. Return { data, total, limit, offset, primaryKey }.

POST /api/data

• Query parameters:
  • table (required).
• Request body: JSON object where keys are column names and values their corresponding data.
• Behaviour:
  1. Validate table allowlist.
  2. Parse payload; ensure non-empty body.
  3. Build parameterised insert query: placeholders $1..$n mapped to values from payload.
  4. Execute INSERT INTO "table" (...) VALUES (...) RETURNING * via query_raw.
  5. Return inserted row with message Created successfully.

PUT /api/data

• Query parameters:
  • table (required).
  • id (required) – primary key value as string (PostgreSQL handles casting where appropriate).
• Behaviour:
  1. Validate table allowlist.
  2. Read payload and compute assignments "col" = $i for each updated field.
  3. Resolve primary key column name via get_primary_key_column(table).
  4. Append primary key comparison as last parameter: WHERE "pk" = $n.
  5. Execute UPDATE ... RETURNING *. If no rows returned, raise HTTP 404.

DELETE /api/data

• Query parameters:
  • table (required).
  • id (required) – primary key value as string.
• Behaviour:
  1. Validate table allowlist.
  2. Determine pk_column via get_primary_key_column.
  3. Execute DELETE FROM "table" WHERE "pk_column" = $1.
  4. Return { "message": "Deleted successfully" }.

🔢 Error Handling & Status Codes

Status	Example Scenario	Notes
200	Successful CRUD / healthcheck.	Standard success path.
400	Table not in ALLOWED_TABLES or invalid request payload.	Returned via HTTPException with message.
404	PUT/DELETE target row not found.	No rows from UPDATE ... RETURNING *.
422	Missing required query parameters.	FastAPI built-in validation error.
500	DB errors such as type mismatches or constraints.	Exception surfaced as generic HTTP 500 with error detail.

🖥️ Frontend Design – Next.js Admin UI

Page Architecture

• frontend/app/page.tsx – landing dashboard, renders tiles for each master table using static metadata from frontend/lib/tables.ts.
• frontend/components/DataTable.tsx – core reusable component providing dynamic CRUD functionality.

DataTable Behaviour

The component is parameterised by tableName and an onClose callback.

• State:
  • data, columns, loading, search, page, total.
  • showModal, editingRow, formData, constant limit = 10.
• API base URL: derived from NEXT_PUBLIC_API_URL env or defaults to http://127.0.0.1:8000.
• Column inference: deduces table schema from keys of first row in data.
• Networking:
  • GET – for listing and search.
  • POST – create.
  • PUT – update (requires PK).
  • DELETE – delete (requires PK).
• PK heuristic in UI: first column with "id" substring; current implementation can be enhanced by using backend-provided primaryKey.
• Forms: text inputs for all columns; type-awareness is intentionally minimal and delegated to DB constraints.

🔒 Security, Governance & Table Allowlisting

Secrets Management

• Backend connection string is stored in backend/.env as DATABASE_URL (not committed).
• Supabase keys or DB credentials are environment-level secrets.

Allowlisting

All operations are constrained by an explicit ALLOWED_TABLES list in main.py. If a table parameter is not present in this list, the API returns HTTP 400. This prevents runtime access to any non-master tables and mitigates risk from malicious table names.

CORS

CORSMiddleware in FastAPI permits calls from the frontend origins http://localhost:3000 and http://127.0.0.1:3000, and in production can be restricted further to the deployed Vercel domain(s).

☁️ Deployment Topology – VPS API & Vercel Frontend

Backend on VPS

1. Clone repository on VPS and navigate to backend.
2. Create and activate virtual environment.
3. Install dependencies and run prisma generate.
4. Run application using: uvicorn app.main:app --host 0.0.0.0 --port 8000, supervised by systemd or another process manager.
5. Optionally place Nginx in front as TLS terminator and reverse proxy for / and /api/data.

Frontend on Vercel

1. Push repository to GitHub.
2. Import project into Vercel with root directory frontend/.
3. Configure env variable NEXT_PUBLIC_API_URL to the VPS API URL (for example https://api.saubhtech.com/master-data).
4. Vercel runs npm install and npm run build by default; produced static assets and serverless functions host the Next.js app.

⚙️ Installation & Environment Setup (Step-by-Step)

Backend

1. Clone: git clone ... and cd master-data-management-platform/backend.
2. Virtualenv: python -m venv env then activate.
3. Install: pip install -r requirements.txt.
4. Configure DB: create .env with DATABASE_URL.
5. Prisma: run prisma db pull (if schema changed) and prisma generate.
6. Run: uvicorn app.main:app --reload.

Frontend

1. Navigate: cd ../frontend.
2. Install: npm install.
3. Configure: create .env.local with NEXT_PUBLIC_API_URL=http://127.0.0.1:8000.
4. Run Dev: npm run dev and open http://localhost:3000.

🧪 Testing Strategy & Validation Matrix

ID	Area	Action	Expected Result
T1	Health	curl http://127.0.0.1:8000/	JSON with status and db keys.
T2	Docs	Open /docs in browser.	Swagger UI listing / and /api/data.
T3	GET	curl "/api/data?table=mast_country&limit=5&offset=0"	Array of ≤5 rows plus total.
T4	Search	curl "/api/data?table=mast_country&search=Ind"	Rows where JSON text contains "Ind".
T5	POST	Insert into mast_region with valid payload.	Created successfully with inserted row.
T6	PUT (UI)	Edit a row via DataTable.	Success toast; row updated in table and DB.
T7	DELETE (UI)	Delete selected row.	Success toast; row removed from UI and DB.
T8	Allowlist	Call with disallowed table.	HTTP 400 with Table not allowed.
T9	CORS	Trigger requests from Next.js dev server.	No CORS errors in browser console.

🧠 Performance & Optimization Considerations

Current Mechanisms

• Pagination: limit/offset parameters prevent fetching full-table datasets.
• Server-side search: executed in PostgreSQL using ILIKE on row_to_json text representation.

Potential Enhancements

• Implement keyset pagination for very large tables to avoid deep offsets.
• Create dedicated indexes on commonly searched text columns instead of full-row JSON search.
• Introduce connection pooling at DB layer (for example, via Prisma options or PG pool configuration).
• Move from row-to-JSON search to column-specific predicates for improved performance and optimizer friendliness.

🧯 Operational Concerns & Troubleshooting

17.1 Common Issues

• PowerShell virtualenv error: resolved by using .\env\Scripts\Activate.ps1.
• Prisma keyword conflict (column named pass): handled in schema.prisma with appropriate mapping.
• CORS missing header: fix via proper CORSMiddleware configuration.
• Type mismatch errors (for example smallint = text): enforce numeric types in frontend for PK fields or cast on backend.
• PK detection in UI: current heuristic based on id substring; recommended enhancement to consume backend primaryKey.

🧩 Maintenance Guidelines

1. Keep ALLOWED_TABLES aligned with database schema and business rules.
2. When adding a new master table:
   • Update DB schema and seed files.
   • Re-run prisma db pull and prisma generate if Prisma models are used elsewhere.
   • Add table to ALLOWED_TABLES.
   • Configure a corresponding tile entry in frontend/lib/tables.ts.
3. Monitor DB logs and error messages for recurring type issues; reflect corrections either in UI validations or backend casting.

🌟 Enhancement Roadmap (Enterprise Focus)

• Expose primaryKey from backend GET responses and update frontend DataTable to rely on it.
• Introduce field-level metadata to render appropriate input types (numeric, boolean, dates, enums).
• Add authentication and role-based access control around the CRUD endpoint.
• Implement audit logging for all Create/Update/Delete operations for compliance and traceability.
• Provide export/import capabilities (CSV) per master table.

🏆 Key Achievements & Readiness Assessment

• Unified CRUD API for 29 master tables with a single endpoint and dynamic PK handling.
• Fully generic Next.js admin UI requiring no per-table components or forms.
• Strict table allowlisting and environment-based configuration for controlled access and portability.
• Completed validation of CRUD flows, CORS configuration, and PK discovery logic.
• Ready for deployment to VPS + Vercel with documented installation and operational steps.

🗂️ Folder Structure (High-Level)

project-root/
├── backend/
│   ├── app/
│   │   └── main.py
│   ├── prisma/
│   │   └── schema.prisma
│   ├── requirements.txt
│   └── .env              # DATABASE_URL (local only)
│
├── database/
│   ├── schema.sql
│   └── seed.sql
│
├── frontend/
│   ├── app/
│   │   ├── layout.tsx
│   │   ├── page.tsx
│   │   ├── globals.css
│   │   └── favicon.ico
│   ├── components/
│   │   └── DataTable.tsx
│   ├── lib/
│   │   └── tables.ts
│   ├── package.json
│   ├── tsconfig.json
│   ├── next.config.ts
│   ├── tailwind.config.js
│   └── .env.local        # NEXT_PUBLIC_API_URL (local only)

🧭 Live Demonstration Script

1. Start backend: uvicorn app.main:app --reload.
2. Start frontend: npm run dev in frontend.
3. Open http://localhost:3000.
4. Select a table (for example Country) from dashboard.
5. Perform the following in order:
   • Search for an existing record.
   • Create a new record using modal.
   • Edit the newly created record.
   • Delete the test record.
6. Optionally, verify the operations directly in PostgreSQL (psql or Supabase UI).

💡 Summary & Closure

The Master Table Management Platform consolidates 29 heterogeneous master tables into a single, coherent CRUD pipeline with a generic FastAPI endpoint and a dynamic Next.js admin UI. It leverages Prisma for safe, parameterised PostgreSQL access and discovers table primary keys at runtime, avoiding per-table backend code. The project adheres strictly to the behaviour defined in the current repository and notes, and is structured for enterprise deployment via a split architecture (VPS backend + Vercel frontend).

Future enhancements—particularly type-aware forms, audit trails, and role-based access control—can be layered on without disrupting the core generic CRUD design, making this platform a solid foundation for broader master data governance initiatives.