Product Backlog

Define and migrate core tables: districts, vendors, vendor_types, vendor_district_relationships, vendor_employees, documents, document_versions, compliance_requirements, audit_logs. UUID primary keys everywhere. Vendor-level tables carry no district_id — district relationship is tracked in the junction table only. vendor_types is a configurable table (not an enum): Clearline ships global default types, districts can create their own. Vendor type is stored on the vendor_district_relationship (not the vendor), so the same vendor can be classified differently per district. compliance_requirements rows are versioned — when a vendor is approved, a snapshot of the active requirement set is recorded so historical compliance queries reflect rules as they existed at that moment, not today's rules. Audit log uses jsonb metadata. Billing fields on districts: stripe_customer_id, trial_ends_at.

Vendors exist as independent entities — two layers: (1) a canonical vendor record owned by the vendor (name, domain, primary contact), and (2) a vendor_district_relationship record per district (compliance state, notes, approval history). Districts control their own relationship layer; they cannot edit the vendor's core identity. Offboarding in one district does not affect the vendor's standing in other districts — only the relationship record for that district is deactivated. Vendor record and documents persist.

Two Supabase projects (dev + prod). Dev doubles as staging until first paying customer. Vercel preview deployments point to dev. Environment variable management per environment.

Email/password login via Supabase Auth. Session management, password reset flow.

Domain-based vendor identity: keyed on company email domain (e.g. @abcstaffing.com). Generic domains (gmail, yahoo, hotmail, etc.) fall back to exact email match. Invite logic: if the vendor already exists in Clearline, send "District X has invited you to join their district" — no signup required, they log in and see the new district. If the vendor is new, send a signup invite that creates their account and routes them to the new district relationship. Vendor account is the entity, not the individual — multiple people can be admins under the same vendor account.

The vendor invite link (/accept-invite) isn't scoped to a specific email or token — anyone who knows or guesses an invited vendor's email can open the link, enter that email, and set the account password before the real vendor does. This was masked while Supabase's own "Confirm email" step forced a second confirmation email that proved inbox ownership before the account went live, but that second email is the two-email anti-pattern flagged during the auth build — collapsing to one email (turning "Confirm email" off) removes the ownership check entirely. Fix: embed a signed, single-use token in the invite link itself and have accept-invite verify the token instead of relying on Supabase's confirmation email for that check. Needed before real (non-demo) vendor invites go out.

Domain-based resolution (B-004) still means one login per vendor account until multi-admin-per-vendor exists. If the one person with credentials leaves the company, a new contact at that same domain can't self-serve in — the system correctly recognizes the domain already has an account, but has no second login to offer them. Decided: don't auto-create a second login for them yet (that's really multi-admin-per-vendor, deferred) and don't silently fail either — surface it to the inviting district admin instead ("this company already has an account under jane@..."), so a human can route around it. For MVP this is support-recoverable, not unrecoverable: reassign vendors.primary_email to the new contact, they go through /accept-invite fresh. Acceptable to defer full self-service recovery until multi-admin-per-vendor is prioritized.

Supabase Row Level Security policies enforcing admin vs. vendor separation. Vendors can only read/write their own records. Admins have district-scoped full access. Interim step taken 2026-08-11 (migration 0016_enable_rls.sql, prompted by a Supabase security alert on clearline-dev): RLS is now on everywhere with a single "must be authenticated" policy per table — closes the actual reported hole (fully anonymous internet access to every row) but doesn't yet distinguish admin from vendor or district from district, since nothing in the app populates that today. Confirmed while doing this: user_profiles (id/email/role/district_id/vendor_id) has existed since the very first migration (0001) and has sat completely empty and unwired the whole time — it's exactly the mapping table this item needs; build on it rather than adding a new one. Also confirmed coordinator@demo.theclearline.io has no real Supabase Auth user behind it and only "worked" because RLS was off — needs a real account before the authenticated-only policies are safe to run.

Admin creates vendor with name, type (campus contact vs. software/data-only), contact info. Vendor type determines which documents are required.

Replace demo static data with real Supabase queries. Search, filter by status/type. Four statuses: Active (all docs current), Expiring (doc expiring within 30 days), Action Required (missing or expired doc), Inactive (offboarded). No separate "approved" state — vendors are invited, so approval is implicit; compliance status tells the full story.

Admin sets offboard date and reason. Only the district's relationship record is deactivated — the vendor account, documents, and BGC records are untouched and remain active for any other districts they serve. Audit log entry written. On the vendor's portal, the offboarded district moves to a "Past districts" section (not removed) so the vendor retains their history. Vendor loses the ability to submit documents for that district but can still see their record.

Admin initiates re-onboard with date and reason. System emails vendor contact. Vendor resubmits expired documents. Audit log entry written.

Vendors upload PDFs (COIs, BGC letters, signed agreements, W-9s) to Supabase Storage. Files scoped by vendor and document type. Admin can view/download.

Vendor submits document → status: Pending review. Admin approves or rejects — rejection requires a written note. Documents are never overwritten: rejection creates a superseded_by link to the new submission, preserving the full chain. Vendor notified on each status change. Audit log entry written on each transition.

Admin can see the full submission history for any document type per vendor: what was submitted, when it was rejected, the rejection note, and what replaced it. "Current" document is always the row where superseded_by is null. Supports point-in-time audit queries ("what did we have on file on March 1?").

Each document instance has two key dates: (1) submission deadline — when the district expects the vendor to submit it, set per requirement; (2) expires_at — how long the document is valid once submitted, confirmed by admin on approval (auto-suggested from the requirement's validity period, but admin can override based on the actual dates on the document). System flags documents approaching expiration. Expiry drives compliance status on vendor record.

Checklist of required documents is driven by the compliance_requirements table for the district (not hardcoded). Admin sees what's submitted, what's pending, and what's missing. Requirements are data, not code — changes to district requirements take effect immediately without a deploy.

Vendors maintain a roster of employees who access campuses. Each employee has their own BGC clearance status. Admin can see roster per vendor.

Each employee row in the roster can have an uploaded DOJ LiveScan clearance letter. Admin can view individual clearance docs. Status: cleared / pending / flagged.

When a vendor adds a new employee to the roster, system flags that a BGC clearance is required before campus access. Drives action item on admin dashboard.

Employee roster shows Active tab by default. Vendor can mark an employee as inactive (left the company) — record is retained, not deleted. Inactive tab shows history. Admin and school staff can search across both tabs. Ensures that a former employee's clearance record isn't lost but also isn't presented as current.

When admin creates a vendor and sends invite, vendor contact receives an email from compliance@theclearline.io with a secure onboarding link. Email includes district name and what's needed from them. Invite link expires after 30 days. If vendor has not registered, admin sees a "Resend invite" button and the original link is invalidated when a new one is sent. Audit log captures: invite sent, invite resent, registration completed.

Automated email to vendor contact when a document is expiring in 30 days (and again at 14 days). Includes which document and a link to the vendor portal to resubmit.

Vendor receives email when admin approves or rejects a submitted document. Rejection email includes admin's note explaining what's needed.

When admin initiates re-onboarding, vendor contact receives email with re-onboard date, reason, and link to portal to resubmit expired documents.

Every state change writes a row: actor, action string (e.g. "document.rejected"), entity type + id, timestamp, and a jsonb metadata column for event-specific context (rejection reason, old/new status, offboard date, etc.). Flexible schema means new event types never require a migration — just start writing rows with a new action string.

Replace demo static log with live Supabase data. Per-vendor log and district-wide log. Filterable by date, action type, actor.

Admin can download a CSV of the full audit log for a specific vendor. Includes timestamp, action, actor, and relevant metadata. One-click download from vendor detail page.

Admin can download a full district audit log across all vendors for a given date range. For use during formal audits or board reviews.

When a vendor is approved against a district's requirements, record a snapshot of which requirement version was active at that moment. Historical compliance queries (e.g. "were they compliant in June?") use the snapshot, not today's rules. Critical for audit defense — if requirements change after approval, the historical record reflects what was actually required at the time. This is a schema decision that is hard to retrofit later; must be designed in from the start.

Clearline ships with pre-built requirement sets and default vendor types (Campus Contact, Software/Data-Only, etc.) as a starting point. These are stored as data in compliance_requirements and vendor_types — not hardcoded — so they can be updated as we learn from real districts without a code deploy. When a new district is onboarded they receive these defaults automatically.

Core selling point: each district starts from the Clearline base template and customizes — adding, removing, or modifying required document types per vendor category. Districts can also create their own vendor types (e.g. "Food Service Contractor", "Maintenance & Facilities") beyond the Clearline defaults, each with their own requirement set. For each requirement, the district specifies: (1) submission deadline and (2) validity period. Changes to requirements apply going forward — existing approved vendors receive a grace period notification rather than becoming instantly non-compliant.

Replace hardcoded demo numbers with real Supabase counts: approved vendors, awaiting decision, expiring within 30 days, flagged for review.

A screen for us (not district admins) to see activity across every district and user at once — who's logged in, what's changed, status at a glance — for troubleshooting before support tickets pile up. Explicitly read-only: no editing, no impersonation, no acting on a user's behalf from this screen. If a real change needs to be made for someone (admin or vendor), that goes through a separate, trackable ticket/request flow instead, so there's always an auditable record of who asked for what and why — never a silent superadmin edit. Wanted before the first real district, not post-launch. Worth noting for whoever scopes it: there's currently no per-district RLS boundary at all (B-027 not built yet) — any authenticated admin can already see every district's data — so this view's "visibility" is mostly a UI/aggregation problem today, not an access-control one. The separate on-behalf-of ticket flow, whenever it's built, is a different story and should probably wait for B-027 so it has an actual boundary to route around.

Role for board members or compliance reviewers. Can view all vendor records, documents, and audit logs but cannot take any action. No write access.

Role scoped to a single school within the district. Use case: a new vendor employee shows up on campus; the front office or principal logs into Clearline and searches by vendor name or employee name to verify BGC clearance before granting access. Read-only, no document or vendor management access. Shows cleared / pending / flagged status and the clearance letter if available.

Multiple district staff can have admin accounts under the same district. Useful for larger districts where compliance is shared across business office staff. Demoed with a two-role UI stub (Coordinator / Administrator, hardcoded to fixed demo emails) — real per-person accounts and role assignment still need building.

The vendor-account equivalent of B-027: more than one real login per vendor account, all resolving to the same vendor_id. Directly unblocks the "sole contact left the company" and "second employee at an existing vendor domain" cases noted in B-004b — right now a vendor account has exactly one login, full stop.

When a Coordinator needs something only an Administrator can do (a background-check override, inviting a new team member, etc.), there's currently no way to ask for it inside Clearline — it happens over email/Slack/hallway conversation, with no record anywhere. Add a lightweight request flow: Coordinator flags what they need and why, it lands on the Administrator's to-do list/dashboard with a timestamp, so these escalations are tracked and dated in the system instead of living entirely outside it. Not urgent — real per-person accounts (B-027) make this more valuable, since right now there's only one shared Coordinator login and one shared Administrator login, so there's no specific colleague to route a request to yet.

Replace demo portal with authenticated vendor session. Vendors see their own required documents, submission status, and compliance checklist. Currently the portal is UI-only.

Vendor-facing upload flow: drag-and-drop or browse, shows required document types, upload progress, submission confirmation. Triggers admin notification.

Admin can generate a compliance report for any vendor as of any date. Shows document status, BGC status, and deadlines as they stood at that moment. Downloadable as PDF or CSV.

District-wide view of all documents expiring within a configurable window (30/60/90 days). Filterable by vendor, document type, and date range.

When a vendor is onboarded to a district, Clearline auto-generates a PDF invoice for the district's annual compliance fee, addressed to the vendor. Emails it directly to the vendor contact, CC's the district admin. Renewal invoices auto-generated annually. District admin can view outstanding vs. paid invoices per vendor. Payment collection happens outside Clearline — this feature eliminates the manual invoicing work for the district, not the payment step.

Admin UI for creating, renaming, and managing vendor types for their district. District can add custom types beyond Clearline defaults (e.g. "Transportation", "Food Service", "Facilities"), each with their own compliance requirement set. Vendor type is set per district relationship — the same vendor can be classified differently across districts. Clearline default types are visible but only editable by Clearline staff. Confirmed 2026-08-11: the district-scoping this depends on is already correct — vendor_types.district_id already separates each district's own types from Clearline's shared defaults, and both the invite modal and Policies page already query for exactly that combination. What's actually missing is the creation UI itself; today Food Service/Transportation/Maintenance & Facilities exist only because they were inserted directly in the database, not through the product. Not a blocker for the current single-district demo — new districts get this via guided onboarding (Clearline sets up their initial types with them) until this ships. Also confirmed: there's no dedicated "change a vendor's type after invite" control yet either. Today's workaround — re-running Invite vendor with the corrected type — does work (upserts vendor_type_id) but has side effects worth fixing later: it resends the real invite email and wipes any per-vendor document exclusions that had been set.

District admin searches for a vendor already in Clearline by name or EIN. System shows what documents and BGC records are already on file. Admin selects the vendor, reviews existing compliance state, and requests only what's missing for their district's requirements. Vendor gets a notification listing the gap — no need to re-upload documents the district already accepts.

Documents are already retained forever once submitted (they're keyed to vendor + requirement, never deleted), but there's no UI to see them once they fall outside the vendor's *current* required-document set — e.g. after a vendor type change (see B-031b) or when a requirement they're not currently subject to still has an old file on record. Build: (1) a real per-vendor document library showing everything ever submitted, current requirement or not; (2) when a new or changed requirement could be satisfied by something already on file (same document category, e.g. Certificate of Insurance), let the vendor choose "reuse this" vs. "upload new" instead of a blind re-upload. Same underlying need as B-032 (vendor joins new district) — a vendor shouldn't have to resubmit what Clearline already has, whether the trigger is a new district or a type change within one they're already in.

Admin can set district-specific deadlines (e.g. all COIs must be renewed by August 1 each year). System flags vendors approaching deadlines based on district calendar.

Vendor document submissions get an "AI reviewed" or "AI flagged" pass before the admin looks at them, shown as a small label directly under the filename (deliberately not styled like a status pill, so it doesn't compete with the real document status) plus, only for flagged documents still awaiting a decision, a full-width panel with the specific finding and an action-oriented recommendation (e.g. "Recommend rejecting. Coverage is below required amount."). Human always makes the final call, never the AI implicitly: the flagged panel offers "Confirm rejection" (pre-fills the rejection note from the AI's finding, editable) and "Dismiss flag" (admin disagrees, document stays pending as normal). Demo version (2026-08-14): scripted per document, not real parsing — an insurance-coverage-shortfall rule applies vendor-wide, plus a second rule scoped specifically to Maggie's Nursery's Data Processing Agreement (flagged as unsigned) for the live-invite demo persona, since several other vendors already have a real, correctly-approved DPA on file that a label-only rule would have wrongly flagged. Real version needs actual document parsing.

Simple chat interface, aimed at C-level users who don't want to dig through the UI — ask about a specific vendor's compliance status and get a plain-language summary back. Decided to build this as a genuinely functional, rule-based / DB-backed feature rather than a faked demo, since a chat that's obviously scripted is easy to expose by asking it something slightly off-script.

Automated daily email to district admins — and anyone else they choose, the send field takes any recipient, not just the logged-in admin — with three sections: (1) recap of today (documents submitted, approved, rejected; vendors invited); (2) what needs your attention (documents currently pending review); (3) what's coming up (documents expiring within the next 7 days). Labeled "AI-generated" with a disclaimer that it's a summary, not a substitute for reviewing records directly. In-app page is styled as a preview of the actual email (masthead, prose recap) rather than a dashboard clone, deliberately distinct from the Home page. Built and demoed 2026-08-13.

When a district needs vendors to sign an updated version of a document (e.g. a revised Data Processing Agreement), the admin should be able to upload the new version and set the date it takes effect — without touching any vendor's already-submitted, already-approved copy of the old version. Before the effective date, nothing changes. On and after it, only new submissions are compared against the new version; a vendor who uploads the prior version after the cutover gets it flagged by AI review as outdated. Explicitly no mass notification or bulk re-flagging of everyone's already-approved documents when the new version goes live — that would create false urgency for vendors who are still legitimately compliant under the version they actually signed. Distinct from B-011a (a single vendor's own resubmission history) and B-024a (requirement rule snapshots) — this is about which version of the template document itself was in effect at a given time, and is a natural extension of B-040's AI review once that exists for real.

Beyond a single plain-text answer, Ask Clearline AI should be able to generate a formatted report on request — e.g. "give me a compliance report for this quarter" or "export everything expiring this month" — output as something presentable and shareable (PDF or CSV), not just a chat reply. Natural extension of B-041's existing rule-based, DB-backed query logic (the same "what needs attention" / "what's expiring" aggregate queries already built for the chat), just rendered as a document instead of a reply.

Regulatory requirements generally need stricter, less negotiable deadlines than Administrative ones (see the Regulatory/Administrative split, 2026-08-17) — right now there's no per-requirement due date at all, just the vendor-level one-time compliance_deadline set at invite. Needs a real due date per requirement, and the ability to override it for a specific vendor — override is explicitly Administrator-only, not Coordinator. Flagged as non-trivial by design, not just to build: it touches the invite flow (deadlines need to be shown/confirmed at invite time, not just assigned silently) and how the admin continues to engage with a vendor after they're already invited (an override happening mid-relationship, after documents may already be in flight). Needs real design thought before implementation — noted here so it isn't lost, not scoped yet.

"Are we improving or slipping" needs a history to compare against — today the app only ever computes current-moment stats live from documents/vendor_district_relationships, nothing is snapshotted over time. Needs a periodic (daily?) job that records aggregate stats (compliance rate, counts by status/category) into a new table, so Ask Clearline AI can answer "how does this compare to last month" instead of only "what's true right now." Natural extension of the chatbot's stats work (2026-08-18) once that ships — blocked on having history to query, not on chatbot logic itself.

One Clearline account managing multiple districts. Useful for county offices of education or management companies overseeing several districts.

District admin can configure how long audit logs are retained before archiving. Default: 7 years. System archives (not deletes) older records.

Admin can upload a CSV to import an existing vendor list. Maps columns to vendor fields. Useful for districts migrating from spreadsheets.

Required disclosure step during vendor approval: vendor certifies no financial relationship with district employees. Admin certifies no financial interest in vendor. Logged and timestamped.

Track per-employee completion of mandated reporter training for campus-contact vendors. Ties into BGC roster. Required in California for anyone working with minors.

Clearline bills districts annually via Stripe. District record holds stripe_customer_id and trial_ends_at (set at signup for 90-day free trial). Webhook handles subscription events: payment succeeded, failed, canceled, trial ending soon. Middleware checks subscription status before granting district admin access.

Invoice generation (B-031a) handles the work of creating and sending invoices to vendors. The payment collection step — how vendors actually pay the district — needs to be designed with early district customers. Options include Stripe Connect (district-owned account), CSV export to district AR systems (Munis, Escape Online), or district-specific integrations. Requires legal review on Prop 26/218 fee structure before building. Do not design until first districts are live and have expressed a need.

Admin tool to reset demo data to a clean state including vendor records, documents, and audit logs. Currently only resets vendor rows; does not reset documents.