🏷️ Project Title

NEXTJS POSTGRES METADATA ADMIN ENGINE

🧾 Executive Summary

The Master Table Administration System is a highly configurable, generic, and secure CRUD platform designed to manage multiple PostgreSQL master tables using a single UI component and two dynamic API routes. The system auto-discovers table schema, detects primary keys at runtime, and enforces strict table-level security via an allow-list.

This report documents the entire journey from problem analysis, database schema structuring, table creation, backend API development, React UI implementation, deployment planning, and security enhancements. The final product delivers a scalable and reusable internal master-data management console suitable for enterprise environments.

🧩 Project Overview

A PostgreSQL-driven admin panel where different master tables such as mast_state, mast_country, mast_status, user_template, etc., can be added, updated, deleted, and searched without creating new UI or API logic. This saves time and eliminates repeated CRUD development for individual tables.

🎯 Objectives & Goals

Provide a generic admin system for all master tables.
Generate UI dynamically from database schema.
Prevent access to unauthorized tables (allow-listing).
Detect primary keys automatically (numeric & character).
Deploy securely using environment variables.

✅ Acceptance Criteria

UI and API should support CRUD for all allowed master tables.
Primary key must be auto-detected at runtime.
User cannot edit primary key values.
Tables should load via dynamic schema fetch.
Search, pagination, and validation must work uniformly.
Unauthorized table access must be blocked.

💻 Prerequisites

Node.js 18+
PostgreSQL 12+
Basic SQL knowledge
Environment variable setup (DATABASE_URL)
Next.js, React, Tailwind basic understanding

⚙️ Installation & Setup (Enterprise-Grade)

This platform is designed to run as a cloud-native, metadata-driven admin engine. It boots dynamically by discovering PostgreSQL schema at runtime and binding it to a generic UI.

1. System Setup


git clone https://github.com/<org>/<repo>.git
cd master-data-admin-panel
npm install

2. PostgreSQL Database


CREATE TABLE mast_country (
  country_id SERIAL PRIMARY KEY,
  country_name VARCHAR(100) NOT NULL,
  status CHAR(1) DEFAULT 'A'
);

3. Environment Variables


DATABASE_URL=postgres://user:password@host:5432/db
ALLOWED_TABLES=mast_country,mast_state,mast_status,user_template

4. Boot Flow


.env → Next.js → pg Pool → API Routes → React UI

🔗 API Documentation (Metadata-Driven)


GET /api/schema?table=mast_country
→ Fetches columns + PK from pg_catalog


GET /api/data?table=mast_country&limit=10&offset=0&search=ind
→ Paginated + filtered result set


POST /api/data?table=mast_country
PUT  /api/data?table=mast_country&id=12
DELETE /api/data?table=mast_country&id=12

API Execution Flow


Request
  ↓
Validate table
  ↓
Check allow-list
  ↓
Fetch schema
  ↓
Generate SQL
  ↓
Execute via pg
  ↓
Return JSON

🖥️ UI / Frontend

A single component DataTable.tsx dynamically generates forms and tables using:

Fetched schema
Detected primary key
Automatic validation
Visual pagination
Search bar

Where to Change Styles?

Global: app/globals.css
Component: components/DataTable.tsx Tailwind classes
Theme edits: tailwind.config.js

🔢 Status Codes

Code	Description
200	Request Successful
400	Bad Request / Invalid Table / Missing ID
404	Record Not Found
500	Database / Server Error

🚀 Features

The Master Data Engine is not a basic CRUD UI. It is a metadata-driven database control plane designed to behave like an ERP-grade master data console. All behavior is derived from PostgreSQL system catalogs at runtime.

Runtime Schema Introspection – Tables, columns, data types, and primary keys are discovered from pg_catalog, not hardcoded.
Auto Primary Key Locking – PK columns are detected and locked automatically in UI and API.
Allow-Listed Table Access – Only explicitly approved tables can be accessed, preventing lateral database access.
Dynamic SQL Generation – INSERT, UPDATE, DELETE statements are assembled at runtime using safe column metadata.
Schema-Driven UI Builder – Forms, grids, and validators are rendered from database metadata.
Search & Pagination Engine – Uses column-aware filters and offset-based pagination.
Zero-Code Table Onboarding – Adding a new table requires only SQL + allow-list entry.
Production-Grade API Gateway – Stateless REST APIs designed for cloud scaling.

🧱 Tech Stack & Architecture

This system is built as a metadata-driven admin engine. Instead of coding screens per table, the database itself becomes the control layer. The UI and APIs adapt in real time by reading PostgreSQL system catalogs.

Layer	Technology	Responsibility
Client	Next.js 14, React, TypeScript	Dynamic UI rendering, table selection, form generation
Styling	Tailwind CSS	Consistent enterprise UI styling
API Gateway	Next.js Route Handlers	Stateless metadata + CRUD APIs
Metadata Engine	pg_catalog, information_schema	Schema discovery, PK detection
Data Engine	node-postgres (pg pool)	Transactional database operations
Security Layer	Allow-list + env vars	Table-level access control
Deployment	Vercel Serverless	Cloud-native runtime


┌──────────────┐
│   Browser    │
└──────┬───────┘
       │
┌──────▼───────┐
│ Next.js UI  │  ← Dynamic DataTable
└──────┬───────┘
       │
┌──────▼──────────────┐
│ Next.js API Gateway │
│  /schema  /data     │
└──────┬──────────────┘
       │
┌──────▼───────┐
│  pg Pool    │
└──────┬───────┘
       │
┌──────▼──────────────┐
│ PostgreSQL Engine   │
│ pg_catalog + data   │
└────────────────────┘

🛠️ Workflow & Implementation

The entire platform is driven by database metadata. No UI or API logic is written per table. Everything is derived at runtime.

Full Operational Flow


User opens UI
      ↓
Selects a table
      ↓
UI calls /api/schema
      ↓
API queries pg_catalog
      ↓
Primary key + columns returned
      ↓
UI builds form + grid
      ↓
User edits data
      ↓
POST / PUT / DELETE sent
      ↓
API validates allow-list
      ↓
Dynamic SQL generated
      ↓
pg executes transaction
      ↓
JSON response sent back
      ↓
UI refreshes

CRUD Execution Pipeline


Request
  ↓
Table allowed?
  ↓
Fetch column metadata
  ↓
Exclude primary key
  ↓
Build SQL
  ↓
Bind parameters
  ↓
Execute via pg
  ↓
Return result

This design makes the system infinitely scalable to new tables without code changes.

🧪 Testing & Validation

ID	Area	Command/Test	Expected Output	Explanation
T1	API Schema	GET /api/schema?table=mast_country	column metadata JSON	Checks allow-list & schema generation
T2	Pagination	GET /api/data?limit=5	5 records + total	Verifies pagination logic
T3	Create	POST valid JSON	success:true	Tests insert
T4	Update	PUT with id	updated row JSON	Checks PK detection
T5	Delete	DELETE /api/data?id=PK	success:true	Validates deletion

🔍 Validation Summary

All CRUD functions operate generically.
Only allowed tables are accessible.
Primary keys correctly detected.
UI dynamically adapts to DB structure.

🧰 Verification Tools & Command Examples

curl "http://localhost:3000/api/schema?table=mast_country"
curl "http://localhost:3000/api/data?table=mast_country"

🧯 Troubleshooting & Debugging

500 error → Check DB connection in .env.
400 error → Table not in ALLOWED_TABLES.
Update not working → PK cannot be altered.
No UI changes appear → Restart: npm run dev.

🔒 Security & Secrets

Never commit .env.
Use Vercel encrypted variables.
Allow-list prevents unauthorized table access.
Optional: Implement RBAC in future.

☁️ Deployment (Vercel)

Push repo to GitHub
Import into Vercel
Add: DATABASE_URL under Env Variables
Deploy

⚡ Quick-Start Cheat Sheet

npm install
echo "DATABASE_URL=..." > .env
npm run dev

🧾 Usage Notes

To add new master table: Create SQL + add to allow-list + UI table list.
PK column must exist and be unique.
Search is substring, not regEx.

🧠 Performance & Optimization

Use pagination to limit results.
Use DB indexing on frequently searched columns.
Consider caching `/api/schema` in production.

🌟 Enhancements & Features (Future)

Role-based authentication
Audit logs
Field-level permissions
Data import/export
Column-based advanced filtering

🧩 Maintenance & Future Work

Keep DB + allow-list in sync.
Add monitoring for DB failures.
Test CRUD after every schema update.

🏆 Key Achievements

One UI for all master tables
Automatic PK detection
Enterprise-grade DB schema work
Secure Deployment & Whitelisting
Reusable CRUD architecture

🧮 High-Level Architecture

This platform is architected like an enterprise Master Data Management (MDM) engine, where PostgreSQL becomes the control plane and Next.js becomes the visualization layer.


+-------------------------+
|        User UI          |
|  Next.js DataTable      |
+-----------+-------------+
            |
            v
+-------------------------+
|  API Control Plane      |
|  /api/schema  /api/data |
+-----------+-------------+
            |
            v
+-------------------------+
|   PostgreSQL Metadata   |
|   + Business Tables     |
+-------------------------+

Information Flow


PostgreSQL → Schema → API → UI → User
User → Data → API → PostgreSQL

This two-way feedback loop allows the UI to always reflect the true database structure in real time.

🗂️ Folder Structure


nextjs-postgres-master-data-engine/
├─ app/
│  ├─ api/
│  │  ├─ schema/
│  │  │  └─ route.ts     ← PK + column discovery
│  │  └─ data/
│  │     └─ route.ts     ← Generic CRUD engine
│  ├─ page.tsx          ← Admin UI entry
│  ├─ layout.tsx
│  └─ globals.css
├─ components/
│  └─ DataTable.tsx     ← Schema-driven UI renderer
├─ lib/
│  ├─ db.ts             ← pg Pool
│  └─ tables.ts         ← Allow-listed tables
├─ .env
└─ package.json

🧭 How to Demonstrate Live

Open the app
Choose a master table
Create → Edit → Delete any record
Show search + pagination
Open Dev Tools → Network → Confirm API calls

💡 Summary, Closure & Compliance

The Master Table Administration System successfully delivers a scalable, secure, schema-driven CRUD application built without hard-coded forms or routes. The architecture complies with enterprise standards, provides production deployment workflows, and demonstrates robust engineering design for future extensibility.

Name		Name	Last commit message	Last commit date
Latest commit History 8 Commits
app		app
components		components
lib		lib
public		public
.gitignore		.gitignore
README.md		README.md
eslint.config.mjs		eslint.config.mjs
next.config.ts		next.config.ts
package-lock.json		package-lock.json
package.json		package.json
postcss.config.mjs		postcss.config.mjs
tailwind.config.js		tailwind.config.js
tsconfig.json		tsconfig.json

bitsandbrains/nextjs-postgres-metadata-admin-engine

Folders and files

Latest commit

History

Repository files navigation