# CSV Template Specification

Source task: `plans/task-d.7.1.md`

All supported CSV imports must run in two phases:

1. Preview: parse, validate, resolve references, detect duplicates, and produce an import report.
2. Commit: upsert in a transaction where database-only writes are involved.

CSV imports must not bypass feature/foundation services once those services exist. Where current seed logic only exists in scripts, D.7.2+ should extract reusable server services first.

## Supported Templates

### `organizations.csv`

Purpose: create tenant organizations.
Target: `organizations`.
Seed type: `organization`.
Required columns: `organization_code`, `organization_name`, `slug`, `created_by_email`.
Optional columns: `tax_id`, `default_currency`, `phone`, `email`, `address`, `logo_url`, `plan`, `status`.
Generated columns: `id`, `created_at`, `updated_at`.
Validation rules: `slug` lowercase and unique; `organization_code` uppercase; `created_by_email` must resolve to a user.
Relationship resolution: `created_by_email -> users.id`.
Duplicate detection: existing organization by `slug`.
Upsert key: `slug`.
Import order: `02`.

```csv
organization_code,organization_name,slug,created_by_email,tax_id,default_currency,status
ALLA,ALLA Public Company Limited,alla,admin@example.com,0107558000393,THB,active
```

Error examples: duplicate slug with conflicting name; missing creator user; invalid lowercase/uppercase format.

### `users.csv`

Purpose: create setup-managed users.
Target: `users` through auth/user service.
Seed type: `organization`.
Required columns: `email`, `name`, `system_role`.
Optional columns: `password`, `active_organization_code`, `image_url`, `reset_password`.
Generated columns: `id`, `password_hash`, `created_at`, `updated_at`.
Validation rules: email format; `system_role` in `super_admin,user`; password required for new credential users unless invitation flow exists.
Relationship resolution: `active_organization_code -> organizations.slug or setup organization_code`.
Duplicate detection: existing user by email.
Upsert key: `email`.
Import order: `01`.

```csv
email,name,system_role,password,active_organization_code,reset_password
admin@example.com,System Administrator,super_admin,ChangeMe123!,ALLA,true
```

Error examples: invalid email; unsupported system role; existing user without reset permission.

### `memberships.csv`

Purpose: grant app-level organization access.
Target: `memberships`.
Seed type: `organization`.
Required columns: `organization_code`, `user_email`, `membership_role`, `business_role`.
Optional columns: `permissions`, `branch_scope_codes`, `product_type_scope_codes`.
Generated columns: `id`, timestamps.
Validation rules: `membership_role` in `admin,user`; `business_role` should be a known CRM role fallback code; scope codes must resolve when provided.
Relationship resolution: organization, user, branch options, product type options.
Duplicate detection: existing membership by `organization_id + user_id`.
Upsert key: `organization_code + user_email`.
Import order: `03`.

```csv
organization_code,user_email,membership_role,business_role,permissions,branch_scope_codes,product_type_scope_codes
ALLA,admin@example.com,admin,crm_admin,crm.dashboard.read|crm.role.read,head_office,crane|dockdoor
```

Error examples: unknown user; unknown organization; invalid branch/product type code.

### `crm-role-assignments.csv`

Purpose: assign CRM role profiles and CRM scopes.
Target: `crm_user_role_assignments`.
Seed type: `organization`.
Required columns: `organization_code`, `user_email`, `crm_role_code`, `is_primary`, `branch_scope_mode`, `product_type_scope_mode`.
Optional columns: `branch_codes`, `product_type_codes`, `is_active`.
Generated columns: `id`, `assigned_at`, timestamps.
Validation rules: role profile must exist; scope mode in `all,selected,none,inherit`; selected modes require matching codes.
Relationship resolution: user, organization, role profile, branch options, product type options.
Duplicate detection: existing assignment by `organization_id + user_id + role_profile_id`.
Upsert key: `organization_code + user_email + crm_role_code`.
Import order: `05`.

```csv
organization_code,user_email,crm_role_code,is_primary,branch_scope_mode,branch_codes,product_type_scope_mode,product_type_codes,is_active
ALLA,admin@example.com,crm_admin,true,all,,all,,true
```

Error examples: selected scope without codes; role profile missing; multiple primary rows for one user unless explicit replacement mode.

### `master-options.csv`

Purpose: import governed option rows.
Target: `ms_options`.
Seed type: `foundation`.
Required columns: `organization_code`, `category`, `code`, `label`.
Optional columns: `value`, `parent_category`, `parent_code`, `sort_order`, `is_active`, `metadata_json`.
Generated columns: `id`, timestamps.
Validation rules: category/code non-empty; parent resolves in same organization when provided; metadata must be valid JSON.
Relationship resolution: organization, optional parent option.
Duplicate detection: existing option by `organization_id + category + code`.
Upsert key: `organization_code + category + code`.
Import order: `06`.

```csv
organization_code,category,code,label,value,parent_category,parent_code,sort_order,is_active,metadata_json
ALLA,crm_customer_group,industrial,Industrial,industrial,,,1,true,{}
```

Error examples: invalid JSON; missing parent option; duplicate category/code in same CSV with conflicting labels.

### `branches.csv`

Purpose: typed convenience template for branch options.
Target: `ms_options` with `category=crm_branch`.
Seed type: `organization`.
Required columns: `organization_code`, `branch_code`, `branch_name`.
Optional columns: `prefix`, `address`, `tax_id`, `phone`, `email`, `sort_order`, `is_active`.
Generated columns: `id`, `category`, timestamps.
Validation rules: branch code unique per organization; code lowercase snake_case recommended; metadata fields serialized into option metadata.
Relationship resolution: organization.
Duplicate detection: existing `crm_branch` option by code.
Upsert key: `organization_code + branch_code`.
Import order: `07`.

```csv
organization_code,branch_code,branch_name,prefix,address,tax_id,sort_order,is_active
ALLA,head_office,Head Office,HO,99/9 Rama IX Road,0107558000393,1,true
```

Error examples: duplicate branch code; invalid organization; malformed metadata.

### `product-types.csv`

Purpose: typed convenience template for product type options.
Target: `ms_options` with `category=crm_product_type`.
Seed type: `organization`.
Required columns: `organization_code`, `product_type_code`, `product_type_name`.
Optional columns: `document_prefix`, `sort_order`, `is_active`, `metadata_json`.
Generated columns: `id`, `category`, timestamps.
Validation rules: product type code unique per organization; `document_prefix` uppercase recommended.
Relationship resolution: organization.
Duplicate detection: existing `crm_product_type` option by code.
Upsert key: `organization_code + product_type_code`.
Import order: `08`.

```csv
organization_code,product_type_code,product_type_name,document_prefix,sort_order,is_active
ALLA,crane,Crane,CR,1,true
```

Error examples: duplicate product type code; invalid JSON metadata; unsupported inactive default product type for document sequence.

### `document-sequences.csv`

Purpose: configure document numbering.
Target: `document_sequences`.
Seed type: `foundation`.
Required columns: `organization_code`, `document_type`, `prefix`, `period`.
Optional columns: `branch_code`, `product_type_code`, `current_number`, `padding_length`, `format`, `reset_policy`, `is_active`.
Generated columns: `id`, timestamps.
Validation rules: document type supported by sequence foundation; branch/product type resolve or default to generic scope; current number non-negative integer.
Relationship resolution: organization, branch option, product type option.
Duplicate detection: `organization_id + branch_id + product_type + document_type + period`.
Upsert key: same as duplicate detection.
Import order: `14`.

```csv
organization_code,branch_code,product_type_code,document_type,prefix,period,current_number,padding_length,format,reset_policy,is_active
ALLA,head_office,crane,quotation,QT-HO-CR-,2607,0,3,{prefix}{period}-{running},period,true
```

Error examples: unknown branch; unknown product type; malformed format missing `{running}`.

### `approval-workflows.csv`

Purpose: configure approval workflow headers.
Target: `crm_approval_workflows`.
Seed type: `foundation`.
Required columns: `organization_code`, `workflow_code`, `workflow_name`, `entity_type`.
Optional columns: `description`, `is_system`, `is_active`.
Generated columns: `id`, timestamps.
Validation rules: workflow code unique per organization; entity type supported, currently `quotation`.
Relationship resolution: organization.
Duplicate detection: `organization_id + workflow_code`.
Upsert key: `organization_code + workflow_code`.
Import order: `15`.

```csv
organization_code,workflow_code,workflow_name,entity_type,description,is_system,is_active
ALLA,quotation_standard,Quotation Standard Approval,quotation,Default quotation approval,false,true
```

Error examples: duplicate workflow code; unsupported entity type.

### `approval-steps.csv`

Purpose: configure workflow steps.
Target: `crm_approval_steps`.
Seed type: `foundation`.
Required columns: `organization_code`, `workflow_code`, `step_number`, `role_code`, `role_name`.
Optional columns: `approval_mode`, `is_required`, `sla_hours`, `calendar_mode`, `timeout_action`.
Generated columns: `id`, timestamps.
Validation rules: workflow resolves; step number positive integer; role code known to CRM role profiles or approved workflow roles.
Relationship resolution: organization, approval workflow.
Duplicate detection: `workflow_id + step_number`.
Upsert key: `organization_code + workflow_code + step_number`.
Import order: `16`.

```csv
organization_code,workflow_code,step_number,role_code,role_name,approval_mode,is_required,sla_hours,calendar_mode,timeout_action
ALLA,quotation_standard,1,sales_manager,Sales Manager,sequential,true,24,calendar_days,none
```

Error examples: missing workflow; duplicate step number; invalid SLA value.

### `approval-matrix.csv`

Purpose: map approval workflows to entity/scope/amount.
Target: `crm_approval_matrices`.
Seed type: `foundation`.
Required columns: `organization_code`, `entity_type`, `workflow_code`, `priority`, `is_default`.
Optional columns: `branch_code`, `product_type_code`, `min_amount`, `max_amount`, `currency`, `is_active`, `description`.
Generated columns: `id`, timestamps.
Validation rules: workflow resolves; branch/product optional; amount range valid; at most one default per target policy unless replacement mode.
Relationship resolution: organization, workflow, branch option, product type option.
Duplicate detection: default matrix by `organization + entity_type + is_default` or scoped matrix by full scope/amount tuple.
Upsert key: `organization_code + entity_type + workflow_code + priority + scope`.
Import order: `17`.

```csv
organization_code,entity_type,workflow_code,branch_code,product_type_code,min_amount,max_amount,currency,priority,is_default,is_active,description
ALLA,quotation,quotation_standard,head_office,crane,0,5000000,THB,100,true,true,Default crane approval
```

Error examples: max less than min; missing workflow; unresolved currency option.

### `customers.csv`

Purpose: import CRM customers.
Target: `crm_customers`.
Seed type: `business`.
Required columns: `organization_code`, `customer_code`, `customer_name`, `customer_type`, `customer_status`, `created_by_email`.
Optional columns: `branch_code`, `abbr`, `tax_id`, `address`, `province`, `district`, `sub_district`, `postal_code`, `country`, `phone`, `fax`, `email`, `website`, `lead_channel`, `customer_group`, `customer_sub_group`, `owner_user_email`, `notes`, `is_active`.
Generated columns: `id`, timestamps, owner assignment timestamps if owner provided.
Validation rules: code unique per organization; option references resolve; email format when provided.
Relationship resolution: organization, branch, owner user, creator user, master options.
Duplicate detection: `organization_id + customer_code`.
Upsert key: `organization_code + customer_code`.
Import order: `30`.

```csv
organization_code,customer_code,customer_name,customer_type,customer_status,owner_user_email,created_by_email,branch_code,customer_group,email,phone
ALLA,CUS-001,Siam Manufacturing,company,active,sales.crane.uat@alla.local,admin@example.com,head_office,industrial,contact@siam.example,02-000-1000
```

Error examples: unresolved owner; invalid customer group/sub-group relationship; duplicate customer code.

### `contacts.csv`

Purpose: import customer contacts.
Target: `crm_customer_contacts`.
Seed type: `business`.
Required columns: `organization_code`, `customer_code`, `contact_name`, `created_by_email`.
Optional columns: `position`, `department`, `phone`, `mobile`, `email`, `is_primary`, `notes`, `is_active`.
Generated columns: `id`, timestamps.
Validation rules: customer resolves; email format; one primary contact warning when multiple rows set primary.
Relationship resolution: organization, customer, creator user.
Duplicate detection: `organization + customer + email` when email exists, otherwise customer + contact name.
Upsert key: `organization_code + customer_code + email/contact_name`.
Import order: `32`.

```csv
organization_code,customer_code,contact_name,email,mobile,position,is_primary,created_by_email
ALLA,CUS-001,Anan Sales,anan@siam.example,081-000-0001,Procurement Manager,true,admin@example.com
```

Error examples: missing customer; duplicate primary contact warning; invalid email.

### `contact-shares.csv`

Purpose: import contact sharing grants.
Target: `crm_contact_shares`.
Seed type: `business`.
Required columns: `organization_code`, `customer_code`, `contact_email`, `shared_to_user_email`, `shared_by_user_email`.
Optional columns: `remark`, `is_active`.
Generated columns: `id`, `shared_at`, timestamps.
Validation rules: contact resolves; users resolve; cannot share to user outside organization membership.
Relationship resolution: organization, customer, contact, users.
Duplicate detection: `organization_id + contact_id + shared_to_user_id`.
Upsert key: same as duplicate detection.
Import order: `33`.

```csv
organization_code,customer_code,contact_email,shared_to_user_email,shared_by_user_email,remark,is_active
ALLA,CUS-001,anan@siam.example,sales.dockdoor.uat@alla.local,admin@example.com,Shared for joint account coverage,true
```

Error examples: contact not found; target user has no membership; self-share warning.

### `leads.csv`

Purpose: import marketing-owned leads.
Target: `crm_leads`.
Seed type: `business`.
Required columns: `organization_code`, `lead_code`, `status`, `created_by_email`.
Optional columns: `branch_code`, `customer_code`, `contact_email`, `description`, `project_name`, `project_location`, `lead_channel`, `product_type_code`, `priority`, `estimated_value`, `awareness_code`, `followup_status`, `lost_reason`, `outcome`, `owner_marketing_user_email`, `assigned_sales_owner_email`, `assignment_remark`.
Generated columns: `id`, assignment timestamps when assigned, timestamps.
Validation rules: status/priority/product type resolve; customer/contact relationship valid; estimated value numeric.
Relationship resolution: organization, branch, customer, contact, users, master options.
Duplicate detection: `organization_id + lead_code`.
Upsert key: `organization_code + lead_code`.
Import order: `34`.

```csv
organization_code,lead_code,status,created_by_email,customer_code,contact_email,branch_code,product_type_code,priority,project_name,owner_marketing_user_email,assigned_sales_owner_email
ALLA,LD-001,assigned,mk.uat@alla.local,CUS-001,anan@siam.example,head_office,crane,high,Warehouse Crane,mk.uat@alla.local,sales.crane.uat@alla.local
```

Error examples: contact does not belong to customer; unresolved status option; assignee outside organization.

### `opportunities.csv`

Purpose: import sales opportunities.
Target: `crm_opportunities`.
Seed type: `business`.
Required columns: `organization_code`, `opportunity_code`, `customer_code`, `title`, `product_type_code`, `status`, `priority`, `created_by_email`, `updated_by_email`.
Optional columns: `lead_code`, `contact_email`, `branch_code`, `description`, `requirement`, `project_name`, `project_location`, `outcome_status`, `lead_channel`, `estimated_value`, `chance_percent`, `expected_close_date`, `competitor`, `source`, `notes`, `is_hot_project`, `pipeline_stage`, `assigned_to_user_email`.
Generated columns: `id`, assignment timestamps when assigned, lifecycle timestamps where appropriate.
Validation rules: product/status/priority resolve; percent 0-100; dates ISO `YYYY-MM-DD`; lead optional.
Relationship resolution: organization, lead, customer, contact, users, options.
Duplicate detection: `organization_id + opportunity_code`.
Upsert key: `organization_code + opportunity_code`.
Import order: `36`.

```csv
organization_code,opportunity_code,customer_code,title,product_type_code,status,priority,created_by_email,updated_by_email,branch_code,contact_email,lead_code,chance_percent,assigned_to_user_email
ALLA,OP-001,CUS-001,Warehouse Crane Expansion,crane,negotiation,high,sales.crane.uat@alla.local,sales.crane.uat@alla.local,head_office,anan@siam.example,LD-001,70,sales.crane.uat@alla.local
```

Error examples: chance percent outside range; unresolved lead; contact/customer mismatch.

### `opportunity-parties.csv`

Purpose: import opportunity project parties.
Target: `crm_opportunity_customers`.
Seed type: `business`.
Required columns: `organization_code`, `opportunity_code`, `customer_code`, `role_code`.
Optional columns: `remark`.
Generated columns: `id`, timestamps.
Validation rules: opportunity/customer resolve; project party role resolves.
Relationship resolution: opportunity, customer, role option.
Duplicate detection: `opportunity_id + customer_id + role`.
Upsert key: same as duplicate detection.
Import order: `37`.

```csv
organization_code,opportunity_code,customer_code,role_code,remark
ALLA,OP-001,CUS-001,end_customer,Primary project owner
```

Error examples: unknown role; opportunity not found; duplicate role/customer row.

### `quotations.csv`

Purpose: import quotation headers.
Target: `crm_quotations`.
Seed type: `business`.
Required columns: `organization_code`, `quotation_code`, `customer_code`, `quotation_date`, `quotation_type`, `status`, `currency`, `subtotal`, `total_amount`, `created_by_email`, `updated_by_email`.
Optional columns: `opportunity_code`, `contact_email`, `branch_code`, `valid_until`, `project_name`, `project_location`, `attention`, `reference`, `notes`, `revision`, `parent_quotation_code`, `revision_remark`, `exchange_rate`, `discount`, `discount_type`, `tax_rate`, `tax_amount`, `chance_percent`, `is_hot_project`, `competitor`, `salesman_email`, `is_sent`, `sent_at`, `sent_via`.
Generated columns: `id`, timestamps, approval/snapshot/artifact fields remain runtime generated.
Validation rules: status/currency/type resolve; amounts numeric; parent quotation resolves for revision imports.
Relationship resolution: organization, opportunity, customer, contact, branch, options, users.
Duplicate detection: `organization_id + quotation_code`.
Upsert key: `organization_code + quotation_code`.
Import order: `40`.

```csv
organization_code,quotation_code,customer_code,quotation_date,quotation_type,status,currency,subtotal,total_amount,created_by_email,updated_by_email,opportunity_code,branch_code,salesman_email
ALLA,QT-HO-CR-2607-001,CUS-001,2026-07-02,crane,draft,THB,1000000,1070000,sales.crane.uat@alla.local,sales.crane.uat@alla.local,OP-001,head_office,sales.crane.uat@alla.local
```

Error examples: parent revision missing; total mismatch warning; approved/runtime fields supplied.

### `quotation-items.csv`

Purpose: import quotation line items.
Target: `crm_quotation_items`.
Seed type: `business`.
Required columns: `organization_code`, `quotation_code`, `item_number`, `product_type_code`, `description`, `quantity`, `unit_price`, `total_price`.
Optional columns: `unit`, `discount`, `discount_type`, `tax_rate`, `notes`, `sort_order`.
Generated columns: `id`, timestamps.
Validation rules: numeric amount fields; product type resolves; quotation resolves.
Relationship resolution: quotation, product type, unit, discount type.
Duplicate detection: `quotation_id + item_number`.
Upsert key: `organization_code + quotation_code + item_number`.
Import order: `41`.

```csv
organization_code,quotation_code,item_number,product_type_code,description,quantity,unit,unit_price,discount,discount_type,tax_rate,total_price,sort_order
ALLA,QT-HO-CR-2607-001,1,crane,Crane installation package,1,set,1000000,0,,7,1000000,1
```

Error examples: missing quotation; invalid item number; total calculation warning.

### `quotation-parties.csv`

Purpose: import quotation project parties.
Target: `crm_quotation_customers`.
Seed type: `business`.
Required columns: `organization_code`, `quotation_code`, `customer_code`, `role_code`.
Optional columns: `is_primary`, `remark`.
Generated columns: `id`, timestamps.
Validation rules: quotation/customer resolve; role resolves; one primary warning per role as applicable.
Relationship resolution: quotation, customer, project party role option.
Duplicate detection: `quotation_id + customer_id + role`.
Upsert key: same as duplicate detection.
Import order: `42`.

```csv
organization_code,quotation_code,customer_code,role_code,is_primary,remark
ALLA,QT-HO-CR-2607-001,CUS-001,end_customer,true,Primary quotation customer
```

Error examples: missing quotation; unknown project party role; duplicate primary warning.

### `quotation-topics.csv`

Purpose: import quotation topic headers.
Target: `crm_quotation_topics`.
Seed type: `business`.
Required columns: `organization_code`, `quotation_code`, `topic_key`, `topic_type`, `title`.
Optional columns: `sort_order`.
Generated columns: `id`, timestamps.
Validation rules: quotation resolves; topic type resolves; topic key unique per quotation CSV batch.
Relationship resolution: quotation, topic type option.
Duplicate detection: `quotation_id + topic_key`.
Upsert key: `organization_code + quotation_code + topic_key`.
Import order: `43`.

```csv
organization_code,quotation_code,topic_key,topic_type,title,sort_order
ALLA,QT-HO-CR-2607-001,scope-main,scope,Scope of Work,1
```

Error examples: topic type missing; duplicate topic key.

### `quotation-topic-items.csv`

Purpose: import quotation topic rows.
Target: `crm_quotation_topic_items`.
Seed type: `business`.
Required columns: `organization_code`, `quotation_code`, `topic_key`, `content`.
Optional columns: `sort_order`.
Generated columns: `id`, timestamps.
Validation rules: topic key resolves from imported or existing topic; content required.
Relationship resolution: quotation topic.
Duplicate detection: `topic_id + sort_order + content`.
Upsert key: `organization_code + quotation_code + topic_key + sort_order`.
Import order: `44`.

```csv
organization_code,quotation_code,topic_key,content,sort_order
ALLA,QT-HO-CR-2607-001,scope-main,Supply and install one overhead crane system,1
```

Error examples: missing topic; empty content; duplicate sort order warning.

## Unsupported or Deferred Templates

### `departments.csv`

Status: unsupported pending schema. No first-class department table was identified. Do not map this to ad hoc options without an ADR.

```csv
department_code,department_name
SALES,Sales
```

### `teams.csv`

Status: unsupported pending schema. Team scope limitations are documented; no first-class team graph exists.

```csv
team_code,team_name
SALES-CRANE,Crane Sales Team
```

### `warehouses.csv`

Status: unsupported pending schema. No warehouse table was identified.

```csv
warehouse_code,warehouse_name
MAIN,Main Warehouse
```

### `sites.csv`

Status: unsupported pending schema. Project/customer site is currently represented by text fields such as `project_location`, not a reusable site entity.

```csv
site_code,site_name,address
BKK-HQ,Bangkok HQ,99/9 Rama IX Road
```

### `services.csv`

Status: unsupported pending schema. Service catalog does not currently exist as first-class CRM data.

```csv
service_code,service_name
INSTALL,Installation Service
```

### `price-books.csv`

Status: unsupported pending schema. Current quotation items are not backed by a price-book foundation.

```csv
price_book_code,product_or_service_code,currency,unit_price
STD-THB,INSTALL,THB,10000
```

### `tax-rates.csv`

Status: unsupported pending schema. Tax rate currently lives as numeric fields on quotation/item rows.

```csv
tax_code,tax_name,tax_rate
VAT7,VAT 7%,7
```

### `attachments.csv`

Status: deferred. Attachment metadata exists, but binary upload/storage import is not safe as a generic CSV-only flow.

```csv
entity_type,entity_code,file_path,description
quotation,QT-HO-CR-2607-001,./files/spec.pdf,Customer specification
```

### `email-settings.csv`

Status: unsupported pending schema. Email configuration appears environment/provider driven; verification can check readiness but CSV import should not persist secrets without a secure configuration design.

```csv
provider,host,port,from_address
smtp,smtp.example.com,587,no-reply@example.com
```