TukTakKaj — Data Flow & Table Relationships

Purpose and reading guide

This is a table-level view of the TukTakKaj service marketplace: a Seeker posts work, TukTakers bid, payment is held in escrow, the task is completed, and the money is settled and paid out. Each stage names the tables that store its durable records.

Solid flow below describes the normal task lifecycle. Side paths describe the payment timeout, disputes, cancellations, safety and operational support. The status lifecycles section shows every allowed status. Table names use schema.table notation.

ref / catalog — reference and task definitions iam — users and profiles task — task lifecycle payment / finance — money movement trust / comms / billing — service and records

Primary data flow

Accounts, sign-up and eligibility UPDATED

One mobile number = one account = one role. iam.users.mobile is unique and iam.users.account_role is either seeker or tuktaker, set at sign-up (staff use is_staff). Sign-up and login are verified by OTP. A Seeker may add business details (iam.business_profiles) to become a business Seeker; it is not a separate role.

A TukTaker submits KYC: each attempt is a kyc_submissions row, its NID and selfie images are kyc_documents stored as ops.files metadata, and the check runs through a configured kyc_providers entry.

iam.usersiam.otp_requestsiam.auth_sessionsiam.seeker_profilesiam.business_profilesiam.tuktaker_profilesiam.kyc_submissionsiam.kyc_documentsiam.kyc_providersops.files

D02 D04 D05 D07

Task definition and draft

The Seeker chooses a service category, enters its configured form answers, sets the budget and service area, and can attach files. Delivery categories can add pickup, drop-off, and stop locations. The task is saved with status draft.

catalog.categoriescatalog.category_fieldstask.taskstask.task_locationstask.task_attachmentsref.districtsref.upazilasref.areas

D11 D13

Publish, discover and bidding window UPDATED

Publishing validates the category, required form fields and any required locations. The task changes draft → open and task.tasks.bidding_closes_at is set. Eligible TukTakers find it by category, area or proximity; the Seeker can also invite TukTakers directly (task_invitations).

At bidding_closes_at the task moves to bidding_closed: no new bids, but the Seeker can still accept a pending bid. If no bid is accepted in time the task becomes expired and its open bids expire. Every change is written to task_status_history.

task.taskstask.task_status_historytask.task_invitationstask.saved_taskscatalog.tuktaker_categoriesiam.tuktaker_service_upazilasref.service_areasref.service_area_upazilas

D12 D16

Bids, negotiation, and selection UPDATED

A TukTaker submits a bid (pending). Each counter-offer round is kept in task.bid_offers and the bid shows countered while negotiation is in progress. A TukTaker can withdraw their bid.

The Seeker accepts one bid → accepted (with accepted_at) and the task becomes assigned. Other bids stay valid while payment is awaited, so the task can reopen if payment times out. Once payment is confirmed they become rejected; bids still open when the task closes become expired.

task.bidstask.bid_offerstask.taskstask.task_status_history

D13 D16

Assignment and Seeker payment (15-minute window) UPDATED

Acceptance creates an assignment with the agreed amount, status pending_payment and payment_due_at = acceptance + 15 minutes (configurable in ops.system_settings).

The payment is initiated through SSLCommerz (payment.payments: initiated → paid / failed). Provider callbacks are stored in gateway_events and processed once, using ops.idempotency_keys. On success a ledger transaction is posted, an escrow hold is created (held) and the assignment becomes active. If the deadline passes first, see Payment timeout.

task.assignmentstask.taskspayment.providerspayment.user_payment_methodspayment.paymentspayment.gateway_eventsops.idempotency_keysops.system_settingspayment.escrow_holdsfinance.ledger_transactionsfinance.ledger_entries

D11 D15 D26

Task execution UPDATED

The assignment moves active → arrived → in_progress → completion_requested → completed. Each step is an assignment_events row with the actor, GPS point and optional photo evidence. The task follows: assigned → in_progress → completion_requested → completed.

The Seeker confirms completion. If the Seeker does not respond, the escrow is released automatically after escrow_rules.auto_release_hours. A complaint within dispute_window_hours opens a dispute instead.

task.assignmentstask.assignment_eventstask.taskstask.task_status_historypayment.escrow_rulesops.files

D14 D17 D18 D19

Escrow release, settlement and accounting UPDATED

The escrow is released (released, with a release_reason; logged in escrow_events). The settlement records the gross amount and snapshots the commission, VAT and pricing-rule IDs used. Commission comes from the TukTaker's current tier (tuktaker_tiers → commission_tier_rates); VAT from vat_rules. Outstanding TukTaker penalties are deducted (penalty_applications) and any credits are applied (credit_applications).

One balanced ledger transaction moves the money from escrow to the TukTaker's wallet account, platform commission and VAT payable. The wallet is a ledger account; its balance is derived from ledger entries. Statement rows make the result readable to each user.

payment.escrow_holdspayment.escrow_eventsfinance.task_settlementsfinance.pricing_plansfinance.commission_rulesfinance.commission_tier_ratesfinance.tuktaker_tiersfinance.tuktaker_tier_historyfinance.vat_rulesfinance.vat_entriesfinance.penalty_applicationsfinance.credit_applicationsfinance.ledger_transactionsfinance.ledger_entriesfinance.ledger_accountsbilling.statement_entries

D27 D28 D29 D32

Ratings and tips NEW

After completion, both sides can rate each other (trust.ratings, one row per rater per assignment); rating_summaries keeps the averages used for tiers and search. Ratings do not wait for the payout.

A Seeker may add a tip: it is its own payment.tips row with its own payment and ledger transaction, and any tip fee comes from fee_schedules.

trust.ratingstrust.rating_summariespayment.tipspayment.paymentsfinance.fee_schedulesfinance.ledger_transactions

D25 D35

TukTaker payout (redemption) UPDATED

The TukTaker requests a payout from their wallet to a verified bKash or Nagad destination. Limits, fees and eligibility (minimum amount, daily / weekly / monthly cap, verified KYC, minimum tier) come from payout_rules.

Status: pending → approved (Finance) → processing (sent in a payout_batches run; every gateway try is a payout_attempts row) → paid. failed = refused by the provider; rejected = refused by Finance. Each money movement is a ledger transaction.

payment.payout_requestspayment.payout_rulespayment.payout_batchespayment.payout_attemptspayment.user_payment_methodsfinance.ledger_transactionsfinance.ledger_entriesbilling.statement_entries

D22 D23

Invoicing and reconciliation NEW

Periodic invoices are issued to Seekers and TukTakers (billing.invoices); each line points to a task settlement (invoice_lines.settlement_id). An invoice fee, if any, comes from fee_schedules.

Finance runs reconciliation against SSLCommerz, bKash and Nagad statements: reconciliation_runs per period, reconciliation_items per payment or payout, catching failed, missing or double payments.

billing.invoicesbilling.invoice_linesfinance.fee_schedulespayment.reconciliation_runspayment.reconciliation_items

D26 D36

Related and exception flows

Payment timeout NEW

If payment_due_at passes without a confirmed payment, the assignment becomes cancelled (a task.cancellations row with reason payment_timeout), the accepted bid becomes expired, and the task goes back to open, or to bidding_closed if bidding_closes_at has passed. Other bids stay valid.

Recommended: a payment confirmed after the deadline is refunded automatically (payment.refunds).

task.assignmentstask.cancellationstask.cancellation_reasonstask.bidstask.taskstask.task_status_historyops.system_settingspayment.refunds

D15

Dispute, freeze, and resolution UPDATED

A dispute (trust.disputes: open → in review → resolved) freezes the escrow (held → frozen) and the assignment and task become disputed. Staff review messages and evidence, then resolve: full release → released, full refund → refunded, split → partially_released plus a refund. Corrections use manual adjustments or credits.

trust.disputestrust.dispute_messagestrust.dispute_evidencepayment.escrow_holdspayment.escrow_eventspayment.refundsfinance.manual_adjustmentsfinance.credits

D34

Cancellations, penalties, refunds and credits UPDATED

A cancellation is a task.cancellations row with stage, reason and the fee / penalty calculated from finance.cancellation_rules (including the free window). A TukTaker penalty is a finance.penalties row whose remaining_amount is recovered from later settlements. Seeker refunds are payment.refunds with the admin fee and who bears it. Credits are granted in finance.credits and used through credit_applications. All money changes post to the ledger; earlier entries are never edited.

task.cancellationstask.cancellation_reasonsfinance.cancellation_rulesfinance.penaltiesfinance.penalty_applicationspayment.refundsfinance.creditsfinance.credit_applicationsfinance.ledger_transactions

D30 D31 D32 D33

Trust, safety and support NEW

Users can report misconduct (trust.user_reports) and block each other (iam.user_blocks). Fraud and abuse signals, such as attempts to move a deal off the platform, are logged in trust.risk_events for review. Support issues are tickets with their own message thread.

trust.user_reportsiam.user_blockstrust.risk_eventscomms.support_ticketscomms.support_ticket_messages

D20 D45

Messages and notifications

Task conversations and user notifications are linked to their task/user context. Delivery status and notification preferences are stored separately from task state; message content comes from versioned templates.

comms.conversationscomms.messagescomms.user_notificationscomms.notification_preferencescomms.notification_templates

Integration and governance

Outbox events support reliable integration delivery; idempotency keys protect retryable requests. Audit, privacy retention, and legal holds govern sensitive data lifecycle.

ops.outbox_eventsops.idempotency_keysaudit.audit_logsops.data_retention_policiesops.legal_holdsops.retention_execution_log

D38 D39

Status lifecycles NEW

Allowed statuses from the database dictionary (decision D16). Green is the normal path, left to right; red are side exits.

Task

draft→open→bidding_closed→assigned→in_progress→completion_requested→completed

Side exits: cancelled expired disputed

bidding_closed is skipped if a bid is accepted while open. On a payment timeout an assigned task returns to open (or bidding_closed).

Bid

pending→countered→accepted

Side exits: rejected withdrawn expired

countered repeats for each negotiation round. An accepted bid becomes expired if the Seeker misses the payment window.

Assignment

pending_payment→active→arrived→in_progress→completion_requested→completed

Side exits: cancelled disputed

pending_payment lasts at most 15 minutes (payment_due_at); cancelled includes reason payment_timeout.

Escrow

held→released

Side exits: frozen partially_released refunded

frozen while a dispute is open; it then ends as released, partially_released (split) or refunded.

Payout

pending→approved→processing→paid

Side exits: rejected failed

rejected = refused by Finance; failed = refused by bKash / Nagad (can be retried as a new attempt).

Core table relationships UPDATED

Keys below follow the key fields listed in the database dictionary. A parent row can have many child rows unless stated otherwise.

Parent table	Child table	Relationship key	Why it exists
iam.users	iam.seeker_profiles, iam.tuktaker_profiles	`user_id` → `users.id`	Each account has exactly one role (`account_role`), so it has either a Seeker or a TukTaker profile. UPDATED
iam.users NEW	iam.business_profiles	`business_profiles.seeker_user_id` → `users.id`	Optional business details for a Seeker account.
iam.kyc_submissions NEW	iam.kyc_documents	`kyc_documents.submission_id` → `kyc_submissions.id`	The NID and selfie files of each verification attempt.
catalog.categories	catalog.category_fields	`category_fields.category_id` → `categories.id`	Defines task form fields per category.
catalog.categories	task.tasks	`tasks.category_id` → `categories.id`	Each task belongs to one service category.
task.tasks	task.task_locations, task.task_attachments, task.bids, task.task_status_history	Child `task_id` → `tasks.id`	Task details, evidence, competing offers and status history.
task.bids	task.bid_offers, task.assignments	Offers use `bid_id`; assignment uses `bid_id`	Preserves negotiation rounds and the selected bid.
task.assignments	payment.payments, payment.escrow_holds, finance.task_settlements	Payment/escrow/settlement use `assignment_id`	Connects the accepted work attempt to payment, held funds, and final settlement.
task.assignments NEW	task.assignment_events, trust.ratings, trust.disputes, payment.tips, task.cancellations	Child `assignment_id` → `assignments.id`	Timeline evidence, ratings, disputes, tips and cancellations of one attempt.
payment.payments	payment.escrow_holds, payment.refunds	Child `payment_id` → `payments.id`	The payment that funds a hold, and refunds against it.
payment.escrow_holds NEW	payment.escrow_events	`escrow_events.escrow_hold_id` → `escrow_holds.id`	Freeze, release and refund actions on a hold.
task.cancellations NEW	finance.penalties	`penalties.cancellation_id` → `cancellations.id`	The penalty caused by a cancellation.
finance.penalties, finance.task_settlements NEW	finance.penalty_applications	`penalty_id`, `settlement_id`	Recovers an outstanding penalty from a later settlement.
payment.payout_requests NEW	payment.payout_attempts	`payout_attempts.payout_id` → `payout_requests.id`	Each gateway try for one payout.
finance.ledger_transactions	finance.ledger_entries	`ledger_entries.transaction_id` → `ledger_transactions.id`	Stores debit and credit lines; a posted transaction must balance.
finance.ledger_accounts	finance.ledger_entries	`ledger_entries.account_id` → `ledger_accounts.id`	Each ledger line changes an account balance.
billing.invoices NEW	billing.invoice_lines	`invoice_lines.invoice_id` → `invoices.id`	Lines of one periodic invoice.
finance.task_settlements	billing.invoice_lines	`invoice_lines.settlement_id` → `task_settlements.id`	Lets invoices include the final task settlement.
finance.ledger_transactions	billing.statement_entries	`statement_entries.ledger_transaction_id` → `ledger_transactions.id`	Links user-facing statement rows to their posted money movement.

Important ledger design: ledger entries are append-only and are never edited or deleted. Correct a posted movement with a reversal transaction. Wallet balances are derived from the ledger, not stored separately. The ledger transaction’s task/assignment references are intentionally loose references (not foreign keys); payment, escrow, and settlement rows provide the explicit business links.

Data handling principles represented in this flow

Snapshot rules: settlements retain rule IDs and computed values so later rule changes do not rewrite history.

Money: store BDT amounts as fixed-precision numeric values; every ledger movement must balance.

Lifecycle: task and assignment status records keep workflow state and event history; deadlines (bidding_closes_at, payment_due_at) drive automatic transitions.

Privacy: sensitive identity documents are file metadata in ops.files; retention jobs must respect legal holds.

Reference source: the TukTakKaj database dictionary (updated 2026-11-04) and the Decisions & Contradictions Tracker. This diagram summarizes the intended table-level flow; it is not a replacement for foreign-key definitions or application workflow validation.

Change log

2026-11-04 — Step 1: one mobile number, one account, one role (account_role); business details belong to a Seeker; OTP and KYC document tables added.
2026-11-04 — Steps 3–6: bidding_closes_at, bidding_closed / expired, the new bid and assignment statuses, pending_payment with the 15-minute payment_due_at, and auto-release through escrow_rules.
2026-11-04 — Ratings moved out of the payout step into a new step 8 with tips. Payout statuses, payout_rules and payout_attempts added. New step 10: invoicing and reconciliation.
2026-11-04 — New side flows: payment timeout, trust / safety / support. Dispute and cancellation flows use the new escrow statuses, cancellation rules, penalty and credit applications.
2026-11-04 — New Status lifecycles section. Relationships table: fixed the business profile key (seeker_user_id) added 8 relationships and extended 2 rows. Decision tags link each step to the tracker.