# Teable Integration Guide

## ⚠️ Critical Rules

1. **Server Actions Only**: Wrap all Teable calls in `"use server"` functions
2. **SQL for Queries**: Use `sqlQuery()` with `dbTableName` and `dbFieldName` from schema
3. **Field IDs for Writes**: Use `fldXXX` IDs when creating/updating records
4. **Double Quotes**: All SQL identifiers must use double quotes: `"schema"."table"`, `"field"`, aliases like `as "total"`. Link columns keep the case of the name they were created with (usually `"__fk_fldXXX"`); an unquoted identifier is lower-cased by PostgreSQL and fails with `column "__fk_fldxxx" does not exist`
5. **No cross-table JOIN**: Each SQL query can only access ONE table. Query separately, join in JS
6. **Error resilience**: `sqlQuery()` throws on failure. Single-query pages let it throw (ErrorReporter catches it). Multi-query pages (dashboards) must use `Promise.allSettled` so one failed query doesn't crash the whole page — show error UI for failed sections, not silent empty data
7. **Rate limit (10 req/sec)**: For read-heavy / public pages, prefer Next.js ISR (see "ISR for read-heavy pages" below) to keep API calls flat under traffic spikes

## Quick Start

```typescript
// Server Action
"use server";
import { sqlQuery, createRecord, signAttachments, safeParseJson } from '@/lib/teable';

// Query (use dbTableName/dbFieldName from schema)
export async function getUsers() {
  const { rows } = await sqlQuery('bseXXX', 
    `SELECT "__id", "fld_name", "fld_status" FROM "bseXXX"."tbl_users" WHERE "fld_status" = 'Active' LIMIT 100`
  );
  return rows;
}

// Aggregation (no LIMIT needed; use COUNT("__id") not COUNT(*); always quote aliases)
export async function getStats() {
  const { rows } = await sqlQuery('bseXXX',
    `SELECT COUNT("__id") as "total", COALESCE(SUM(CAST("fld_amount" AS numeric)), 0) as "sum" FROM "bseXXX"."tbl_orders"`
  );
  return rows[0];
}

// Create (use field IDs)
export async function addUser(name: string) {
  return createRecord('tblXXX', { fldName: name, fldStatus: 'Active' });
}
```

## SQL Reference

| Rule | Example |
|------|---------|
| Table name | `"bseXXX"."tbl_users"` (from `dbTableName`) |
| Field name | `"fld_name"` (from `dbFieldName`) |
| Record ID | `"__id"` |
| String value | `'Active'` (single quotes) |
| Always add | `LIMIT 100` for non-aggregate queries |
| Aggregation | `COUNT("__id")` not `COUNT(*)`, always `COALESCE(SUM(...), 0)` |
| Aliases | Always double-quote: `as "total"`, `as "cnt"` |
| Number arithmetic | `CAST("fld" AS numeric)` for SUM/AVG |

### Field Type → SQL

| Type | SQL Usage |
|------|-----------|
| text | `"fld_name" = 'value'` |
| number | `CAST("fld_amount" AS numeric)` for SUM/AVG |
| checkbox | `"fld_done" = true` |
| singleSelect | `"fld_status" = 'Active'` |
| multipleSelect | `"fld_tags" @> '["tag1"]'` |
| date | `"fld_date" > '2024-01-01'` |
| attachment | Parse JSON, use `signAttachments()` |

### Link Field (Important!)

Single-value links have **two columns**: JSON (`dbFieldName`) and FK (`options.foreignKeyName`). Read the FK name from the field instead of constructing it, and note that only the side owning the foreign key holds the linked record id: there `foreignKeyName` is `__fk_<fieldId>`. On the inverse side of a two-way one-one link `foreignKeyName` is `__id` — that is the row's **own** id, so filtering it by the linked record id returns the wrong rows; read the linked id from the JSON column instead, or query the owning table and join in application code.

| Type | JSON Column | FK Column |
|------|-------------|-----------|
| Single (ManyOne/OneOne) | `{"id":"recXXX","title":"..."}` | `options.foreignKeyName` = `recXXX` when it is `"__fk_fldXXX"`; `"__id"` on the inverse side is the row's own id, not the linked one |
| Multi (OneMany/ManyMany) | `[{"id":".."},{}]` | N/A (use JSON) |

Filter a single-value link through the FK column of the side that owns it, quoted like every other identifier:

```sql
-- ✅ SELECT "__id", "fld_name" FROM "bseXXX"."tbl_tasks" WHERE "__fk_fldProject" = 'recXXX' LIMIT 100
-- ❌ WHERE __fk_fldProject = 'recXXX'   → PostgreSQL lower-cases the written name →
--    error: column "__fk_fldproject" does not exist
```

**⚠️ Cross-table JOIN is NOT allowed — each SQL query can only access one table.** Use FK column to get the linked record ID, then query the other table separately and join in application code:

```typescript
// ✅ CORRECT: Separate queries + application-side join
const { rows: tasks } = await sqlQuery('bseXXX',
  `SELECT "__id", "fld_name", "__fk_fldProject" FROM "bseXXX"."tbl_tasks" LIMIT 100`
);
const { rows: projects } = await sqlQuery('bseXXX',
  `SELECT "__id", "fld_name" FROM "bseXXX"."tbl_projects" LIMIT 100`
);
const projMap = new Map(projects.map(p => [p.__id, p.fld_name]));
const result = tasks.map(t => ({
  ...t,
  project_name: projMap.get(t.__fk_fldProject) || null,
}));
```

```sql
-- ❌ WRONG: Cross-table JOIN causes "permission denied" error
SELECT t.*, p."fld_name" FROM "bseXXX"."tbl_tasks" t
LEFT JOIN "bseXXX"."tbl_projects" p ON p."__id" = t."__fk_fldProject";
```

**Multi-value links (use JSON within the same table):**
```sql
SELECT * FROM "bseXXX"."tbl_projects" WHERE "fld_tasks"::jsonb @> '[{"id":"recXXX"}]';
SELECT "fld_name", jsonb_array_length("fld_tasks"::jsonb) as "cnt" FROM "bseXXX"."tbl_projects";
```

### User Field

User fields: `{ id, title, email? }`. Check `isMultipleCellValue` for single vs multi.

```sql
-- Single user
SELECT "fld_assignee"::jsonb->>'id' as "user_id", "fld_assignee"::jsonb->>'title' as "user_name"
FROM "bseXXX"."tbl_tasks";

-- Multi user: check contains
SELECT * FROM "bseXXX"."tbl_tasks" WHERE "fld_members"::jsonb @> '[{"id":"usrXXX"}]';
```

## Attachments

Batch ALL attachments in ONE request:

```typescript
const { rows } = await sqlQuery(baseId, `SELECT "__id", "fld_files" FROM "bseXXX"."tbl_docs" LIMIT 50`);

// Collect all attachments (use safeParseJson from above)
const all = rows.flatMap(row => {
  const files = safeParseJson(row.fld_files) || [];
  return files.map((f: any) => ({ ...f, rowId: row.__id }));
});

// Sign once
const signed = await signAttachments(baseId, all);
```

## Write Operations

Use field IDs (`fldXXX`), not `dbFieldName`:

```typescript
await createRecord('tblXXX', { fldName: 'Task', fldStatus: 'Pending' });
await updateRecord('tblXXX', 'recXXX', { fldStatus: 'Done' });
await deleteRecord('tblXXX', 'recXXX');
```

| Type | Format |
|------|--------|
| Text | `"value"` |
| Number | `123.45` |
| Checkbox | `true` / `false` |
| Date | `"2024-01-15T00:00:00.000Z"` |
| Select | `"Option"` or `["A", "B"]` |
| User/Link | `["usrXXX"]` / `["recXXX"]` |

## ⚠️ Common Mistakes

### 1. Wrong field name
```sql
-- ❌ SELECT "Access Key" FROM ...     (uses 'name' with spaces)
-- ✅ SELECT "Access_Key" FROM ...     (uses 'dbFieldName')
```

### 2. Missing quotes
```sql
-- ❌ SELECT fld_name FROM bseXXX.users
-- ✅ SELECT "fld_name" FROM "bseXXX"."users"

-- ❌ SELECT "__id" FROM "bseXXX"."tbl_tasks" WHERE __fk_fldXxxYyy = 'recXXX'
--     PostgreSQL lower-cases the unquoted identifier → column "__fk_fldxxxyyy" does not exist
--     (names that only use lower case, like __fk_fldproject, happen to work, so this
--      breaks only the link fields whose field id contains upper-case letters)
-- ✅ SELECT "__id" FROM "bseXXX"."tbl_tasks" WHERE "__fk_fldXxxYyy" = 'recXXX'
```

### 3. Reserved words (must quote)
`"Group"`, `"Order"`, `"User"`, `"Date"`, `"Name"`, `"Status"`, `"Type"`, `"Key"`

### 4. Alias without quotes
```sql
-- ❌ SELECT "Group" as group FROM ...
-- ✅ SELECT "Group" as "group_name" FROM ...
```

### 5. Quote rule
- **Double quotes** `"..."` → identifiers (tables, fields, aliases)
- **Single quotes** `'...'` → string values

### 6. JSON field parsing
JSON fields (User, Link, Attachment) may be string OR already-parsed object. Always use safe parse:

```typescript
import { safeParseJson } from '@/lib/teable';

const user = safeParseJson(row.fld_assignee);
const attachments = safeParseJson(row.fld_files) || [];
```

### 7. COUNT(*) → use COUNT("__id")
```sql
-- ❌ COUNT(*)              (may cause permission issues)
-- ✅ COUNT("__id")         (explicit column reference)
```

### 8. Promise.all with raw sqlQuery
```typescript
// ❌ Promise.all + sqlQuery → one failure crashes all
await Promise.all([sqlQuery(...), sqlQuery(...), sqlQuery(...)]);

// ✅ Promise.allSettled → each query independent, check .status
const results = await Promise.allSettled([sqlQuery(...), sqlQuery(...), sqlQuery(...)]);
```

### 9. Silently swallowing errors
```typescript
// ❌ safeSqlQuery hides errors — user never knows something is broken
async function safeSqlQuery(sql: string) {
  try { return await sqlQuery(BASE_ID, sql); }
  catch { return { rows: [] }; }  // errors invisible!
}

// ✅ Promise.allSettled — errors are captured and shown in UI
const results = await Promise.allSettled([...]);
// results[i].status === "rejected" → show error message in that section
```

## Schema Files

`schema/table-{id}.json` contains:
- `dbTableName`: Use in SQL (e.g., `"bseXXX"."tbl_xxx"`)
- `fields[].dbFieldName`: Column name for SQL
- `fields[].id`: Field ID for write operations
- `fields[].isMultipleCellValue`: true = multi-value (link/user)
- `fields[].options.foreignKeyName`: FK column for single-value link (e.g., `"__fk_fldXXX"`)

## Runtime

Keep `<ErrorReporter />` in `app/layout.tsx`.

## Teable Resources Context

### Current Teable Project

- Project ID: `bseOeAxH4J1cOlemT2F`
- Use this `baseId` for any API that requires a project identifier

### Teable Resources Schema

All Teable table schemas are stored as JSON files under the `schema/` directory of this project.

Available tables (id → name → schema file):

- `tblMBGcoInXI1tzaQO3` → Vídeos VSL → `schema/table-tblMBGcoInXI1tzaQO3.json` (SQL: `"bseOeAxH4J1cOlemT2F"."tblMBGcoInXI1tzaQO3"`)
- `tblMSN7uQ207Wcb70ge` → Sessões VSL → `schema/table-tblMSN7uQ207Wcb70ge.json` (SQL: `"bseOeAxH4J1cOlemT2F"."tblMSN7uQ207Wcb70ge"`)
- `tblOK62ymRKVmygidsC` → Configurações ArkVideo → `schema/table-tblOK62ymRKVmygidsC.json` (SQL: `"bseOeAxH4J1cOlemT2F"."tblOK62ymRKVmygidsC"`). Update the existing unique `Nome = 'ArkVideo'` record; never seed configuration duplicates.

## ArkVideo app boundaries

This app is the authenticated administration panel. Native App Login uses the existing Acessos ArkVideo table (`tblIaDmk1vpT7Oy4gGY`, email `fld6nN555VQMIunqNeQ`), email OTP and Google, existing-only. Do not implement another authentication system. There are no public video or telemetry endpoints in this app.

A separate player app reads active videos and writes real production sessions into Sessões VSL. Admin preview never writes sessions. Embed URLs come only from the saved HTTPS origin in `fld8JZT6wMpPFDOtpAm`, followed by `/embed/{Identificador público}`. Empty origin means awaiting publication; never infer it from this admin's URL.

The dashboard excludes `Previa = true`. For retention, the player should store `{"version":1,"duration":120,"intervals":[[0,15],[40,52]]}` in Retenção JSON (documentation only; no sample data is inserted). Intervals are actual watched media-time ranges, not seek ranges. The parser also accepts a raw array of `[start,end]` or `{start,end}` intervals with duration from the video record. Unsupported/missing data is reported as unavailable. Each valid started session counts at most once per 10% duration bin. Other metrics aggregate directly from the prepared session columns.

- `tblIaDmk1vpT7Oy4gGY` → Acessos ArkVideo → `schema/table-tblIaDmk1vpT7Oy4gGY.json` (SQL: `"bseOeAxH4J1cOlemT2F"."tblIaDmk1vpT7Oy4gGY"`)
- `tblOK62ymRKVmygidsC` → Configurações ArkVideo → `schema/table-tblOK62ymRKVmygidsC.json` (SQL: `"bseOeAxH4J1cOlemT2F"."tblOK62ymRKVmygidsC"`)