> For the complete documentation index, see [llms.txt](https://nytshift.gitbook.io/nytshift-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://nytshift.gitbook.io/nytshift-docs/agents-identity-and-data/customer-database.md).

# Customer database operations

## Scope

The customer PostgreSQL database owns authenticated profile preferences and customer-facing trading history. It does not own signing keys, execution claims or blind retry authority. The Arcus/Hyperliquid signer SQLite WAL database remains isolated and follows its separate recovery runbook.

## Production requirements

* PostgreSQL 17 or a reviewed compatible managed release.
* Private network access or provider-enforced TLS; never a public unauthenticated listener.
* A dedicated NIGHTSHIFT application role with only the required schema privileges.
* `DATABASE_URL` only in the private service environment. Never place it in `.nightshift/config.json`, source, browser state, logs or release artifacts.
* `CUSTOMER_ACCOUNT_DIGEST_KEY` is a private random value of at least 32 bytes. It HMACs wallet, venue and provider identifiers before storage. Keep it outside the repository/browser/logs and include it in the protected infrastructure-secret backup; losing it prevents future observations from matching existing account history.
* Automated encrypted backups plus point-in-time recovery. Retention and restore objectives must be chosen before customer launch.
* Capacity, connection, replication/backup and restore alerts owned by the operator.

## Self-hosted PostgreSQL boundary

The reviewed Hetzner topology runs PostgreSQL 16 on the application VPS with `listen_addresses=localhost`, SCRAM authentication for TCP connections and no public database listener. PostgreSQL 16 is accepted only after the exact migration set and both PostgreSQL integration suites pass against that server. Keep three separate roles:

* a connection-limited migration owner that has no superuser, database-create, role-create, replication or row-security-bypass capability;
* a connection-limited runtime role with schema usage plus required table and sequence DML only, and no DDL capability;
* an ephemeral test owner for a disposable isolated database.

Store the runtime URL and account-digest key in a root-owned, service-readable environment file. Keep the migration URL root-only and outside the systemd web environment. PostgreSQL stays reachable through loopback only; remote administration and tests use a temporary SSH local forward that is closed after the operation.

## Deploy or migrate

1. Take or verify a current provider backup/PITR point.
2. Run `pnpm db:status` from the exact release.
3. Review every pending migration and its SHA-256 in that release.
4. Run `pnpm db:migrate` once. It serializes with an advisory lock.
5. Run `pnpm db:verify`.
6. Start the web release and confirm `/api/health` reports `customerDatabaseState=ready`.
7. Exercise one authenticated profile read/write and PAPER create/save/reset/reload flow before promotion.
8. Confirm `/api/health` reports `customerAccountDigestState=configured`, then bind one exact Privy-verified wallet through same-origin `PUT /api/profile/venues`.
9. On testnet only, reconcile one known Hyperliquid CLOID twice. The first response must report `persistence.state=persisted`; the exact replay must report `replayed` with the same session/revision and no duplicate order/fill.

Never edit an applied migration. A checksum mismatch or unknown migration is an incident, not a reason to rewrite the registry.

The local Compose service creates only the PostgreSQL database and role. It does not preload the SQL directory through `docker-entrypoint-initdb.d`; `pnpm db:migrate` is the single schema authority and records every exact checksum.

The deterministic CI database job uses a digest-pinned PostgreSQL 17 service and runs `pnpm test:postgres`. That wrapper accepts only matching loopback `DATABASE_URL` and `NIGHTSHIFT_TEST_DATABASE_URL` values whose database name ends in `test` or `audit`; it migrates, verifies exact checksums and runs both customer persistence integration suites. Missing configuration is a failure, never a silent skip.

## Verified identity-provider app migration

`004_customer_identity_aliases.sql` preserves one canonical customer history when an explicitly reviewed Privy app migration assigns the same person a new DID. The application derives both opaque customer IDs with the normal `customerIdForPrivyUser` SHA-256 boundary and resolves the new ID to the existing canonical ID before any profile, PAPER or verified venue-binding store access.

This is an operator recovery path, not automatic account matching:

1. Take and verify a fresh encrypted PostgreSQL backup.
2. In both Privy app dashboards, independently open the user records and confirm the same verified provider account. Do not rely on an email typed by the user, browser-local state or an embedded wallet address alone.
3. Derive the old and new opaque customer IDs locally from the exact verified DIDs. Confirm the old ID owns the expected database profile and sessions and the new ID owns no database history.
4. Create one `customer_identity_aliases` row with the new opaque ID as `alias_customer_id`, the old opaque ID as `canonical_customer_id`, `reason='privy-app-migration'`, a SHA-256 digest of the separately retained operator evidence, and the current time.
5. Run `pnpm db:verify`, reload the signed-in customer, and prove profile, PAPER revision/history and venue binding all resolve to the canonical customer ID.
6. Record the backup, migration, evidence and signed-in result outside the database without copying tokens, raw DIDs, raw email or wallet addresses into logs or source.

Never auto-create an alias from email equality, silently fall back after an alias lookup failure, rewrite historical `customer_id` foreign keys, or insert raw provider identity into the alias table. A missing/corrupt alias table fails closed while PostgreSQL is configured.

## Verified account and execution ingestion

`003_verified_execution_projection.sql` adds the customer-owned side of the live/testnet history boundary:

* A Privy access token and matching identity token must prove the exact wallet before binding. The database stores one keyed account digest for `venue + network + wallet`, never the raw address.
* A unique venue/network/digest owner constraint prevents two customer identities from claiming the same account.
* Testnet and live sessions are distinct and may have unknown starting capital. PAPER still requires its exact starting capital.
* Only a value that passes `HyperliquidOrderReconciliationSchema` may enter the execution projection. An unavailable or malformed signer response is labeled `skipped` and writes nothing.
* CLOIDs, venue order/trade IDs and transaction hashes become keyed provider digests. Raw signer/venue response JSON is not stored.
* Every accepted observation is immutable and idempotent by account, provider-order digest and observation time. An exact replay returns the retained revision; changed evidence under the same identity is a conflict; older evidence after a newer observation is rejected.
* Orders, fills, exact quantity/price/notional, fee token/amount, builder fee and closed PnL are queryable. The already-reconciled configured-account snapshot additionally writes account equity, available margin, gross exposure, realized/unrealized PnL, live position quantity/entry/mark/notional/liquidation/leverage/margin/funding and bounded funding events.
* `complete` and `bounded-partial` coverage remain distinct in observation/reconciliation rows. Bounded evidence is never promoted to converged history.
* Bracket reconciliation projects each strictly normalized parent/TP/SL leg. The grouped signer contract remains the authority for sibling-cancel and attention-required semantics.
* Portfolio generations are immutable and idempotent. Account and position rows require complete coverage; funding rows retain `complete` or `bounded` coverage, while unavailable funding writes no invented events. Provider funding IDs are keyed digests.

The customer database still cannot sign, submit, cancel, retry, fund, withdraw, alter leverage, register an API wallet or enable mainnet. Operator reconciliation can write only after a customer has separately bound the exact signer account.

The portfolio adapter remains a normalized risk/account projection, not an exchange statement or tax ledger. Decimal text records the validated reconciler value deterministically; coverage and bounded history must stay visible. Do not call bounded fills/funding lifetime-complete, derive missing historical events, or treat database aggregates as signer/venue finality.

## Backup and restore

The repository's JSON `backup:*` workflow does not cover PostgreSQL. Use the managed provider's encrypted snapshot/PITR facility or a separately protected logical backup. A valid restore rehearsal must prove:

* both migration rows and schema objects are present;
* foreign keys and unique active-session constraints validate;
* profile revisions and paper session revisions remain monotonic;
* order/fill counts and SHA-256 snapshot digests match the source checkpoint;
* no raw Privy DID/email/token, signature, key or signer path appears;
* no raw wallet address, CLOID, provider order/trade ID or transaction hash appears;
* the restored database stays disconnected from production until verification completes.

Rehearse restoration into an isolated database at least quarterly and before a material retention or provider change. Record the backup identity, source time, restore time, migration set, row-count checks and operator approval outside the database.

For the reviewed self-hosted topology, install `infra/postgres/backup.sh` as `/usr/local/sbin/nytshift-postgres-backup` and install the matching systemd service/timer from `infra/systemd`. The job:

* creates a custom-format `pg_dump` without ownership or grants;
* verifies the source dump catalog, encrypts it with GPG AES-256, then decrypts the candidate into the protected temporary boundary and verifies that catalog again before any encrypted artifact is published; the source and verification plaintexts are removed immediately after their respective checks, retaining `roundTripVerified: true` in the bounded metadata;
* writes an encrypted-file SHA-256 and bounded metadata;
* retains exactly the newest configured count under the fixed `/var/backups/nytshift/postgres` boundary;
* refuses alternate database, secret or deletion paths.

The passphrase must be a root-owned `0600` file at `/etc/nytshift/postgres-backup.passphrase`. Same-host encrypted backups provide operator-error recovery but not host-loss recovery or point-in-time recovery. Before customer launch or real-money trading, copy every encrypted backup and its checksum/metadata to a separately credentialed off-host destination and enable a provider snapshot/PITR policy. Do not call the same-host timer a disaster-recovery control. The reviewed no-delete rclone replication boundary, protected configuration requirements, independent remote check and separate PITR rehearsal are documented in [`postgres-offsite-recovery.md`](/nytshift-docs/release-and-readiness/postgres-offsite-recovery.md).

## Degraded database

If PostgreSQL is unavailable:

* authenticated health is unsafe;
* new remote PAPER synchronization pauses and the browser continues its strict local copy;
* local exits, protection and cancellation remain available because they are simulation only;
* no client retries a conflicting revision blindly;
* divergent/newer local state is retained in the bounded recovery slot;
* live/testnet execution authority does not move to the browser or database.
* signer reconciliation may continue for operator safety, but a database-unavailable result must remain visibly `persistence.state=unavailable`; do not retry a venue action to repair customer history.

Recover the database, run `pnpm db:verify`, inspect provider health, then reload one signed-in PAPER session. A replay-safe local extension may import automatically; a divergence requires explicit operator/customer recovery review.

## Privacy deletion

Customer deletion must be a separately reviewed workflow. It must account for regulatory/audit retention, archive active sessions, revoke access, and delete or anonymize every customer-scoped child table transactionally. Do not implement deletion by manually removing a parent row or by editing backups.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://nytshift.gitbook.io/nytshift-docs/agents-identity-and-data/customer-database.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.