---
title: "End-to-End Walkthrough"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{a04_walkthrough}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

This vignette walks you from "I have a remote OMOP CDM somewhere" to "the
Syrona dashboard is showing my comparison" in a single sitting. Each step
ends with a verification check so you know it worked before moving on.

The example scenario: you have a multi-site OMOP CDM and you want to
compare the patient population at two care sites (e.g. two hospitals
within the same database). The same flow works for any pair of cohorts.

## Step 0 - Prerequisites

Before you start, make sure you have:

- **R 4.1 or newer** with the `syrona` package installed:
  ```r
  remotes::install_github("HealthInformaticsUT/Syrona")
  ```
  If you get `HTTP error 401 / Bad credentials`, you likely have a stale
  `GITHUB_PAT` environment variable that overrides your git credential
  store. Fix it by running `usethis::edit_r_environ()`, removing (or
  commenting out) the `GITHUB_PAT=...` line, saving, and restarting R.
  If the repository is public, no PAT is needed at all.
- **Read access** to an OMOP CDM database (PostgreSQL or DuckDB).
- **A writable schema** if your CDM is on PostgreSQL. Cohort tables and
  some extraction queries need a place to write temp tables. This is
  typically called `results_<your_user>` and is separate from the
  read-only `cdm_schema`. If you do not know whether you have a writable
  schema, see "Finding your schemas" below.
- **SSH access** to the database host if it lives behind a firewall.
- **A working directory** where Syrona can write `data/sources/` and
  `data/comparisons/`. Defaults to `getwd()`.

If you do not have a writable schema, ask your DB admin to create one.
Without it, `create_caresite_cohort()` and some extractions will fail.

### Finding your schemas

OMOP databases typically have multiple schemas - for example, different
CDM versions (`ohdsi_cdm_202410`, `ohdsi_cdm_202503`) or per-user
results schemas. You may not have access to all of them.

To discover what is available, connect to the database with `psql` via
your SSH tunnel:

```bash
psql -h localhost -p 5432 -U your_user -d omop
```

Then, inside the `psql` prompt:

```sql
-- List all schemas
\dn

-- See your username
SELECT current_user;

-- Check which schemas you can read (USAGE) and write to (CREATE)
SELECT nspname,
       has_schema_privilege(current_user, nspname, 'USAGE')  AS can_use,
       has_schema_privilege(current_user, nspname, 'CREATE') AS can_create
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
ORDER BY nspname;
```

Use a schema where `can_use` is `t` as your `cdm_schema`, and one where
`can_create` is `t` as your `write_schema`. If no schema is writable,
ask your DB admin to grant you one.

**Why are there multiple CDM schemas?** Each schema contains the same
OMOP CDM table structure but with different data - for instance,
different data refresh dates (`ohdsi_cdm_202410` vs `ohdsi_cdm_202503`),
different hospital sites, or different access levels. Your user account
may have read access to some but not all of them. A "permission denied
for schema" error usually means that schema exists but your account has
not been granted `USAGE` on it - try a different version, or ask your
admin.

## Step 1 - Open the SSH tunnel and connect

If your OMOP CDM is remote, open an SSH tunnel in a **separate terminal**
and leave it running for the rest of the session:

```bash
ssh -L 5432:localhost:5432 your_user@db-host.example.com
```

This forwards your local port 5432 to the database server's port 5432.
While the tunnel is open, your R session can connect to `localhost:5432`
as if the database were running on your machine.

Then, in R:

```{r, eval=FALSE}
library(syrona)

db <- syrona_connect_pg(
  host         = "localhost",        # via the SSH tunnel
  port         = 5432,
  dbname       = "omop",
  user         = "your_user",
  cdm_schema   = "ohdsi_cdm_202511", # ask your DB admin if unsure
  write_schema = "results_your_user" # must be writable
)
```

If you do not pass `password`, `syrona_connect_pg()` will look in your
`~/.pgpass` file or the `PGPASSWORD` environment variable (which you can
set in `~/.Renviron`). Setting up `.pgpass` is the cleanest way to keep
credentials out of your R history.

### Verify

```{r, eval=FALSE}
# A quick row count on person - confirms your connection and schema access
DBI::dbGetQuery(db$con, "SELECT COUNT(*) FROM ohdsi_cdm_202511.person")
```

**Note:** `DBI::dbListTables(db$con)` will likely return `character(0)`
(empty). This is normal - it only looks in the default `public` schema,
not in your named CDM schema. To list the tables inside your CDM schema,
use:

```{r, eval=FALSE}
DBI::dbGetQuery(db$con,
  "SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'ohdsi_cdm_202511'
   ORDER BY table_name")
```

You should see the standard OMOP tables: `person`, `visit_occurrence`,
`condition_occurrence`, `procedure_occurrence`, `drug_exposure`, etc.

If the person count query fails with "permission denied for schema",
your user does not have access to that particular schema version. Run
the schema discovery queries from "Finding your schemas" above to
find one you can access.

### For local DuckDB users

If your CDM is a local DuckDB file, replace step 1 with:

```{r, eval=FALSE}
db <- syrona_connect("path/to/omop.duckdb", read_only = FALSE)
```

`read_only = FALSE` is needed so you can create cohort tables in the same
file. Everything else in this walkthrough works identically.

## Step 2 - Explore care sites

`list_care_sites()` shows every care site in the CDM with at least 100
patients (configurable via `min_patients`). Use it to pick two
populations to compare:

```{r, eval=FALSE}
list_care_sites(db$con, cdm_schema = "ohdsi_cdm_202511")
#> # A tibble: 8 x 3
#>   care_site_id care_site_name        n_patients
#>          <int> <chr>                      <int>
#> 1          101 Central Hospital           45000
#> 2          205 University Clinic          28000
#> 3          312 Regional Hospital          15000
#> ...
```

Pick two `care_site_id` values you want to compare. For this walkthrough
we'll use `101` (Central Hospital) and `205` (University Clinic).

## Step 3 - Create two cohorts

A cohort tells Syrona which subset of persons to extract data for, and
clips events to each person's window in that cohort. For care-site
comparisons, the window runs from each person's first visit at that
hospital to their last visit (clipped to their observation period).

```{r, eval=FALSE}
create_caresite_cohort(
  con           = db$con,
  care_site_id  = 101,
  cohort_id     = 1,
  cohort_schema = "results_your_user",
  cdm_schema    = "ohdsi_cdm_202511"
)
#> v Cohort 1 (care_site 101): 45000 rows inserted.

create_caresite_cohort(
  con           = db$con,
  care_site_id  = 205,
  cohort_id     = 2,
  cohort_schema = "results_your_user",
  cdm_schema    = "ohdsi_cdm_202511"
)
#> v Cohort 2 (care_site 205): 28000 rows inserted.
```

### Verify

```{r, eval=FALSE}
cohort_summary(db$con, cohort_id = 1, cohort_schema = "results_your_user")
#> # A tibble: 1 x 5
#>   cohort_definition_id n_entries n_persons min_start  max_end
#>                  <int>     <int>     <int> <date>     <date>
#> 1                    1     45000     45000 2012-01-03 2019-12-28

cohort_summary(db$con, cohort_id = 2, cohort_schema = "results_your_user")
```

`n_persons` should match the `n_patients` you saw in `list_care_sites()`.
If it does not, something went wrong - usually a wrong `care_site_id`,
or `restrict_to_observation = TRUE` (the default) clipped people who
have visits but no observation period overlap.

## Step 4 - Extract the first cohort

`extract_all()` runs the full Phase 1 pipeline (denominators, demographics,
death counts, plus prevalence + info + chapters + attributes for each
selected domain) and writes one folder of CSVs per dataset.

```{r, eval=FALSE}
extract_all(
  dataset_name  = "Central_Hospital",
  db            = db,
  cohort_id     = 1,
  cohort_schema = "results_your_user"
)
#> i Applying cohort filter (cohort_id = 1)...
#>
#> -- Extracting dataset: Central_Hospital [conditions, procedures, drugs] --
#>
#> * Extracting denominators (ACHILLES-116)...
#> * Extracting demographics...
#> * Extracting death counts (ACHILLES-504)...
#> * Extracting condition prevalence (ACHILLES-404)...
#> * Extracting condition info...
#> * Extracting condition chapters...
#> * Extracting condition attributes...
#> * Extracting procedure prevalence...
#> ...
#> v Saved to data/sources/Central_Hospital/
```

How long this takes depends on database size and network latency. A
single care-site cohort with ~50k patients on a well-tuned PostgreSQL
typically finishes in a few minutes per domain.

If you only want to test the pipeline first, restrict to one domain:

```{r, eval=FALSE}
extract_all("Central_Hospital", db = db, cohort_id = 1,
            cohort_schema = "results_your_user",
            domains = "conditions")
```

### Verify

```{r, eval=FALSE}
list.files("data/sources/Central_Hospital/")
#>  [1] "_metadata.csv"             "condition_attributes.csv"
#>  [3] "condition_chapters.csv"    "condition_info.csv"
#>  [5] "condition_prevalence.csv"  "death_counts.csv"
#>  [7] "demographics.csv"          "drug_attributes.csv"
#>  [9] "drug_chapters.csv"         "drug_info.csv"
#> [11] "drug_prevalence.csv"       "procedure_attributes.csv"
#> [13] "procedure_chapters.csv"    "procedure_info.csv"
#> [15] "procedure_prevalence.csv"
```

You should see 15 CSVs (or fewer if you restricted to a subset of
domains). The presence of all four `condition_*` files is the quickest
sanity check that the conditions pipeline ran.

You can also load the extracted dataset back into R immediately:

```{r, eval=FALSE}
d1 <- load_dataset("Central_Hospital")
nrow(d1$condition_info)            # number of distinct conditions
sum(d1$demographics$patient_count) # total F+M persons
```

## Step 5 - Extract the second cohort

Same call, different `cohort_id` and `dataset_name`:

```{r, eval=FALSE}
extract_all(
  dataset_name  = "University_Clinic",
  db            = db,
  cohort_id     = 2,
  cohort_schema = "results_your_user"
)
```

### Verify

```{r, eval=FALSE}
list_datasets()
#> [1] "Central_Hospital" "University_Clinic"
```

If both names appear, both extractions completed.

## Step 6 - Compare

`compare_all()` runs Phase 2 (yearly prevalence ratios with confidence
intervals) and Phase 3 (random-effects meta-analysis at multiple
aggregation levels) for each domain present in both datasets.

```{r, eval=FALSE}
compare_all(
  d1 = "Central_Hospital",
  d2 = "University_Clinic"
)
#> -- Comparing Central_Hospital vs University_Clinic --
#> * conditions: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
#> * procedures: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
#> * drugs: yearly -> meta_agegroups -> meta_by_sex -> meta_summary
#> v Saved to data/comparisons/Central_Hospital_vs_University_Clinic/
```

### Verify

```{r, eval=FALSE}
list.files("data/comparisons/Central_Hospital_vs_University_Clinic/")
#>  [1] "_metadata.csv"
#>  [2] "condition_meta_agegroups.csv"
#>  [3] "condition_meta_by_sex.csv"
#>  [4] "condition_meta_summary.csv"
#>  [5] "condition_yearly.csv"
#>  [6] "drug_meta_agegroups.csv"
#>  [7] "drug_meta_by_sex.csv"
#>  [8] "drug_meta_summary.csv"
#>  [9] "drug_yearly.csv"
#> [10] "procedure_meta_agegroups.csv"
#> [11] "procedure_meta_by_sex.csv"
#> [12] "procedure_meta_summary.csv"
#> [13] "procedure_yearly.csv"

list_comparisons()
#> [1] "Central_Hospital_vs_University_Clinic"
```

You can also peek at the top hits without launching the dashboard:

```{r, eval=FALSE}
comp <- load_comparison("Central_Hospital", "University_Clinic")
comp$condition_meta_summary |>
  dplyr::arrange(dplyr::desc(abs(log2_pr))) |>
  dplyr::select(concept_name, log2_pr, ci_low, ci_high, fold_diff) |>
  head(10)
```

## Step 7 - Launch the dashboard

```{r, eval=FALSE}
run_app()
```

You should see a startup message like:
```
[syrona] DATA_DIR = /your/working/directory
```
followed by the Shiny app opening in your browser.

In the dashboard:

1. The **dataset selector** should list both `Central_Hospital` and
   `University_Clinic`.
2. The **comparison selector** should list `Central_Hospital_vs_University_Clinic`.
3. Pick the comparison and explore the prevalence ratio plots, the
   chapter breakdowns, and the meta-analysis tables.

If the dashboard launches but the dropdowns are empty, the most common
cause is a wrong working directory - see Troubleshooting below.

## Step 8 - Cleanup and disconnect

When you are done:

```{r, eval=FALSE}
# Optional: drop the cohorts you created
delete_cohort(db$con, cohort_id = 1, cohort_schema = "results_your_user")
delete_cohort(db$con, cohort_id = 2, cohort_schema = "results_your_user")

# Always disconnect from the database
syrona_disconnect(db)
```

Then close the SSH tunnel in your other terminal (Ctrl-C or `exit`).

The CSV files in `data/sources/` and `data/comparisons/` stay on disk -
you can re-launch `run_app()` any time without re-running extraction.

## Troubleshooting

### `install_github()` fails with "HTTP error 401 / Bad credentials"

A stale `GITHUB_PAT` environment variable is overriding your git
credential store. Run `usethis::edit_r_environ()`, remove or comment
out the `GITHUB_PAT=...` line, save, and restart R. If the Syrona
repository is public, no PAT is needed.

### "Could not connect" / "Connection refused"

The SSH tunnel is not running, or it forwards a different port. Check
the tunnel terminal is still open and that the port matches `port =`
in `syrona_connect_pg()`.

### "Permission denied for schema ..."

Your DB user does not have `USAGE` on that schema. This is common when
the database has multiple CDM schema versions and your account only has
access to some of them. To find which schemas you can access:

```{r, eval=FALSE}
DBI::dbGetQuery(db$con,
  "SELECT nspname,
          has_schema_privilege(current_user, nspname, 'USAGE') AS can_use,
          has_schema_privilege(current_user, nspname, 'CREATE') AS can_create
   FROM pg_namespace
   WHERE nspname NOT LIKE 'pg_%'
   ORDER BY nspname")
```

Use a schema where `can_use` is `TRUE` as your `cdm_schema`. If you
need write access (for cohort tables), use one where `can_create` is
`TRUE` as your `write_schema`. If nothing is writable, ask your DB
admin to grant:

```sql
GRANT USAGE ON SCHEMA cdm TO your_user;
GRANT SELECT ON ALL TABLES IN SCHEMA cdm TO your_user;
GRANT ALL ON SCHEMA results_your_user TO your_user;
```

### `dbListTables()` returns empty

This is expected. `DBI::dbListTables()` only lists tables in the
default `public` schema. OMOP tables live in a named schema like
`ohdsi_cdm_202511`. Use an explicit query instead:

```{r, eval=FALSE}
DBI::dbGetQuery(db$con,
  "SELECT table_name FROM information_schema.tables
   WHERE table_schema = 'ohdsi_cdm_202511'
   ORDER BY table_name")
```

### "Cohort 1: 0 rows inserted"

Either the `care_site_id` does not exist, or none of its visits overlap
with any observation period. Re-run `list_care_sites()` to confirm the
ID, and try `restrict_to_observation = FALSE` to see if observation
period clipping is the issue.

### `extract_all()` is very slow

Each extraction step runs a query against the CDM. On a slow network or
a large CDM, this can take a long time. Run with `domains = "conditions"`
first as a smoke test, then add `procedures` and `drugs` once you know
the connection is working.

### Dashboard launches but dropdowns are empty

The dashboard reads from `getOption("syrona.data_dir", ".")`. If you
launched R from a different working directory than where you extracted
the data, point Syrona at the right place explicitly:

```{r, eval=FALSE}
options(syrona.data_dir = "/path/to/folder/that/contains/data/")
run_app()
```

Or set the environment variable `SYRONA_DATA_DIR` before starting R.

## What to read next

- [a01_extraction](a01_extraction.html) - all the parameters and
  per-domain extraction details
- [a02_comparison](a02_comparison.html) - the meta-analysis cascade and
  output column reference
- [a03_cohorts](a03_cohorts.html) - more cohort patterns, including
  cohorts loaded from local data frames
