waters-boards/README.md

# Waters Boards

A self-hosted work-management app for the Waters Church team: boards, groups, items, statuses,
people, dates, updates and automations. It covers the parts of monday.com the team uses and
runs on a small AWS Lightsail server for a few dollars a month, covered by the church's AWS
credits for now.

It's one Node server (Express + SQLite) and a front end with no build step. It runs on desktop
browsers and on phones, and a phone can install it to the home screen like an app.

---

## What's in it

| | |
|---|---|
| **Workspaces & folders** | Several workspaces, nested folders, favorites, a searchable sidebar. Drag boards into folders. |
| **Boards** | Colored, collapsible groups you can reorder. Items and subitems, drag-and-drop rows, multi-select bulk actions (duplicate, set status, move, archive, delete). |
| **Column types** | Status (custom labels and colors, with "done" labels), People, Date (optional time), Timeline, Text, Long text, Numbers (with unit), Dropdown, Tags, Checkbox, Rating, Link, Email, Phone, Files, Creation log, Last updated, Item ID. You can resize, reorder, rename, duplicate and change the type of any column. Group footers show summaries. |
| **Views** | Main table, Kanban (drag between lanes), Calendar (drag to reschedule; agenda list on phones), Timeline/Gantt (drag or resize bars), Chart (bar/pie plus KPIs), Files gallery. |
| **Board tools** | Search, person filter, advanced filters, sort, hide columns, CSV import and export (opens in Excel), duplicate or move boards, activity log. |
| **Items** | A side panel (full screen on phones) with Updates (@mentions, replies, likes, attachments, paste images), an Info tab with every column, Files, Activity history, subitems, move to another board. |
| **Automations** | "When status changes to X / a column changes / an item is created / a date arrives" → "move to group / notify / set status / assign / set date / clear / archive". |
| **People** | Google sign-in limited to company domains. The Inbox collects mentions, assignments, replies and automation alerts, and live presence shows who's online. *My work* lists everything assigned to you by due date. A Team directory and admin tools control roles and access. |
| **Live** | Changes appear on everyone's screen right away (server-sent events), with no refresh. |
| **Migration** | One-click import from monday.com through its API: workspaces, folders, boards, columns, groups, items, subitems, updates, replies and files. |
| **Themes** | Light, dark and night, or follow the system setting. |
| **Safety** | Nightly database snapshots (14 kept), files served only to signed-in users, deactivated people are signed out everywhere. |

---

## Run it locally

Needs Node **22.13+** (it uses Node's built-in SQLite, so there's nothing native to compile).

```bash
cd server
npm install
cp .env.example .env      # then set DEV_LOGIN=1 for passwordless local sign-in
npm run dev
```

Open <http://localhost:3100>. With `DEV_LOGIN=1`, "Developer sign-in" accepts any
allowed-domain email. That button is disabled in production.

Data goes to `server/data/` (`boards.db`, `uploads/`, `backups/`). The folder is gitignored.

---

## Set up Google sign-in (once)

Google only allows sign-in from **https** addresses (or `localhost`), so the app lives at
**`https://boards.watersmedia.org`**, behind Caddy on the AWS box (see *Deploy to AWS* below).

1. Go to <https://console.cloud.google.com/> and sign in with the church Google Workspace admin account.
2. Create a project (e.g. "Waters Boards").
3. **APIs & Services → OAuth consent screen**: choose **Internal**, so only accounts in the
   church's Google Workspace can ever use it. Fill in the app name and support email, then save.
4. **APIs & Services → Credentials → Create credentials → OAuth client ID**
   - Application type: **Web application**
   - Authorized JavaScript origins: `https://boards.watersmedia.org` (and `http://localhost:3100` for local testing)
   - No redirect URIs are needed.
5. Copy the **Client ID** (ends in `.apps.googleusercontent.com`) into `GOOGLE_CLIENT_ID` in `.env`.
   On the AWS box, `deploy/aws/set-google-client-id.sh` does that for you: it prompts for the ID,
   writes it to `/opt/waters-boards/.env`, recreates the app container and prints the sign-in
   config the server reports afterwards.

The server checks every Google sign-in itself. It rejects any account outside
`ALLOWED_DOMAINS` and any email Google hasn't verified.

Set `ADMIN_PASSWORD` in `.env` as a break-glass login for the `ADMIN_EMAILS` accounts, in case
Google sign-in is ever misconfigured.

---

## Deploy to AWS (Lightsail)

Waters Boards runs on one AWS Lightsail server. Docker runs two containers there: the app, and
Caddy in front of it. Caddy gets and renews the Let's Encrypt certificate itself, so TLS ends at
Caddy and the app speaks plain HTTP on the private Docker network. Only ports 80 and 443 are
public.

**The box:** Ubuntu 24.04, 2 GB RAM, `us-east-1`, a static IPv4 address, Docker Engine with the
compose plugin. Lightsail snapshots the disk every day at 07:00 UTC.

**Folder on the box** (owned by `ubuntu`):

```
/opt/waters-boards/
  .env                  settings and secrets. Made by hand once, never in git
  data/                 database, uploads, nightly backups
  Caddyfile             copied from deploy/aws/
  docker-compose.yml    copied from deploy/aws/
  server/               the app source, copied from this repo's server/
```

**One-time setup**

1. **DNS.** `watersmedia.org` is served by the church's own nameservers (`ns1` and
   `ns2.waterschurch.org`), so there's no registrar panel to use. The zone is *not* in the
   `watersch` cPanel account; it belongs to whichever cPanel/WHM account owns `watersmedia.org`
   (its SOA contact is `nick@waterschurch.org`). In that account's **Zone Editor**, add an `A`
   record `boards` → the box's static IP, TTL 300. Caddy can't get a certificate until that
   record resolves, and ports 80 and 443 must be open in the Lightsail firewall.
2. **`.env`.** Copy `server/.env.example` to `/opt/waters-boards/.env` and fill it in. The deploy
   script refuses to run without it, so do this from your laptop before the first deploy:

   ```bash
   scp server/.env.example boards-aws:/opt/waters-boards/.env
   ssh boards-aws nano /opt/waters-boards/.env
   ```

   Set `ADMIN_EMAILS` and `ADMIN_PASSWORD`. `ADMIN_PASSWORD` is the break-glass login until
   Google sign-in is configured. Add `GOOGLE_CLIENT_ID` once you have it (see above).
3. **First deploy**, as below. When it finishes, <https://boards.watersmedia.org> should load.

**Deploy from a laptop.** Needs the `boards-aws` SSH alias (user `ubuntu`) in `~/.ssh/config`.
From the repo root:

```bash
deploy/aws/deploy.sh
```

It checks the server code for syntax errors, confirms `.env` exists on the box, copies `server/`,
`docker-compose.yml` and `Caddyfile` over, rebuilds the containers in place, waits for the
healthcheck, and prints what `https://boards.watersmedia.org/healthz` returns. Set `BOARDS_HOST`
to deploy to a host other than `boards-aws`.

**Deploy from Gitea Actions.** The Gitea server is only reachable from inside the church network,
so AWS can't pull from it. Instead a runner inside the network runs the same `deploy.sh` and
pushes to the box over SSH. That runner is a container on Chronicles-AB (the NAS that hosts
Gitea), in `/volume2/docker/gitea-runner-boards`, registered for this repo only with the label
`aws-deploy`. The checklist app's `nas` runner on Chronicles-NA is separate and untouched. The
runner and its job containers use Docker host networking, because containers on bridge
networks on that NAS have no outbound access at all (no DNS, no internet). The workflow
(`.gitea/workflows/deploy.yml`) runs on every push to `main` that touches `server/` or
`deploy/aws/`, or by hand from the Gitea Actions tab. One-time setup:

1. Make a deploy-only SSH key. Add its public half to `/home/ubuntu/.ssh/authorized_keys` on
   the box.
2. In the Gitea repo, **Settings → Actions → Secrets**, add:
   - `DEPLOY_SSH_KEY`: the private key.
   - `DEPLOY_KNOWN_HOSTS`: the output of `ssh-keyscan -t ed25519 <static IP>`.
3. In **Settings → Actions → Variables**, add `DEPLOY_HOST` (the static IP) and
   `DEPLOY_ENABLED` = `true`. Until `DEPLOY_ENABLED` is `true`, the deploy job is skipped, so
   pushes made before the box was ready don't show as failures.
4. If the runner ever needs rebuilding: on Chronicles-AB, `start-runner.sh` in that folder
   recreates the container. To register it afresh, delete `.runner` there, put a new token in
   `runner.env` (`docker exec -u git gitea gitea actions generate-runner-token --scope
   christian/waters-boards` on that NAS), and run the script again.

**What a deploy never touches:** `data/` and `.env`. It deletes nothing on the box. It rebuilds
without taking the running container down first, so a failed build leaves the old version
serving.

**Restore**

- *From a Lightsail snapshot* (the whole box): in the Lightsail console, create a new instance
  from the latest snapshot, then move the static IP to it. The folder, `.env`, data and SSH host
  key all come back as they were, so nothing else changes.
- *From `data/backups/`* (the database only): `data/` is owned by root, hence `sudo`.

  ```bash
  ssh boards-aws
  cd /opt/waters-boards
  docker compose stop boards
  sudo rm -f data/boards.db-wal data/boards.db-shm
  sudo cp data/backups/boards-YYYY-MM-DD.db data/boards.db
  docker compose start boards
  ```

---

## Bring your monday.com data over

1. Sign in as an admin and open **Admin → Import from monday.com**.
2. In monday.com, click your avatar → **Developers** → **My access tokens** → copy.
3. Paste the token, click **Load boards**, pick boards (all are selected by default), then click **Import**.

People are matched by email. Anyone who isn't signed in yet gets a placeholder, and their
assignments carry over when they first sign in with Google. You can re-run the import: boards
that were already imported are skipped. The token is used only for that run and is never
stored.

Subitems get their own columns in monday. Here, they show the parent board's columns, so
subitem values land in the parent column with the same title. Any value without a matching
column is kept as a note in the subitem's updates. Mirror, formula and connect-board columns
come across as their text value.

---

## Installing on phones

Open the site in Safari (iPhone) or Chrome (Android), then choose **Share → Add to Home
Screen** or **Install app**. It opens full screen with a bottom tab bar (Home, My work, Boards,
Inbox, Search). Pickers open as bottom sheets.

---

## Backups

- Every night after 2am the app writes `data/backups/boards-YYYY-MM-DD.db` and keeps 14.
- Lightsail takes an automatic snapshot of the whole disk every day and keeps 7. That covers
  `data/` (database + `uploads/`), `.env` and everything else on the box.
- To restore a database snapshot, follow *Restore* under *Deploy to AWS* above. Don't just copy
  the file over `boards.db`: the database runs in WAL mode, and a stale `boards.db-wal` beside a
  restored file corrupts it.

---

## Layout

```
server/
  server.js            Express app, admin import routes, nightly backup
  src/db.js            SQLite schema (node:sqlite) and helpers
  src/auth.js          Google ID-token sign-in, sessions, admin password, dev login
  src/api.js           REST API — boards, columns, groups, items, updates, files, views, automations, search
  src/model.js         Board loading, value formatting, activity log, notifications
  src/automations.js   Automation engine + daily date trigger
  src/realtime.js      Server-sent events
  src/importMonday.js  monday.com GraphQL importer
  src/templates.js     Board templates and default labels
  public/              Front end (Preact + htm, no build step)
    js/app.js          Shell, routing, sign-in
    js/board.js        Board header, views, toolbar, filters
    js/table.js        Main table (and the phone card list)
    js/cells.js        Column types: cells, pickers, summaries
    js/item.js         Item panel: updates, info, files, activity
    js/kanban.js · calendar.js · timeline.js · chart.js
    js/pages.js        Home, My work, Inbox, Search, Team, Admin
deploy/aws/
  docker-compose.yml   The app and Caddy containers, as run on the Lightsail box
  Caddyfile            Caddy config: HTTPS for boards.watersmedia.org, proxy to the app
  deploy.sh            Deploys from a laptop or Gitea: check, copy, rebuild, wait for healthy
  set-google-client-id.sh   Writes GOOGLE_CLIENT_ID to the box's .env and recreates the app
```