🚀 DBScope - Universal SQL Database Dashboard

---

🧾 Project Description

DBScope is a full-stack application / REST API for exploring and managing external SQL databases without predefined entities for the target schema.

Core idea:

• metadata is loaded dynamically from external databases (schemas, tables, columns, PK/FK)
• data is read and written generically
• backend logic is not hardwired to one external database schema

The system uses two data layers:

• internal application DB (users, roles, connections, sharing, audit)
• external DB (user-defined SQL database)

Important

Currently only PostgreSQL is supported in the connector

---

✨ Application Features

🔐 Authentication and Roles

• Login: POST /api/auth/login (JWT access + refresh tokens)
• Current user: GET /api/auth/me
• Logout: POST /api/auth/logout
• Roles:
  • ADMIN - full access
  • READ_ONLY - read-only access

🔌 Connection Management

• Create/update/delete connections (ADMIN)
• Connection test
• Share connections from ADMIN to READ_ONLY users
• READ_ONLY users can work with shared connections

🧭 Metadata Explorer

• List schemas
• List tables in a schema
• Table detail (columns, primary key, foreign keys)

📊 Data Explorer

• Read table data
• Pagination (limit, offset)
• Sorting
• Search
• Column filtering

✍️ CRUD Operations on Data

• INSERT, UPDATE, DELETE (ADMIN)
• Dynamic row forms generated from table metadata
• Write actions are hidden for READ_ONLY users in the frontend UI

🧾 Audit Log

• Logs INSERT/UPDATE/DELETE
• Filters by connectionId and table
• Audit endpoint: GET /api/v1/audit

🎁 Additional Features

• Export visible table data to CSV (frontend)
• Sidebar includes:
  • app version,
  • current date/time,
  • last response time
• Modal pages for:
  • Release Notes,
  • Terms of Service,
  • Privacy Policy

---

🧱 Technology and Architecture

• Backend: Java 17, Spring Boot 4.0.3, Spring Web MVC, Spring Security, Spring Data JPA, JDBC, Bean Validation, Actuator, Springdoc OpenAPI (Swagger), Flexmark
• Frontend: React 19, TypeScript, Vite, Axios, React Router, TailwindCSS
• Database: PostgreSQL
• Containerization: Docker, Docker compose

🧠 Data Model and Access

• Internal DB (JPA): users, connections, sharing, audit
• External DB (JDBC): metadata + dynamic SQL
• Generic structures for external data:
  • Map<String, Object>
  • List<Map<String, Object>>

---

🐳 Docker Services

Service	Purpose	Container	Image/Build	Ports	Depends	Notes
db	Internal app PostgreSQL	dbscope-postgres-db	postgres:16-alpine	5431:5432	-	data persisted in db_data volume
backend	Spring Boot API	dbscope-backend	build from Dockerfile	8080:8080	db	prod profile, API under /api
frontend	React build served by Apache	dbscope-frontend	build from frontend/Dockerfile	80:80	-	UI at http://localhost

---

📁 Project Structure

DBScope/
|- src/main/java/cz/jpmad/dbscope
|  |- api
|  |- service
|  |- model
|  |- repository
|  |- config
|- src/main/resources
|  |- application.yml
|  |- application-dev.yml
|- frontend/
|  |- src/
|  |- Dockerfile
|- docker-compose.yml
|- docker-compose.dev.yml
|- Dockerfile
|- pom.xml
|- .env
|- VERSION

---

⚙️ Prerequisites

• Docker + Docker Compose
• Java 17 (for local backend run without Docker)
• Node.js 20+ and npm (for local frontend run)
• Free ports:
  • 80 (frontend)
  • 8080 (backend)
  • 5431 (internal PostgreSQL on host)
• Enough RAM (8+ GB recommended, minimum 6 GB)
• Additional disk space (2 GB for deployment itself + free space for internal DB)

Warning

Before running in containers, create and populate .env files. One file belongs in the root directory of the project, the other in the /frontend directory

---

🛠️ Installation (Dev and Prod Modes)

🧪 Dev Mode

Start only PostgreSQL for development:

git clone https://github.com/petrsafrata/DBScope.git
cd DBScope
docker compose -f docker-compose.dev.yml up -d

Then run backend locally:

.\mvnw.cmd spring-boot:run

Tip

Spring Boot uses the development settings from application.yml or application-dev.yml — no configuration is required.

Then run frontend locally:

cd frontend
npm install
npm run dev

Will typically run on http://localhost:5173 (or another port that Vite assigns)

Note

The frontend automatically communicates with the backend (localhost:8080)

🚀 Prod Mode (container runtime)

For full container runtime, download the latest project release first:

• https://github.com/petrsafrata/DBScope/releases

Then run the full application using docker-compose.yml:

cd DBScope
docker-compose --env-file .env up -d --build

🚀 Prod Mode: Backend API only

You can also run only the backend API as a container image from GitHub Packages (GHCR).

In this mode:

• pull backend image from GHCR (for example: ghcr.io/petrsafrata/dbscope-backend:<tag>),
• create your own docker-compose.yml,
• include and configure a separate container for the internal application database,
• configure backend environment variables (DB/JWT/profile).

Example image pull:

docker pull ghcr.io/petrsafrata/dbscope-backend:v1.0.0

Without an internal DB container (or an equivalent reachable DB service), backend API runtime will fail.

---

🌐 Access Points

Access Point	URL	Description
Frontend	http://localhost:80	Main user interface
Backend API	http://localhost:8080/api	Backend REST API base path
Swagger UI	http://localhost:8080/api/swagger-ui/index.html	Interactive API docs
OpenAPI JSON	http://localhost:8080/api/v3/api-docs	OpenAPI specification
Actuator	http://localhost:8080/api/actuator	Technical endpoints (health, info, metrics)
Internal PostgreSQL (from host)	localhost:5431	App internal DB mapped from container

Note

Backend uses context path /api; business endpoints are versioned under /api/v1/....

---

📚 API Endpoint Documentation (Swagger)

Use Swagger UI for API documentation:

• URL: http://localhost:8080/api/swagger-ui/index.html

Main endpoint groups:

• Auth - login, refresh, current user, logout
• Connections - connection CRUD, test, sharing
• Metadata - schemas, tables, table details
• Data - table reads and write operations
• Audit - audit records
• Info - version, release notes, terms, privacy

OpenAPI JSON:

• http://localhost:8080/api/v3/api-docs

---

🧯 Troubleshooting

Backend does not start

• Check logs

docker compose logs backend
docker compose logs frontend
docker compose logs db

Backend in Docker cannot access external DB

• If the external DB runs on your host machine, do not use localhost in connection settings.
• Use host host.docker.internal.

Login fails (401)

• Verify credentials.
• Check that frontend sends bearer token requests correctly.
• Verify JWT secret and token TTL configuration on backend.

Port already in use

• Free ports 80, 8080, 5431 or adjust mappings in docker-compose.yml.

External DB still unreachable

• Verify firewall, DB listener configuration (listen_addresses), DB access rules (pg_hba.conf for PostgreSQL), and port.

---

📉 Current Limitations

• External DB connector is currently limited to postgresql
• Pagination is offset/limit without total count
• Audit UI currently emphasizes basic columns
• Flyway is disabled in current setup (spring.flyway.enabled=false)

---

⚖️ Licence

This project is open-source and released under the Apache License 2.0. You are free to use, modify, distribute, and use it commercially under the terms of the Apache 2.0 license. See the LICENSE file for full details.

Apache-2.0 – Copyright (c) 2025 Petr Šafrata

---