---
title: "Putting the tables back together"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Putting the tables back together}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

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

```{r setup}
library(transferegovr)
library(dplyr)
```

Each API is a relational database published one table at a time. Nothing is
nested and nothing is pre-joined, so an analysis almost always means retrieving
two or three tables and joining them yourself. This vignette maps the keys.

## The shape of each module

Two identifiers do most of the work across all three modules:

* **`id_programa`** — the program, which is the funding instrument as a whole.
* **`id_plano_acao`** — the action plan, which is one recipient's share of it.

Everything else hangs off one of those.

### TED

```
programa ──< plano_acao ──< plano_acao_meta ──< plano_acao_etapa
    │            ├──< termo_execucao
    ├──< programa_acao_orcamentaria
    └──< programa_beneficiario
                 ├──< nota_credito ──< evento
                 ├──< programacao_financeira ──< trf
                 ├──< plano_acao_analise
                 └──< plano_acao_parecer
```

`plano_acao_etapa` joins to `plano_acao_meta` on `id_meta`, not directly to the
plan. `trf` joins to `programacao_financeira` on `id_programacao`, and `evento`
to `nota_credito` on `id_nota`.

### Fund-to-fund

```
programa ──< plano_acao ──< plano_acao_meta ──< plano_acao_meta_acao
    │            ├──< empenho
    ├──< programa_beneficiario
    ├──< plano_acao_dado_bancario
    └──< programa_gestao_agil
                 ├──< plano_acao_destinacao_recursos
                 ├──< plano_acao_historico
                 ├──< termo_adesao ──< termo_adesao_historico
                 └──< relatorio_gestao ──< relatorio_gestao_acoes
                                        └──< relatorio_gestao_analise
```

The financial management tables form a chain of their own:
`gestao_financeira_lancamentos` joins to
`gestao_financeira_categorias_despesa` on
`id_categoria_despesa_gestao_financeira`, and
`gestao_financeira_subtransacoes` joins to the entries on
`id_lancamento_gestao_financeira`.

Three tables across the APIs carry a foreign key with an `_fk` suffix rather
than an `id_` prefix: `plano_acao_analise_responsavel.plano_acao_analise_fk` and
`relatorio_gestao_analise_responsavel.relatorio_gestao_analise_fk` here, and
`plano_acao_parecer.plano_acao_hist_fk` in TED.

### Special transfers

```
programa_especial ──< plano_acao_especial ──< plano_trabalho_especial
                              ├──< executor_especial ──< meta_especial
                              │                      └──< finalidade_especial
                              ├──< empenho_especial ──< documento_habil_especial
                              ├──< relatorio_gestao_especial
                              └──< relatorio_gestao_novo_especial
```

`documento_habil_especial` leads on to
`ordem_pagamento_ordem_bancaria_especial` through `id_dh`, and from there to
`historico_pagamento_especial` through `id_op_ob`. That is the payment trail:
commitment, document, payment order, history.

Note that `meta_especial` and `finalidade_especial` hang off the *executor*
(`id_executor`), not off the plan.

## A worked example

Which federal bodies decentralize the most money, and to what?

```{r}
programas <- tg_get(
  "ted", "programa",
  .select = c(
    "id_programa", "tx_nome_programa", "sigla_unidade_descentralizadora"
  ),
  .limit = Inf
)

planos <- tg_get(
  "ted", "plano_acao",
  .select = c(
    "id_plano_acao", "id_programa", "vl_total_plano_acao", "aa_ano_plano_acao"
  ),
  .limit = Inf
)

planos |>
  inner_join(programas, by = "id_programa") |>
  group_by(sigla_unidade_descentralizadora) |>
  summarise(planos = n(), total = sum(vl_total_plano_acao, na.rm = TRUE)) |>
  arrange(desc(total))
#> # A tibble: 5 × 3
#>   sigla_unidade_descentralizadora planos         total
#>   <chr>                            <int>         <dbl>
#> 1 MDS                                229 422595208242.
#> 2 MS                                 794  14472373138.
#> 3 FNDCT                              154  13779027285.
#> 4 MIDR                               603   5450415783.
#> 5 MAPA                               284   2835531350.
```

Retrieving both tables in full is reasonable here — 5500 and 6176 rows, six
requests between them.

## Check the join, do not assume it

A join that quietly drops rows looks exactly like a join that worked. Count
before and after:

```{r}
sum(!planos$id_programa %in% programas$id_programa)
#> [1] 0
```

Every action plan in TED belongs to a program that is also published. That is
worth knowing rather than assuming, because it is not true of every key in these
APIs. Sampling two hundred values from each relationship in the diagrams above,
most resolve completely, but three do not:

| Child | Parent | Resolved |
|---|---|---|
| `meta_especial.id_executor` | `executor_especial.id_executor` | 175 / 200 |
| `finalidade_especial.id_executor` | `executor_especial.id_executor` | 175 / 200 |
| `programa_acao_orcamentaria.id_programa` | `programa.id_programa` | 198 / 200 |

These are gaps in what the platform publishes, not in the retrieval. An
`inner_join()` will drop those rows silently; use `anti_join()` to see them
first, and decide deliberately.

If you are joining a filtered subset, fetch the other side with a matching
filter rather than trimming afterwards:

```{r}
planos_2024 <- tg_get(
  "ted", "plano_acao",
  aa_ano_plano_acao = 2024,
  .select = c("id_plano_acao", "id_programa"),
  .limit = Inf
)

notas <- tg_get(
  "ted", "nota_credito",
  id_plano_acao = in_(planos_2024$id_plano_acao),
  .limit = Inf
)
```

`in_()` sends the whole set to the service, so the filtering happens there. Be
aware that a very long list makes for a very long URL; a few thousand values is
usually fine, more than that is better done by fetching the table and filtering
locally.

## Identifiers are doubles

Columns the API declares as `bigint` come back as `double`, not `integer`,
because a value beyond `.Machine$integer.max` would become `NA` as an integer.
Joins between them work as expected, since both sides use the same type. Be
careful only if you convert one side yourself.

```{r}
class(planos$id_plano_acao)
#> [1] "numeric"
```

## Repeated identifiers are real

Some of these tables are views with a join already baked in, so an identifier
that reads like a key can repeat. `fundoafundo/programa` has 129 rows but 125
distinct `id_programa`. Check before you use one as a key:

```{r}
programas_ff <- tg_get("fundoafundo", "programa", .limit = Inf)

nrow(programas_ff)
#> [1] 129
n_distinct(programas_ff$id_programa)
#> [1] 125
```

`tg_tables()` reports the primary key where the API declares one, which it does
for ten of the forty-eight tables. Where `primary_key` is `NA`, no uniqueness is
guaranteed.

```{r}
tg_tables() |> filter(!is.na(primary_key))
```
