Goa-gel-fullstack/backend/QUICK_START.md

# Goa GEL Database - Quick Start Guide

## 5-Minute Setup

### 1. Install Dependencies
```bash
npm install typeorm pg uuid crypto dotenv ts-node
```

### 2. Create `.env` File
```env
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_USER=postgres
DATABASE_PASSWORD=your_password
DATABASE_NAME=goa_gel_db
DATABASE_LOGGING=true
DATABASE_SSL=false
NODE_ENV=development
```

### 3. Create Database
```bash
createdb goa_gel_db
```

### 4. Run Migrations
```bash
npx typeorm migration:run -d src/database/data-source.ts
```

### 5. Seed Sample Data
```bash
npx ts-node src/database/seeders/seed.ts
```

### 6. Verify
```bash
psql goa_gel_db -c "\dt"
```

## Database Structure

### 12 Core Entities

```
Applicant (License applicants)
  ├── id, digilockerId, name, email, phone, walletAddress

Department (Government departments)
  ├── id, code, name, walletAddress, apiKeyHash, webhookUrl

Workflow (Multi-stage workflows)
  ├── id, workflowType, definition (JSONB), stages

LicenseRequest (Main entity)
  ├── id, requestNumber, applicantId, workflowId
  ├── status (8 values), metadata, currentStageId

Document (Uploaded files)
  ├── id, requestId, docType, currentHash (SHA-256)

DocumentVersion (File versions)
  ├── id, documentId, version, hash, fileSize

Approval (Department approvals)
  ├── id, requestId, departmentId
  ├── status (5 values), remarks, blockchainTxHash

WorkflowState (Execution tracking)
  ├── id, requestId, currentStageId, executionLog (JSONB)

Webhook (Webhook configs)
  ├── id, departmentId, url, events (JSONB)

WebhookLog (Webhook audit)
  ├── id, webhookId, eventType, status, retryCount

AuditLog (Change tracking)
  ├── id, entityType, entityId, action, oldValue, newValue

BlockchainTransaction (NFT minting)
  ├── id, txHash, txType, status, blockNumber
```

## Key Features

### Status Tracking
- **LicenseRequest**: DRAFT, SUBMITTED, IN_REVIEW, PENDING_RESUBMISSION, APPROVED, REJECTED, REVOKED, CANCELLED
- **Approval**: PENDING, APPROVED, REJECTED, CHANGES_REQUESTED, REVIEW_REQUIRED
- **WebhookLog**: PENDING, SUCCESS, FAILED
- **BlockchainTransaction**: PENDING, CONFIRMED, FAILED

### Workflow Stages (RESORT_LICENSE)
1. Fire Safety Approval (FIRE_DEPT)
2. Tourism Clearance (TOURISM_DEPT)
3. Health Department Approval (HEALTH_DEPT)
4. Municipal Approval (MUNI_DEPT)
5. License Issuance (System Action - NFT Minting)

### Sample Data After Seeding
- 4 Departments (Fire, Tourism, Municipal, Health)
- 2 Applicants
- 1 RESORT_LICENSE Workflow with 5 stages
- 1 License Request in DRAFT status

## TypeORM Commands

```bash
# Run migrations
npx typeorm migration:run -d src/database/data-source.ts

# Generate migration from entity changes
npx typeorm migration:generate -d src/database/data-source.ts -n MigrationName

# Revert last migration
npx typeorm migration:revert -d src/database/data-source.ts

# Show migration status
npx typeorm migration:show -d src/database/data-source.ts

# Sync schema (development only)
npx typeorm schema:sync -d src/database/data-source.ts

# Drop database schema
npx typeorm schema:drop -d src/database/data-source.ts
```

## Common Queries

### Find Applicant with Requests
```typescript
const applicant = await applicantRepository.findOne({
  where: { id: applicantId },
  relations: ['licenseRequests'],
});
```

### Get Pending Approvals
```typescript
const pending = await approvalRepository.find({
  where: {
    status: ApprovalStatus.PENDING,
    departmentId: deptId,
    isActive: true
  },
  relations: ['request', 'department'],
  order: { createdAt: 'ASC' }
});
```

### Find License Request with Details
```typescript
const request = await licenseRequestRepository.findOne({
  where: { id: requestId },
  relations: [
    'applicant',
    'workflow',
    'documents',
    'documents.versions',
    'approvals',
    'approvals.department',
    'workflowState'
  ]
});
```

### Get Audit Trail
```typescript
const auditTrail = await auditLogRepository.find({
  where: { entityId: entityId },
  order: { createdAt: 'DESC' },
  take: 100
});
```

### Track Blockchain Transactions
```typescript
const txs = await blockchainTransactionRepository.find({
  where: { relatedEntityId: requestId },
  order: { createdAt: 'DESC' }
});
```

## File Structure

```
/src/database/
├── entities/              # 12 entity files + types
│   ├── applicant.entity.ts
│   ├── department.entity.ts
│   ├── workflow.entity.ts
│   ├── license-request.entity.ts
│   ├── document.entity.ts
│   ├── document-version.entity.ts
│   ├── approval.entity.ts
│   ├── workflow-state.entity.ts
│   ├── webhook.entity.ts
│   ├── webhook-log.entity.ts
│   ├── audit-log.entity.ts
│   ├── blockchain-transaction.entity.ts
│   ├── types.ts
│   └── index.ts
├── migrations/
│   └── 1704067200000-InitialSchema.ts
├── seeders/
│   └── seed.ts
├── data-source.ts
├── index.ts
└── README.md
```

## Indexes (40+ Total)

### Applicant Indexes
- digilockerId, walletAddress, email

### LicenseRequest Indexes
- requestNumber, applicantId, workflowId, status, createdAt
- Composite: (applicantId, status)

### Approval Indexes
- requestId, departmentId, status
- Composite: (requestId, departmentId), (requestId, status)

### Document Indexes
- requestId, currentHash
- Composite: (requestId, docType)

### AuditLog Indexes
- entityType, entityId, action, actorType, createdAt
- Composite: (entityType, entityId), (actorId, createdAt)

### BlockchainTransaction Indexes
- txHash, status, txType, relatedEntityId, createdAt
- Composite: (status, txType)

### WebhookLog Indexes
- webhookId, eventType, status, createdAt
- Composite: (webhookId, status)

## Environment Variables

```env
# Database Connection
DATABASE_HOST=localhost          # PostgreSQL host
DATABASE_PORT=5432              # PostgreSQL port
DATABASE_USER=postgres           # DB username
DATABASE_PASSWORD=***            # DB password
DATABASE_NAME=goa_gel_db         # Database name

# Application
NODE_ENV=development             # development|production
DATABASE_LOGGING=true            # Enable query logging
DATABASE_SSL=false               # SSL connection
```

## Sample SQL Queries

### Get Applicant with Active Requests
```sql
SELECT a.*, COUNT(lr.id) as request_count
FROM applicants a
LEFT JOIN license_requests lr ON a.id = lr.applicantId AND lr.status != 'CANCELLED'
WHERE a.isActive = true
GROUP BY a.id;
```

### Get Workflow Progress
```sql
SELECT
  lr.requestNumber,
  lr.status,
  ws.currentStageId,
  COUNT(CASE WHEN a.status = 'APPROVED' THEN 1 END) as approved_count,
  COUNT(CASE WHEN a.status = 'PENDING' THEN 1 END) as pending_count
FROM license_requests lr
JOIN workflow_states ws ON lr.id = ws.requestId
LEFT JOIN approvals a ON lr.id = a.requestId AND a.isActive = true
GROUP BY lr.id, ws.id;
```

### Get Department Statistics
```sql
SELECT
  d.code,
  d.name,
  COUNT(a.id) as total_approvals,
  COUNT(CASE WHEN a.status = 'PENDING' THEN 1 END) as pending,
  COUNT(CASE WHEN a.status = 'APPROVED' THEN 1 END) as approved,
  COUNT(CASE WHEN a.status = 'REJECTED' THEN 1 END) as rejected
FROM departments d
LEFT JOIN approvals a ON d.id = a.departmentId AND a.isActive = true
GROUP BY d.id;
```

### Get Recent Audit Trail
```sql
SELECT *
FROM audit_logs
WHERE entityId = $1
ORDER BY createdAt DESC
LIMIT 100;
```

## Troubleshooting

### Database Won't Connect
```bash
# Check if PostgreSQL is running
sudo systemctl status postgresql

# Test connection
psql -h localhost -U postgres -c "SELECT 1"
```

### Migration Failed
```bash
# Check migration status
npx typeorm migration:show -d src/database/data-source.ts

# Revert problematic migration
npx typeorm migration:revert -d src/database/data-source.ts

# Check for entity/migration conflicts
ls -la src/database/entities/
ls -la src/database/migrations/
```

### Seeding Failed
```bash
# Drop and recreate
npx typeorm schema:drop -d src/database/data-source.ts
npx typeorm migration:run -d src/database/data-source.ts
npx ts-node src/database/seeders/seed.ts
```

### Check Database
```bash
# Connect to database
psql goa_gel_db

# List tables
\dt

# List indexes
\di

# Check constraint
\d license_requests

# View migration history
SELECT * FROM typeorm_migrations;
```

## Performance Tips

1. Always use indexes for WHERE clauses
2. Use relations only when needed
3. Use pagination for large result sets
4. Cache workflow definitions
5. Batch document uploads
6. Monitor slow queries

## Next Steps

1. Configure your application to use the database
2. Create repositories for each entity
3. Implement business logic services
4. Add API endpoints
5. Set up webhook listeners
6. Implement blockchain integration

## Support Files

- `/src/database/README.md` - Detailed documentation
- `/DATABASE_SETUP.md` - Complete setup guide
- `/src/database/entities/types.ts` - TypeScript interfaces