data/DATABASE_SCHEMA.md

BPS School Finder - Database Schema & API Reference

SQLite Table: schools

The database contains 1,027 schools (111 BPS + 916 non-BPS) stored in a single schools table.

Fields

Column	Type	Description	Useful if user wants to know about...
id	TEXT (PK)	Unique identifier for the school	Looking up a specific school's full details
school	TEXT NOT NULL	Official school name	Searching for a school by name
dba	TEXT	"Doing Business As" — alternate/trade name for the school	Finding a school known by a different or informal name
address	TEXT	Street address of the school	Where a school is located, directions, or neighborhood
latitude	REAL	Geographic latitude coordinate	Finding schools near a specific location or address
longitude	REAL	Geographic longitude coordinate	Finding schools near a specific location or address
provider_type	TEXT	Category of school (e.g. "Boston Public School", "Family Child Care", "Center Based")	What type of school/program it is, filtering by school category
grade_min	INTEGER	Lowest grade served (BPS only). Encoding: K0=-2, K1=-1, K2=0, 1-12 as integers	Which schools serve their child's grade level (BPS)
grade_max	INTEGER	Highest grade served (BPS only). Same encoding as grade_min	Which schools serve their child's grade level (BPS)
grade_min_sped	INTEGER	Lowest grade for special education students (BPS only), if different from grade_min	Special education availability at earlier grade levels
age_min_months	INTEGER	Minimum age served in months (non-BPS only)	Which childcare/preschool accepts their child's age
age_max_months	INTEGER	Maximum age served in months (non-BPS only)	Which childcare/preschool accepts their child's age
hours_of_operation	TEXT	Operating hours	Drop-off/pick-up times, scheduling, work-life compatibility
phone_number	TEXT	Contact phone number	How to contact or call the school
email	TEXT	Contact email address	How to email or reach out to the school
website	TEXT	School website URL	Where to learn more, apply, or get additional information
surround_care	INTEGER	1 if the school offers surround care (before/after school care), 0 otherwise	Before/after school care, extended day options for working parents
curriculum	TEXT	Description of curriculum used	Teaching approach, pedagogy, or educational philosophy
tuition	INTEGER	1 if tuition is charged, 0 if not, NULL if unknown	Cost, whether the school is free, affordability
headstart	INTEGER	1 if the school is a Head Start program, 0 otherwise	Free early childhood programs, federally funded options, low-income eligibility
accepts_ccfa	INTEGER	1 if the school accepts CCFA (Child Care Financial Assistance) vouchers, 0 if not, NULL if unknown	Financial assistance, vouchers, subsidized childcare
has_language_program	INTEGER	1 if the school offers a language/dual-language program, 0 otherwise	Bilingual education, dual-language immersion, learning a second language
has_international_baccalaureate	INTEGER	1 if the school offers an IB program, 0 otherwise	IB programs, internationally recognized diplomas
international_baccalaureate_text	TEXT	Description of the IB program (empty if none or just "Yes")	Specific IB program details, which IB tracks are offered
has_advanced_placement	INTEGER	1 if the school offers AP courses, 0 otherwise	AP classes, college-level coursework, college prep
advanced_placement_text	TEXT	Description of AP offerings (empty if none or just "Yes")	Which specific AP subjects are available
uniform	INTEGER	1 if a uniform is required, 0 if not, NULL if unknown	Dress code, uniform requirements, what their child needs to wear
UPK	INTEGER	1 if the school participates in Universal Pre-Kindergarten, 0 otherwise	Free pre-K, universal pre-kindergarten seats
ADA	INTEGER	1 if the school is ADA accessible, 0 if not, NULL if unknown	Wheelchair accessibility, physical disability accommodations
special_admission	INTEGER	1 if the school requires a special admissions process, 0 otherwise	Application requirements, exam schools, selective enrollment
special_admission_link	TEXT	URL to special admission application or information	How to apply to a special-admission school
school_quality_framework	TEXT	BPS school quality framework rating or report link	School ratings, performance, quality metrics
state_report_card	TEXT	Link to the state report card for the school	Test scores, accountability data, state evaluations
point_of_contact	TEXT	Name of the school's designated point of contact	Who to talk to, who to reach out to at the school
school_leader	TEXT	Name of the school's principal or leader	Who runs the school, principal name
build_care	INTEGER	1 if the school participates in the BuildCare program, 0 otherwise	BuildCare program availability, additional childcare support

Indexes

• idx_provider_type — on provider_type
• idx_grades — on (grade_min, grade_max)
• idx_age — on (age_min_months, age_max_months)
• idx_latlon — on (latitude, longitude)

---

BPSDatabase Public Methods

Hard Filtering (SQL)

find_schools_by_grade(grade: int) -> list

Find BPS schools that serve a specific grade level. Uses the integer grade encoding (K0=-2, K1=-1, K2=0, 1-12). Returns all schools where the given grade falls within their grade_min to grade_max range.

find_schools_by_age(age_months: int) -> list

Find non-BPS schools that serve a specific age given in months. Returns all schools where the given age falls within their age_min_months to age_max_months range.

find_schools_near(lat: float, lon: float, radius_miles: float = 1.0) -> list

Find schools within radius_miles of a given latitude/longitude. Uses a bounding-box pre-filter for efficiency, then applies exact Haversine distance. Results are sorted by distance (ascending) and include a distance_miles field.

find_schools_by_provider_type(provider_type: str) -> list

Find schools by exact match on provider_type (e.g. "Boston Public School", "Family Child Care").

find_schools_by_filters(**kwargs) -> list

Combined AND filter across boolean/integer fields. Supported keyword arguments: UPK, ADA, accepts_ccfa, headstart, has_language_program, has_advanced_placement, has_international_baccalaureate, uniform, special_admission, surround_care, build_care, tuition. Only non-None values are applied.

hard_filter(grade, age_months, provider_type, lat, lon, radius_miles, **boolean_filters) -> list

Combined hard filter that applies all specified conditions with AND logic. Combines grade, age, provider type, location proximity, and boolean filters into a single query. If location is provided, results are post-filtered by Haversine distance and sorted by distance.

Soft Filtering (Vector Search / RAG)

semantic_search(query: str, top_k: int = 10, pre_filter_ids: set = None) -> list

Semantic search over BPS school descriptions using FAISS and sentence embeddings. Returns the top-k most relevant schools ranked by cosine similarity. If pre_filter_ids is provided, only considers schools with those IDs. Falls back to keyword search if the vector store is not loaded. Each result includes id, school, score, description, and metadata.

Combined Pipeline

search(query, grade, provider_type, lat, lon, radius_miles, top_k, **filters) -> list

Full search pipeline that first applies hard filters, then ranks the matching results by semantic similarity to the query. If no query is provided, returns hard-filtered results directly. If hard filtering returns no results, falls back to a pure semantic search.

---

RAG Descriptions (BPS Schools Only)

RAG descriptions are generated only for BPS schools during the vector store build process (build_database.py:build_description). Each description is a multi-paragraph text document assembled by concatenating the following fields from the raw JSON data, in order:

1. Header sentence — School name, address, and human-readable grade span (e.g. "K1 to 6 (K0 for special education)")
2. Overview / mission statement — From overview_mission_statement, if non-empty (excludes #VALUE! artifacts)
3. Unique features — From unique_features, joined if a list
4. Specialized education programs — From specialized_education_programs
5. Language programs — From language_programming_text
6. Early college / dual enrollment — From early_college_dual_enrollment
7. CTE pathways — From CTE_Pathways_TXT, prefixed with "Career and technical education pathways:"
8. After school program — From after_school_program
9. Before school program — From before_school_program
10. Extracurriculars — From extra_curriculars_text
11. Sports — From sports, joined if a list
12. Partners — From partners, joined if a list
13. Accessibility — From ada_description
14. Family engagement — From family_engagement_opportunities

Each section is separated by a double newline. Empty or missing fields are silently omitted. These descriptions are then embedded using the all-MiniLM-L6-v2 sentence transformer model and indexed in a FAISS inner-product index for semantic search.

---

Utility Methods

get_school_detail(school_id: str) -> dict

Get the full database record for a single school by its ID. If a vector store description exists for the school, it is included in the result under the description key. Returns None if no school is found.

get_all_provider_types() -> list

Get a sorted list of all distinct provider_type values in the database.

close()

Close the underlying SQLite database connection.