-
Notifications
You must be signed in to change notification settings - Fork 13
Volunteer Onboarding ‐ Save and Continue feature
This documentation provides a comprehensive technical breakdown of the Volunteer Onboarding, designed to facilitate a "Save and Continue" experience while ensuring high data integrity through the use of automated database triggers.
The primary use case is a Multi-Step Volunteer Application Process that allows users to progress through various stages of onboarding (Personal Info, Skills, Availability). By utilizing user_id as a unique identifier, the schema enables state persistence, allowing users to close their browser and return later to the exact step where they left off, which is implemented by the backend.
This table serves as the Digital Junk Drawer or staging area for all in-progress applications.
Schema:
The table utilizes a user_id (email) as a primary identifier, a status ENUM to track progress, and a JSONB container named application_data to store flexible form responses. It also includes timestamps for path_updated_at and terms_accepted_at to track specific user actions.
CREATE TYPE virginia_dev_saayam_rdbms.app_status_type AS ENUM ('STARTED', 'IN_REVIEW', 'ACCEPTED', 'REJECTED');
-- Create the volunteer_applications table
CREATE TABLE virginia_dev_saayam_rdbms.volunteer_applications (
user_id VARCHAR(255) PRIMARY KEY,
terms_and_conditions BOOLEAN DEFAULT FALSE,
terms_accepted_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
govt_id_path TEXT,
path_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
skill_codes JSON,
availability JSONB,
current_page INT DEFAULT 1, -- Changed from TEXT to INT for numerical tracking
application_status virginia_dev_saayam_rdbms.app_status_type DEFAULT 'STARTED',
is_completed BOOLEAN DEFAULT FALSE, --extra precaution
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
last_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
FOREIGN KEY (user_id) REFERENCES virginia_dev_saayam_rdbms.users(user_id)
);Decisions Taken:
To ensure flexibility, the team opted for a single-table design and JSONB storage. This allows the application to evolve (adding or removing form fields) without requiring frequent database migrations. Furthermore, a "GMT Protective Layer" was implemented to ensure all timestamps are stored as TIMESTAMP WITHOUT TIME ZONE at UTC.
Triggers and Functions:
CREATE OR REPLACE FUNCTION virginia_dev_saayam_rdbms.updated_at_handler()
RETURNS TRIGGER AS $$
BEGIN
-- check if the last_updated_at was explicitly changed in the UPDATE statement.
-- if it WASN'T changed by the API, we force it to the current UTC.
IF (NEW.last_updated_at IS NOT DISTINCT FROM OLD.last_updated_at) THEN
NEW.last_updated_at = (now() AT TIME ZONE 'UTC');
END IF;
-- handle application table
IF (TG_TABLE_NAME = 'volunteer_applications') THEN
-- Update path timestamp if path changed
IF (NEW.govt_id_path IS DISTINCT FROM OLD.govt_id_path) THEN
NEW.path_updated_at = (now() AT TIME ZONE 'UTC');
END IF;
-- Update terms timestamp if accepted
IF (NEW.terms_and_conditions IS TRUE AND (OLD.terms_and_conditions IS FALSE OR OLD.terms_and_conditions IS NULL)) THEN
NEW.terms_accepted_at = (now() AT TIME ZONE 'UTC');
END IF;
-- handle details table
ELSIF (TG_TABLE_NAME = 'volunteer_details') THEN
-- update path1 timestamp if changed
IF (NEW.govt_id_path1 IS DISTINCT FROM OLD.govt_id_path1) THEN
NEW.path1_updated_at = (now() AT TIME ZONE 'UTC');
END IF;
-- update path2 timestamp if changed
IF (NEW.govt_id_path2 IS DISTINCT FROM OLD.govt_id_path2) THEN
NEW.path2_updated_at = (now() AT TIME ZONE 'UTC');
END IF;
-- update terms timestamp if accepted
IF (NEW.terms_and_conditions IS TRUE AND (OLD.terms_and_conditions IS FALSE OR OLD.terms_and_conditions IS NULL)) THEN
NEW.terms_accepted_at = (now() AT TIME ZONE 'UTC');
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION virginia_dev_saayam_rdbms.handle_volunteer_application()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.application_status = 'ACCEPTED' AND OLD.application_status != 'ACCEPTED') THEN
-- Migrate to volunteer_details
INSERT INTO virginia_dev_saayam_rdbms.volunteer_details (
user_id, terms_and_conditions, terms_accepted_at,
govt_id_path1, path1_updated_at, path2_updated_at, availability_days, availability_times,
created_at, last_updated_at
) VALUES (
NEW.user_id, NEW.terms_and_conditions, NEW.terms_accepted_at,
NEW.govt_id_path, NEW.path_updated_at, NULL, (NEW.availability -> 'days')::JSONB, (NEW.availability -> 'time')::JSONB,
NEW.last_updated_at, NEW.last_updated_at
);
-- Migrate to user_skills (unrolling the JSON array)
IF (NEW.skill_codes IS NOT NULL) THEN
INSERT INTO virginia_dev_saayam_rdbms.user_skills (
user_id, cat_id, created_at, last_updated_at
)
SELECT
NEW.user_id,
skill_id,
NEW.last_updated_at,
NEW.last_updated_at
FROM json_array_elements_text(NEW.skill_codes) AS skill_id;
END IF;
DELETE FROM virginia_dev_saayam_rdbms.volunteer_applications WHERE user_id = NEW.user_id;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_volunteer_app_updated_at
BEFORE UPDATE ON virginia_dev_saayam_rdbms.volunteer_applications
FOR EACH ROW EXECUTE FUNCTION virginia_dev_saayam_rdbms.updated_at_handler();
CREATE TRIGGER trg_handle_volunteer_application
AFTER UPDATE ON virginia_dev_saayam_rdbms.volunteer_applications
FOR EACH ROW EXECUTE FUNCTION virginia_dev_saayam_rdbms.handle_volunteer_application();The Update Trigger (updated_at_handler) automatically refreshes the last_updated_at timestamp whenever a row is modified. It is intelligent enough to update sub-timestamps; for example, if govt_id_path changes, it specifically refreshes path_updated_at.
The Migration Trigger (handle_volunteer_application) monitors the application_status. When the status changes to ACCEPTED, it orchestrates the movement of data from this staging table to the permanent production tables and subsequently deletes the draft.
trg_volunteer_app_updated_at: This is a BEFORE UPDATE trigger that calls the updated_at_handler() function.
trg_handle_volunteer_application: This is an AFTER UPDATE trigger that calls the handle_volunteer_application() function.
Once an application is approved, the core volunteer data is moved to this table for long-term storage.
Schema: This table replicates the identity and metadata from the application but separates govt_id_path1 and govt_id_path2 for multi-document support. It also stores normalized availability_days and availability_times as JSONB for advanced querying.
CREATE TABLE virginia_dev_saayam_rdbms.volunteer_details (
user_id VARCHAR(255) PRIMARY KEY,
terms_and_conditions BOOLEAN,
terms_accepted_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
govt_id_path1 TEXT,
govt_id_path2 TEXT,
path1_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
path2_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
availability_days JSONB,
availability_times JSONB,
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
last_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
FOREIGN KEY (user_id) REFERENCES virginia_dev_saayam_rdbms.users(user_id)
);
CREATE TRIGGER trg_volunteer_details_updated_at
BEFORE UPDATE ON virginia_dev_saayam_rdbms.volunteer_details
FOR EACH ROW EXECUTE FUNCTION virginia_dev_saayam_rdbms.updated_at_handler();Benefit of Indexes: GIN (Generalized Inverted Index) indexes are placed on the availability_days and availability_times columns. Because availability is stored in JSON format, standard indexes cannot search inside the array; GIN indexes allow the database to instantly find all volunteers available on a specific day (e.g., "Monday") without scanning every row in the table.
-- Index for searching specific days (e.g., 'Monday')
CREATE INDEX idx_volunteer_availability_days
ON virginia_dev_saayam_rdbms.volunteer_details USING GIN (availability_days);
-- Index for searching specific time slots
CREATE INDEX idx_volunteer_availability_times
ON virginia_dev_saayam_rdbms.volunteer_details USING GIN (availability_times);This is a normalized junction table that maps volunteers to their specific help categories.
Schema: It consists of a user_id and a cat_id (linking to help_categories), alongside timestamps.
CREATE TABLE virginia_dev_saayam_rdbms.user_skills (
user_id VARCHAR(255),
cat_id VARCHAR(50) NOT NULL,
created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
last_updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT (now() AT TIME ZONE 'UTC'),
PRIMARY KEY (user_id, cat_id),
FOREIGN KEY (user_id) REFERENCES virginia_dev_saayam_rdbms.users(user_id),
FOREIGN KEY (cat_id) REFERENCES virginia_dev_saayam_rdbms.help_categories(cat_id)
);
CREATE TRIGGER trg_user_skills_updated_at
BEFORE UPDATE ON virginia_dev_saayam_rdbms.user_skills
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();Benefit of Indexes: A B-Tree index is implemented on the cat_id column. This is critical for performance when the administrative team needs to filter thousands of volunteers by a specific skill (e.g., "Medical Assistance" or "Logistics"). It allows the database to perform a direct lookup rather than a slow sequential scan.
CREATE INDEX idx_user_skills_cat_id ON virginia_dev_saayam_rdbms.user_skills (cat_id);From the user's perspective, the process is seamless and password-less: they click the button "Become a Volunteer" in their dashboard. After starting the process, they can stop in the middle and continue when needed. The user completes the form and submits, changing the record status to ACCEPTED.
- The current page ensures that if the user leaves and returns later via the same email, they are redirected exactly to where they left off.
- The BEFORE UPDATE trigger in volunteer_application handles all the timestamps (any change would update the last_updated_at, any path change would update the path_updated_at, and if terms are accepted, then terms_accepted_at).
- When the application status is updated to "ACCEPTED" in the volunteer_applications table. Then the AFTER UPDATE trigger in volunteer_application is invoked, initiating the Migration Logic.
- A new record is created in volunteer_details. The code explicitly sets the created_at and last_updated_at to match the application's final timestamp to ensure chronological synchronization. This also has a BEFORE UPDATE trigger, same as volunteer_application.
- The skill_codes (stored as a JSON array in the application) are "normalized" into individual rows in the user_skills table using the json_array_elements_text function.
- After the data is successfully inserted into the production tables, the trigger executes a DELETE command on the volunteer_applications table to remove the now-redundant draft.
- For any applications that never reach the "ACCEPTED" stage, a daily scheduled job identifies rows where the updated_at timestamp is older than 30 days and removes them to comply with the 30-day cleanup policy.
- Automatic migrations to volunteer_details and user_skills
- Automatic updations of timestamps when updated
- APIs
- Make sure that the right time is given to the database (GMT)
- If the user changes the skills, those queries need to be handled by the backend. There isn't any trigger in the database to automate it.
- Any details changed in the availability, those queries need to be handled by the backend.