TukTakKaj — Database Dictionary

ref.divisions

Bangladesh divisions. Top-level geography used in service areas and addresses.

• id integer — primary key
• code text — division code
• name_en text — English name
• name_bn text — Bangla name
• geo geography(Point,4326) — map position
• is_active boolean — active flag

ref.districts

Districts under each division.

• id integer — primary key
• division_id integer — parent division
• name_en text — district name
• name_bn text — Bangla name
• geo geography(Point,4326) — location

ref.upazilas

Upazila or thana records under each district.

• id integer — primary key
• district_id integer — parent district
• name_en text — upazila name
• name_bn text — Bangla name
• is_active boolean — active flag

ref.areas

Detailed location records such as ward, union, village, or mahalla.

• id integer — primary key
• upazila_id integer — parent upazila
• area_type text — location type
• name_en text — area name
• name_bn text — Bangla name
• geo geography(Point,4326) — map coordinate

ref.post_codes

Bangladesh postal codes and their post office/upazila mapping.

• id integer — primary key
• post_code text — four-digit postal code
• post_office text — post office name
• upazila_id integer — related upazila, when known

ref.service_areas

Regions where the platform plans, pilots, or runs services.

• id integer — primary key
• code text — unique service area code
• name text — display name
• boundary geography(MultiPolygon,4326) — optional geographic boundary
• status text — planned, pilot, live, or paused
• launched_at date — launch date
• created_at timestamptz — record creation time

ref.service_area_upazilas

Join table mapping service areas to the upazilas they cover.

• service_area_id integer — linked service area; part of primary key
• upazila_id integer — covered upazila; part of primary key

ref.currencies

Supported currencies and their display/precision settings.

• code ops.currency_code — currency code and primary key
• name text — currency name
• symbol text — display symbol
• minor_units smallint — decimal precision, defaults to 2
• is_active boolean — whether the currency is enabled

ops.files

Metadata for uploaded files stored in object storage.

Key fields: id bigint, public_id uuid, owner_user_id bigint, purpose ops.file_purpose, storage_provider text, bucket text, object_key text, mime_type text, size_bytes bigint, scan_status ops.scan_status, created_at timestamptz, deleted_at timestamptz

ops.system_settings

Admin-managed settings that control application behavior.

Key fields: scope text, key text, value jsonb, description text, is_secret boolean, updated_by bigint, updated_at timestamptz

ops.feature_flags

Feature toggles and gradual rollout configuration.

Key fields: key text, description text, is_enabled boolean, rollout_pct smallint, rules jsonb, updated_at timestamptz

ops.outbox_events

Transactional event queue for reliably delivering integration events.

Key fields: id bigint, aggregate_type text, aggregate_id text, event_type text, payload jsonb, created_at timestamptz, available_at timestamptz, processed_at timestamptz, attempts integer, last_error text

ops.idempotency_keys

Prevents duplicate processing when clients retry API requests.

Key fields: scope text, key text, request_hash bytea, response_code integer, response_body jsonb, locked_until timestamptz, created_at timestamptz, expires_at timestamptz

ops.data_retention_policies

Defines retention periods and lifecycle actions for stored data.

Key fields: id integer, entity text, retain_for interval, action ops.retention_action, legal_basis text, notify_before_days integer, requires_approval boolean, archive_destination text, is_active boolean, updated_at timestamptz

ops.backup_runs

Backup execution and restore-test records.

Key fields: id bigint, backup_type text, destination text, started_at timestamptz, finished_at timestamptz, size_bytes bigint, status text, checksum text, restore_tested_at timestamptz, notes text

ops.app_releases

App version information and minimum supported versions.

Key fields: id integer, platform text, version text, min_supported_version text, force_update boolean, release_notes text, released_at timestamptz

ops.maintenance_windows

Planned downtime windows and localized user messages.

Key fields: id integer, title text, starts_at timestamptz, ends_at timestamptz, is_read_only boolean, message_en text, message_bn text, created_at timestamptz

ops.encryption_keys

Key-version registry used to manage encryption and key rotation.

Key fields: id integer, key_version smallint, key_alias text, algorithm text, kms_provider text, kms_key_id text, is_primary boolean, is_active boolean, rotated_at timestamptz, created_at timestamptz

ops.legal_holds

Compliance holds that prevent deletion of specified data.

Key fields: id bigint, public_id uuid, hold_code text, hold_type text, scope text, target_user_id bigint, target_task_id bigint, data_categories text[], starts_at timestamptz, ends_at timestamptz, status text, issued_by bigint, approved_by bigint, created_at timestamptz

ops.retention_execution_log

Audit trail of retention and data lifecycle jobs.

Key fields: id bigint, policy_id integer, action_taken text, records_affected integer, legal_hold_id bigint, executed_by bigint, job_reference text, error_message text, details jsonb, created_at timestamptz

iam.users

Master identity for all Seekers, TukTakers, and staff. A mobile number can register only one account, so the same number cannot be both a Seeker and a TukTaker.

• id bigint — internal user ID
• public_id uuid — public UUID
• mobile ops.bd_mobile — phone number, unique: one mobile number = one account updated
• account_role iam.account_role — seeker or tuktaker; set at signup, one role per account (staff use is_staff) new
• email citext — email address
• password_hash text — authentication hash
• status iam.user_status — active, blocked, disabled, etc.
• is_staff boolean — staff/admin flag
• created_at timestamptz — signup timestamp
• updated_at timestamptz — last update

iam.seeker_profiles

Seeker account details and activity counters.

• user_id bigint — parent user
• first_name text — first name
• last_name text — last name
• date_of_birth date — DOB
• gender iam.gender — sex/gender
• profile_photo_file_id bigint — profile image
• tasks_posted_count integer — total tasks created
• total_spent ops.money — lifetime spend

iam.tuktaker_profiles

Worker service profile and verification state.

• user_id bigint — parent user
• first_name text — first name
• last_name text — last name
• verification_status iam.verification_status — pending/verified/rejected
• is_ready boolean — online availability
• service_radius_km numeric(5,1) — coverage radius
• last_known_geo geography(Point,4326) — last known coordinate
• total_earned ops.money — lifetime income

iam.auth_sessions

Login sessions and refresh-token tracking.

• id bigint — session key
• user_id bigint — owning user
• refresh_token_hash bytea — session token hash
• user_agent text — browser/device details
• ip inet — client IP
• expires_at timestamptz — expiration time
• revoked_at timestamptz — session invalidation time

iam.otp_requests

One-time password requests for signup, login, password reset, and sensitive actions.

Key fields: id bigint, mobile ops.bd_mobile, user_id bigint, purpose iam.otp_purpose, channel iam.otp_channel, code_hash bytea, attempts smallint, max_attempts smallint, expires_at timestamptz, verified_at timestamptz, provider text, ip inet, device_uid text, created_at timestamptz

iam.kyc_submissions

Identity verification attempts.

• id bigint — submission ID
• tuktaker_user_id bigint — worker being verified
• status iam.kyc_status — pending/approved/rejected
• submitted_at timestamptz — verification date
• reviewed_by bigint — staff reviewer
• reviewer_notes text — review explanation
• nid_number_hash bytea — encrypted or hashed NID

iam.password_history

Stores prior password hashes to prevent password reuse.

Key fields: id bigint, user_id bigint, password_hash text, created_at timestamptz

iam.devices

Registered user devices and push notification tokens.

Key fields: id bigint, user_id bigint, device_uid text, platform text, push_provider text, push_token text, app_version text, os_version text, device_model text, is_active boolean, first_seen_at timestamptz, last_seen_at timestamptz

iam.login_events

Security log of successful and failed login attempts.

Key fields: id bigint, user_id bigint, mobile text, success boolean, failure_reason text, ip inet, user_agent text, device_uid text, created_at timestamptz

iam.roles

Staff role definitions such as Support Agent or Finance Admin.

Key fields: id integer, code text, name text, description text, is_super_user boolean, is_system boolean, created_at timestamptz

iam.permissions

Fine-grained permissions grouped by application module.

Key fields: id integer, code text, module text, description text

iam.role_permissions

Maps permissions to roles.

Key fields: role_id integer, permission_id integer

iam.user_roles

Staff role assignments, including grant and expiry details.

Key fields: user_id bigint, role_id integer, granted_by bigint, granted_at timestamptz, expires_at timestamptz

iam.staff_profiles

Employee details for staff accounts.

Key fields: user_id bigint, employee_no text, full_name text, department text, designation text, work_email citext, is_active boolean, created_at timestamptz, updated_at timestamptz

iam.business_profiles

Business identity and verification details for seeker accounts.

Key fields: seeker_user_id bigint, company_name text, trade_licence_no text, bin text, tin text, invoice_email citext, verification_status iam.verification_status, verified_at timestamptz, verified_by bigint, updated_at timestamptz

iam.tuktaker_service_upazilas

Maps TukTakers to the upazilas where they accept work.

Key fields: user_id bigint, upazila_id integer

iam.tuktaker_availability

Weekly working hours used for matching and scheduling.

Key fields: id bigint, user_id bigint, weekday smallint, start_time time, end_time time

iam.user_addresses

Saved user addresses for home, work, billing, and other purposes.

Key fields: id bigint, user_id bigint, kind iam.address_kind, division_id integer, district_id integer, upazila_id integer, area_id integer, post_code text, road_name_house text, address_line1 text, address_line2 text, landmark text, geo geography(Point,4326), is_default boolean, created_at timestamptz, updated_at timestamptz, deleted_at timestamptz

iam.user_blocks

Records users who have blocked one another.

Key fields: blocker_user_id bigint, blocked_user_id bigint, reason text, created_at timestamptz

iam.kyc_providers

Configuration and health status for identity verification providers.

Key fields: id integer, code text, name text, provider_type text, supported_docs text[], config jsonb, priority integer, health_status text, is_active boolean, created_at timestamptz, updated_at timestamptz

iam.kyc_verification_rules

Configurable KYC requirements and provider selection rules.

Key fields: id integer, code text, name text, requires_nid_front boolean, requires_nid_back boolean, requires_selfie boolean, requires_face_match boolean, requires_fingerprint boolean, min_age_years smallint, primary_provider_id integer, fallback_provider_id integer, is_active boolean, created_at timestamptz, updated_at timestamptz

iam.kyc_documents

Document files and OCR data attached to KYC submissions.

Key fields: id bigint, submission_id bigint, doc_type iam.kyc_doc_type, slot smallint, file_id bigint, ocr_data jsonb, created_at timestamptz

iam.kyc_rejection_reasons

Standardized KYC rejection reasons and resubmission rules.

Key fields: id integer, code text, description_en text, description_bn text, allows_resubmission boolean, is_active boolean

iam.kyc_api_logs

Audit and performance records for KYC provider API calls.

Key fields: id bigint, provider_id integer, user_id bigint, submission_id bigint, call_type text, request_payload jsonb, response_payload jsonb, response_status text, latency_ms integer, cost numeric(10,4), error_message text, created_at timestamptz

iam.kyc_decryption_audit

Immutable record of staff access to decrypted KYC information.

Key fields: id bigint, submission_id bigint, accessed_by bigint, field_accessed text, purpose text, ip inet, created_at timestamptz

iam.data_removal_requests

Tracks formal user requests to delete personal data.

Key fields: id bigint, user_id bigint, status iam.removal_status, reason text, requested_at timestamptz, processed_by bigint, completed_at timestamptz, rejection_reason text, retained_data_note text

catalog.categories

Service category tree. Root categories have no parent; tasks must reference an active leaf category.

• id integer — category ID
• parent_id integer — parent category; null for a root category
• level smallint — hierarchy depth, calculated from the parent
• slug text — URL/category key
• name_en text — English label
• name_bn text — Bangla label
• description text — category description
• icon_key text — UI icon identifier
• launch_status catalog.launch_status — MVP, planned, future, paused, or retired
• is_active boolean — whether users can select the category
• requires_pickup_dropoff boolean — require both route locations before publishing
• min_tuktaker_age smallint — minimum worker age for category eligibility
• requires_compliance_review boolean / compliance_cleared_at — compliance gate and clearance time
• allow_free_text_search boolean — allow free-text category matching, used by Other
• form_schema jsonb — optional JSON form configuration
• search_keywords text[] / search_vector — category terms and indexed search text
• sort_order integer, created_at, updated_at — display order and timestamps

catalog.category_fields

Dynamic form fields for a given task category.

• id integer — field ID
• category_id integer — category link
• field_key text — form field key
• label_en text — form label
• label_bn text — Bangla form label
• field_type text — text, textarea, number, date, boolean, select, multiselect, or file
• is_required boolean — required-answer flag
• options jsonb / validation — choices and JSON validation rules
• sort_order integer / is_active — field order and availability

catalog.tuktaker_categories

Categories the TukTaker can perform.

• user_id bigint — TukTaker ID
• category_id integer — skill category
• years_experience smallint — experience level
• is_primary boolean — primary skill flag
• added_at timestamptz — skill creation time

catalog.category_price_hints

Suggested price ranges for a category in a service area.

Key fields: id integer, category_id integer, service_area_id integer, min_amount ops.money, typical_amount ops.money, max_amount ops.money, effective_from date, effective_to date

catalog.certifications

Training and certification types that workers can hold.

Key fields: id integer, code text, name text, issuer text, category_id integer, is_required_for_category boolean, is_active boolean

catalog.tuktaker_certifications

Certifications held and verified for a TukTaker.

Key fields: user_id bigint, certification_id integer, issued_at date, expires_at date, file_id bigint, verified_by bigint, verified_at timestamptz

catalog.search_synonyms

Alternate search terms used to expand marketplace searches.

Key fields: id integer, term text, synonym text, locale character(2), category_id integer

Delivery category setup

Delivery is the parent category; Goods and Documents are selectable leaf categories. Both leaves require pickup and drop-off locations and have a minimum TukTaker age of 18.

• item_description textarea — required on Delivery; form validation config sets a 5-character minimum
• approx_weight_kg number — required for Goods; form validation config sets a 0.1 kg minimum
• is_fragile boolean and declared_value_bdt number — optional Goods details
• document_type select — required for Documents; letter, contract, certificate, bank/legal, or other
• signature_required boolean — optional Documents delivery instruction
• Form answers are stored in task.tasks.attributes; the database checks required answers on publish, while configured value constraints are validated by the application.
• Publish flow: create as draft, add pickup and drop-off rows, then publish. Direct insertion as open is rejected.

Category search configuration

Seed data supplies English, Banglish, and Bangla keywords and synonym mappings for category search, including delivery terms such as courier, parcel, package, and কুরিয়ার.

Tables: catalog.categories.search_keywords, catalog.categories.search_vector, catalog.search_synonyms

task.tasks

Main task posting record created by a seeker.

• id bigint — task ID
• public_id uuid — public UUID
• seeker_id bigint — post owner
• category_id integer — service category
• title text — task title
• description text — task description
• attributes jsonb — JSON answers to the category’s dynamic form fields
• status task.task_status — draft, open, bidding_closed, assigned, in_progress, completion_requested, completed, cancelled, expired, disputed updated
• budget_amount ops.money — expected spend
• scheduled_start_at timestamptz — planned start
• bidding_closes_at timestamptz — when bidding ends; no new bids after this time new
• current_assignment_id bigint — active job link

task.bids

Offers placed by TukTakers on a task.

• id bigint — bid ID
• task_id bigint — parent task
• tuktaker_id bigint — bidder
• initial_amount ops.money — initial quote
• current_amount ops.money — current quote
• status task.bid_status — pending, countered, accepted, rejected, withdrawn, expired updated
• message text — bidder note
• accepted_at timestamptz — acceptance timestamp

task.assignments

Accepted bid that becomes the live work relationship.

• id bigint — assignment ID
• task_id bigint — associated task
• bid_id bigint — accepted bid
• seeker_id bigint — customer
• tuktaker_id bigint — assigned worker
• agreed_amount ops.money — final price
• status task.assignment_status — pending_payment, active, arrived, in_progress, completion_requested, completed, cancelled, disputed updated
• payment_due_at timestamptz — payment deadline (acceptance + 15 min); if missed, the assignment is cancelled and the task reopens new
• started_at timestamptz — work start
• completed_at timestamptz — completion time

task.task_locations

Service, pickup, drop-off, and extra-stop addresses with route order and contact details. Delivery Goods and Documents require at least one pickup and one drop-off before publication.

Key fields: id bigint, task_id bigint, kind task.location_kind (service/pickup/dropoff/stop), seq smallint, district_id integer, upazila_id integer, area_id integer, ward_village text, post_code text, road_name_house text, address_line text, landmark text, geo geography(Point,4326), contact_name text, contact_mobile ops.bd_mobile, notes text

task.bid_offers

History of price and message exchanges during bid negotiation.

Key fields: id bigint, bid_id bigint, round_no smallint, offered_by task.offer_by, offered_by_user_id bigint, amount ops.money, message text, status task.offer_status, expires_at timestamptz, created_at timestamptz, responded_at timestamptz

task.assignment_events

Timeline of assignment lifecycle events and evidence.

Key fields: id bigint, assignment_id bigint, task_id bigint, event_type text, actor_user_id bigint, geo geography(Point,4326), notes text, media_file_id bigint, metadata jsonb, created_at timestamptz

task.task_status_history

Append-only history of task status changes.

Key fields: id bigint, task_id bigint, from_status task.task_status, to_status task.task_status, changed_by bigint, reason text, metadata jsonb, changed_at timestamptz

task.task_attachments

Photos, measurements, and other evidence attached to a task.

Key fields: id bigint, task_id bigint, file_id bigint, kind task.attachment_kind, uploaded_by bigint, caption text, sort_order smallint, created_at timestamptz

task.saved_tasks

Tasks bookmarked by users.

Key fields: user_id bigint, task_id bigint, saved_at timestamptz

task.task_invitations

Direct invitations asking a TukTaker to bid on a task.

Key fields: id bigint, task_id bigint, tuktaker_id bigint, status text, expires_at timestamptz, created_at timestamptz

task.cancellation_reasons

Standard cancellation reason options for each participant role.

Key fields: id integer, code text, applies_to text, description_en text, description_bn text, is_active boolean

task.cancellations

Cancellation record including fees, penalties, and allocation.

Key fields: id bigint, task_id bigint, assignment_id bigint, cancelled_by_user_id bigint, cancelled_by_role text, stage task.cancel_stage, reason_id integer, reason_text text, task_amount_basis ops.money, fee_kind text, fee_amount ops.money, penalty_amount ops.money, platform_share ops.money, counterparty_share ops.money, vat_amount ops.money, created_at timestamptz

payment.payments

Original payment record for a task or milestone.

• id bigint — payment ID
• task_id bigint — payment belongs to task
• assignment_id bigint — current assignment
• seeker_id bigint — payer
• provider_id integer — gateway or wallet
• amount ops.money — payment amount
• currency ops.currency_code — currency code
• status payment.payment_status — initiated/paid/failed
• gateway_tran_id text — provider transaction id

payment.escrow_holds

Funds held safely until completion or final dispute resolution.

• id bigint — hold ID
• assignment_id bigint — assignment linked
• payment_id bigint — payment source
• amount_held ops.money — escrow total
• status payment.escrow_status — held, frozen, released, partially_released, refunded updated
• held_at timestamptz — hold start
• released_at timestamptz — release time
• release_reason payment.release_reason — settlement reason

payment.payout_requests

Withdrawal request for proceeds due to a TukTaker.

• id bigint — request ID
• tuktaker_id bigint — worker receiving funds
• amount ops.money — payout total
• fee_amount ops.money — provider or admin fee
• net_amount ops.money — amount after fees
• payment_method_id bigint — destination account
• status payment.payout_status — pending, approved, processing, paid, failed, rejected updated
• gateway_ref text — provider reference

payment.providers

Payment channels such as mobile wallets, cards, and banks.

Key fields: id integer, code text, name text, provider_type payment.provider_type, supports_pay_in boolean, supports_payout boolean, is_mvp boolean, is_active boolean, display_order smallint, config jsonb, created_at timestamptz

payment.user_payment_methods

Saved and optionally verified payment accounts for users.

Key fields: id bigint, public_id uuid, user_id bigint, provider_id integer, purpose payment.method_purpose, account_ref_enc bytea, account_ref_hash bytea, account_ref_masked text, holder_name text, qr_file_id bigint, is_default boolean, status payment.method_status, verified_at timestamptz, last_used_at timestamptz, created_at timestamptz, updated_at timestamptz, deleted_at timestamptz

payment.escrow_rules

Configuration for escrow holds, dispute windows, and automatic release.

Key fields: id integer, code text, name text, auto_release_hours integer, dispute_window_hours integer, dispute_blocks_release boolean, allow_partial_refund boolean, on_dispute_timeout text, platform_hold_hours integer, plan_id integer, status finance.schedule_status, effective_from timestamptz, effective_to timestamptz, created_by bigint, created_at timestamptz

payment.gateway_events

Stores payment gateway callbacks and webhook processing results.

Key fields: id bigint, gateway text, event_type text, external_id text, payment_id bigint, payout_id bigint, payload jsonb, headers jsonb, signature_valid boolean, received_at timestamptz, processed_at timestamptz, attempts smallint, processing_error text

payment.escrow_events

Chronological record of escrow freezes, releases, and related actions.

Key fields: id bigint, escrow_hold_id bigint, action text, amount ops.money, beneficiary_user_id bigint, performed_by bigint, reason text, created_at timestamptz

payment.tips

Optional extra payments from Seekers to TukTakers.

Key fields: id bigint, public_id uuid, assignment_id bigint, task_id bigint, seeker_id bigint, tuktaker_id bigint, amount ops.money, status payment.tip_status, payment_id bigint, message text, ledger_transaction_id bigint, created_at timestamptz, paid_at timestamptz

payment.refunds

Refund requests and processing records for disputes or cancellations.

Key fields: id bigint, public_id uuid, payment_id bigint, task_id bigint, assignment_id bigint, beneficiary_user_id bigint, dispute_id bigint, gross_amount ops.money, admin_fee_amount ops.money, admin_fee_borne_by text, net_amount ops.money, destination payment.refund_destination, status payment.refund_status, reason text, requested_by bigint, approved_by bigint, approved_at timestamptz, gateway_refund_ref text, processed_at timestamptz, failure_reason text, ledger_transaction_id bigint, created_at timestamptz

payment.payout_rules

Defines payout limits, fees, processing time, and eligibility requirements.

Key fields: id integer, code text, name text, provider_id integer, min_amount ops.money, max_amount_per_txn ops.money, daily_limit ops.money, weekly_limit ops.money, monthly_limit ops.money, fee_type finance.calc_type, fee_value numeric(12,4), fee_min ops.money, fee_max ops.money, requires_verified_kyc boolean, min_tier_id integer, processing_hours integer, status finance.schedule_status, effective_from timestamptz, effective_to timestamptz, created_by bigint, created_at timestamptz

payment.payout_batches

Groups payout requests for submission to a provider.

Key fields: id bigint, provider_id integer, status payment.batch_status, item_count integer, total_amount ops.money, gateway_batch_ref text, created_by bigint, created_at timestamptz, submitted_at timestamptz, completed_at timestamptz

payment.payout_attempts

Records each gateway attempt when processing a payout.

Key fields: id bigint, payout_id bigint, attempt_no smallint, gateway_ref text, amount ops.money, status text, request_payload jsonb, response_payload jsonb, error_message text, created_at timestamptz

payment.reconciliation_runs

Reconciliation run comparing expected payments with gateway statements.

Key fields: id bigint, gateway text, period_start date, period_end date, statement_file_id bigint, status text, expected_total ops.money, settled_total ops.money, difference ops.money, run_by bigint, created_at timestamptz, finished_at timestamptz

payment.reconciliation_items

Line-level matches and exceptions within a reconciliation run.

Key fields: id bigint, run_id bigint, payment_id bigint, payout_id bigint, gateway_ref text, expected_amount ops.money, settled_amount ops.money, match_status text, notes text, resolved_by bigint, resolved_at timestamptz

finance.ledger_accounts

Chart of accounts for wallet, escrow, commissions, VAT, and cash flow.

• id bigint — ledger account ID
• code text — account code
• kind finance.account_kind — asset, liability, equity, revenue, expense
• currency ops.currency_code — account currency
• balance numeric(16,2) — current balance
• owner_user_id bigint — user owning the account

finance.ledger_transactions

Money movement headers that record financial changes in the system.

• id bigint — transaction ID
• txn_type finance.txn_type — credit/debit or event type
• status finance.txn_status — pending/posted/reversed
• description text — explanation
• currency ops.currency_code — transaction currency
• ref_type text — linked object type
• ref_id bigint — linked object ID
• created_at timestamptz — transaction time

finance.task_settlements

Final payout breakdown for a completed task.

• id bigint — settlement ID
• task_id bigint — task reference
• assignment_id bigint — assignment reference
• gross_amount ops.money — amount before fees
• commission_amount ops.money — platform commission
• vat_amount ops.money — VAT charge
• net_to_tuktaker ops.money — payout to worker
• status finance.settlement_status — pending/settled

finance.pricing_plans

Commission policies grouped by scope, such as global, category, or service area.

Key fields: id integer, code text, name text, description text, scope finance.plan_scope, category_id integer, service_area_id integer, user_segment text, is_default boolean, status finance.schedule_status, priority integer, effective_from timestamptz, effective_to timestamptz, created_by bigint, approved_by bigint, created_at timestamptz, updated_at timestamptz

finance.commission_rules

Commission calculation formula associated with a pricing plan.

Key fields: id integer, plan_id integer, mode finance.commission_mode, flat_amount ops.money, base_percentage ops.pct, hybrid_flat_amount ops.money, hybrid_percentage ops.pct, hybrid_combination finance.hybrid_combination, min_commission ops.money, max_commission ops.money, created_at timestamptz

finance.commission_tier_rates

Commission percentages and limits by TukTaker tier.

Key fields: rule_id integer, tier_id integer, commission_pct ops.pct, min_commission ops.money, max_commission ops.money

finance.vat_rules

VAT policy, calculation, rounding, and tax-bearer rules.

Key fields: id integer, code text, name text, vat_pct ops.pct, bearer finance.vat_bearer, shared_seeker_pct ops.pct, shared_tuktaker_pct ops.pct, taxable_base text, calc_method text, rounding_mode finance.rounding_mode, rounding_precision smallint, min_vat ops.money, max_vat ops.money, plan_id integer, status finance.schedule_status, effective_from timestamptz, effective_to timestamptz, created_by bigint, approved_by bigint, created_at timestamptz

finance.fee_schedules

Fixed or percentage fees such as invoice, refund, and tip fees.

Key fields: id bigint, code finance.fee_code, calc_type finance.calc_type, value numeric(12,4), min_amount ops.money, max_amount ops.money, currency ops.currency_code, category_id integer, tier_id integer, service_area_id integer, status finance.schedule_status, effective_from timestamptz, effective_to timestamptz, notes text, created_by bigint, approved_by bigint, created_at timestamptz

finance.cancellation_rules

Cancellation fees, penalties, call-out charges, and allocation rules.

Key fields: id integer, code text, name text, stage task.cancel_stage, cancelled_by_role text, fee_calc finance.calc_type, fee_value numeric(12,4), fee_min ops.money, fee_max ops.money, callout_fee_amount ops.money, callout_fee_cap ops.money, combination finance.fee_combination, combined_cap ops.money, platform_share_pct ops.pct, counterparty_share_pct ops.pct, free_window_minutes integer, plan_id integer, priority integer, status finance.schedule_status, effective_from timestamptz, effective_to timestamptz, created_by bigint, approved_by bigint, created_at timestamptz

finance.tuktaker_tiers

Worker tier definitions, thresholds, and benefits.

Key fields: id integer, code text, name text, rank smallint, min_tasks_in_period integer, min_total_task_value ops.money, min_avg_rating numeric(3,2), evaluation_period_days integer, downgrade_grace_days integer, benefits jsonb, is_active boolean

finance.tuktaker_tier_history

Historical tier assignments and evaluation context for TukTakers.

Key fields: id bigint, user_id bigint, tier_id integer, effective_from timestamptz, effective_to timestamptz, reason text, metrics jsonb, created_by bigint

finance.user_plan_assignments

Pricing plan assigned to a user over a defined period.

Key fields: id bigint, user_id bigint, plan_id integer, effective_from timestamptz, effective_to timestamptz, reason text, created_by bigint

finance.ledger_entries

Debit and credit lines that make up each ledger transaction.

Key fields: id bigint, transaction_id bigint, account_id bigint, direction character(1), amount numeric(16,2), currency ops.currency_code, category finance.entry_category, user_id bigint, task_id bigint, memo text, posted_at timestamptz

finance.vat_returns

Periodic VAT filing totals and submission records.

Key fields: id bigint, period_start date, period_end date, total_taxable ops.money, total_vat ops.money, status text, challan_no text, submitted_at timestamptz, submitted_by bigint, file_id bigint, notes text

finance.vat_entries

VAT amounts accrued from taxable services and cancellations.

Key fields: id bigint, source finance.vat_source, settlement_id bigint, cancellation_id bigint, task_id bigint, user_id bigint, taxable_amount ops.money, vat_rate ops.pct, vat_amount ops.money, tax_period date, fiscal_year smallint, status finance.vat_status, vat_return_id bigint, ledger_transaction_id bigint, created_at timestamptz

finance.credits

Goodwill credits granted to users by authorized staff.

Key fields: id bigint, public_id uuid, user_id bigint, recipient_role text, amount ops.money, remaining_amount ops.money, status finance.credit_status, reason text, dispute_id bigint, granted_by bigint, approved_by bigint, expires_at timestamptz, redeemable_as_cash boolean, ledger_transaction_id bigint, created_at timestamptz

finance.credit_applications

Records credits applied to a task or settlement.

Key fields: id bigint, credit_id bigint, settlement_id bigint, task_id bigint, amount ops.money, applied_at timestamptz

finance.penalties

Penalties assigned to TukTakers, including outstanding balances and waivers.

Key fields: id bigint, user_id bigint, assignment_id bigint, cancellation_id bigint, reason text, amount ops.money, remaining_amount ops.money, status finance.penalty_status, waived_by bigint, waived_at timestamptz, created_at timestamptz

finance.penalty_applications

Penalty deductions applied to task settlements.

Key fields: id bigint, penalty_id bigint, settlement_id bigint, amount ops.money, applied_at timestamptz

finance.manual_adjustments

Admin-requested financial corrections with approval and execution tracking.

Key fields: id bigint, adjustment_type finance.adjustment_type, user_id bigint, task_id bigint, dispute_id bigint, amount ops.money, reason text, status finance.adjustment_status, requested_by bigint, approved_by bigint, requested_at timestamptz, decided_at timestamptz, executed_at timestamptz, decision_note text, ledger_transaction_id bigint

trust.ratings

Star ratings and comments after completion of a task.

• id bigint — rating record ID
• assignment_id bigint — task assignment
• rater_id bigint — user leaving rating
• ratee_id bigint — user being rated
• stars ops.stars — 1 to 5 score
• comment text — rating note
• created_at timestamptz — rating timestamp

trust.disputes

Disputes raised against a task or worker with resolution decision.

• id bigint — dispute ID
• task_id bigint — related task
• assignment_id bigint — referenced assignment
• raised_by bigint — person raising complaint
• category trust.dispute_category — dispute reason category
• status trust.dispute_status — open/in review/resolved
• resolution trust.dispute_resolution — final decision
• resolution_amount ops.money — adjustment amount

trust.rating_summaries

Aggregated rating counts, totals, and averages by user role.

Key fields: user_id bigint, role trust.party_role, rating_count integer, rating_sum integer, star_1 integer, star_2 integer, star_3 integer, star_4 integer, star_5 integer, rating_avg numeric(3,2), updated_at timestamptz

trust.testimonials

Public-facing customer or TukTaker quotes with consent and approval status.

Key fields: id bigint, user_id bigint, role trust.party_role, display_name text, location_label text, body text, stars ops.stars, language character(2), source text, consent_to_publish boolean, status trust.testimonial_status, approved_by bigint, published_at timestamptz, sort_order integer, created_at timestamptz

trust.dispute_messages

Messages exchanged by participants and staff within a dispute.

Key fields: id bigint, dispute_id bigint, author_id bigint, author_kind text, body text, is_internal boolean, created_at timestamptz

trust.dispute_evidence

Files and descriptions submitted as evidence for a dispute.

Key fields: id bigint, dispute_id bigint, file_id bigint, submitted_by bigint, description text, created_at timestamptz

trust.user_reports

User reports about abuse or misconduct related to a person or task.

Key fields: id bigint, reporter_id bigint, reported_user_id bigint, task_id bigint, reason text, details text, status text, handled_by bigint, created_at timestamptz, handled_at timestamptz

trust.risk_events

Fraud and abuse signals recorded for review.

Key fields: id bigint, user_id bigint, signal_type text, severity smallint, score numeric(6,2), details jsonb, reviewed_at timestamptz, created_at timestamptz

comms.conversations

Messaging thread between a seeker and a TukTaker.

• id bigint — conversation ID
• task_id bigint — job reference
• seeker_id bigint — buyer
• tuktaker_id bigint — worker
• last_message_at timestamptz — newest message time
• created_at timestamptz — conversation created

comms.messages

Actual chat message records.

• id bigint — message ID
• conversation_id bigint — parent thread
• sender_id bigint — author
• message_type comms.message_type — text/image/audio/file
• body text — message text
• created_at timestamptz — send time

comms.support_tickets

Customer support issue record.

• id bigint — ticket ID
• user_id bigint — reporting user
• task_id bigint — linked task
• subject text — issue summary
• status comms.ticket_status — open/closed/pending
• priority smallint — urgency
• assigned_to bigint — assigned support staff

comms.notification_templates

Reusable, versioned message content for notification channels and locales.

Key fields: id integer, code text, channel comms.channel, locale character(2), version integer, subject text, body text, variables text[], is_active boolean, created_at timestamptz

comms.notification_preferences

User opt-in or opt-out preferences by notification category and channel.

Key fields: user_id bigint, category text, channel comms.channel, enabled boolean

comms.user_notifications

Notification delivery lifecycle and provider status for each user message.

Key fields: id bigint, user_id bigint, template_code text, channel comms.channel, title text, body text, data jsonb, entity_type text, entity_id bigint, status comms.notif_status, provider text, provider_message_id text, scheduled_at timestamptz, sent_at timestamptz, delivered_at timestamptz, read_at timestamptz, attempts smallint, failed_reason text, created_at timestamptz

comms.email_logs

Sent email records retained according to the email retention policy.

Key fields: id bigint, user_id bigint, from_address text, to_address text, subject text, template_code text, body_text text, provider_message_id text, status text, sent_at timestamptz, created_at timestamptz, retain_until date

comms.support_ticket_messages

Replies and attachments exchanged within a support ticket.

Key fields: id bigint, ticket_id bigint, author_id bigint, is_staff boolean, is_internal boolean, body text, file_id bigint, created_at timestamptz

comms.feedback_submissions

Feedback and ratings submitted through user feedback forms.

Key fields: id bigint, user_id bigint, name text, email citext, mobile text, category text, rating ops.stars, message text, source text, created_at timestamptz, retain_until date

billing.invoices

Invoice snapshot issued to a Seeker or TukTaker for a billing period.

Key fields: id bigint, public_id uuid, invoice_no bigint, user_id bigint, recipient_role text, period_type billing.invoice_period, period_start date, period_end date, issuer_snapshot jsonb, customer_snapshot jsonb, service_count integer, subtotal ops.money, commission_total ops.money, vat_total ops.money, total ops.money, invoice_fee ops.money, fee_payment_id bigint, net_payout_total ops.money, status billing.invoice_status, pdf_file_id bigint, generated_by bigint, generated_at timestamptz, issued_at timestamptz, voided_at timestamptz, void_reason text

billing.invoice_lines

Service and financial line items included in an invoice.

Key fields: id bigint, invoice_id bigint, line_no integer, task_id bigint, settlement_id bigint, service_date date, category_name text, sub_category_name text, description text, amount ops.money, commission_amount ops.money, vat_amount ops.money, net_amount ops.money

billing.statement_entries

User-facing ledger of payments, earnings, fees, refunds, credits, and payouts.

Key fields: id bigint, user_id bigint, entry_type billing.statement_type, task_id bigint, task_no bigint, category_name text, description text, amount ops.money, balance_after ops.money, ledger_transaction_id bigint, occurred_at timestamptz, entry_year smallint, entry_month smallint

content.company_profile

Company details used by the app and generated invoices.

Key fields: key text, value jsonb, updated_at timestamptz

content.legal_documents

Terms and policy document definitions, audience, and category scope.

Key fields: id integer, code text, title text, audience text, category_id integer, is_mandatory boolean, is_active boolean

content.legal_document_versions

Localized, versioned legal text with publication and effective dates.

Key fields: id integer, document_id integer, version integer, locale character(2), body text, effective_from timestamptz, published_at timestamptz, created_by bigint

content.user_acceptances

Evidence that a user accepted a particular legal document version.

Key fields: id bigint, user_id bigint, document_version_id integer, accepted_at timestamptz, ip inet, user_agent text, device_uid text, revoked_at timestamptz

content.disclaimers

Localized warnings shown in task, bidding, payment, or other app flows.

Key fields: id integer, code text, context text, locale character(2), body text, is_active boolean, sort_order integer

content.system_messages

Localized UI messages and pop-ups keyed by message code.

Key fields: code text, locale character(2), message text, message_type text, updated_at timestamptz

content.faqs

Localized, audience-specific frequently asked questions.

Key fields: id integer, audience text, locale character(2), question text, answer text, sort_order integer, is_published boolean

marketing.promotions

Discount and credit campaigns scoped by audience, category, service area, and period.

Key fields: id bigint, code text, name text, promo_type marketing.promo_type, audience text, value numeric(12,4), max_discount ops.money, min_task_amount ops.money, category_id integer, service_area_id integer, usage_limit_total integer, usage_limit_per_user integer, used_count integer, starts_at timestamptz, ends_at timestamptz, is_active boolean, created_by bigint, created_at timestamptz

marketing.promotion_redemptions

Record of each promotion used by a user or applied to a task.

Key fields: id bigint, promotion_id bigint, user_id bigint, task_id bigint, discount_amount ops.money, redeemed_at timestamptz

marketing.referrals

Referral relationships and qualification/reward status.

Key fields: id bigint, referrer_id bigint, referred_id bigint, status text, reward_credit_id bigint, created_at timestamptz, qualified_at timestamptz

audit.audit_logs

Append-only, partitioned audit trail of sensitive user, staff, and system changes.

Key fields: id bigint, actor_user_id bigint, actor_kind text, action text, entity_type text, entity_id text, old_values jsonb, new_values jsonb, ip inet, user_agent text, request_id text, created_at timestamptz

reporting.daily_kpis

Precomputed daily performance metrics by service area.

Key fields: kpi_date date, service_area_id integer, new_seekers integer, new_tuktakers integer, verified_tuktakers integer, tasks_posted integer, tasks_completed integer, tasks_cancelled integer, bids_placed integer, gmv ops.money, commission_revenue ops.money, fee_revenue ops.money, vat_collected ops.money, payouts_paid ops.money, refunds_paid ops.money, generated_at timestamptz

reporting.report_runs

Ad hoc or scheduled report requests, processing status, and result file.

Key fields: id bigint, public_id uuid, report_type text, requested_by bigint, period_start timestamptz, period_end timestamptz, parameters jsonb, status text, result_file_id bigint, row_count integer, error_message text, created_at timestamptz, completed_at timestamptz

reporting.report_schedules

Recurring report schedule, recipients, and next-run information.

Key fields: id bigint, report_type text, frequency text, recipients text[], parameters jsonb, is_active boolean, last_run_at timestamptz, next_run_at timestamptz, created_by bigint, created_at timestamptz