# Design This file fixes the parts that are easy to get wrong. Choose the language and framework that fit the person's project; nothing here depends on them. ## The record, as data Everything the importer knows about the record comes from one description. Keep it in one place in code. | Field | Label | Type | Rules | Other names it answers to | | --- | --- | --- | --- | --- | | `email` | Email | email | required, key, at most 254 characters | `e-mail`, `email address`, `mail` | | `name` | Name | text | required, at most 200 characters | `full name`, `contact name` | | `company` | Company | text | at most 200 characters | `organisation`, `organization`, `account` | | `phone` | Phone | text | at most 40 characters | `telephone`, `mobile`, `tel` | | `joined_on` | Joined on | date | | `joined`, `signup date`, `created` | ## Data model Use the database in the `database` input. | Table | Columns | | --- | --- | | `contacts` | `id`, `owner_id`, `email` (unique per owner, lowercase), `name`, `company`, `phone`, `joined_on`, `created_at`, `updated_at` | | `imports` | `id`, `user_id`, `file_name`, `file_key`, `separator`, `mapping` (JSON), `date_format`, `state`, `rows_total`, `rows_done`, `created`, `updated`, `skipped`, `refused`, `report_key`, `created_at`, `finished_at` | | `import_changes` | `import_id`, `record_id`, `action`, `before` (JSON), `after` (JSON) (only if `undo` is on) | | `import_mappings` | `user_id`, `header_hash`, `mapping`; primary key on the first two | `state` is one of `uploaded`, `checked`, `running`, `done`, `cancelled` and `failed`. ## Parsing cases Each line of the first column is one line of the file. `⏎` marks a line break inside the file. | File | Fields | | --- | --- | | `a,b,c` | `a`, `b`, `c` | | `"a,b",c` | `a,b`, `c` | | `"say ""hi""",x` | `say "hi"`, `x` | | `"one⏎two",x` | one field holding `one`, a line break and `two`; then `x` | | `a,,c` | `a`, an empty field, `c` | | `a;b;c` in a file whose lines all have two semicolons | `a`, `b`, `c` | | ` a , b ` | `a`, `b`: spaces around a value are trimmed | | `" a "` | `a`: also when the value was quoted | | A byte order mark, then `email,name` | `email`, `name`: the mark is not part of the first header | | `a,b` then an empty last line | one row; the empty line is not a row | Lines end with a line feed, or a carriage return and a line feed. Accept both in one file. Use a maintained CSV library if the language has one, and run these cases against it. ## Rules - Read the file as a stream. Never hold all of it in memory. - The dry run and the import run the same code for each row, with one switch that says whether to save. That is what keeps the counts of the two equal. - Row numbers in messages count the header as row 1, as a spreadsheet shows them. - To carry on after an interruption: `rows_done` is updated in the same transaction as its batch. Starting again skips that many rows. - A row's fate is decided against the database inside its batch's transaction, so a record created by someone else during the import is updated or skipped, not duplicated. The unique index on `email` is the last guard. - For undo, write one `import_changes` row for each record created or updated, in the same transaction, with the values before and after. To undo an update, restore a field only if it still holds the `after` value. ## Configuration | Secret | Purpose | | --- | --- | | `DATABASE_URL` | Connection string for the chosen database. | ## Who is signed in The spec reaches the app's sign-in through one function, `current_user(request)`. It answers with the person's id, and says whether they are an administrator where this spec asks. In the person's app it reads their session. When there is no app yet, build this stand-in and nothing more: a page that lists two seeded people, one of them an administrator, with a button for each that sets a signed cookie naming them. The stand-in refuses to start in production. ## Named here - When two separators split the first lines equally well, comma wins, then semicolon, then tab. - The other date forms a person may pick: `MM/DD/YYYY`, `DD/MM/YYYY`, `DD.MM.YYYY`. - Empty rows are not counted in any total. ## What this spec leaves to you Where this spec names something, use that name, so that two builds of it agree. Where it does not, the choice is yours. List what you chose in the README.