jayjennings
61c876edec
- Implemented Form301 component for displaying OSHA Form 301 equivalent, including employee and case information. - Created Osha component to manage OSHA recordkeeping, including tabs for 300 Log, 300A Summary, Hours & rates, and Submit to OSHA. - Added functionality for printing and downloading CSV files for OSHA submissions. - Integrated loading and error handling states for data fetching.
231 lines
11 KiB
TypeScript
231 lines
11 KiB
TypeScript
// Loads demo data into an empty database: `npm run db:seed`. Not for production use.
|
||
import { hashPassword } from '../auth/password.ts'
|
||
import { loadConfig } from '../config.ts'
|
||
import { openDatabase } from './client.ts'
|
||
import { assessRecordability } from '../../shared/osha.ts'
|
||
import { auditLog, areas, correctiveActions, hoursWorked, incidentCounters, incidents, injuredPersons, investigations, organizations, rootCauses, sites, users } from './schema.ts'
|
||
|
||
const DEMO_PASSWORD = 'safetylog-demo'
|
||
|
||
const config = loadConfig()
|
||
const { db, close } = await openDatabase(config.databaseUrl)
|
||
|
||
try {
|
||
if ((await db.select({ id: users.id }).from(users).limit(1)).length > 0) {
|
||
console.error('Database already has users; seeding is only for empty databases.')
|
||
process.exitCode = 1
|
||
} else {
|
||
await db.transaction(async (tx) => {
|
||
const [org] = await tx.insert(organizations).values({ name: 'Northstar Co.' }).returning()
|
||
const passwordHash = await hashPassword(DEMO_PASSWORD)
|
||
const people = await tx
|
||
.insert(users)
|
||
.values([
|
||
{ name: 'Jordan Lee', email: 'admin@example.com', role: 'admin' as const },
|
||
{ name: 'Priya Shah', email: 'manager@example.com', role: 'safety_manager' as const },
|
||
{ name: 'Sam Rivera', email: 'supervisor@example.com', role: 'supervisor' as const },
|
||
{ name: 'Maya Chen', email: 'reporter@example.com', role: 'reporter' as const },
|
||
].map((u) => ({ ...u, organizationId: org.id, passwordHash })))
|
||
.returning()
|
||
const person = (email: string) => people.find((p) => p.email === email)!.id
|
||
|
||
const layout: Record<string, string[]> = {
|
||
'North Campus': ['Building A', 'Warehouse 04', 'Lab 2'],
|
||
'South Campus': ['Fabrication 02', 'Yard', 'Office block'],
|
||
'West Campus': ['Warehouse 01', 'Loading dock'],
|
||
}
|
||
const areaIds: Record<string, { siteId: string; areaId: string }> = {}
|
||
for (const [siteName, buildings] of Object.entries(layout)) {
|
||
const [site] = await tx.insert(sites).values({ organizationId: org.id, name: siteName }).returning()
|
||
for (const name of buildings) {
|
||
const [area] = await tx.insert(areas).values({ siteId: site.id, name, kind: 'building' }).returning()
|
||
areaIds[name] = { siteId: site.id, areaId: area.id }
|
||
}
|
||
}
|
||
|
||
const daysAgo = (days: number) => new Date(Date.now() - days * 86_400_000)
|
||
const demo = [
|
||
{ title: 'Damaged guardrail on mezzanine', type: 'property_damage', severity: 'low', status: 'closed', building: 'Warehouse 01', locationDetail: 'Mezzanine east', reporter: 'manager@example.com', days: 9 },
|
||
{ title: 'Hand laceration during material handling', type: 'injury', severity: 'high', status: 'investigation', building: 'Fabrication 02', locationDetail: 'Line 3', reporter: 'supervisor@example.com', days: 7 },
|
||
{ title: 'Wet floor near employee entrance', type: 'hazard', severity: 'medium', status: 'actions', building: 'Building A', locationDetail: 'Ground floor lobby', reporter: 'reporter@example.com', days: 6 },
|
||
{ title: 'Forklift reversed into pallet stack', type: 'near_miss', severity: 'high', status: 'triage', building: 'Warehouse 04', locationDetail: 'Loading bay C', reporter: 'admin@example.com', days: 5 },
|
||
] as const
|
||
const created = await tx
|
||
.insert(incidents)
|
||
.values(
|
||
demo.map((item, index) => ({
|
||
organizationId: org.id,
|
||
number: index + 1,
|
||
title: item.title,
|
||
type: item.type,
|
||
severity: item.severity,
|
||
status: item.status,
|
||
occurredAt: daysAgo(item.days),
|
||
createdAt: new Date(daysAgo(item.days).getTime() + 2 * 3_600_000),
|
||
...areaIds[item.building],
|
||
locationDetail: item.locationDetail,
|
||
reporterId: person(item.reporter),
|
||
closedAt: item.status === 'closed' ? daysAgo(item.days - 3) : null,
|
||
})),
|
||
)
|
||
.returning()
|
||
await tx.insert(auditLog).values(
|
||
created.map((incident) => ({
|
||
organizationId: org.id,
|
||
actorId: incident.reporterId,
|
||
entityType: 'incident',
|
||
entityId: incident.id,
|
||
action: 'create',
|
||
changes: { number: incident.number, title: incident.title },
|
||
createdAt: incident.createdAt,
|
||
})),
|
||
)
|
||
// A year of history so dashboards and the OSHA log have something to show
|
||
const lastNumber = await seedHistory(tx as unknown as typeof db, org.id, demo.length, areaIds, people.map((p) => p.id))
|
||
await tx.insert(incidentCounters).values({ organizationId: org.id, lastNumber })
|
||
})
|
||
console.log(`Seeded demo data. Sign in as admin@example.com, manager@example.com, supervisor@example.com or reporter@example.com with password "${DEMO_PASSWORD}".`)
|
||
}
|
||
} finally {
|
||
await close()
|
||
}
|
||
|
||
/** Deterministic pseudo-random numbers, so every seed produces the same demo. */
|
||
function random(seed: number) {
|
||
let state = seed
|
||
return () => {
|
||
state = (state * 1_664_525 + 1_013_904_223) % 2 ** 32
|
||
return state / 2 ** 32
|
||
}
|
||
}
|
||
|
||
async function seedHistory(
|
||
database: typeof db,
|
||
organizationId: string,
|
||
startNumber: number,
|
||
areaIds: Record<string, { siteId: string; areaId: string }>,
|
||
userIds: string[],
|
||
): Promise<number> {
|
||
const rand = random(42)
|
||
const pick = <T>(items: readonly T[]) => items[Math.floor(rand() * items.length)]
|
||
const day = 86_400_000
|
||
const now = Date.now()
|
||
const locations = Object.values(areaIds)
|
||
// Near misses and hazards outnumber injuries, as in a healthy reporting culture
|
||
const types = ['near_miss', 'near_miss', 'near_miss', 'hazard', 'hazard', 'hazard', 'injury', 'injury', 'property_damage', 'environmental', 'illness'] as const
|
||
// Busier around shift changes (06–08, 14–16) and mid-shift
|
||
const hours = [6, 7, 7, 9, 10, 11, 13, 14, 14, 15, 15, 16, 19, 22, 2]
|
||
const titles = {
|
||
near_miss: ['Forklift nearly struck pedestrian', 'Load slipped from pallet jack', 'Unsecured ladder almost fell', 'Dropped tool from mezzanine'],
|
||
hazard: ['Oil leak under press', 'Blocked emergency exit', 'Damaged guard on conveyor', 'Frayed extension cord', 'Missing machine guard'],
|
||
injury: ['Hand laceration from sheet metal', 'Back strain lifting boxes', 'Slip on wet floor', 'Pinched finger in die'],
|
||
illness: ['Dermatitis from cutting fluid'],
|
||
property_damage: ['Forklift hit racking', 'Damaged dock door'],
|
||
environmental: ['Hydraulic fluid spill', 'Coolant leak to drain'],
|
||
} as const
|
||
const categories = ['people', 'process', 'equipment', 'materials', 'environment', 'management'] as const
|
||
|
||
let number = startNumber
|
||
for (let i = 0; i < 150; i++) {
|
||
const type = pick(types)
|
||
const occurred = new Date(now - Math.floor(rand() * 360 + 3) * day)
|
||
occurred.setHours(pick(hours), Math.floor(rand() * 60), 0, 0)
|
||
const created = new Date(occurred.getTime() + (1 + rand() * 6) * 3_600_000)
|
||
const age = (now - occurred.getTime()) / day
|
||
const status = age > 60 ? (rand() < 0.85 ? 'closed' : 'actions') : age > 20 ? pick(['investigation', 'actions', 'verification', 'closed'] as const) : pick(['reported', 'triage', 'investigation'] as const)
|
||
const closedAt = status === 'closed' ? new Date(created.getTime() + (5 + rand() * 35) * day) : null
|
||
number++
|
||
const [incident] = await database
|
||
.insert(incidents)
|
||
.values({
|
||
organizationId,
|
||
number,
|
||
title: pick(titles[type]),
|
||
type,
|
||
severity: type === 'injury' ? pick(['medium', 'high', 'high'] as const) : pick(['low', 'medium', 'medium', 'high'] as const),
|
||
status,
|
||
occurredAt: occurred,
|
||
createdAt: created,
|
||
closedAt: closedAt && closedAt.getTime() < now ? closedAt : null,
|
||
...pick(locations),
|
||
reporterId: pick(userIds),
|
||
})
|
||
.returning()
|
||
const auditRows: (typeof auditLog.$inferInsert)[] = [
|
||
{ organizationId, actorId: incident.reporterId, entityType: 'incident', entityId: incident.id, action: 'create', changes: { number, title: incident.title }, createdAt: created },
|
||
]
|
||
if (status !== 'reported') {
|
||
// Most reports are triaged within a day; the team got quicker over the year
|
||
const delayHours = rand() < 0.55 + (1 - age / 365) * 0.35 ? 2 + rand() * 18 : 26 + rand() * 70
|
||
auditRows.push({ organizationId, actorId: pick(userIds), entityType: 'incident', entityId: incident.id, action: 'status', changes: { status: ['reported', 'triage'] }, createdAt: new Date(created.getTime() + delayHours * 3_600_000) })
|
||
}
|
||
await database.insert(auditLog).values(auditRows)
|
||
|
||
if (['investigation', 'actions', 'verification', 'closed'].includes(status) && type !== 'near_miss') {
|
||
const [investigation] = await database.insert(investigations).values({ incidentId: incident.id, method: 'five_whys', startedAt: created }).returning()
|
||
const causes = await database
|
||
.insert(rootCauses)
|
||
.values(Array.from({ length: 1 + Math.floor(rand() * 2) }, () => ({ investigationId: investigation.id, category: pick(categories), description: 'Identified during investigation' })))
|
||
.returning()
|
||
const due = new Date(created.getTime() + 21 * day)
|
||
const done = status === 'closed' || status === 'verification' || rand() < 0.4
|
||
const completed = new Date(created.getTime() + (8 + rand() * 30) * day)
|
||
await database.insert(correctiveActions).values({
|
||
incidentId: incident.id,
|
||
rootCauseId: causes[0].id,
|
||
title: 'Fix the cause and brief the team',
|
||
assigneeId: pick(userIds),
|
||
dueDate: due.toISOString().slice(0, 10),
|
||
status: done && completed.getTime() < now ? (status === 'closed' ? 'verified' : 'done') : 'open',
|
||
completedAt: done && completed.getTime() < now ? completed : null,
|
||
})
|
||
}
|
||
|
||
if ((type === 'injury' || type === 'illness') && rand() < 0.6) {
|
||
const answers = {
|
||
employmentType: 'employee' as const,
|
||
workRelated: true,
|
||
newCase: true,
|
||
died: false,
|
||
daysAwayFromWork: rand() < 0.3,
|
||
restrictedOrTransfer: rand() < 0.4,
|
||
medicalTreatment: rand() < 0.7,
|
||
lossOfConsciousness: false,
|
||
significantDiagnosis: false,
|
||
specialCase: 'none' as const,
|
||
}
|
||
const verdict = assessRecordability(answers)
|
||
await database.insert(injuredPersons).values({
|
||
incidentId: incident.id,
|
||
caseNumber: `INC-${String(number).padStart(4, '0')}`,
|
||
name: pick(['Alex Moreno', 'Jamie Patel', 'Chris Nguyen', 'Taylor Brooks', 'Morgan Diaz']),
|
||
jobTitle: pick(['Press operator', 'Material handler', 'Maintenance technician', 'Assembler']),
|
||
natureOfInjury: incident.title,
|
||
caseType: type === 'illness' ? 'skin_disorder' : 'injury',
|
||
...answers,
|
||
daysAway: answers.daysAwayFromWork ? 1 + Math.floor(rand() * 10) : 0,
|
||
daysRestricted: answers.restrictedOrTransfer ? 2 + Math.floor(rand() * 14) : 0,
|
||
recordable: verdict.status === 'recordable',
|
||
outcome: verdict.outcome,
|
||
})
|
||
}
|
||
}
|
||
|
||
// Monthly hours worked and headcount for every site, for the last 13 months
|
||
const siteIds = [...new Set(locations.map((l) => l.siteId))]
|
||
const today = new Date()
|
||
for (const siteId of siteIds) {
|
||
for (let m = 12; m >= 0; m--) {
|
||
const month = new Date(today.getFullYear(), today.getMonth() - m, 1)
|
||
const employees = 70 + Math.floor(rand() * 30)
|
||
await database.insert(hoursWorked).values({
|
||
siteId,
|
||
month: `${month.getFullYear()}-${String(month.getMonth() + 1).padStart(2, '0')}-01`,
|
||
hours: String(employees * 168),
|
||
averageEmployees: employees,
|
||
})
|
||
}
|
||
}
|
||
return number
|
||
}
|