Case studyOur own product2026
e-Schoolbase: the school office, finally in one system.
e-SchoolBase runs the whole back office of a K-12 school: the register, the session, attendance, exams and marks, the library, the hostel, staff and payroll, and the marksheets that come out at the end of it. It has an AI assistant too. What the assistant doesn't have is the database.
The product lives at eschoolbase.in (opens in a new tab)
Eight pre-written queries, run by the backend, scoped to your school. No natural-language-to-SQL anywhere in the product.
Where does the answer come from?
No SQL is written here
- Step 1: The question“How many students are in class 6 this session?”
- Step 2: The model picks a keyFrom a menu of eight. It answers with JSON, never with SQL.
- Step 3: A parser checks itKnown key, declared parameters only, primitives, 50 characters, no braces or quotes.
- Step 4: The backend runs the queryYour school id from the token in the WHERE clause, 50 rows maximum.
- Step 5: A second call writes the sentenceFrom those rows, and nothing else.
What it can ask for
Eight queries, every one of them filtered to your school.
What it can't
Anything else. There is no text-to-SQL path to switch off.
- queries the assistant can run
- 8
- queries the assistant can run
- role-checked API endpoints
- 284
- role-checked API endpoints
- backend tests per pull request
- 487
- backend tests per pull request
A school management platform, in short
e-SchoolBase holds the student register, the academic session, attendance, examinations and marks, the library, the hostel, staff records and payroll, and turns all of it into the documents a school actually has to hand out: marksheets, report cards and register exports.
The assistant answers questions about the school, designs a marksheet template and writes a question paper. Every school-data question is answered by the model picking one key out of an allow-list of eight pre-written queries, which the backend then runs itself, scoped to the school in the caller's token. Adding a text-to-SQL path would mean deleting the parser that refuses unknown keys.
We designed and built all of it, from the database schema and the AI gateway to the admin console and the billing engine, and we run it as a subscription product.
The problem: school software is either rigid or reckless
One kind won't bend to how your school names anything. The other points a model at everyone's student records.
A school's year has one genuinely hard week in it. Marks come in on paper or in a spreadsheet, somebody re-keys them, somebody else checks the totals, and then several hundred report cards have to come out of a layout the board approved and the principal likes. Most schools do this in Excel and Word, and the result is a week of overtime and a stack of reprints.
The software sold to fix it tends to fail in one of two ways. The first is rigidity: the ERP calls a class “Class 5”, the school calls it “V”, the school down the road calls it “Grade 5” and splits it into streams the software has never heard of. The marksheet is a fixed template, and changing it is a support ticket.
The second failure is newer. “AI for schools” usually means a chat widget that summarises a help page, or a natural-language-to-SQL feature pointed at a shared database. The second one is the dangerous one. Student names, dates of birth, guardians, marks and staff salaries live in that database, several hundred schools share it, and the thing deciding which rows to read is a model that will confidently do whatever a well-written message tells it to.
The brief we set ourselves
- 1
One schema underneath, your names on top
Classes, subjects and assessments are canonical at the platform level, and every school renames them for itself, every session.
- 2
The model is never an authority
School, role, budget and database access are decided by the backend from the token before any model is called.
- 3
Documents are the product
A marksheet isn't a screen. It's a PDF that has to be right four hundred times in a row, so the rendering path stays deterministic.
- 4
Every guardrail on one door
One gateway that all model calls pass through, so a control added once applies to every feature at once.
- 5
Priced the way schools actually buy
Per class group and student limit, not per login, with AI usage metered and capped like any other resource.
How it works: one door, one allow-list, one axis
Every model call in the product goes through a single class. Nothing else in the codebase is allowed to touch the OpenAI SDK, so a control added once applies to every feature at once. Underneath it, the academic session is the axis everything else hangs off, and the interesting security decision is a list of eight queries.
One gateway, in three stages
Brass marks the one place a person decides. Everything else is the backend making up its own mind before, during and after the call.
Stage 1
Before a token is spent
Six checks, all of them the backend's decision.
- Trigger
A request arrives from the console
- Rules & checks
Kill switch and feature policy
One feature or the whole AI layer can be switched off without a restart.
- Rules & checks
Identity and role
School, user and role come from the signed token. A school header that disagrees with it is a 403.
- Rules & checks
Lockout and rate limits
Per user, per school and a global ceiling. Keep tripping the filters and AI access suspends itself.
- Rules & checks
Input screening
13 patterns over Unicode-normalised text, a moderation pass, then a second cheap model as a classifier.
- Rules & checks
Budget
Daily and monthly token allowances, checked before the spend, not after.
Every outcome, pass or block, lands in an audit row. The row holds metadata, never what anyone typed.
Stage 2
The call itself
Two model calls with a database query in between.
- System update
Prompt assembly
Hardening preamble, feature prompt, server-derived facts, then history and the question as untrusted input.
- AI step
First call: pick a key
Eight queries and four generation actions to choose from. This one never streams, because it's the decision being validated.
- Rules & checks
The parser
Unknown key, undeclared parameter, wrong type, stray brace: any of them and the answer is “I can't answer that”.
- System update
The query runs
Entity Framework, filtered on the token's school, capped at 50 rows.
- AI step
Second call: write the answer
From those rows. This one streams.
Stage 3
Before it reaches the screen
The output is checked even while it's still arriving.
- Rules & checks
Output validation
Fails closed. A wrong answer is worse than no answer.
- Rules & checks
Leak scan
A sliding window watches for verbatim system prompt, wide enough that a leak can't hide across two chunks. A hit aborts the stream.
- Rules & checks
HTML sanitising
Generated markup is cleaned through an allow-list before it's stored, not after it's read.
- Human control
Preview before save
A generated marksheet is rendered against sample student data, and saved only when the school says yes.
- System update
Audit
One of eleven statuses with a reason code, plus a watcher that raises a warning when blocks or provider errors spike.
- Trigger
- AI step
- Rules & checks
- Human control
- System update
The assistant picks a key, the backend runs the query
The school-data chatbot is the part people ask about, so it's worth being precise. The first model call sees a menu of eight query keys and their parameters, and has to answer with JSON like {"query":"students_in_class","parameters":{"class_name":"6"}}. A parser then checks that the key exists, that every parameter was declared, that nothing undeclared was smuggled in, and that the values are primitives no longer than 50 characters, drawn from a character set with no braces, angle brackets or quotes in it. Anything else and the user gets “I can't answer that”.
Only then does the backend run the query, in Entity Framework, with the school id from the token in the WHERE clause and a hard cap of 50 rows. The second model call sees those rows and writes a sentence about them.
That ordering is the whole point. Suppose every pattern, the moderation pass and the second-stage classifier all miss a clever message. The model still has exactly eight things it can ask for, all of them scoped to the caller's own school, and the worst outcome available to it is a wrong but harmless choice among them.
Four more actions sit beside the queries, and they classify intent rather than fetch anything: design a marksheet template, revise the one already in this conversation, write a question paper, revise the one already in this conversation.
Three memories, three trust levels
“Memory” here is three separate mechanisms, deliberately kept apart. The assistant's identity lives in code, above the memory tier, because memory is writable by whoever the pipeline listens to and a system prompt isn't.
Conversation memory is the last six messages, filtered on session, school and user together, so a guessed session id from another school returns nothing. Generated documents are the exception: a question paper is thousands of tokens that would be charged on every later question, so the history replays it as “[Generated a question paper]”. Substituting rather than dropping keeps the window even, and it's also how the model knows that “make question 3 harder” is a revision rather than a new paper.
User memory is derived, not stored: who is asking, their role, their school, the board, the medium, the session and today's date, assembled fresh before each request. School names are typed by administrators, so every value is collapsed to one line, capped at 120 characters and screened like any other input.
The trust ladder
Written down in this order, because every AI bug worth the name was something climbing a rung.
- Hardening preambleImmutableAlways first, and it lives in code rather than in memory.
- Feature system promptServer-ownedWritten by us, per feature.
- Context factsServer-derivedWho's asking, their role, school, board, medium, session and today's date. Labelled as reference data.
- Conversation historyUntrustedThe last six messages, replayed as ordinary user and assistant turns.
- The new questionUntrustedScreened like anything else a person types.
Streaming without giving up the output checks
The chat streams, because two model calls and a database query in between is several seconds of silence otherwise. Server-sent events carry three status codes while the pipeline runs, understanding, retrieving and composing, and then the answer arrives token by token.
Only the second call streams. The first one decides which allow-listed query runs, and the parser needs the whole payload to validate it. Streaming that call would mean validating a decision after acting on it.
The output side kept its guarantee with a sliding-window scanner: the reply is checked for verbatim fragments of the system prompt as it flows, with a buffer wide enough that a leak can't be split across two chunks and escape. A hit aborts the stream mid-answer and audits it.
Marksheets that have to be right four hundred times
Marksheets and report cards run on Liquid templates. A school starts from a platform template or asks the assistant to design one, and the result is rendered against sample student data on screen before anything is saved. Generated HTML is sanitised before it's stored, not after it's read, so no saved copy ever carries active markup. Templates uploaded directly get a stricter guard that rejects rather than rewrites, because rewriting would break the Liquid control flow that makes the template useful.
Rendering to PDF is headless Chromium, which means the renderer is a browser, with a browser's appetite for fetching whatever a document points at. So it runs with JavaScript disabled behind a request policy that allows the public web and blocks loopback, link-local addresses including the cloud metadata endpoint, private ranges, DNS-rebinding answers, and the file and ftp schemes.
Reports are a second path. Platform templates are uploaded as spreadsheets or documents, given named fields that map onto entities in the schema, and scoped to the boards, classes, sessions and schools they apply to. A school maps its own values in once and generates the report on demand after that, as a spreadsheet or a PDF.
The academic session is the axis
Everything that changes yearly is scoped to a session, and everything that doesn't, isn't. A student is admitted once and has one record. Their class, section, roll number and attendance belong to a session, so last year's marksheet keeps pointing at last year's section after the promotion run has moved them on.
The aliasing layer sits on top. Classes, subjects and assessments are canonical at the platform level, which is what makes shared report templates and the query catalog possible, and each school renames each of them per session. The school sees “V” and “Hindi (Second Language)”. The template and the query see the canonical class and subject. Nobody has to choose between a stable schema and the names the school actually uses.
The console
The admin console is Next.js 16 and React 19, about 63,000 lines of TypeScript across 425 files, built component-first: roughly 40 shared primitives and a semantic theme, so a table, a side panel or a status pill behaves the same in the hostel module as in the library one. It opens on the current session with student and examination analytics, in a layout the school can rearrange.
- Students and sessions
- Student information, the SR register, promotion into the next session, classes, sections, subjects and academic streams.
- Attendance and exams
- Attendance against an academic calendar, exam groups, assessments and marks entry.
- Marksheets
- Platform templates, the designer, and an AI-built template previewed against sample students before it's saved.
- Library, hostel and staff
- Books, cards and issue-return, room allocation, staff records, staff attendance and payroll.
- Reports and documents
- Report templates with per-school field mapping, generated as a spreadsheet or a PDF, and the document directory.
- Billing and the rest
- Plan and usage, student and AI add-ons, coupons, referrals and support.
The assistant is available from any page as a flyout, and has a full-page view with conversation history, so a question asked in passing and a question paper built over several turns live in the same place.
How it's sold
Indian K-12 schools don't buy per-seat software, so the pricing follows the shape of the school instead.
e-SchoolBase is a multi-tenant subscription product. A plan is priced per class group, so a primary school and a senior secondary school don't pay the same, and the AI usage inside a plan is metered and capped like any other resource.
What a plan looks like
- Priced per class group, so a primary school and a senior secondary school don't pay the same.
- Each plan detail carries its own student limit, and schools that outgrow one buy students by the head instead of jumping a tier.
- AI usage is metered inside the plan: a daily and a monthly token allowance, with add-ons for schools that lean on the assistant.
- Coupons have their own validity windows and use limits, and a referral credits a share of the referred school's subscription to the referrer's wallet.
- Payment credentials for PhonePe, Cashfree and Razorpay are stored per account in test or live mode, encrypted at rest and never returned by the API. PhonePe and Cashfree are integrated end to end today.
A school without an active subscription is stopped at middleware rather than in the UI, so an expired plan closes the API instead of just hiding a button.
Want numbers? Ask us for current plans and we'll size one to your classes and your roll.
Engineering facts
What went into the build, counted from the repositories rather than estimated.
| Area | What was delivered |
|---|---|
| API | 284 endpoints across 43 controllers in three areas (school app, shared, platform CRM); 40 of the 43 carry an authorization attribute, and 99 of those name an explicit role allow-list |
| Domain | 86 entity classes across six schemas (dbo, school, session, ai, payment, snapshot) and 25 EF Core migrations on SQL Server |
| AI layer | About 4,500 lines across 24 files: one gateway, five guardrail components, 8 allow-listed queries, 4 generation intents, a parser, a context builder and a retention job |
| Guardrails | 13 compiled screening patterns, Unicode normalisation folding more than 70 lookalike characters, a second-stage LLM classifier, an output leak detector and an allow-list HTML sanitiser |
| Audit | 11 audit statuses covering every outcome, stored as metadata only, with an anomaly watcher on blocks, provider errors and every moderation fail-open |
| Documents | Liquid marksheet templates, an Excel-to-HTML renderer and a headless Chromium PDF service behind an SSRF request policy |
| Tests | 487 backend test methods across 49 classes, about 9,100 lines, run on every pull request before an image is published |
| Codebase | About 44,500 lines of C# outside tests and migrations, and about 63,400 lines of TypeScript across 425 files |
| Delivery | Pull-request-gated pipeline: build, test, publish to a container registry, deploy to staging, then promote the tagged image to production behind a manual approval |
Security posture
The console hides what you can't use. The backend is what actually says no.
- School, user and role come from the signed token on every request. A request whose school header disagrees with its token is a 403, and the model's output is never a source of identity.
- Role allow-lists are enforced by the backend. The console's guards are there for convenience only.
- Authentication is ASP.NET Core Identity with signed access tokens and server-side refresh tokens that are replaced on every refresh, one per user. Password reset runs through a tracked one-time key, not a guessable link.
- Payment secrets are encrypted at rest against a key ring kept outside the container. The API returns a masked value and a boolean, never the secret, and the console can encrypt one in the browser first.
- Generated HTML is sanitised before it's stored. Uploaded templates are rejected rather than rewritten when they carry scripts, event handlers or javascript: URLs, because rewriting would break the Liquid the template runs on.
- The PDF renderer runs with JavaScript off and a network policy that blocks loopback, link-local, private ranges and non-HTTP schemes.
- Conversation text has a retention window and a purge job, with a floor so the purge can never eat rows the monthly budget still sums over.
- Third-party text is data, not instruction. A school renamed to “ignore all previous instructions” is dropped from the prompt and logged.
Per-school and per-user token budgets are built and covered by tests, held behind a flag until the first production deployment. The request limits, the lockout and the global ceiling are live now.
What we learned building it
Five things we'd tell anyone putting a model next to other people's records.
Structural beats heuristic.
The patterns, the moderation call and the classifier are all useful and all bypassable. The allow-list isn't. Spending the design effort on what the model is physically able to ask for, instead of on catching every phrasing of a bad question, is what makes the guarantee explainable to a principal in one sentence.
A prompt is a trust ladder, and the bugs are rungs being skipped.
Writing the ladder down turned a vague worry into a checklist. Every AI defect worth the name here was something climbing a rung it hadn't earned: a school name reaching the system message unscreened, a generated document replayed as trusted content, a raw selector payload showing up as history.
Compilers are cheaper than code review.
Three production defects came from one class of mistake: an async void nobody could await, a CopyToAsync missing its await that handed an empty buffer to the spreadsheet parser, and a .Result blocking a request thread. None of them broke the build. Eight async-correctness analyser rules are now errors, which cost nothing to turn on at zero violations.
Fail closed on correctness, fail open on availability.
The validator, the leak detector and the parser all fail closed, because a wrong answer is worse than no answer. Moderation and the injection classifier fail open, because a provider outage shouldn't take the assistant down, and every fail-open raises an alert so nothing runs unmoderated in silence.
Key rings outlive containers, or the data is gone.
Encrypted gateway credentials are only recoverable while the Data Protection key ring survives, and the default location lives with the process. The first redeploy would have turned every stored secret into unreadable bytes. The app now complains loudly at boot when the key path isn't persisted.
e-SchoolBase: questions people ask
What is e-SchoolBase?
A school management platform for K-12 schools. It covers the student register and academic sessions, attendance, examinations and marks, marksheets and report cards, library, hostel, staff records and payroll, documents and reporting, with an AI assistant that answers questions about the school's own data and generates marksheet templates and question papers.
Can the AI assistant see another school's data?
No. The school is read from the signed token, never from the request, and every catalog query filters on it in the database. A request whose school header disagrees with its token is rejected before it reaches any handler.
Does the assistant write SQL?
Never. It picks one key from a list of eight pre-written queries and supplies parameters that a parser validates for name, type, length and character set. The backend runs the query. There is no natural-language-to-SQL path in the product.
What happens if somebody tries to trick the assistant?
The input is screened for injection patterns, encoded payloads and prohibited topics, on text that has been Unicode-normalised so zero-width characters and lookalike letters don't slip through, then checked by a second cheap model call. Repeated blocked attempts suspend AI access for that user. Even when every screen is bypassed, the model can still only choose among eight school-scoped queries.
Which AI models does it use?
OpenAI models, selected per feature in configuration rather than in code, so a feature can move to a cheaper or newer model without a deploy. The whole AI layer has a live kill switch and per-feature switches that take effect without a restart.
Can we design our own report cards and marksheets?
Yes. Start from a platform template, edit one, or describe what you want to the assistant and revise it in conversation until it looks right. Every design is previewed against sample student data before it is saved, and generated output is sanitised before it is stored.
What happens at the end of an academic year?
A promotion run moves students to their next class and section in the new session. The student record is continuous; the class, section, roll number and attendance belong to the session, so previous years stay intact and last year's marksheet keeps pointing at last year's data.
Our school doesn't use the same names for classes and subjects. Is that a problem?
No. Classes, subjects and assessments are canonical underneath and renamed by each school for each session. You see your names, and shared report templates and the assistant keep working.
How is it priced?
Per school, by class group and student limit, with add-ons for extra students and extra AI usage. Coupons and referral credit apply at renewal. Ask us for current plans.
How is staff and student data protected?
Access is role-based and enforced by the backend, not in the browser. Secrets are encrypted at rest, conversation text has a retention window and an automatic purge, payment credentials are never returned by the API, and every AI action, allowed or blocked, is auditable.