📦 FichaYa — Modelo de Datos (D1)

Convención

Todas las tablas usan el prefijo fichaya_ para coexistir en la misma D1 de Cadences (projectos-db). IDs generados con formato {tipo}_{nanoid(12)} (ej: org_a8f3k2m9x1b4).

1. `fichaya_organizations` — Empresas/Organizaciones

CREATE TABLE IF NOT EXISTS fichaya_organizations (
  id TEXT PRIMARY KEY,                     -- "org_a8f3k2m9x1b4"
  
  -- Datos empresa
  name TEXT NOT NULL,                      -- "Bar El Rinconcito"
  legal_name TEXT,                         -- "Rinconcito S.L."
  cif TEXT,                                -- "B12345678" (NIF/CIF)
  sector TEXT,                             -- "hosteleria"|"comercio"|"construccion"|...
  employee_count_range TEXT,               -- "1-5"|"6-10"|"11-25"|"26-50"|"51-250"|"250+"
  
  -- Ubicación principal
  address TEXT,
  city TEXT,
  province TEXT,
  postal_code TEXT,
  country TEXT DEFAULT 'ES',
  latitude REAL,
  longitude REAL,
  
  -- Plan y facturación
  plan TEXT NOT NULL DEFAULT 'free',       -- "free"|"starter"|"pro"|"business"|"enterprise"
  plan_started_at TEXT,
  plan_expires_at TEXT,
  max_employees INTEGER NOT NULL DEFAULT 3,
  stripe_customer_id TEXT,                 -- Para facturación futura
  
  -- Configuración
  timezone TEXT DEFAULT 'Europe/Madrid',
  work_week_hours REAL DEFAULT 40.0,       -- Jornada semanal legal
  max_overtime_year REAL DEFAULT 80.0,     -- Horas extra máx anuales (ET art. 35)
  geofencing_enabled INTEGER DEFAULT 0,
  geofencing_radius_m INTEGER DEFAULT 500, -- Radio por defecto (metros)
  pause_tracking INTEGER DEFAULT 1,        -- Registrar pausas sí/no
  auto_clock_out_hours REAL DEFAULT 12.0,  -- Auto-cierre jornada a las Xh
  
  -- Código de acceso para empleados
  join_code TEXT UNIQUE,                   -- "RINCON-7K3M" — para unirse
  join_code_active INTEGER DEFAULT 1,      -- Código activo/desactivo
  
  -- Metadata
  owner_id TEXT NOT NULL,                  -- user_id del creador
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  deleted_at TEXT                           -- Soft delete
);

CREATE INDEX IF NOT EXISTS idx_orgs_owner ON fichaya_organizations(owner_id);
CREATE INDEX IF NOT EXISTS idx_orgs_join_code ON fichaya_organizations(join_code) WHERE join_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_orgs_plan ON fichaya_organizations(plan);
CREATE UNIQUE INDEX IF NOT EXISTS idx_orgs_cif ON fichaya_organizations(cif) WHERE cif IS NOT NULL;

2. `fichaya_users` — Usuarios (Admins + Empleados)

CREATE TABLE IF NOT EXISTS fichaya_users (
  id TEXT PRIMARY KEY,                     -- "usr_b3k2m9x1a8f4"
  
  -- Google SSO
  google_id TEXT UNIQUE NOT NULL,          -- Google sub ID
  email TEXT NOT NULL,
  name TEXT NOT NULL,                      -- Nombre completo de Google
  avatar_url TEXT,                         -- Foto de perfil de Google
  
  -- Datos laborales (rellenados tras unirse a org)
  org_id TEXT REFERENCES fichaya_organizations(id),
  role TEXT DEFAULT 'employee',            -- "owner"|"admin"|"manager"|"employee"
  department TEXT,                         -- "Cocina", "Sala", "Almacén"...
  position TEXT,                           -- "Camarero", "Chef", "Encargado"
  employee_code TEXT,                      -- Código interno empresa (opcional)
  workcenter_id TEXT,                      -- Centro asignado (FK workcenters)
  
  -- Contrato (para cálculos legales)
  contract_type TEXT,                      -- "full_time"|"part_time"|"temporary"|"intern"
  weekly_hours REAL,                       -- Horas/semana contratadas (null = usa las de org)
  start_date TEXT,                         -- Fecha inicio contrato
  end_date TEXT,                           -- Fecha fin (si temporal)
  
  -- Estado
  status TEXT DEFAULT 'active',            -- "active"|"inactive"|"pending_invite"
  last_clock_at TEXT,                      -- Último fichaje
  last_clock_type TEXT,                    -- "in"|"out"|"pause_start"|"pause_end"
  last_latitude REAL,                      -- Última posición conocida
  last_longitude REAL,
  
  -- Preferencias
  notifications_enabled INTEGER DEFAULT 1,
  locale TEXT DEFAULT 'es',
  
  -- Metadata
  invited_by TEXT,                         -- user_id que lo invitó
  joined_at TEXT,                          -- Cuándo se unió a la org
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  deleted_at TEXT                           -- Soft delete
);

CREATE INDEX IF NOT EXISTS idx_users_google ON fichaya_users(google_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON fichaya_users(email);
CREATE INDEX IF NOT EXISTS idx_users_org ON fichaya_users(org_id);
CREATE INDEX IF NOT EXISTS idx_users_org_role ON fichaya_users(org_id, role);
CREATE INDEX IF NOT EXISTS idx_users_org_status ON fichaya_users(org_id, status);
CREATE INDEX IF NOT EXISTS idx_users_workcenter ON fichaya_users(workcenter_id);

3. `fichaya_workcenters` — Centros de Trabajo

CREATE TABLE IF NOT EXISTS fichaya_workcenters (
  id TEXT PRIMARY KEY,                     -- "wc_x1b4a8f3k2m9"
  org_id TEXT NOT NULL REFERENCES fichaya_organizations(id),
  
  name TEXT NOT NULL,                      -- "Oficina Central", "Obra Calle Mayor"
  address TEXT,
  city TEXT,
  province TEXT,
  postal_code TEXT,
  
  -- Geolocalización
  latitude REAL NOT NULL,
  longitude REAL NOT NULL,
  geofence_radius_m INTEGER,              -- Override del radio de org (null = usa el de org)
  
  -- Configuración
  is_default INTEGER DEFAULT 0,           -- Centro por defecto
  is_active INTEGER DEFAULT 1,
  color TEXT,                              -- Color en mapa "#FF5733"
  icon TEXT,                               -- Emoji para pin "🏢" "🏗️" "🏪"
  
  -- Metadata
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now')),
  deleted_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_wc_org ON fichaya_workcenters(org_id);
CREATE INDEX IF NOT EXISTS idx_wc_org_active ON fichaya_workcenters(org_id, is_active);

4. `fichaya_clockings` — Fichajes (tabla principal)

Esta es la tabla más importante y la que requiere más cuidado. Cada fila es un evento de fichaje (entrada, salida, inicio pausa, fin pausa).

CREATE TABLE IF NOT EXISTS fichaya_clockings (
  id TEXT PRIMARY KEY,                     -- "clk_m9x1b4a8f3k2"
  org_id TEXT NOT NULL,                    -- Siempre presente para aislamiento
  user_id TEXT NOT NULL,
  
  -- Tipo de fichaje
  type TEXT NOT NULL,                      -- "in"|"out"|"pause_start"|"pause_end"
  
  -- Momento
  clock_time TEXT NOT NULL,                -- ISO 8601: "2026-04-08T08:32:15+02:00"
  clock_date TEXT NOT NULL,                -- "2026-04-08" (para queries por día)
  
  -- Geolocalización
  latitude REAL,
  longitude REAL,
  accuracy_m REAL,                         -- Precisión GPS en metros
  workcenter_id TEXT,                      -- Centro más cercano al fichar
  distance_to_center_m REAL,              -- Distancia al centro (null si no hay centro)
  is_within_geofence INTEGER,             -- 1 si dentro del radio, 0 si fuera, null si no aplica
  
  -- Contexto
  device_info TEXT,                        -- "Chrome/iOS" | "Firefox/Android" (para auditoría)
  ip_hash TEXT,                            -- Hash de IP (no la IP real, GDPR)
  is_offline_sync INTEGER DEFAULT 0,      -- Fichaje hecho offline y sincronizado después
  original_clock_time TEXT,               -- Si offline, hora original del dispositivo
  
  -- Estado
  is_corrected INTEGER DEFAULT 0,         -- Si este fichaje ha sido corregido
  correction_id TEXT,                      -- FK a la corrección que lo modificó
  
  -- Inmutabilidad
  -- ESTE REGISTRO NO SE MODIFICA NI ELIMINA NUNCA
  
  created_at TEXT DEFAULT (datetime('now'))
);

-- Índices críticos para rendimiento
CREATE INDEX IF NOT EXISTS idx_clk_org_date ON fichaya_clockings(org_id, clock_date);
CREATE INDEX IF NOT EXISTS idx_clk_user_date ON fichaya_clockings(user_id, clock_date);
CREATE INDEX IF NOT EXISTS idx_clk_org_user_date ON fichaya_clockings(org_id, user_id, clock_date);
CREATE INDEX IF NOT EXISTS idx_clk_org_type ON fichaya_clockings(org_id, type, clock_date);
CREATE INDEX IF NOT EXISTS idx_clk_user_time ON fichaya_clockings(user_id, clock_time);

4.1 Ejemplo de jornada típica

clk_001 | type: "in"          | 08:30 | lat/lng | dentro geofence
clk_002 | type: "pause_start" | 13:00 | lat/lng | —
clk_003 | type: "pause_end"   | 14:00 | lat/lng | —
clk_004 | type: "out"         | 17:30 | lat/lng | dentro geofence

→ Jornada: 9h total – 1h pausa = 8h efectivas

4.2 Cálculo de horas (lógica en Worker)

function calculateDayHours(clockings) {
  // Ordenar por clock_time
  // Sumar intervalos: in→out = trabajo, pause_start→pause_end = pausa
  // Total trabajo = suma(intervalos trabajo) - suma(intervalos pausa)
  // Si hay un "in" sin "out" → jornada abierta (en curso)
  // Si hay un "in" sin "out" y han pasado >12h → auto-cierre
}

5. `fichaya_corrections` — Correcciones de Fichaje

CREATE TABLE IF NOT EXISTS fichaya_corrections (
  id TEXT PRIMARY KEY,                     -- "cor_f3k2m9x1b4a8"
  org_id TEXT NOT NULL,
  clocking_id TEXT NOT NULL REFERENCES fichaya_clockings(id),
  
  -- Quién solicita
  requested_by TEXT NOT NULL,              -- user_id (empleado o admin)
  requested_at TEXT DEFAULT (datetime('now')),
  
  -- Qué se corrige
  field TEXT NOT NULL,                     -- "clock_time"|"type"|"delete"
  old_value TEXT,                          -- Valor anterior
  new_value TEXT,                          -- Valor nuevo
  reason TEXT NOT NULL,                    -- "Olvidé fichar salida", "Hora incorrecta"
  
  -- Aprobación
  status TEXT DEFAULT 'pending',           -- "pending"|"approved"|"rejected"
  reviewed_by TEXT,                        -- user_id (admin/manager)
  reviewed_at TEXT,
  review_note TEXT,                        -- Nota del reviewer
  
  -- Si se aprueba, se crea un NUEVO fichaje correcto (el original queda intacto)
  new_clocking_id TEXT,                    -- FK al fichaje correcto creado
  
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_cor_org ON fichaya_corrections(org_id, status);
CREATE INDEX IF NOT EXISTS idx_cor_clocking ON fichaya_corrections(clocking_id);
CREATE INDEX IF NOT EXISTS idx_cor_user ON fichaya_corrections(requested_by);

6. `fichaya_invitations` — Invitaciones

CREATE TABLE IF NOT EXISTS fichaya_invitations (
  id TEXT PRIMARY KEY,                     -- "inv_k2m9x1b4a8f3"
  org_id TEXT NOT NULL,
  
  -- Tipo
  type TEXT NOT NULL,                      -- "email"|"code"|"link"
  
  -- Email (si tipo = email)
  email TEXT,                              -- email del invitado
  
  -- Código/link
  code TEXT UNIQUE NOT NULL,               -- "RINCON-7K3M" o nanoid
  
  -- Configuración
  role TEXT DEFAULT 'employee',            -- Rol que tendrá al aceptar
  department TEXT,                         -- Departamento pre-asignado
  workcenter_id TEXT,                      -- Centro pre-asignado
  
  -- Estado
  status TEXT DEFAULT 'pending',           -- "pending"|"accepted"|"expired"|"revoked"
  accepted_by TEXT,                        -- user_id que aceptó
  accepted_at TEXT,
  
  -- Caducidad
  expires_at TEXT,                         -- 7 días por defecto
  max_uses INTEGER DEFAULT 1,             -- 1 para email, N para código compartido
  use_count INTEGER DEFAULT 0,
  
  -- Metadata
  created_by TEXT NOT NULL,                -- user_id del admin que invitó
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_inv_code ON fichaya_invitations(code);
CREATE INDEX IF NOT EXISTS idx_inv_org ON fichaya_invitations(org_id, status);
CREATE INDEX IF NOT EXISTS idx_inv_email ON fichaya_invitations(email);

7. `fichaya_audit_log` — Log de Auditoría

Inmutable. Solo INSERT. Nunca DELETE ni UPDATE.

CREATE TABLE IF NOT EXISTS fichaya_audit_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  org_id TEXT NOT NULL,
  
  -- Quién
  user_id TEXT NOT NULL,
  user_email TEXT,
  user_role TEXT,
  
  -- Qué
  action TEXT NOT NULL,                    -- "clock_in"|"clock_out"|"correction_request"|
                                           -- "correction_approve"|"user_invite"|"user_remove"|
                                           -- "org_update"|"workcenter_create"|...
  entity_type TEXT,                        -- "clocking"|"correction"|"user"|"org"|"workcenter"
  entity_id TEXT,
  
  -- Detalle
  details TEXT,                            -- JSON con datos relevantes
  
  -- Contexto
  ip_hash TEXT,
  device_info TEXT,
  
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_org ON fichaya_audit_log(org_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_user ON fichaya_audit_log(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_action ON fichaya_audit_log(org_id, action);

8. `fichaya_alerts` — Alertas y Notificaciones

CREATE TABLE IF NOT EXISTS fichaya_alerts (
  id TEXT PRIMARY KEY,
  org_id TEXT NOT NULL,
  
  -- Destinatario
  user_id TEXT NOT NULL,                   -- A quién va dirigida
  
  -- Contenido
  type TEXT NOT NULL,                      -- "missing_clock"|"overtime_day"|"overtime_week"|
                                           -- "open_shift"|"correction_pending"|
                                           -- "correction_resolved"|"geofence_breach"
  title TEXT NOT NULL,
  message TEXT,
  severity TEXT DEFAULT 'info',            -- "info"|"warning"|"critical"
  
  -- Estado
  is_read INTEGER DEFAULT 0,
  is_dismissed INTEGER DEFAULT 0,
  
  -- Referencia
  entity_type TEXT,
  entity_id TEXT,
  
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_alerts_user ON fichaya_alerts(user_id, is_read);
CREATE INDEX IF NOT EXISTS idx_alerts_org ON fichaya_alerts(org_id, created_at);

9. `fichaya_reports_cache` — Cache de Informes

CREATE TABLE IF NOT EXISTS fichaya_reports_cache (
  id TEXT PRIMARY KEY,
  org_id TEXT NOT NULL,
  
  -- Tipo y periodo
  report_type TEXT NOT NULL,               -- "monthly_org"|"monthly_user"|"legal"|"overtime"
  period TEXT NOT NULL,                    -- "2026-04" (mes) o "2026" (año)
  user_id TEXT,                            -- null para informes de org
  
  -- Datos
  data TEXT NOT NULL,                      -- JSON con el informe pre-calculado
  
  -- Metadata
  generated_at TEXT DEFAULT (datetime('now')),
  expires_at TEXT                           -- Cuándo recalcular
);

CREATE INDEX IF NOT EXISTS idx_reports_org ON fichaya_reports_cache(org_id, report_type, period);

10. Diagrama de Relaciones

fichaya_organizations
  │
  ├── 1:N → fichaya_users (org_id)
  │            │
  │            ├── 1:N → fichaya_clockings (user_id)
  │            │            │
  │            │            └── 1:N → fichaya_corrections (clocking_id)
  │            │
  │            └── 1:N → fichaya_alerts (user_id)
  │
  ├── 1:N → fichaya_workcenters (org_id)
  │
  ├── 1:N → fichaya_invitations (org_id)
  │
  ├── 1:N → fichaya_audit_log (org_id)
  │
  └── 1:N → fichaya_reports_cache (org_id)

11. Volumen Estimado (Año 1)

2.000 empresas × 8 empleados promedio × 22 días laborales × 4 fichajes/día
= 2.000 × 8 × 22 × 4 = 1.408.000 fichajes/mes

D1 writes: ~1,4M/mes = ~47K/día → OK en plan free (100K writes/día)
D1 reads:  ~5x writes (queries, reportes) = ~7M/mes → OK en plan free
Almacenamiento: ~200 bytes/fichaje × 1,4M/mes × 12 = ~3,4 GB/año → OK

12. Índices de Rendimiento Críticos

Las queries más frecuentes y sus índices:

Query	Frecuencia	Índice
Mis fichajes de hoy	Cada vez que empleado abre app	`idx_clk_user_date`
Lista en vivo (quién está)	Admin check cada minuto	`idx_clk_org_date` + filtro type="in"
Mapa de equipo	Admin abre dashboard	`idx_users_org_status` + last_lat/lng
Informe mensual por empleado	1x al mes	`idx_clk_org_user_date`
Correcciones pendientes	Admin revisa	`idx_cor_org` (status=pending)
Auditoría por fecha	Inspección o review	`idx_audit_org`

FichaYa — Modelo de Datos (D1)

📦 FichaYa — Modelo de Datos (D1)

Convención

1. fichaya_organizations — Empresas/Organizaciones

2. fichaya_users — Usuarios (Admins + Empleados)

3. fichaya_workcenters — Centros de Trabajo

4. fichaya_clockings — Fichajes (tabla principal)