Feature spec
IntermediateCategory
notifications-alerts
Build with AI
4-6 hours with Lovable or V0
Custom build
1-2 weeks custom dev
Running cost
$0-5/mo up to 100 users · $25-45/mo at 1,000 users
Works on
Everything it takes to ship Document Expiration Reminders — parts, prompts, and real costs.
Document expiration reminders need a documents table with expiry dates, a daily cron job that compares today's date against each document's threshold array (90, 30, 7, 1 days), and a reminder log that blocks duplicate sends. With Lovable or V0 you can ship this in 4-6 hours. Running cost is $0-5/month at 100 users, rising to $25-45/month at 1,000 users once Supabase Pro is needed for pg_cron.
What Document Expiration Reminders Actually Are
Document expiration reminders are a combination of a date-aware data model and a scheduled notification pipeline. Users upload passports, driver's licenses, insurance certificates, professional certifications, or contracts and record the expiry date. A daily cron job compares today's date against each document's configured thresholds — typically 90, 30, 7, and 1 day before expiry — and sends an email (or push notification) exactly once per threshold crossing, tracked in a reminder log table. The product decisions that matter are: who configures the reminder schedule (user-editable per document vs. system-wide defaults), how expired documents are surfaced in the UI (red badge, locked state, or just a warning), and whether you need optional OCR to extract expiry dates automatically from uploaded files.
What users consider table stakes in 2026
- Document list shows expiry date and a color-coded status badge (green = valid, amber = expiring within 30 days, red = expired) with days-remaining count visible at a glance
- Reminders sent automatically at 90, 30, 7, and 1 day before expiry — each threshold fires exactly once per document, never repeatedly
- Reminder notification includes the document name, days remaining, and a direct link to upload the renewal or add a note
- Users can customize which thresholds trigger reminders per document (e.g., only 30-day and 7-day) without deleting and re-adding the document
- Expired documents are clearly separated from active ones and remain visible with an 'Update document' call to action rather than disappearing
- Audit trail of all notifications sent per document available in the UI so users can confirm a reminder was dispatched
Anatomy of the Feature
Seven components. AI tools handle the document list and upload form reliably. The cron job, reminder deduplication, and timezone-safe date arithmetic are where builds break.
Document List Screen
UITable or card grid showing document name, type (passport, driver's license, insurance, contract, certification), expiry date, and a computed status badge. Status is derived client-side using date-fns differenceInDays(parseISO(expiry_date), new Date()) or a Supabase computed column. Built with shadcn/ui Table on web or Flutter DataTable on mobile.
Note: Parse expiry_date as a date-only string (YYYY-MM-DD) to avoid timezone shifts — date-fns parseISO handles this correctly; new Date(expiry_date) does not.
Document Upload and Date Entry
UIFile upload input that stores the file in Supabase Storage and writes the returned public URL to the documents table alongside the manually entered expiry_date via a date picker. Optional: an AI OCR step using Google Cloud Vision or Tesseract.js that extracts the expiry date from the uploaded image to pre-populate the date field.
Note: OCR must be prompted explicitly — AI tools will not scaffold it automatically. Tesseract.js is free but lower accuracy; Cloud Vision is $1.50 per 1,000 pages (approx) and handles non-English documents.
Documents Table
DataSupabase PostgreSQL table: documents (id, user_id, name, type, file_url, expiry_date date, reminder_days int[] DEFAULT '{90,30,7,1}', is_active bool, created_at). The reminder_days array is per-document so users can customize their threshold schedule. Indexed on (user_id, expiry_date) for fast expiry queries.
Note: Store expiry_date as a PostgreSQL date type, not timestamptz — this prevents timezone drift when the cron compares CURRENT_DATE.
Expiry Check Cron Job
BackendSupabase pg_cron job or Vercel Cron scheduled to run daily at 06:00 UTC. SQL logic: SELECT d.* FROM documents d WHERE d.is_active = true AND d.expiry_date >= CURRENT_DATE AND (d.expiry_date - CURRENT_DATE) = ANY(d.reminder_days) AND NOT EXISTS (SELECT 1 FROM reminder_log rl WHERE rl.document_id = d.id AND rl.days_before = (d.expiry_date - CURRENT_DATE) AND rl.channel = 'email'). This finds documents that are exactly N days away, where N is in their reminder_days array, and have not yet been notified at this threshold.
Note: pg_cron is only available on Supabase Pro ($25/mo). On the free tier, use an external free scheduler (cron-job.org) to call a Supabase Edge Function via HTTP on a daily schedule.
Reminder Sender
BackendSupabase Edge Function invoked by the cron job. Fetches the document owner's email from auth.users, renders an email via Resend with the document name, days remaining, and a renewal CTA link, then inserts a row into reminder_log to prevent re-send. Uses INSERT INTO reminder_log ... ON CONFLICT DO NOTHING for idempotency.
Note: Store RESEND_API_KEY in Supabase Secrets (Cloud tab), never in frontend code. Edge Functions run as Deno — import Resend via esm.sh or the @resend/node npm equivalent available in Deno.
Reminder Log Table
Datareminder_log (id, document_id REFERENCES documents ON DELETE CASCADE, days_before int, channel text, sent_at timestamptz) with a UNIQUE(document_id, days_before, channel) constraint. The UNIQUE constraint is the core deduplication mechanism — any duplicate send attempt from a cron retry fails silently via ON CONFLICT DO NOTHING.
Note: ON DELETE CASCADE is critical — without it, reminders for deleted documents will error or try to re-send for a document that no longer exists.
Expired Document Alert UI State
UIAutomated visual state: documents where expiry_date < CURRENT_DATE show a red 'Expired' badge and an 'Update document' CTA. Documents within 30 days show amber. Documents valid for 30+ days show green. Implemented as a computed column in a Supabase view or as a client-side classification using differenceInDays from date-fns.
Note: A Supabase view with a CASE expression for status is cleaner than computing it in every client — status is always consistent regardless of which device or screen queries it.
The data model
Run this in the Supabase SQL editor. Creates the documents table, reminder log with deduplication constraint, RLS policies, and a supporting index. PostGIS is not required for this feature.
1-- Enable storage extension if using Supabase Storage for file uploads2-- (already enabled by default on Supabase projects)34create table public.documents (5 id uuid default gen_random_uuid() primary key,6 user_id uuid references auth.users not null,7 name text not null,8 type text,9 file_url text,10 expiry_date date not null,11 reminder_days int[] default '{90,30,7,1}',12 is_active bool default true,13 created_at timestamptz default now()14);1516alter table public.documents enable row level security;1718create policy "Users can manage own documents"19 on public.documents20 for all21 using (auth.uid() = user_id)22 with check (auth.uid() = user_id);2324create index documents_user_expiry_idx25 on public.documents (user_id, expiry_date);2627-- Reminder log: prevents duplicate sends via UNIQUE constraint28create table public.reminder_log (29 id uuid default gen_random_uuid() primary key,30 document_id uuid references public.documents(id) on delete cascade not null,31 days_before int not null,32 channel text default 'email',33 sent_at timestamptz default now(),34 unique(document_id, days_before, channel)35);3637alter table public.reminder_log enable row level security;3839-- Service role inserts logs; users can read their own via join40create policy "Service role can insert reminder logs"41 on public.reminder_log42 for insert43 to service_role44 with check (true);4546create policy "Users can view own reminder logs"47 on public.reminder_log48 for select49 using (50 exists (51 select 1 from public.documents d52 where d.id = document_id and d.user_id = auth.uid()53 )54 );5556-- Convenience view for document status57create view public.documents_with_status as58select59 *,60 (expiry_date - current_date) as days_remaining,61 case62 when expiry_date < current_date then 'expired'63 when (expiry_date - current_date) <= 30 then 'expiring'64 else 'valid'65 end as status66from public.documents67where is_active = true;Heads up: The UNIQUE(document_id, days_before, channel) constraint on reminder_log is the most important line in this schema — it makes all cron retries and Edge Function re-executions safe without extra code.
Build it — pick your path
Each tool fits this feature differently. Switch tabs to compare fit, time, the exact steps, and a copy-paste prompt.
Full control over the OCR pipeline, multi-channel delivery, renewal workflow integrations, and compliance audit logging. Right for regulated industries or organizations where multiple team members manage shared documents.
Step by step
- 1OCR extraction pipeline: Google Cloud Vision API for multi-language document scanning; parse expiry dates from passports, visas, and certificates automatically on upload
- 2Multi-channel delivery: email via Resend, push via FCM, SMS via Twilio — user configures channel preference per document type
- 3Role-based notification routing for teams: document owner receives the reminder, team admins receive a summary digest if any team member has expiring documents
- 4Renewal workflow integration: DocuSign or Adobe Sign triggered from the reminder email so users can start the renewal process in one click
- 5Compliance audit log: immutable log of every reminder dispatched, opened, and actioned for ISO/SOC 2 audits
Where this path bites
- OCR accuracy for non-English or handwritten documents requires model fine-tuning beyond what off-the-shelf APIs provide
- DocuSign and Adobe Sign integrations require business-tier API plans and considerable setup
Third-party services you'll need
Core email delivery and file storage are the only required paid services. OCR is optional and only needed if you want automatic date extraction from uploaded documents.
| Service | What it does | Free tier | Paid from |
|---|---|---|---|
| Supabase pg_cron | Schedules the daily SQL function that identifies documents hitting reminder thresholds | Not available on free tier | Included in Supabase Pro plan $25/mo |
| Resend | Transactional email delivery for expiry reminder notifications | 3,000 emails/mo free | Pro $20/mo for 50,000 emails/mo |
| Supabase Storage | File storage for uploaded document images and PDFs | 1 GB included on free tier | Pro plan $25/mo includes 100 GB; $0.021/GB/mo additional |
| Google Cloud Vision OCR | Automatic expiry date extraction from uploaded document images (optional) | 1,000 pages/mo free | $1.50 per 1,000 pages (approx) |
| Tesseract.js | Open-source OCR alternative for date extraction; lower accuracy than Cloud Vision (optional) | Free, open-source | Free |
Swipe the table sideways to see pricing.
What it costs to run
Drag through the tiers to see how your monthly bill scales with users — no surprises later.
Estimated monthly running cost
Supabase free tier covers DB and 1 GB Storage for document files. Resend free tier covers email volume at 100 users. pg_cron not available on free tier — use cron-job.org free plan to call the Edge Function daily. Approximate cost: $0-5/mo if file storage stays under 1 GB.
Estimates use verified 2026 pricing and assume typical usage per user. Your real bill depends on activity, storage, and third-party plans.
What breaks when AI tools build this
The failures people actually hit on their first build — the symptom, why it happens, and the exact fix.
The same reminder email sends multiple times on the same day
Symptom: pg_cron runs the expiry check at 06:00 UTC, but if the Edge Function times out and pg_cron retries it, or if the function is manually re-invoked, the same reminder fires again. Without a UNIQUE constraint on reminder_log, each execution inserts a new row and triggers a new email send.
Fix: Add UNIQUE(document_id, days_before, channel) to the reminder_log table and use INSERT INTO reminder_log ... ON CONFLICT DO NOTHING in the Edge Function. Every re-execution becomes idempotent — the unique constraint rejects the duplicate silently.
Reminders fire for documents the user already deleted
Symptom: If the reminder_log foreign key lacks ON DELETE CASCADE, deleting a document leaves orphaned reminder_log rows. The cron job tries to re-evaluate those rows, finds no matching document, and depending on implementation may error or silently skip — but in some setups it re-sends to a stale email address.
Fix: Define the foreign key as: document_id uuid REFERENCES public.documents(id) ON DELETE CASCADE. This automatically removes all reminder_log rows when the parent document is deleted, so deleted documents are naturally excluded from the next cron run.
Reminder shows the wrong days-remaining count because of timezone offset
Symptom: The pg_cron job runs at 06:00 UTC and calculates (expiry_date - CURRENT_DATE). A user in UTC+10 (Sydney) whose document expires on August 1 in their timezone may receive a '0 days remaining' email on July 31 UTC — one day early — because CURRENT_DATE in the database is already August 1 UTC. The email count says 0 days remaining but the user still has a full local day.
Fix: Store expiry_date as a PostgreSQL date type (not timestamptz) to remove time component drift. At email render time, calculate days remaining from the user's stored timezone_preference using a timezone-aware formula rather than raw CURRENT_DATE arithmetic. Run the cron at 06:00 UTC to give a buffer for all UTC+ timezones.
V0-generated dashboard shows 'NaN days remaining' for some documents
Symptom: V0 generates date arithmetic using new Date(expiry_date) which parses a date string like '2025-08-01' as midnight UTC. When the user's browser is in a UTC-offset timezone, the parsed date shifts by one day, and differenceInDays produces an off-by-one result or NaN for edge cases.
Fix: Always parse date-only strings with date-fns parseISO(expiryDate) instead of new Date(expiryDate). parseISO treats the string as a local date without timezone conversion, giving the correct day difference regardless of the user's timezone.
Best practices
Store expiry_date as PostgreSQL date type, never as timestamptz — this is the single most important decision for preventing timezone-related reminder drift
Make the UNIQUE(document_id, days_before, channel) constraint and ON CONFLICT DO NOTHING your first line of defense against duplicate emails, not application-level checks
Let users customize reminder_days per document as an array — a user managing annual certifications wants 90-day and 30-day notices; one tracking weekly compliance checks may only want 1-day
Never automatically delete expired documents — mark them with is_active or a status column so users retain historical records and can update the expiry date on renewal
Log every sent reminder in reminder_log with a timestamp and channel — this single table serves as both deduplication guard and audit trail without extra infrastructure
Show the reminder_log history in the UI per document so users can confirm a notification was dispatched and troubleshoot missed emails before contacting support
For the free-tier alternative to pg_cron, use cron-job.org (free) to send a daily POST request to your Supabase Edge Function with a shared secret header for authentication
Test reminder delivery with a document whose expiry_date is exactly N days in the future before going live — set reminder_days to '{1}' and a document expiring tomorrow to verify the full pipeline end-to-end
When You Need Custom Development
Lovable and V0 handle the core expiry-check-and-email pipeline well. You outgrow them when the feature becomes an organizational compliance workflow:
- Documents are shared across a team or organization with role-based notification routing — team admin receives a digest, document owner receives the individual reminder, and a designated renewal owner gets a separate escalation
- Regulatory compliance audit trail required for all reminder events with immutable timestamp records (ISO 27001, SOC 2, HIPAA) that survive document deletion
- OCR extraction pipeline needed for bulk document upload where hundreds of passports or licenses must have their expiry dates extracted automatically at intake
- Integration with renewal workflows — DocuSign e-signature, a third-party renewal portal, or a ticketing system — so a reminder triggers an automated renewal process, not just an email
RapidDev builds these features for production
Real auth, database, and payments — fixed price, delivered in weeks. You own 100% of the code.
Frequently asked questions
How do I add a new document type like vehicle registration?
The document type is stored as a text field in the documents table, so any string is valid. In the UI, offer a selector with preset types (passport, driver's license, insurance, contract, certification) plus a 'Custom' option that shows a free-text input. No schema change needed — the new type appears in the list immediately on next query.
What if I only want reminders 30 days and 7 days out, not 90 and 1?
The reminder_days column is an integer array per document, so users can set any combination. In the UI, render checkboxes for 90, 30, 7, and 1 day options and store the selected values as the reminder_days array. The cron query uses = ANY(d.reminder_days) so only the configured thresholds trigger a send — no backend logic change needed.
Will a reminder still fire if the user deletes the document?
No, provided you add ON DELETE CASCADE to the reminder_log foreign key on document_id. When a document row is deleted, all its reminder_log rows are deleted too. The cron query joins to the documents table, so deleted documents are excluded naturally.
Can the app automatically read the expiry date from an uploaded photo?
Yes, with an OCR step. Google Cloud Vision Document Text Detection can extract dates from standard document images with high accuracy — free for the first 1,000 pages per month, then $1.50 per 1,000 pages (approx). Tesseract.js is a free open-source alternative that runs in the browser or Edge Function but has lower accuracy on low-quality scans. You need to prompt this step explicitly — AI tools will not scaffold it automatically.
What happens to the document record when it expires — is it deleted?
Documents should never be auto-deleted on expiry. Set is_active = false or add a status column and show expired documents in a separate 'Expired' section with an 'Update document' call to action. Users need the historical record to track renewal timelines, and deleting it would also remove all reminder_log history.
Can I share document expiry tracking with a team?
The base schema stores user_id on each document so only the owner sees their records. To add team access, add an organization_id column and update RLS policies to allow SELECT for users who belong to the same organization. This is a significant schema extension — if team document management is a core use case from day one, build the org structure into the data model before you launch.
Does this work for certifications that renew every year?
Yes, but auto-renewal of the expiry_date is not built in by default. When the user uploads the renewed certificate, they update expiry_date to the new expiry. Any existing reminder_log rows for the old thresholds remain but are no longer relevant — new reminders will start firing based on the updated date. You can optionally auto-clear reminder_log rows on expiry_date UPDATE via a database trigger.
How do I change my notification email?
The reminder sender fetches the email from auth.users at send time, so it always uses the current Supabase Auth email for the account. If the user changes their email in your app's profile settings via Supabase Auth's updateUser(), future reminders go to the new address automatically — no changes to the reminder pipeline needed.
Need this feature production-ready?
RapidDev builds document expiration reminders into real apps — auth, database, payments — at $13K–$25K.
Book a free consultation30-min call. No commitment.