scripts/loaders/miami/directory.ts
Served verbatim from the project repository. Internal working document conventions apply: documents may reference file paths, branch names, and findings-ledger anchors from the repo.
// Build miami_schools from the FLDOE Master School ID file (the universe:
// every district-13 number, active + closed + future), enriched with the NCES
// CCD directory (federal id, level, charter cross-check), the county GIS
// Public School layer (region, permanent FISH capacity, lat/lng fallback) and
// the latest School Grades file (Title I, current grade, charter cross-check).
//
// Decisions M.2 / M.3 in docs/cities/miami/00_overview.md; code tables from the
// MSID Application Guidelines (Appendix B), findings.md F.12.
//
// Run: npx tsx scripts/loaders/miami/directory.ts
// Idempotent: upserts on school_number.
import fs from "node:fs";
import { parse } from "csv-parse/sync";
import * as XLSX from "xlsx";
import {
requireSource, startDataLoad, finishDataLoad, stageLoadOperation, batched, padSchoolNumber, runMain,
} from "./_lib";
import { streamZipCsv } from "./_zip";
import type { MiamiSchoolType, MiamiAdmissionCategory } from "@prisma/client";
const SCRIPT_PATH = "scripts/loaders/miami/directory.ts";
const MSID_ID = "fldoe_msid_all_schools";
const CCD_ID = "nces_ccd_directory_2023-24";
const GIS_ID = "mdc_gis_schoolsite";
const GRADES_ID = "fldoe_school_grades_2024-25";
// MSID Appendix B: grade code → grade combination. (Codes present in Dade plus
// the rest of the table so other districts would work too.)
const GRADE_CODES: Record<string, string> = {
"1": "PK", "2": "KG", "3": "KG-1", "4": "PK-2", "5": "KG-2", "6": "KG-3", "7": "PK-3", "8": "PK-5", "9": "KG,3-5", "10": "KG,3-6",
"11": "KG-4", "12": "PK-KG", "13": "KG,4-6", "14": "PK-12", "15": "KG-5", "16": "PK-4", "17": "KG,5-6", "18": "KG-6", "19": "KG,6",
"20": "KG,1,6", "21": "KG,6-8", "22": "KG-7", "23": "KG,7", "24": "KG,7-12", "25": "KG-8", "26": "KG-9", "27": "PK-8", "28": "KG-12",
"29": "PK-9", "30": "2", "31": "1-2", "32": "1-3", "33": "1-4", "34": "1-5", "35": "1-6", "36": "PK,3-5", "37": "PK,9-12", "38": "PK-KG,3-5",
"39": "PK-KG,5-6", "40": "1-7", "41": "2-3", "42": "2-4", "43": "3-5", "44": "3-6", "45": "3-7", "46": "3-4", "47": "KG-11", "48": "PK-KG,4-6",
"49": "PK,4-5", "50": "4", "51": "4-5", "52": "4-6", "53": "4-8", "54": "6", "55": "PK,6-12", "56": "PK-6", "57": "3-8", "58": "5", "59": "5-6",
"60": "5-7", "61": "5-8", "62": "7-11", "63": "5-12", "64": "7-9,12", "65": "6,9-12", "66": "6-7", "67": "6-8", "68": "6-9", "69": "6-12",
"70": "8-10", "71": "2-6", "72": "7", "73": "7-8", "74": "7-9", "75": "7-12", "76": "6-10", "77": "4-12", "78": "7-10", "79": "8-9", "80": "8-12",
"81": "7-Adult", "82": "9", "83": "9-10", "84": "9-11", "85": "9-12", "86": "10-12", "87": "11-12", "88": "9-Adult", "90": "Adult", "99": "Unassigned",
"100": "KG-10", "101": "1-8", "102": "2-8", "103": "6-11", "104": "PK-1", "105": "1-12", "106": "2-7", "107": "4-9", "108": "2-12", "109": "PK-7",
"110": "6-Adult", "111": "12", "113": "3-10", "114": "3-12", "115": "11-Adult", "116": "5-11", "117": "12-Adult", "118": "3-Adult", "119": "PK-2,9-12",
"120": "PK,6-8", "121": "2-5", "122": "PK,9-Adult",
};
function gradeSpan(code: string | null): { low: string | null; high: string | null; combo: string | null } {
if (!code) return { low: null, high: null, combo: null };
const combo = GRADE_CODES[String(Number(code))];
if (!combo || combo === "Unassigned") return { low: null, high: null, combo: combo ?? null };
const toks = combo.split(/[,\-]/).map((t) => t.trim()).filter(Boolean);
return { low: toks[0] ?? null, high: toks[toks.length - 1] ?? null, combo };
}
// MSID TYPE (01 elementary / 02 middle / 03 senior high / 04 combination / 05 adult / 07 other)
// + grade combination → the level label used for peer-group hard filters.
function schoolLevel(type: string | null, combo: string | null): string | null {
const t = type ? String(Number(type)) : null;
if (t === "1") return "Elementary";
if (t === "2") return "Middle";
if (t === "3") return combo && /^(5|6|7)-12$/.test(combo) ? "6-12" : "High";
if (t === "5") return "Adult";
if (t === "7") return "Other";
if (t === "4") {
if (!combo) return "Combination";
if (/^(PK|KG|1|2|3|4)(-|,).*8$/.test(combo) || combo === "KG,6-8" || combo === "PK,6-8") return "K-8";
if (/12$/.test(combo)) return "K-12";
return "Combination";
}
return null;
}
// PRIMARY_SERV_TYPE (R general / S special ed / B alternative / V CTE / A adult / O other)
// and SCHL_FUNC_SETTING (Z none / B adult gen ed / D DJJ / J jail / T CTE center /
// V virtual / L hospital-homebound / N migrant / H home ed / P FES program).
function schoolType(serv: string | null, func: string | null, type: string | null): MiamiSchoolType {
const f = (func ?? "Z").trim();
const s = (serv ?? "R").trim();
const t = type ? String(Number(type)) : null;
if (f === "V") return "VIRTUAL";
if (f === "T") return "TECHNICAL_COLLEGE";
if (f === "D" || f === "J") return "DJJ";
if (f === "B" || t === "5" || s === "A") return "ADULT";
if (f === "L" || f === "N" || f === "H" || f === "P") return "OTHER";
if (t === "7") return "OTHER";
if (s === "R") return "REGULAR";
if (s === "S") return "ESE_CENTER";
if (s === "B") return "ALTERNATIVE";
if (s === "V") return "TECHNICAL_COLLEGE";
if (s === "O") return "OTHER";
return "OTHER";
}
function admission(isCharter: boolean, magnet: string | null, st: MiamiSchoolType): MiamiAdmissionCategory {
if (isCharter) return "CHARTER_LOTTERY";
if (magnet === "S") return "MAGNET_SCHOOL";
if (magnet === "P") return "MAGNET_PROGRAM_HOST";
if (st === "ALTERNATIVE") return "ALTERNATIVE";
if (st === "REGULAR" || st === "ESE_CENTER") return "NEIGHBORHOOD";
return "OTHER";
}
function parseDate(v: string | null): Date | null {
if (!v) return null;
const m = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/.exec(v.trim());
if (!m) return null;
return new Date(Date.UTC(Number(m[3]), Number(m[1]) - 1, Number(m[2])));
}
const nz = (v: unknown): string | null => {
if (v === null || v === undefined) return null;
const s = String(v).trim();
return s === "" || s.toLowerCase() === "nan" ? null : s;
};
async function main() {
const msid = requireSource(MSID_ID);
const ccd = requireSource(CCD_ID);
const gis = requireSource(GIS_ID);
const grades = requireSource(GRADES_ID);
// ── MSID: the universe ──
const msidText = fs.readFileSync(msid.filePath, "latin1");
const msidRows = parse(msidText, { columns: true, delimiter: "\t", quote: null, relax_column_count: true, skip_empty_lines: true, trim: true, bom: true }) as Record<string, string>[];
const dade = msidRows.filter((r) => String(r.DISTRICT).trim() === "13");
console.log(`[directory] MSID rows: ${msidRows.length}; district 13: ${dade.length}`);
// ── CCD 2023-24: federal ids, level, charter flag ──
const ccdRows = await streamZipCsv(ccd.filePath, /ccd_sch_029_.*\.csv$/i, (r) => r.ST_LEAID === "FL-13");
const ccdByNum = new Map<string, Record<string, string>>();
for (const r of ccdRows) {
const num = padSchoolNumber(String(r.ST_SCHID).replace("FL-13-", ""));
if (num) ccdByNum.set(num, r);
}
console.log(`[directory] CCD FL-13 rows: ${ccdRows.length}`);
// ── County GIS: region, permanent capacity, coordinates ──
const gisJson = JSON.parse(fs.readFileSync(gis.filePath, "utf-8")) as { features: { attributes: Record<string, unknown> }[] };
const gisByNum = new Map<string, Record<string, unknown>>();
for (const f of gisJson.features) {
const num = padSchoolNumber(f.attributes.ID);
if (num) gisByNum.set(num, f.attributes);
}
console.log(`[directory] GIS points: ${gisJson.features.length}`);
// ── School Grades 2025: Title I, current grade, charter flag ──
const wb = XLSX.readFile(grades.filePath);
const sheet = wb.Sheets[wb.SheetNames[0]];
const gradeRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, { range: 3, defval: null });
const gradesByNum = new Map<string, Record<string, unknown>>();
for (const r of gradeRows) {
if (String(r["District Number"]).trim() !== "13") continue;
const num = padSchoolNumber(r["School Number"]);
if (num) gradesByNum.set(num, r);
}
console.log(`[directory] School Grades 2025 Dade rows: ${gradesByNum.size}`);
const dataLoad = await startDataLoad({
sourceId: MSID_ID, sourceUrl: msid.prov.url, sha256: msid.prov.sha256, bytes: msid.prov.bytes,
yearCovered: "2025-26", scriptPath: SCRIPT_PATH,
notes: `Enriched with ${CCD_ID} (${ccd.prov.sha256.slice(0, 12)}), ${GIS_ID} (${gis.prov.sha256.slice(0, 12)}), ${GRADES_ID} (${grades.prov.sha256.slice(0, 12)})`,
});
let upserted = 0;
const charterDisagreements: string[] = [];
const levelCounts = new Map<string, number>();
await batched(dade, 25, async (batch) => {
for (const r of batch) {
const num = padSchoolNumber(r.SCHOOL);
if (!num) continue;
const name = nz(r.SCHOOL_NAME_LONG) ?? nz(r.SCHOOL_NAME_SHORT);
if (!name) continue;
const charterCode = nz(r.CHARTER_SCHL_STAT);
const isCharter = !!charterCode && ["R", "C", "T", "B", "H"].includes(charterCode);
const gs = gradeSpan(nz(r.GRADE_CODE));
const type = schoolType(nz(r.PRIMARY_SERV_TYPE), nz(r.SCHL_FUNC_SETTING), nz(r.TYPE));
const level = schoolLevel(nz(r.TYPE), gs.combo);
const magnet = nz(r.MAGNET_STATUS);
const c = ccdByNum.get(num);
const g = gisByNum.get(num);
const sg = gradesByNum.get(num);
// Three-way charter check (MSID is canonical; disagreements logged).
const ccdCharter = c ? c.CHARTER_TEXT === "Yes" : null;
const sgCharter = sg ? String(sg["Charter School"] ?? "").toUpperCase() === "YES" : null;
if ((ccdCharter !== null && ccdCharter !== isCharter) || (sgCharter !== null && sgCharter !== isCharter)) {
charterDisagreements.push(`${num} ${name}: msid=${isCharter} ccd=${ccdCharter} grades=${sgCharter}`);
}
const activity = nz(r.ACTIVITY_CODE);
const dateClosed = parseDate(nz(r.DATE_CLOSED));
const lat = Number.parseFloat(nz(r.LATITUDE) ?? "") ;
const lng = Number.parseFloat(nz(r.LONGITUDE) ?? "");
const gisLat = g ? Number(g.LAT) : NaN;
const gisLng = g ? Number(g.LON) : NaN;
const openYear = nz(r.CHARTER_SCHL_OPEN) ? Number.parseInt(String(r.CHARTER_SCHL_OPEN).slice(0, 4), 10) : null;
const fedNo = nz(r.FEDERAL_SCHL_NO);
const ncesId = c ? String(c.NCESSCH) : fedNo && nz(r.FEDERAL_DIST_NO) === "1200390" ? `1200390${fedNo.padStart(5, "0")}` : null;
const letter = sg ? nz(sg["Grade 2025"]) : null;
levelCounts.set(`${type}|${level}`, (levelCounts.get(`${type}|${level}`) ?? 0) + 1);
const fields = {
ncesId,
name,
nameShort: nz(r.SCHOOL_NAME_SHORT),
schoolType: type,
isCharter,
charterStatusCode: charterCode,
charterOpenYear: openYear && Number.isFinite(openYear) ? openYear : null,
admissionType: admission(isCharter, magnet, type),
magnetStatus: magnet,
schoolChoice: nz(r.CS_SCHOOL_CHOICE) === null ? null : r.CS_SCHOOL_CHOICE.trim() === "Y",
titleI: sg ? String(sg["Title I"] ?? "").toUpperCase() === "YES" : false,
schoolLevel: level,
gradeLow: gs.low,
gradeHigh: gs.high,
region: g ? nz(g.REGION) : null,
municipality: nz(r.PHYSICAL_CITY),
streetAddress: nz(r.PHYSICAL_ADDRESS),
zipCode: nz(r.PHYSICAL_ZIP)?.slice(0, 5) ?? null,
latitude: Number.isFinite(lat) ? lat : Number.isFinite(gisLat) ? gisLat : null,
longitude: Number.isFinite(lng) ? lng : Number.isFinite(gisLng) ? gisLng : null,
activityCode: activity,
dateOpened: parseDate(nz(r.DATE_OPENED)),
dateClosed,
currentSchoolGrade: letter,
fishCapacityPermanent: g && Number(g.CAPACITY) > 0 ? Number(g.CAPACITY) : null,
includeInDefaultComparisons: activity === "A" && type === "REGULAR",
closedAt: activity === "C" ? dateClosed : null,
};
// fish_capacity_permanent is owned by fish_los.ts (state FISH report); the GIS CAPACITY attribute
// only seeds a brand-new record so a directory re-run never reverts FISH values (review 2026-09-16, C11).
const { fishCapacityPermanent: _seedCapacity, ...updateFields } = fields;
void _seedCapacity;
stageLoadOperation(dataLoad.id, (tx) => tx.miamiSchool.upsert({ where: { schoolNumber: num }, update: updateFields, create: { schoolNumber: num, ...fields } }));
upserted++;
}
});
await finishDataLoad(dataLoad.id, { inserted: upserted, updated: 0, suppressed: 0 },
`${upserted} district-13 schools upserted; charter disagreements: ${charterDisagreements.length}`);
console.log(`[directory] upserted ${upserted} schools`);
console.log(`[directory] charter flag disagreements (MSID canonical): ${charterDisagreements.length}`);
for (const d of charterDisagreements.slice(0, 20)) console.log(" ", d);
console.log(`[directory] type|level counts:`);
for (const [k, v] of [...levelCounts.entries()].sort()) console.log(` ${k.padEnd(32)} ${v}`);
}
runMain(main);