# Backtest Database Schema For Point In Time Queries

> >-

- **Type:** Skill
- **Install:** `agentstack add skill-himanshuj16-algo-trading-skills-backtest-database-schema-for-point-in-time-queries`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [HimanshuJ16](https://agentstack.voostack.com/s/himanshuj16)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [HimanshuJ16](https://github.com/HimanshuJ16)
- **Source:** https://github.com/HimanshuJ16/Algo-Trading-Skills/tree/main/skills/backtest-database-schema-for-point-in-time-queries
- **Website:** https://skills.himanshujangir.com

## Install

```sh
agentstack add skill-himanshuj16-algo-trading-skills-backtest-database-schema-for-point-in-time-queries
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

## When to Use

Invoke this skill when building data infrastructure for backtesting. Standard database tables return the latest value for a query, silently introducing lookahead bias. A point-in-time (PIT) schema stores records on two independent time axes, so queries like "what was the P/E ratio of AAPL as known on 2023-01-15?" return only data available at that historical moment.

The two axes follow the SQL:2011 temporal vocabulary:

| Axis | Column | Meaning |
|---|---|---|
| Knowledge time | `known_at` | When the value became **externally known** (vendor publication / filing release). Not the DB insert time. |
| Valid (application) time | `valid_from` | When the value **came into effect** in the real world (e.g. fiscal period end). |

## When NOT to Use

- **Market price bars** already carry their own event timestamp and are not restated; a PIT knowledge axis adds cost without benefit. Use ordinary time-series storage.
- **Live trading reads of current state** — as-of machinery is for reconstructing the past. Query the latest row directly.
- Four sibling skills cover adjacent ground; pick by what you are building:
  - designing the **schema and as-of query layer** itself → this skill;
  - joining **fundamentals** on SEC filing dates → `point-in-time-fundamentals-data-joins`;
  - assembling an **ML feature/label training matrix** → `point-in-time-database-for-ml-training-data`;
  - tracking **index membership** over time → `point-in-time-index-constituent-tracking`.

## Prerequisites

- Database or data store with temporal versioning capability.
- Historical fundamental/reference data with publication timestamps.
- A single documented timestamp convention (see `references/standards.md`) agreed across every ingesting vendor feed.

## Workflow

1. **Design PIT Schema**: Add `known_at` (knowledge time) and `valid_from` (valid time) to every fact table, plus a `revision` counter so simultaneous corrections resolve deterministically. Make the table append-only.
2. **Normalize Every Timestamp On Ingest**: Store one canonical representation — UTC, zero-padded, fixed-width ISO 8601. Reject anything else at the boundary rather than storing it and comparing later.
3. **Query with As-Of Semantics**: Filter `known_at  Full procedure: see `references/workflows.md`.
> Standards reference: see `references/standards.md`.
> Printable pre-flight checklist: see `assets/checklist.md`.

## Common Pitfalls

- **Using `created_at` Instead of `known_at`**: Database insertion time != when data was publicly available. A backfill loaded today would make every historical row look known today.
- **Restated Earnings Without Versioning**: Overwriting Q1 earnings with restated figures without preserving the original destroys the only record of what you could actually have traded on.
- **Comparing Timestamps As Strings**: Lexicographic comparison is chronologically correct only when every value shares an identical UTC offset representation and fractional-second precision (RFC 3339 §5.1). `2023-02-01T09:00:00-05:00` string-compares as earlier than `2023-02-01T12:00:00Z` but is actually two hours later — a silent leak.
- **Unpadded or Mixed-Format Dates**: A single `2023-9-01` sorts after `2023-10-01`, so the row becomes invisible to every as-of query that should have returned it. Validate the format on write, not on read.
- **Filtering Only the Knowledge Axis**: Guidance announced in January for a period ending in June is *known* in February but not yet *in effect*; returning it as February's value is a bitemporal modelling error.
- **Treating a Publication Date as Midnight**: An earnings release timestamped only `2023-06-01` is usually after the close. Assuming start-of-day makes it tradable a full session early.

## Verification

- Run `python -m unittest discover -s skills/backtest-database-schema-for-point-in-time-queries/scripts` — 100% pass rate.
- Insert an original figure and a later restatement sharing one `valid_from`; assert an as-of query before the restatement returns the original value, and after it returns the restated one.
- Assert that a record whose `known_at` carries a UTC offset is excluded when its UTC instant falls after the as-of cutoff, even though raw string comparison would admit it.

## Related Skills

- `point-in-time-fundamentals-data-joins`
- `point-in-time-database-for-ml-training-data`
- `point-in-time-index-constituent-tracking`
- `backtest-look-ahead-in-universe-selection`
- `backtest-determinism-and-reproducibility`
---

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [HimanshuJ16](https://github.com/HimanshuJ16)
- **Source:** [HimanshuJ16/Algo-Trading-Skills](https://github.com/HimanshuJ16/Algo-Trading-Skills)
- **License:** Apache-2.0
- **Homepage:** https://skills.himanshujangir.com

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-himanshuj16-algo-trading-skills-backtest-database-schema-for-point-in-time-queries
- Seller: https://agentstack.voostack.com/s/himanshuj16
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.