# 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. ## What is searched, as data | | | | --- | --- | | Table | `notes` | | Fields | `title` with weight A, `body` with weight B | | Shown as title | `title` | | Who may see a row | The row's `workspace_id` is one the person belongs to | | Newest by | `updated_at` | Keep this in one place in code. The schema below and the query are both made from it. ## The index Use the database in the `database` input. | `database` | Index | | --- | --- | | `postgres` | A generated column of type `tsvector`, made with `setweight(to_tsvector(LANGUAGE, title), 'A')` joined to the same for `body` with `'B'`, and a GIN index on it. | | `sqlite` | An FTS5 table over `title` and `body` with the `porter` tokenizer, kept in step with `notes` by three triggers, for insert, update and delete. | `LANGUAGE` is the `language` input. SQLite's stemmer only knows English: with any other language, use the `unicode61` tokenizer and say in the README that words then match exactly. ## Reading what was typed 1. Cut to 200 characters. Lowercase. 2. Take each run of text between two double quotes as a phrase. A quote with no partner is dropped. 3. In what is left, every character that is not a letter or a digit separates words, except a minus sign directly before a word, which marks it as a word to leave out. 4. Drop empty terms. Keep the first 8. | Typed | Terms | | --- | --- | | `hello world` | words `hello`, `world` | | `"hello world"` | phrase `hello world` | | `hello -world` | word `hello`; leave out `world` | | `say "hello world" now` | phrase `hello world`; words `say`, `now` | | `hello "` | word `hello` | | `a:b & c \| !d` | words `a`, `b`, `c`, `d` | | `it's` | words `it`, `s` | | `-` | none | | `""` | none | | three spaces | none | | `Invoice` | word `invoice` | ## Building the query Build the database's query from the terms, never from the typed text. | | `postgres` | `sqlite` | | --- | --- | --- | | A word | `'word'` | `"word"` | | The last word, with `prefix_matching` | `'word':*` | `"word"*` | | A phrase | words joined by `<->` | `"hello world"` | | Left out | `!'word'` | `NOT "word"`, after the kept terms | | Joined by | `&` | `AND`, between kept terms only | In Postgres pass the built text to `to_tsquery(LANGUAGE, $1)` as a parameter. In SQLite pass it to `MATCH` as a parameter. Quote each term as shown, doubling any quote inside it, though the rules above leave none. In SQLite `NOT` stands between two queries. It cannot start a query and cannot follow `AND`: `"hello" AND NOT "world"` is a syntax error. Join the kept terms with `AND`, put them in brackets, and follow them with `NOT "word"` for each word left out: ``` ("hello" AND "big"*) NOT "world" NOT "old" ``` In Postgres every term, kept or left out, is joined by `&`: `'hello' & 'big':* & !'world' & !'old'`. A query that only leaves words out matches nothing: answer with no results without asking the database. ## Order and pieces | | `postgres` | `sqlite` | | --- | --- | --- | | Rank | `ts_rank_cd` with weights that favour A | `bm25` with a weight of 10 for `title` and 1 for `body` | | Piece | `ts_headline` | `snippet` | For the piece, have the database mark matches with two characters that cannot be in the text, such as U+E000 and U+E001. Escape the piece as HTML, then replace the two characters with `` and ``. The cursor is the rank, the time and the id of the last result. The next page is everything after that in the same order. ## 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 - Results that tie on rank and time are ordered by id, lowest first. ## 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.