data-entry

name: data-entry description: Expert data entry for the educational planning database. Use this skill to add, modify, or query users, areas, subjects, courses, schedules, coordination documents, and all other database entities.

Data Entry Expert - Educational Planning System

You are an expert data entry operator for this educational planning system. When the user asks to add, modify, or query data, execute the appropriate SQL commands.

Database Connection

psql "postgresql://postgres:postgres@localhost:5480/av3"

Complete Database Schema

1. users

Teachers and coordinators.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

2. areas

Subject groupings with a coordinator.

CREATE TABLE areas (
    id SERIAL PRIMARY KEY,
    coordinator_id INTEGER REFERENCES users(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

3. subjects

Individual subjects belonging to an area.

CREATE TABLE subjects (
    id SERIAL PRIMARY KEY,
    area_id INTEGER NOT NULL REFERENCES areas(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

4. courses

Student groups (e.g., "3a", "5b") with weekly schedule.

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    schedule JSONB,  -- Weekly schedule with time slots
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Schedule JSONB format:

{
  "monday": [
    {"time": "08:00-09:30", "subject": "Matemáticas"},
    {"time": "09:45-11:15", "subject": "Física"}
  ],
  "tuesday": [...],
  "wednesday": [
    {"time": "08:00-09:30", "subject": "Física", "shared_with": "Matemáticas"}
  ],
  "thursday": [...],
  "friday": [...]
}

Shared classes: Add "shared_with": "SubjectName" when two subjects are taught simultaneously.

Current courses: 3a (ID 1), 3b (ID 2), 5c (ID 3)

5. students

Students enrolled in a course.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    course_id INTEGER NOT NULL REFERENCES courses(id),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. course_subjects

Assignment of a teacher to teach a subject in a course.

CREATE TABLE course_subjects (
    id SERIAL PRIMARY KEY,
    course_id INTEGER NOT NULL REFERENCES courses(id),
    subject_id INTEGER NOT NULL REFERENCES subjects(id),
    teacher_id INTEGER NOT NULL REFERENCES users(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    school_year INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current assignments:

7. problematic_nuclei

High-level knowledge themes.

CREATE TABLE problematic_nuclei (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

8. knowledge_areas

Specific knowledge domains under nuclei.

CREATE TABLE knowledge_areas (
    id SERIAL PRIMARY KEY,
    nucleus_id INTEGER NOT NULL REFERENCES problematic_nuclei(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

9. categories

Granular skills/concepts to teach.

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    knowledge_area_id INTEGER NOT NULL REFERENCES knowledge_areas(id),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Current data:

10. coordination_documents

Planning documents created by coordinators.

CREATE TABLE coordination_documents (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    area_id INTEGER NOT NULL REFERENCES areas(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(50) DEFAULT 'draft',  -- draft, published, archived
    methodological_strategies TEXT,
    subjects_data JSONB,  -- Per-subject planning
    nucleus_ids INTEGER[] DEFAULT '{}',
    category_ids INTEGER[] DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

subjects_data JSONB format:

{
  "1": {  // subject_id as key
    "class_count": 5,
    "category_ids": [5, 6, 7],
    "class_plan": [
      {"class_number": 1, "title": "Introduction", "objective": "...", "category_ids": [5]},
      {"class_number": 2, "title": "Deep dive", "objective": "...", "category_ids": [5, 6]}
    ]
  },
  "2": {...}
}

11. moment_types

Types of class moments (apertura, desarrollo, cierre).

CREATE TABLE moment_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

12. activities

Didactic activities available for lesson planning.

CREATE TABLE activities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

13. teacher_lesson_plans

Individual lesson plans created by teachers.

CREATE TABLE teacher_lesson_plans (
    id SERIAL PRIMARY KEY,
    course_subject_id INTEGER NOT NULL REFERENCES course_subjects(id),
    coordination_document_id INTEGER NOT NULL REFERENCES coordination_documents(id),
    class_number INTEGER NOT NULL,
    title VARCHAR(255),
    category_ids INTEGER[] DEFAULT '{}',
    objective TEXT,
    knowledge_content TEXT,
    didactic_strategies TEXT,
    class_format VARCHAR(100),
    moments JSONB,  -- {apertura: {...}, desarrollo: {...}, cierre: {...}}
    status VARCHAR(50) DEFAULT 'pending',  -- pending, planned, completed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(course_subject_id, coordination_document_id, class_number)
);

Common Operations

Add a new user (teacher or coordinator)

INSERT INTO users (email, name) VALUES ('email@test.com', 'Full Name');

Add a new area

INSERT INTO areas (coordinator_id, name, description)
VALUES (1, 'Area Name', 'Description');

Add a new subject to an area

INSERT INTO subjects (area_id, name, description)
VALUES (1, 'Subject Name', 'Description');

Add a new course with schedule

INSERT INTO courses (name, schedule) VALUES (
    '4a',
    '{"monday": [{"time": "08:00-09:30", "subject": "Matemáticas"}], "tuesday": [], "wednesday": [], "thursday": [], "friday": []}'
);

Add a class slot to existing course schedule

UPDATE courses
SET schedule = jsonb_set(
    schedule,
    '{thursday}',
    (COALESCE(schedule->'thursday', '[]'::jsonb) || '[{"time": "10:00-11:30", "subject": "Biología"}]'::jsonb)
)
WHERE name = '3a';

Add a shared class slot

UPDATE courses
SET schedule = jsonb_set(
    schedule,
    '{friday}',
    (COALESCE(schedule->'friday', '[]'::jsonb) || '[{"time": "08:00-09:30", "subject": "Historia", "shared_with": "Literatura"}]'::jsonb)
)
WHERE name = '3b';

Add a student to a course

INSERT INTO students (course_id, name) VALUES (1, 'Student Name');

Assign a teacher to a subject in a course

INSERT INTO course_subjects (course_id, subject_id, teacher_id, start_date, end_date, school_year)
VALUES (1, 1, 3, '2026-03-01', '2026-12-15', 2026);

Create a coordination document

INSERT INTO coordination_documents (name, area_id, start_date, end_date, status, nucleus_ids, category_ids)
VALUES ('Document Name', 1, '2026-03-01', '2026-12-15', 'draft', '{2}', '{5,6,7}');

Update document subjects_data

UPDATE coordination_documents
SET subjects_data = '{"1": {"class_count": 5, "category_ids": [5,6], "class_plan": []}}'::jsonb
WHERE id = 1;

Publish a document

UPDATE coordination_documents SET status = 'published' WHERE id = 1;

Add a problematic nucleus

INSERT INTO problematic_nuclei (name, description) VALUES ('Name', 'Description');

Add a knowledge area

INSERT INTO knowledge_areas (nucleus_id, name, description) VALUES (1, 'Name', 'Description');

Add a category

INSERT INTO categories (knowledge_area_id, name, description) VALUES (1, 'Name', 'Description');

Query Reference Data

List all users

SELECT id, name, email FROM users ORDER BY id;

List all areas with coordinators

SELECT a.id, a.name, u.name as coordinator FROM areas a JOIN users u ON a.coordinator_id = u.id;

List all subjects with their areas

SELECT s.id, s.name, a.name as area FROM subjects s JOIN areas a ON s.area_id = a.id;

List all courses with their schedules

SELECT id, name, schedule FROM courses;

List teacher assignments

SELECT cs.id, c.name as course, s.name as subject, u.name as teacher
FROM course_subjects cs
JOIN courses c ON cs.course_id = c.id
JOIN subjects s ON cs.subject_id = s.id
JOIN users u ON cs.teacher_id = u.id;

List all categories with hierarchy

SELECT c.id, c.name, ka.name as knowledge_area, pn.name as nucleus
FROM categories c
JOIN knowledge_areas ka ON c.knowledge_area_id = ka.id
JOIN problematic_nuclei pn ON ka.nucleus_id = pn.id;

Important Notes

1. Foreign key constraints: Always verify that referenced IDs exist before inserting
2. Schedule format: Days must be lowercase: monday, tuesday, wednesday, thursday, friday
3. Shared classes: Both subjects in a shared class must belong to the same area
4. subjects_data keys: Use subject_id as string keys (e.g., "1", "2")
5. Status values: coordination_documents use 'draft', 'published', 'archived'
6. Array syntax: Use PostgreSQL array syntax '{1,2,3}' for integer arrays