Friend Import & Mapping

Overview

This domain has three tables covering two mechanisms. line_user_import and line_user_import_detail handle importing new friends from a file (job header plus per-row detail), while import_mapping_job handles updating existing friends from a CSV by matching rows on a chosen unique key.

Table line_user_import

Column	Type	Nullable	Default	Description
id	INTEGER	NOT NULL	nextval (serial)	Primary key of the import job
line_oa_id	INTEGER	NOT NULL	–	Target LINE OA channel for the import
title	TEXT	NOT NULL	–	User-supplied name for the import job
file_name	TEXT	NOT NULL	–	Original uploaded file name
file_path	TEXT	NOT NULL	–	Storage path of the uploaded file
archive_path	TEXT	NULL	–	Path the file is moved to once processing completes
created_by	INTEGER	NOT NULL	0	User id who started the import
total_records	INTEGER	NOT NULL	0	Total number of records in the file
total_processed	INTEGER	NOT NULL	0	Number of records processed so far
process_attempt	INTEGER	NOT NULL	0	Number of processing attempts (used to cap retries)
status	LineUserImportStatus (enum)	NOT NULL	new	Job status: new, processing, completed, failed
summary_stats	JSONB	NULL	–	Import summary, e.g. counts of inserted / duplicate / malformed rows
note	TEXT	NULL	–	Free-text note about the import job
organization_id	INTEGER	NOT NULL	–	Organization owning the job
created_date	TIMESTAMPTZ(3)	NOT NULL	CURRENT_TIMESTAMP	Job creation timestamp
updated_date	TIMESTAMPTZ(3)	NULL	–	Last update timestamp
deleted_date	TIMESTAMPTZ(3)	NULL	–	Soft-delete timestamp

Table line_user_import_detail

Column	Type	Nullable	Default	Description
id	UUID	NOT NULL	gen_random_uuid()	Primary key of the detail row
import_id	INTEGER	NOT NULL	–	Import job this row belongs to (FK to line_user_import.id)
line_oa_id	INTEGER	NOT NULL	–	Target LINE OA channel
line_user_id	TEXT	NOT NULL	–	LINE user ID read from the file
line_user_profile	JSONB	NULL	–	Profile fetched from the LINE API for this user ID
status	LineUserImportDetailStatus (enum)	NOT NULL	new	Row outcome: new, insert (added), exist (already present), undefined (not found on LINE), error_format (malformed)
error_message	TEXT	NULL	–	Error description for this row
organization_id	INTEGER	NOT NULL	–	Organization owning the data
created_date	TIMESTAMPTZ(3)	NOT NULL	CURRENT_TIMESTAMP	Creation timestamp
updated_date	TIMESTAMPTZ(3)	NULL	–	Last update timestamp
deleted_date	TIMESTAMPTZ(3)	NULL	–	Soft-delete timestamp

Table import_mapping_job

Column	Type	Nullable	Default	Description
id	INTEGER	NOT NULL	nextval (serial)	Primary key of the mapping job
line_oa_id	INTEGER	NOT NULL	–	LINE OA channel whose friend data will be updated
organization_id	INTEGER	NOT NULL	–	Organization owning the job
file_name	TEXT	NOT NULL	–	Original CSV file name
file_path	TEXT	NOT NULL	–	Storage path of the uploaded file
status	VARCHAR(20)	NOT NULL	'pending'	Job status: pending, processing, success, partial, failed
unique_key	JSONB	NOT NULL	–	Key used to match CSV rows against line_user
mappings	JSONB	NOT NULL	–	CSV-column-to-target-field mappings (JSON array)
validation	JSONB	NULL	–	Pre-import validation summary (total / will update / no match / errors)
stats	JSONB	NULL	–	Final counts after processing (total, updated, skipped, errors)
error_log_path	TEXT	NULL	–	Path to the per-row error log file
created_by	INTEGER	NULL	–	User id who started the job
created_date	TIMESTAMPTZ	NOT NULL	now()	Job creation timestamp
updated_date	TIMESTAMPTZ	NULL	–	Last update timestamp
deleted_date	TIMESTAMPTZ	NULL	–	Soft-delete timestamp

Notes

• Foreign key: line_user_import_detail.import_id -> line_user_import.id. import_mapping_job has no FK constraints — its references are logical only.
• Indexes: line_user_import is indexed on line_oa_id; import_mapping_job has import_mapping_job_oa_idx on (line_oa_id, status).
• import_mapping_job is absent from schema.prisma. It was created via manual SQL (manual-sql/9.import_mapping.sql) and is processed asynchronously by worker-go through a queue of the same name. That is why its timestamps are plain TIMESTAMPTZ and status is a VARCHAR(20) rather than an enum.
• unique_key is a JSON object shaped like {"field":"mobile_no","csvColumn":"Phone Number"}, where field may be a line_user column or a custom attribute in the form custom.<key>.
• mappings is a JSON array shaped like [{"csvColumn":"Member Tier","target":"custom.member_tier"}], where target points at a line_user column or a key inside custom_attribute.
• The import-mapping module is registered in system_module with full grants for roles 1-2 and readAll,read only for role 3, as defined in manual-sql/9.import_mapping.sql.