Visa Applications Data Model¶

This document describes the sequences and tables used for Visa Application management.
They are created in the TQPro database, under the nts schema.

Table of Contents

Sequences
Table nts.visaapplication
Table nts.visaapplicant
Table nts.visadocument
Table nts.documentrequirementcache
Table nts.invoice
Table nts.visadelivery

Sequences¶

Sequence Name	Purpose
`nts.visaapplication_seq`	Generates primary key values for `visaapplication.visaapplicationid`.
`nts.visaapplicant_seq`	Generates primary key values for `visaapplicant.visaapplicantid`.
`nts.documentrequirementcache_seq`	Generates primary key values for `documentrequirementcache.documentrequirementcacheid`.
`nts.invoice_seq`	Generates primary key values for `invoice.invoiceid`.
`nts.visadelivery_seq`	Generates primary key values for `visadelivery.visadeliveryid`.

Tables¶

nts.visaapplication¶

Header record for each visa request.

Column	Data Type	Nullable	Default	Description
`visaapplicationid`	`bigint`	NO		Primary key
`customerid`	`bigint`	NO		References ERP customer ID
`customer_name`	`varchar(255)`	NO		Customer full name
`customer_phone`	`varchar(50)`	NO		Customer phone number
`customer_email`	`varchar(255)`	NO		Customer email address
`supplierid`	`bigint`	YES		References ERP supplier ID
`supplier_application_date`	`date`	YES		Date when application was sent to supplier
`issuance_status`	`varchar(50)`	YES		Application status (Pending/In-Process/Approved/Rejected)
`payment_status`	`varchar(50)`	YES		Payment status (Unpaid/Paid/Refunded)
`supplier_cost`	`numeric(12,2)`	YES		Cost charged by supplier
`customer_price`	`numeric(12,2)`	YES		Price charged to customer
`payment_override`	`boolean`	YES	`false`	Allows submit-before-payment override
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record

nts.visaapplicant¶

One row per traveller on a visa application.

Column	Data Type	Nullable	Default	Description
`visaapplicantid`	`bigint`	NO		Primary key
`visaapplicationid`	`bigint`	NO		Foreign key to `visaapplication.visaapplicationid`
`applicant_name`	`varchar(200)`	NO		Full name of applicant
`date_of_birth`	`date`	NO		Applicant's birth date
`passport_nationality`	`char(3)`	NO		Passport nationality (ISO 3-letter code)
`passport_number`	`varchar(50)`	NO		Passport number
`passport_issue_date`	`date`	YES		Passport issue date
`passport_expiry_date`	`date`	YES		Passport expiration date
`travel_country`	`char(3)`	NO		Destination country (ISO 3-letter code)
`passport_image_s3_url`	`text`	YES		URL to passport image stored in S3
`travel_start_date`	`date`	NO		Travel start date
`travel_end_date`	`date`	NO		Travel end date
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record

Foreign Keys: - visaapplicationid → nts.visaapplication(visaapplicationid)

nts.visadocument¶

Stores uploaded documents related to a visa application.

Column	Data Type	Nullable	Default	Description
`visadocumentid`	`bigint`	NO		Primary key
`visaapplicationid`	`bigint`	NO		Foreign key to `visaapplication.visaapplicationid`
`doc_description`	`varchar(200)`	NO		Human-readable description or title of the document
`doc_link`	`varchar(200)`	NO		S3 key (e.g. `applications/42/documents/a3f9c21b.pdf`) or legacy filesystem path
`doc_type`	`varchar(20)`	NO		Categorical document type (e.g. "passport", "photo")
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record

Foreign Keys: - visaapplicationid → nts.visaapplication(visaapplicationid)

nts.documentrequirementcache¶

Cached visa document requirements per origin-destination pair.

Column	Data Type	Nullable	Default	Description
`documentrequirementcacheid`	`bigint`	NO		Primary key
`passport_nationality`	`char(3)`	NO		Origin country (ISO 3-letter code)
`travel_country`	`char(3)`	NO		Destination country (ISO 3-letter code)
`requirements`	`jsonb`	NO		JSONB payload of document requirements
`last_fetched`	`timestamp with time zone`	YES	`current_timestamp`	When the requirements were last retrieved
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record

nts.invoice¶

Generated PDF invoice and Telr payment link for an application.

Column	Data Type	Nullable	Default	Description
`invoiceid`	`bigint`	NO		Primary key
`visaapplicationid`	`bigint`	NO		Foreign key to `visaapplication.visaapplicationid`
`invoice_date`	`date`	NO		Invoice issuance date
`invoice_pdf_s3_url`	`text`	NO		URL to PDF invoice in S3
`telr_payment_link`	`text`	NO		Telr payment URL
`payment_status`	`varchar(50)`	YES		Payment status (Unpaid/Paid/Refunded)
`payment_received_date`	`timestamp with time zone`	YES		When payment was received (webhook update)
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record

Foreign Keys: - visaapplicationid → nts.visaapplication(visaapplicationid)

nts.visadelivery¶

Secure delivery workflow details and confirmation code.

Column	Data Type	Nullable	Default	Description
`visadeliveryid`	`bigint`	NO		Primary key
`visaapplicationid`	`bigint`	NO		Foreign key to `visaapplication.visaapplicationid`
`visa_pdf_s3_url`	`text`	NO		URL to issued visa PDF in S3
`confirmation_code`	`varchar(20)`	NO		One-time code for secure download
`code_generated_date`	`timestamp with time zone`	YES	`current_timestamp`	When the confirmation code was generated
`code_expiry_date`	`timestamp with time zone`	NO		When the confirmation code expires
`email_sent_date`	`timestamp with time zone`	YES		When email with code and link was sent
`sms_sent_date`	`timestamp with time zone`	YES		When SMS with code was sent
`download_date`	`timestamp with time zone`	YES		When the customer downloaded the visa
`create_date`	`timestamp with time zone`	NO	`current_timestamp`	Record creation timestamp
`create_user`	`varchar(100)`	NO		User who created the record
`update_date`	`timestamp with time zone`	NO	`current_timestamp`	Last update timestamp
`update_user`	`varchar(100)`	NO		User who last updated the record
`otp_attempts`	`integer`	YES	`0`	Number of OTP validation attempts (TQ-18)
`otp_locked_until`	`timestamp with time zone`	YES		OTP validation lockout expiry (TQ-18)
`verify_attempts`	`integer`	YES	`0`	Number of identity verification attempts (TQ-18)
`verify_locked_until`	`timestamp with time zone`	YES		Identity verification lockout expiry (TQ-18)

Foreign Keys: - visaapplicationid → nts.visaapplication(visaapplicationid)

Notes: - visa_pdf_s3_url stores an S3 key (e.g. applications/42/documents/a3f9c21b.pdf), not a full URL - confirmation_code stores a SHA-256 hash (truncated to 20 chars) of the OTP, never the plaintext - otp_attempts / verify_attempts are reset on successful validation or new OTP generation - Lockout columns are set when max attempts are exceeded; checked before processing new attempts