Database Setup - CCDigital

CCDigital uses MySQL 8 as its primary transactional database for managing persons, documents, access requests, and audit events.

Requirements

MySQL Version: 8.0 or higher
Storage Engine: InnoDB (default)
Character Set: utf8mb4
Collation: utf8mb4_unicode_ci
Timezone: UTC (recommended)

MySQL 8 is required for proper JSON support and modern security features.

Database Creation

Create Database

CREATE DATABASE ccdigital
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

Create Application User

CREATE USER 'ccdigital_user'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON ccdigital.* TO 'ccdigital_user'@'localhost';
FLUSH PRIVILEGES;

Use a strong password and restrict host access appropriately for production.

Verify Connection

mysql -u ccdigital_user -p -h localhost ccdigital

Schema Overview

The CCDigital database schema consists of core tables, views, stored procedures, and triggers.

Core Tables

Identity and Access

persons

Central table storing citizen identity information.Key Fields:

id: Primary key
id_type: Document type (CC, TI, etc.)
id_number: National ID number
first_name, last_name: Personal names
email: Contact email
created_at, updated_at: Timestamps

users

Application users linked to persons. Supports multiple user types (government, issuer, end-user).Key Fields:

id: Primary key
person_id: Foreign key to persons
username: Login username
password: Hashed password
role: User role (ROLE_GOBIERNO, ROLE_ISSUER, ROLE_USER)
access_state: User access state (ENABLED, SUSPENDED, DISABLED)
totp_enabled, totp_secret: TOTP configuration

entities

Organizations that can request document access (issuers).Key Fields:

id: Primary key
name: Entity name
entity_type: Type of organization
nit: Tax identification number

entity_users

Links users to entities for issuer role management.Key Fields:

entity_id: Foreign key to entities
user_id: Foreign key to users

Document Management

documents

Document type definitions (birth certificates, diplomas, etc.).Key Fields:

id: Primary key
name: Document type name
description: Document description
category_id: Foreign key to categories

person_documents

Links specific document instances to persons.Key Fields:

id: Primary key
person_id: Foreign key to persons
document_id: Foreign key to documents
review_status: Document approval status (PENDING, APPROVED, REJECTED)
uploaded_at: Upload timestamp
reviewed_at: Review timestamp
reviewed_by: Admin user who reviewed
blockchain_ref: Reference to Fabric ledger entry

files

Physical file metadata for stored documents.Key Fields:

id: Primary key
person_document_id: Foreign key to person_documents
storage_path: Relative path from base directory
sha256: File integrity hash
size_bytes: File size
version: Auto-incremented version (via trigger)
uploaded_at: Upload timestamp

Access Control

access_requests

Document access requests from entities to persons.Key Fields:

id: Primary key
entity_id: Foreign key to entities (requester)
person_id: Foreign key to persons (document owner)
status: Request status (PENDING, APPROVED, REJECTED)
purpose: Access justification
valid_from, valid_until: Access validity window
created_at: Request creation timestamp

access_request_items

Individual documents included in an access request.Key Fields:

id: Primary key
access_request_id: Foreign key to access_requests
person_document_id: Foreign key to person_documents

consents

User consent records for access grants.Key Fields:

id: Primary key
access_request_id: Foreign key to access_requests
granted: Boolean consent flag
granted_at: Consent timestamp

Audit and Traceability

audit_events

System audit trail for all sensitive operations.Key Fields:

id: Primary key
event_type: Type of event (LOGIN, DOCUMENT_ACCESS, STATUS_CHANGE, etc.)
user_id: Foreign key to users (actor)
person_id: Foreign key to persons (subject)
details: JSON event details
ip_address: Source IP
created_at: Event timestamp

Company Management

companies

Organization definitions for document templates.Key Fields:

id: Primary key
name: Company name
nit: Tax identification

company_document_definitions

Document templates defined by companies.

entity_document_definitions

Document templates accessible by entities.

Database Views

CCDigital includes optimized views for common queries:

v_documents

Consolidated view of all documents with category information.Purpose: Simplifies document listing and filtering queries.

v_person_full_documents

Complete document information joined with person data.Purpose: Provides full context for person-document relationships including review status, files, and blockchain references.

Stored Procedures

The database includes stored procedures for complex operations:

sp_add_person_document

Adds a new document association for a person.Parameters:

p_person_id: Person ID
p_document_id: Document type ID
p_uploaded_by: User ID of uploader

Usage: Ensures consistent document creation with proper defaults.

sp_create_user_with_person

Creates a new user account linked to an existing person.Parameters:

p_person_id: Existing person ID
p_username: Login username
p_password_hash: Pre-hashed password
p_role: User role

Usage: Atomic user creation ensuring referential integrity.

sp_upload_file_path

Records file metadata for a document using file path.Parameters:

p_person_document_id: Person document ID
p_storage_path: Relative file path
p_sha256: File hash
p_size_bytes: File size

Usage: Used by FileStorageService to persist file metadata.

sp_upload_pdf_blob

Stores PDF file as BLOB (legacy support).Note: Current implementation uses filesystem storage via sp_upload_file_path.

Triggers

trg_files_autoversion

Table: files
Event: BEFORE INSERTPurpose: Automatically increments file version number for the same person_document_id.Behavior:

Queries maximum existing version for the document
Sets new file version to MAX(version) + 1
Enables automatic file versioning without application logic

Schema Relationships

Key foreign key relationships:

persons (1) ─→ (N) users
persons (1) ─→ (N) person_documents
persons (1) ─→ (N) access_requests [as owner]

documents (1) ─→ (N) person_documents
categories (1) ─→ (N) documents

person_documents (1) ─→ (N) files
person_documents (1) ─→ (N) access_request_items

entities (1) ─→ (N) access_requests [as requester]
entities (1) ─→ (N) entity_users

access_requests (1) ─→ (N) access_request_items
access_requests (1) ─→ (1) consents

users (1) ─→ (N) audit_events
users (1) ─→ (N) entity_users

Importing Database Backup

If you have a database backup file, import it using MySQL command line:

mysql -h <HOST> -P <PORT> -u <USERNAME> -p <DATABASE_NAME> < backup.sql

Example:

mysql -h localhost -P 3306 -u ccdigital_user -p ccdigital < ccdigital_backup_20260307.sql

The backup should include schema definitions (tables, views, procedures, triggers) and optionally seed data.

JPA/Hibernate Configuration

CCDigital uses Spring Data JPA with Hibernate for database access.

Connection Configuration

Database connection is configured via environment variables:

spring.datasource.url=${DB_URL}
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}

Schema Management

In production, always use JPA_DDL_AUTO=none to prevent automatic schema modifications.

Hibernate schema management modes:

none (production): No automatic schema changes
validate: Validates entity mappings match schema
update (development only): Auto-updates schema
create (testing only): Drops and recreates schema

export JPA_DDL_AUTO='none'

SQL Logging

For debugging, enable SQL logging:

export JPA_SHOW_SQL='true'
export JPA_FORMAT_SQL='true'

Disable SQL logging in production for performance and security.

Entity Mapping

All database tables are mapped to JPA entities in:

src/main/java/co/edu/unbosque/ccdigital/entity/

Key entity classes:

Person.java → persons
User.java → users
PersonDocument.java → person_documents
FileRecord.java → files
AccessRequest.java → access_requests
Entity.java → entities
AuditEvent.java → audit_events

Database Maintenance

Backups

Create regular backups using mysqldump:

mysqldump -h localhost -u ccdigital_user -p ccdigital > ccdigital_backup_$(date +%Y%m%d).sql

For automated backups:

#!/bin/bash
BACKUP_DIR="/var/backups/ccdigital"
DATE=$(date +%Y%m%d_%H%M%S)
mysqldump -h localhost -u ccdigital_user -p${DB_PASSWORD} ccdigital | gzip > ${BACKUP_DIR}/ccdigital_${DATE}.sql.gz

Monitoring

Monitor key database metrics:

Connection pool usage
Slow query log
Table sizes and growth
Index usage
Replication lag (if using replication)

Optimization

Regularly analyze and optimize tables:

-- Analyze tables for query optimization
ANALYZE TABLE persons, users, person_documents, files, access_requests;

-- Optimize tables (reclaim space, defragment)
OPTIMIZE TABLE audit_events;

Connection Pooling

Spring Boot uses HikariCP as the default connection pool. Monitor pool health:

Active connections
Idle connections
Connection wait time
Connection acquisition timeout

Adjust pool size based on load:

# In application.properties (via environment variables if needed)
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000

Troubleshooting

Connection Refused

Problem: Application cannot connect to MySQL. Solutions:

Verify MySQL is running: systemctl status mysql
Check firewall rules allow port 3306
Verify DB_URL uses correct host and port
Test connection manually: mysql -h <HOST> -u <USER> -p

Access Denied

Problem: Authentication fails for database user. Solutions:

Verify username and password in DB_USERNAME and DB_PASSWORD
Check user grants: SHOW GRANTS FOR 'ccdigital_user'@'localhost';
Ensure user has privileges: GRANT ALL PRIVILEGES ON ccdigital.* TO 'ccdigital_user'@'localhost';

Slow Queries

Problem: Database queries taking too long. Solutions:

Enable slow query log in MySQL configuration
Analyze slow queries with EXPLAIN
Add missing indexes
Review JPA fetch strategies (avoid N+1 queries)

Disk Space

Problem: Database disk space exhausted. Solutions:

Archive old audit_events records
Purge binary logs: PURGE BINARY LOGS BEFORE '2026-01-01';
Optimize tables to reclaim space
Monitor files table growth (file metadata)

Setup

Blockchain Integration

​Requirements

​Database Creation

​Schema Overview

​Core Tables

​Identity and Access

persons

users

entities

entity_users

​Document Management

documents

categories

person_documents

files

​Access Control

access_requests

access_request_items

consents

​Audit and Traceability

audit_events

​Company Management

companies

company_document_definitions

entity_document_definitions

​Database Views

v_documents

v_person_full_documents

​Stored Procedures

sp_add_person_document

sp_create_user_with_person

sp_upload_file_path

sp_upload_pdf_blob

​Triggers

trg_files_autoversion

​Schema Relationships

​Importing Database Backup

​JPA/Hibernate Configuration

​Connection Configuration

​Schema Management

​SQL Logging

​Entity Mapping

​Database Maintenance

​Backups

​Monitoring

​Optimization

​Connection Pooling

​Troubleshooting

​Connection Refused

​Access Denied

​Slow Queries

​Disk Space

Build docs developers (and LLMs) love

Requirements

Database Creation

Schema Overview

Core Tables

Identity and Access

Document Management

Access Control

Audit and Traceability

Company Management

Database Views

Stored Procedures

Triggers

Schema Relationships

Importing Database Backup

JPA/Hibernate Configuration

Connection Configuration

Schema Management

SQL Logging

Entity Mapping

Database Maintenance

Backups

Monitoring

Optimization

Connection Pooling

Troubleshooting

Connection Refused

Access Denied

Slow Queries

Disk Space