# How to Add Document Expiration Reminders to Your App — Copy-Paste Prompts Inside

- Tool: App Features
- Last updated: July 2026

## TL;DR

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.

## 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** (ui): Table 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.
- **Document Upload and Date Entry** (ui): File 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.
- **Documents Table** (data): Supabase 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.
- **Expiry Check Cron Job** (backend): Supabase 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.
- **Reminder Sender** (backend): Supabase 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.
- **Reminder Log Table** (data): reminder_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.
- **Expired Document Alert UI State** (ui): Automated 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.

## 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.

```sql
-- Enable storage extension if using Supabase Storage for file uploads
-- (already enabled by default on Supabase projects)

create table public.documents (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users not null,
  name text not null,
  type text,
  file_url text,
  expiry_date date not null,
  reminder_days int[] default '{90,30,7,1}',
  is_active bool default true,
  created_at timestamptz default now()
);

alter table public.documents enable row level security;

create policy "Users can manage own documents"
  on public.documents
  for all
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create index documents_user_expiry_idx
  on public.documents (user_id, expiry_date);

-- Reminder log: prevents duplicate sends via UNIQUE constraint
create table public.reminder_log (
  id uuid default gen_random_uuid() primary key,
  document_id uuid references public.documents(id) on delete cascade not null,
  days_before int not null,
  channel text default 'email',
  sent_at timestamptz default now(),
  unique(document_id, days_before, channel)
);

alter table public.reminder_log enable row level security;

-- Service role inserts logs; users can read their own via join
create policy "Service role can insert reminder logs"
  on public.reminder_log
  for insert
  to service_role
  with check (true);

create policy "Users can view own reminder logs"
  on public.reminder_log
  for select
  using (
    exists (
      select 1 from public.documents d
      where d.id = document_id and d.user_id = auth.uid()
    )
  );

-- Convenience view for document status
create view public.documents_with_status as
select
  *,
  (expiry_date - current_date) as days_remaining,
  case
    when expiry_date < current_date then 'expired'
    when (expiry_date - current_date) <= 30 then 'expiring'
    else 'valid'
  end as status
from public.documents
where is_active = true;
```

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 paths

### Lovable — fit 4/10, 4-6 hours

Lovable handles Supabase Storage for file uploads, pg_cron for the daily expiry check, and Resend via Edge Function for email delivery. Good fit for document management SaaS where auth and database are needed together.

1. Create a new Lovable project and connect Lovable Cloud so auth, the Supabase database, and Storage are provisioned automatically
2. Open the Cloud tab, go to Secrets, and add your RESEND_API_KEY — never put it in frontend code
3. Paste the prompt below in Agent Mode; let it scaffold the document list screen, upload form, Edge Function, and reminder log table
4. Enable pg_cron in the Supabase Dashboard under Database → Extensions, then add the daily schedule via the pg_cron UI or the SQL editor
5. Click Publish and test on the live URL — the upload and reminder flow requires the real Supabase Storage endpoint, not the preview

Starter prompt:

```
Build a document expiration reminder feature. Create a Supabase table called documents (id uuid primary key, user_id uuid references auth.users, name text not null, type text, file_url text, expiry_date date not null, reminder_days int[] default '{90,30,7,1}', is_active bool default true, created_at timestamptz default now()) with RLS so users only access their own rows. Create a companion reminder_log table (document_id references documents ON DELETE CASCADE, days_before int, channel text default 'email', sent_at timestamptz, UNIQUE(document_id, days_before, channel)) with service_role INSERT policy. UI: a document list screen showing name, type, expiry_date, and a status badge computed from days_remaining (expired = red, expiring within 30 days = amber, valid = green) using differenceInDays from date-fns. Expired documents show an 'Update document' button. A document upload modal with a file input (upload to Supabase Storage and store the returned URL in file_url), a document type selector (passport, driver's license, insurance, contract, certification, custom), a date picker for expiry_date, and a reminder_days multi-select (90, 30, 7, 1 days — default all four selected). A Supabase Edge Function called check-document-expiry that: queries documents WHERE is_active = true AND expiry_date >= CURRENT_DATE AND (expiry_date - CURRENT_DATE) = ANY(reminder_days) AND NOT EXISTS (SELECT 1 FROM reminder_log WHERE document_id = d.id AND days_before = (expiry_date - CURRENT_DATE) AND channel = 'email'); for each match, fetches the user's email from auth.users via service_role, sends an email via Resend (RESEND_API_KEY from env) with the document name, days remaining, and a link back to the app; then inserts into reminder_log using ON CONFLICT DO NOTHING. Edge case: if user deletes a document, ON DELETE CASCADE removes its reminder_log rows automatically. Also create an 'Update document' flow that lets the user replace the file and update expiry_date on an existing document record rather than creating a duplicate.
```

Limitations:

- pg_cron is not available on the Supabase free tier — on free, use cron-job.org to call the Edge Function via HTTP on a daily schedule
- AI date extraction (OCR) from uploaded documents must be prompted as a separate explicit step — Lovable will not scaffold it automatically
- The Lovable preview iframe cannot test file uploads to Supabase Storage — always test on the published URL

### V0 — fit 4/10, 4-5 hours

V0 generates a clean Next.js document dashboard with status badges; Vercel Cron handles the daily check; Resend API route handles email. Best when the document reminder is one feature inside a larger Next.js app.

1. Prompt V0 with the spec below to generate the document list page, upload modal, and reminder logic
2. Add Supabase environment variables in the V0 Vars panel (NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_ANON_KEY, SUPABASE_SERVICE_ROLE_KEY, RESEND_API_KEY)
3. Run the SQL schema from this page in the Supabase SQL editor to create documents and reminder_log tables
4. Add the Vercel Cron configuration in vercel.json: { "crons": [{ "path": "/api/check-document-expiry", "schedule": "0 6 * * *" }] }
5. Deploy to Vercel and test the upload and email flow on the live preview URL

Starter prompt:

```
Build a Next.js document expiration reminder feature using Supabase and Resend. Document list page at /documents: fetch from a Supabase 'documents' table (id, user_id, name, type, file_url, expiry_date date, reminder_days int[], is_active bool, created_at) using the user's session. Compute days_remaining = differenceInDays(parseISO(doc.expiry_date), new Date()) for each document. Display a shadcn/ui Table with columns: Name, Type, Expiry Date, Days Remaining, Status badge (expired red / expiring amber / valid green based on days_remaining), and Actions (edit, delete). Add an 'Add document' button that opens a shadcn/ui Dialog with: name text input, type selector (passport, driver's license, insurance, contract, certification, or custom text), expiry date date picker, reminder thresholds checkboxes (90 / 30 / 7 / 1 days, all checked by default), and a file upload that sends the file to Supabase Storage and stores the URL in file_url. Create a Next.js API route at /api/check-document-expiry (POST, protected by a CRON_SECRET header check) that: uses the Supabase service role client to SELECT documents WHERE is_active = true AND expiry_date >= CURRENT_DATE AND (expiry_date - CURRENT_DATE) = ANY(reminder_days) AND NOT EXISTS (SELECT 1 FROM reminder_log WHERE document_id = d.id AND days_before = (expiry_date - CURRENT_DATE) AND channel = 'email'); for each document, fetches the user's email, sends via Resend with template showing document name and days remaining; inserts into reminder_log with ON CONFLICT DO NOTHING. Edge case: parse expiry_date with date-fns parseISO not new Date() to prevent timezone-related off-by-one errors.
```

Limitations:

- V0 does not auto-generate the reminder deduplication logic — the UNIQUE constraint and ON CONFLICT DO NOTHING clause must be explicit in your prompt or the cron will send duplicate emails
- Vercel Cron requires manual addition of vercel.json — V0 does not include it in generated output
- V0 does not provision Supabase Storage — bucket creation and public/private settings must be done in the Supabase Dashboard

### Flutterflow — fit 3/10, 6-8 hours

Firebase Firestore stores document records; Cloud Scheduler triggers daily expiry checks via Cloud Functions; FCM delivers push notifications. Right choice for a mobile-first document management app.

1. Create a documents Firestore collection with fields: name, type, fileUrl, expiryDate (Timestamp), reminderDays (List of int), isActive (bool), userId
2. In the Action editor, wire a file upload action to Firebase Storage; save the download URL to fileUrl in the documents collection on save
3. Add a document list page using a Firestore collection query filtered by userId = currentUser.uid, sorted by expiryDate ascending; add a Custom Function to compute daysRemaining = expiryDate.difference(DateTime.now()).inDays and set a conditional color for the status badge
4. Write a Firebase Cloud Function (Node.js) triggered daily via Cloud Scheduler that queries documents with matching reminder thresholds and sends FCM push notifications; store sent notifications in a reminderLog sub-collection with a composite key to prevent duplicates
5. In Settings → Permissions, add the NSPhotoLibraryUsageDescription and add the Firebase project's google-services.json and GoogleService-Info.plist before building

Limitations:

- Cloud Scheduler configuration for the daily reminder trigger must be done in the Google Cloud Console — FlutterFlow's visual editor cannot configure it
- The reminder deduplication logic (reminderLog sub-collection check) must be written in Cloud Functions JavaScript; there is no visual way to configure it in FlutterFlow
- Firestore costs more per read/write at scale than PostgreSQL — for large document counts, reminder evaluation queries become expensive

### Custom — fit 5/10, 1-2 weeks

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.

1. OCR extraction pipeline: Google Cloud Vision API for multi-language document scanning; parse expiry dates from passports, visas, and certificates automatically on upload
2. Multi-channel delivery: email via Resend, push via FCM, SMS via Twilio — user configures channel preference per document type
3. Role-based notification routing for teams: document owner receives the reminder, team admins receive a summary digest if any team member has expiring documents
4. Renewal workflow integration: DocuSign or Adobe Sign triggered from the reminder email so users can start the renewal process in one click
5. Compliance audit log: immutable log of every reminder dispatched, opened, and actioned for ISO/SOC 2 audits

Limitations:

- 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

## Gotchas

- **The same reminder email sends multiple times on the same day** — 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** — 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** — 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** — 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

## 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.

---

Source: https://www.rapidevelopers.com/app-features/document-expiration-reminders
© RapidDev — https://www.rapidevelopers.com/app-features/document-expiration-reminders