# Database Schema Documentation

This document outlines the JSON database schema for ENERConnect.EU.

## Overview

ENERConnect uses a dual database approach:

1. **Supabase Database**
   - User authentication and profiles
   - Dynamic application data
   - Real-time features
   - API routing

2. **JSON Database (GitHub)**
   - Publishing quality information
   - Version-controlled content
   - Public research data
   - Educational resources

This document focuses on the JSON database schema.

## JSON Database Structure

```
json-database/
├── projects/        # Research projects
├── case-studies/    # Case studies
├── resources/       # Educational resources
└── metadata/        # System metadata
```

Each directory contains:
- `schema.json`: JSON Schema definition
- Data files in `.json` format

## Schema Definitions

### Projects Schema
```json
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "required": ["id", "title", "description", "created_at"],
  "properties": {
    "id": {
      "type": "string",
      "description": "Unique identifier for the project"
    },
    "title": {
      "type": "string",
      "minLength": 1,
      "maxLength": 200
    },
    "description": {
      "type": "string",
      "maxLength": 2000
    },
    "created_at": {
      "type": "string",
      "format": "date-time"
    },
    "updated_at": {
      "type": "string",
      "format": "date-time"
    },
    "tags": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "contributors": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "role": { "type": "string" }
        }
      }
    },
    "status": {
      "type": "string",
      "enum": ["draft", "active", "completed", "archived"]
    },
    "visibility": {
      "type": "string",
      "enum": ["public", "private", "limited"]
    }
  }
}
```

### Case Studies Schema
```json
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "required": ["id", "title", "content", "created_at"],
  "properties": {
    "id": {
      "type": "string"
    },
    "title": {
      "type": "string",
      "minLength": 1,
      "maxLength": 200
    },
    "content": {
      "type": "string"
    },
    "created_at": {
      "type": "string",
      "format": "date-time"
    },
    "author": {
      "type": "object",
      "properties": {
        "id": { "type": "string" },
        "name": { "type": "string" }
      }
    },
    "related_projects": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "tags": {
      "type": "array",
      "items": {
        "type": "string"
      }
    }
  }
}
```

### Resources Schema
```json
{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "type": "object",
  "required": ["id", "title", "type", "url"],
  "properties": {
    "id": {
      "type": "string"
    },
    "title": {
      "type": "string",
      "minLength": 1,
      "maxLength": 200
    },
    "type": {
      "type": "string",
      "enum": ["document", "dataset", "tool", "publication"]
    },
    "url": {
      "type": "string",
      "format": "uri"
    },
    "description": {
      "type": "string",
      "maxLength": 1000
    },
    "created_at": {
      "type": "string",
      "format": "date-time"
    },
    "tags": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "license": {
      "type": "string"
    }
  }
}
```

## File Organization

### Directory Structure
Each entity type has its own directory:
```
projects/
└── {project_id}.json

case-studies/
└── {case_study_id}.json

resources/
└── {resource_id}.json

metadata/
├── tags.json        # Global tag registry
├── statistics.json  # System statistics
└── config.json     # System configuration
```

## Data Validation

1. **Schema Validation**
   - All JSON files must conform to their respective schemas
   - Validation is performed on write operations
   - Invalid data is rejected with appropriate error messages

2. **Data Integrity**
   - References between entities are validated
   - Orphaned references are prevented
   - Circular references are detected

3. **Data Types**
   - Dates must be in ISO 8601 format
   - URLs must be valid and accessible
   - IDs must be unique within their scope

## Best Practices

1. **File Management**
   - Keep files under 1MB for optimal performance
   - Use compression for large text content
   - Implement regular cleanup of unused files

2. **Updates**
   - Use atomic writes to prevent corruption
   - Maintain a transaction log
   - Implement backup before write

3. **Performance**
   - Cache frequently accessed data
   - Use indexes for common queries
   - Implement pagination for large datasets

## Related Documentation
- [Architecture Overview](Architecture.md)
- [API Documentation](API-Documentation.md)
- [Contributing Guide](Contributing.md)