Database Structure

Overview

SGD-MCS uses Google Sheets as its database, with each sheet (tab) representing a different entity table. The system supports students, faculty, thesis projects, events, institutions, and external participants.

The database uses a spreadsheet-based architecture with automatic ID generation and audit logging.

Database Connection

const SPREADSHEET_ID = '13DnE1bamQgWQ2G5cuGq9vdlP-tHu6QgdBtZptqKkLuc';

function getDB() {
    return SpreadsheetApp.openById(SPREADSHEET_ID);
}

Location: Backend/core/config.js:11-62

Entity Sheets

Estudiantes (Students)

Sheet Name: Estudiantes
Color: #4285F4 (Google Blue)
ID Prefix: EST

ID_Estudiante

string

required

Unique student identifier (e.g., EST0001)

Nombre1

string

required

First name (auto-normalized to Title Case)

Nombre2

string

Middle name (optional)

Apellido1

string

required

First last name

Apellido2

string

Second last name (optional)

string

required

Student email (auto-converted to lowercase)

Estado

enum

default:"Matriculado"

Student status: Matriculado, Egresado, Graduado

Cohorte_Ingreso

string

Enrollment cohort (e.g., “2023-1” for 2023 semester 1)

ID_Carpeta_Drive

string

Google Drive folder ID for student documents

URL_Carpeta_Drive

string

Public URL to student’s Drive folder

Fecha_Registro

datetime

Record creation timestamp (auto-generated)

Usuario_Registro

string

Email of user who created the record

Ultima_Actualizacion

datetime

Last modification timestamp

Ultimo_Usuario

string

Email of user who last modified the record

Docentes (Faculty)

Sheet Name: Docentes
Color: #FF9800 (Orange)
ID Prefix: DOC Structure similar to Estudiantes with faculty-specific fields:

ID_Docente - Faculty ID (e.g., DOC0001)
Nombre1, Nombre2, Apellido1, Apellido2 - Names
Email - Faculty email
Especialidad - Area of expertise
ID_Carpeta_Drive, URL_Carpeta_Drive - Drive integration
Audit fields (Fecha_Registro, Usuario_Registro, etc.)

ParticipantesExternos (External Participants)

Sheet Name: ParticipantesExternos
Color: #8BC34A (Light Green)
ID Prefix: EXT For external collaborators, industry experts, guest speakers:

ID_Externo - External participant ID (e.g., EXT0001)
Name and contact fields
Organizacion - Organization/company
Pais - Country
Drive and audit fields

Tesis (Thesis Projects)

Sheet Name: Tesis
Color: #9C27B0 (Purple)
ID Prefix: TES

ID_Tesis

string

required

Unique thesis identifier (e.g., TES0001)

Titulo_Investigacion

string

required

Thesis title (auto-normalized to Sentence case)

ID_Estudiante

string

required

Foreign key to Estudiantes sheet

ID_Director

string

Foreign key to Docentes sheet (thesis advisor)

ID_Codirector

string

Foreign key to Docentes sheet (co-advisor, optional)

Estado_Tesis

enum

default:"En Curso"

Thesis status: En Curso, Sustentada, Aprobada

Año

number

Year of thesis work (used for folder organization)

ID_Carpeta_Drive

string

Drive folder for thesis documents

Audit fields included.

Eventos (Events)

Sheet Name: Eventos
Color: #E91E63 (Pink)
ID Prefix: EVT

ID_Evento

string

required

Unique event identifier (e.g., EVT0001)

Nombre_Evento

string

required

Event name (auto-converted to UPPERCASE)

Tipo_Evento

enum

Event type: Seminario, Taller, Conferencia, Defensa, etc.

Fecha_Evento

date

Event date

ID_Carpeta_Drive

string

Drive folder for event documents (certificates, attendance, etc.)

Audit fields included.

Instituciones (Institutions)

Sheet Name: Instituciones
Color: #009688 (Teal)
ID Prefix: INS Partner universities, research centers, and organizations:

ID_Institucion - Institution ID
Nombre_Institucion - Institution name
Pais - Country
Tipo_Convenio - Partnership type
Contact information
Drive and audit fields

Relational Sheets

Participaciones (Event Participations)

Sheet Name: Participaciones
Color: #607D8B (Blue Grey) Many-to-many relationship between entities and events:

ID_Participacion

string

required

Unique participation record ID

ID_Evento

string

required

Foreign key to Eventos sheet

ID_Participante

string

required

Can reference ID_Estudiante, ID_Docente, or ID_Externo

Tipo_Participante

enum

required

Entity type: estudiante, docente, externo

Rol

string

Participation role: Asistente, Ponente, Organizador, etc.

Historial_Documentos (Document History)

Sheet Name: Historial_Documentos
Color: #3F51B5 (Indigo) Audit trail for all document and entity operations:

UUID

string

required

Unique audit record identifier (auto-generated)

Tipo_Documento

string

required

Action type: ENTITY_CREATE, ENTITY_UPDATE, UPLOAD_FILE, SYNC_FOLDER, etc.

ID_Beneficiario

string

Entity ID affected by the action

Nombre_Beneficiario

string

Name of affected entity

Detalle_Origen

string

Context: entity, file, folder

Detalles_JSON

json

Additional action details in JSON format

Fecha_Emision

datetime

required

Timestamp of action

Usuario_Emisor

string

required

Email of user who performed the action

Location: Backend/utils/Utils.js:96-128

System Configuration Sheet

Configuracion (System Config)

Sheet Name: Configuracion
Color: #263238 (Dark Grey) Stores ID counters and system settings:

Column A	Column B	Column C	Column D
Counter Name	Current Value	Description	Last Updated
Siguiente_ID_Estudiante	15	Student counter	2026-03-04
Siguiente_ID_Docente	8	Faculty counter	2026-03-01
Siguiente_ID_Externo	5	External counter	2026-02-28
Siguiente_ID_Tesis	12	Thesis counter	2026-03-03
Siguiente_ID_Evento	20	Event counter	2026-03-04

Do not manually edit counter values unless you understand the implications. The system auto-increments these atomically.

ID Generation System

Automatic ID generation uses the Configuration sheet:

function generateUniqueId(prefix, counterName) {
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    const configSheet = ss.getSheetByName(SHEETS.CONFIG);
    const data = configSheet.getDataRange().getValues();

    // Find counter row
    for (let i = 1; i < data.length; i++) {
        if (data[i][0] === counterName) {
            const currentVal = parseInt(data[i][1]);
            const rowIndex = i + 1;
            
            // Increment atomically
            const newVal = currentVal + 1;
            configSheet.getRange(rowIndex, 2).setValue(newVal);
            configSheet.getRange(rowIndex, 4).setValue(new Date());
            
            // Format: EST0001, DOC0045, etc.
            return `${prefix}${newVal.toString().padStart(4, '0')}`;
        }
    }
    
    throw new Error(`Counter '${counterName}' not found`);
}

Location: Backend/utils/Utils.js:13-40

Counter Mapping

Entity Type	Prefix	Counter Name
estudiante	EST	Siguiente_ID_Estudiante
docente	DOC	Siguiente_ID_Docente
externo	EXT	Siguiente_ID_Externo
tesis	TES	Siguiente_ID_Tesis
evento	EVT	Siguiente_ID_Evento

Data Normalization

The system automatically normalizes data before storage:

function normalizeData(type, data) {
    // Trim all strings
    for (const key in data) {
        if (typeof data[key] === 'string') data[key] = data[key].trim();
    }

    // Entity-specific normalization
    switch (type) {
        case 'estudiante':
        case 'docente':
        case 'externo':
            if (data.Nombre1) data.Nombre1 = toTitleCase(data.Nombre1);
            if (data.Apellido1) data.Apellido1 = toTitleCase(data.Apellido1);
            if (data.Email) data.Email = data.Email.toLowerCase();
            break;

        case 'evento':
            if (data.Nombre_Evento) data.Nombre_Evento = data.Nombre_Evento.toUpperCase();
            break;

        case 'tesis':
            if (data.Titulo_Investigacion) {
                let t = data.Titulo_Investigacion;
                data.Titulo_Investigacion = t.charAt(0).toUpperCase() + t.slice(1);
            }
            break;
    }

    return data;
}

Location: Backend/utils/Utils.js:57-89

Drive Integration

Each entity can have an associated Google Drive folder:

Folder Structure

SGD_DATABASE_ROOT/
├── Estudiantes/
│   ├── 2023-1/
│   │   ├── EST0001 - Juan Perez/
│   │   └── EST0002 - Maria Garcia/
│   └── 2023-2/
│       └── EST0003 - Carlos Lopez/
├── Docentes/
│   └── 2026/
│       └── DOC0001 - Dr. Ana Martinez/
├── Tesis/
│   ├── 2024/
│   │   └── TES0001 - Machine Learning Study/
│   └── 2025/
│       └── TES0002 - Education Platform/
└── Eventos/
    └── 2026/
        └── EVT0001 - SEMINARIO DE INVESTIGACION/

Folder Creation

function createEntityFolder(type, data) {
    const rootFolder = getSystemRootFolder();
    const typeFolderName = getSubfolderNameByType(type);
    const typeFolder = getOrCreateFolder(rootFolder, typeFolderName);
    
    // Organize by cohort/year
    let folderName = new Date().getFullYear().toString();
    
    if (type === 'estudiante' && data.Cohorte_Ingreso) {
        folderName = data.Cohorte_Ingreso; // e.g., "2023-1"
    }
    
    if (type === 'tesis' && data.Año) {
        folderName = data.Año.toString();
    }
    
    const yearFolder = getOrCreateFolder(typeFolder, folderName);
    
    // Entity folder name
    const id = data.ID_Estudiante || data.ID_Docente || data.ID_Tesis || data.ID_Evento;
    const label = data.Nombre1 ? `${data.Nombre1} ${data.Apellido1}` : 
                  (data.Titulo_Investigacion || data.Nombre_Evento);
    const entityName = `${id} - ${label}`.substring(0, 100);
    
    const finalFolder = getOrCreateFolder(yearFolder, entityName);
    
    return {
        id: finalFolder.getId(),
        url: finalFolder.getUrl()
    };
}

Location: Backend/services/DriveManager.js:73-130

Entity Relationships

CRUD Operations

All entity operations use the EntityManager:

Create

const result = createItem('estudiante', {
    Nombre1: 'Juan',
    Apellido1: 'Perez',
    Email: '[email protected]',
    Estado: 'Matriculado',
    Cohorte_Ingreso: '2023-1'
});

Location: Backend/core/EntityManager.js:9-76

Update

const result = updateItem('estudiante', 'EST0001', {
    Estado: 'Egresado'
});

Location: Backend/core/EntityManager.js:81-141

Delete

const result = deleteItem('estudiante', 'EST0001');

Location: Backend/core/EntityManager.js:146-177

Deletion moves the Drive folder to trash but does not permanently delete it. Restore is possible from Google Drive trash within 30 days.

Data Retrieval

Sheet data is converted to JSON arrays:

function getSimpleData(sheet) {
    if (!sheet) return [];
    const data = sheet.getDataRange().getDisplayValues();
    if (data.length < 2) return [];

    const headers = data.shift();
    
    return data.map(row => {
        let obj = {};
        headers.forEach((h, i) => {
            if (h) obj[h] = row[i];
        });
        return obj;
    });
}

Location: Backend/utils/DataUtils.js:9-25

Best Practices

Always Use Config Constants

Reference sheets using SHEETS.ESTUDIANTES instead of hardcoding "Estudiantes" to avoid typos and maintain consistency.

Never Skip Normalization

Always call normalizeData() before saving to ensure consistent formatting across the database.

Preserve Audit Fields

Let the system manage Fecha_Registro, Usuario_Registro, Ultima_Actualizacion, and Ultimo_Usuario automatically.

Validate Foreign Keys

When referencing IDs from other sheets (e.g., ID_Estudiante in Tesis), verify the record exists first.

Get Started

Core Features

User Guides

Administration

Database Structure

Overview

Database Connection

Entity Sheets

Estudiantes (Students)

Docentes (Faculty)

ParticipantesExternos (External Participants)

Tesis (Thesis Projects)

Eventos (Events)

Instituciones (Institutions)

Relational Sheets

Participaciones (Event Participations)

Historial_Documentos (Document History)

System Configuration Sheet

Configuracion (System Config)

ID Generation System

Counter Mapping

Data Normalization

Drive Integration

Folder Structure

Folder Creation

Entity Relationships

CRUD Operations

Create

Update

Delete

Data Retrieval

Best Practices

Next Steps

Configuration

Permissions

Build docs developers (and LLMs) love

Get Started

Core Features

User Guides

Administration

​Overview

​Database Connection

​Entity Sheets

​Estudiantes (Students)

​Docentes (Faculty)

​ParticipantesExternos (External Participants)

​Tesis (Thesis Projects)

​Eventos (Events)

​Instituciones (Institutions)

​Relational Sheets

​Participaciones (Event Participations)

​Historial_Documentos (Document History)

​System Configuration Sheet

​Configuracion (System Config)

​ID Generation System

​Counter Mapping

​Data Normalization

​Drive Integration

​Folder Structure

​Folder Creation

​Entity Relationships

​CRUD Operations

​Create

​Update

​Delete

​Data Retrieval

​Best Practices

​Next Steps

Configuration

Permissions

Build docs developers (and LLMs) love

Overview

Database Connection

Entity Sheets

Estudiantes (Students)

Docentes (Faculty)

ParticipantesExternos (External Participants)

Tesis (Thesis Projects)

Eventos (Events)

Instituciones (Institutions)

Relational Sheets

Participaciones (Event Participations)

Historial_Documentos (Document History)

System Configuration Sheet

Configuracion (System Config)

ID Generation System

Counter Mapping

Data Normalization

Drive Integration

Folder Structure

Folder Creation

Entity Relationships

CRUD Operations

Create

Update

Delete

Data Retrieval

Best Practices

Next Steps