caishen

PDSP — Price Store Database Schema

RISE Framework Specification

Spec ID: 71 Version: 1.0 Document ID: caishen-rise-pdsp-schema-v1.0 Last Updated: 2026-08-01 Source of truth: src/Caishen/Databases/SpiderDbDatabase/gia-mssql/PDSDb.script-221031.sql (UTF-16LE)

Creative Intent

What This Schema Enables:

Desired Outcomes:

  1. A bar can be located by identity alone, with no scan
  2. A series window can be read without knowing which periods exist
  3. Derived analysis (indicators, segments, patterns) joins to prices by the same natural key, so nothing needs re-keying

Overview

The price store (PDSDb) holds seven tables in two clusters:

Price cluster — the core, written by the refresh engine:

Analysis cluster — derived, written by the indicator/analysis engines:

The strategy/ordering domain lives in a separate database (SpiderDb, spec 76). The two are joined only in application code, never by a cross-database foreign key. Preserve that separation: price data has a fundamentally different write pattern, retention need, and blast radius than strategy data.

Price Cluster

Table: PDSPPrices

The central table.

TABLE PricePoints
  povTlid                        varchar(32)   NOT NULL   -- PRIMARY KEY
  instrument                     varchar(16)   NOT NULL
  timeframe                      varchar(4)    NULL       -- see note
  askO, askH, askL, askC         float         NOT NULL
  bidO, bidH, bidL, bidC         float         NOT NULL
  volume                         int           NOT NULL
  dt                             datetime      NOT NULL   -- period START
  isIncompleted                  bit           NULL       -- see note
  instrumentPropertyRef          varchar(16)   NOT NULL   -- FK

  PRIMARY KEY CLUSTERED (povTlid)
  FOREIGN KEY (instrumentPropertyRef) REFERENCES InstrumentProperties(instrument)

Key derivation: povTlid is computed, not assigned. See spec 70, Bar Identity. The clustered primary key on a content-derived string is the schema’s defining choice — it makes every write idempotent and every identity lookup a single seek.

Schema corrections required for re-implementation:

Issue Original Required
timeframe nullable NULL allowed NOT NULL — every bar has a timeframe; the key already encodes it
isIncompleted nullable NULL allowed, readers treat null as false NOT NULL DEFAULT false
instrument duplicated Both instrument and instrumentPropertyRef hold the same value Collapse to one column that is both the FK and the filter column
dt zone Naive datetime Timestamp with timezone, stored UTC (spec 70, Time semantics)
Missing index Only the clustered PK exists Add the covering index below

Required index — this is the load-bearing one. Stated as a requirement, with DDL only as an illustration:

Requirement I1. Reads of the form (instrument, timeframe) + dt descending must be served by an index that covers the bar columns — i.e. satisfiable without touching the base table.

-- Illustration in one dialect; express it however the target store covers.
INDEX ix_series_time ON PricePoints (instrument, timeframe, dt DESC)
      INCLUDE (askO, askH, askL, askC, bidO, bidH, bidL, bidC, volume, isIncompleted)

Targets without an INCLUDE-style payload satisfy I1 differently — a composite index with the payload columns in the key, a clustering/sort key on (instrument, timeframe, dt), or a columnar layout. The requirement is coverage and ordering, not this syntax.

Every read in the system has the shape WHERE instrument = ? AND timeframe = ? AND dt <= ? ORDER BY dt DESC LIMIT n. Without this index that is a full scan of the clustered key, which is ordered by key string — i.e. by instrument name then by lexical timestamp, which is not chronological across timeframe formats. The original had no such index and compensated with periodic index reorganization, which does not address the problem. This index is not an optimization; it is a correctness-of-performance requirement.

Second index, for the incremental path:

Requirement I2. Locating the forming bar for a series must be a bounded lookup, not a scan.

INDEX ix_incomplete ON PricePoints (instrument, timeframe, isIncompleted, dt DESC)
      WHERE isIncompleted = true          -- filtered/partial index where supported

The incremental algorithm (spec 72) probes for the anchor on every run, for every series. Where filtered/partial indexes are unavailable, the unfiltered composite still satisfies I2; the filter is an optimization that keeps the index to at most one row per active series.

Table: PDSPInstrumentProperties

TABLE InstrumentProperties
  instrument             varchar(16)    NOT NULL   -- PRIMARY KEY
  pipSize                float          NOT NULL
  precision              int            NOT NULL
  mmr                    float          NOT NULL   -- minimum margin requirement
  lmr                    float          NOT NULL   -- liquidation margin requirement
  quantityMinimim        int            NOT NULL   -- [sic] original spelling
  quantityMaximim        int            NOT NULL   -- [sic] original spelling
  baseUnitSize           int            NOT NULL
  contractMultiplier     float          NOT NULL
  contractCurrency       varchar(8000)  NOT NULL   -- see note
  trailingStepMinimum    int            NOT NULL
  trailingStepMaximum    int            NOT NULL
  subscriptionStatus     bit            NOT NULL
  marketCode             varchar(64)    NOT NULL

  PRIMARY KEY CLUSTERED (instrument)

Corrections required: fix the Maximim/Minimim misspellings; size contractCurrency to an actual currency code (varchar(8)) rather than varchar(8000); make marketCode a constrained enumeration (FOREX | COMMODITY | INDICE | TREASURY).

The varchar(8000) widths appear throughout both databases — they are an artifact of the code generator defaulting to maximum width, not a design intent. Size every string column to its actual domain.

Analysis Cluster

These tables are written by the indicator and analysis engines (specs 05, 04), not by the price refresher. They are documented here because they live in the same database and key off the same identity.

Table: PanoIndicators

One row per bar, holding the full computed indicator panel. Keyed by the same povTlid as the bar it describes — a strict 1:1 extension of PricePoints.

~64 columns. Grouped by concern:

Group Columns
Alligator (Bill Williams) Lips, Teeth, Jaw, Throat, GatorLower, GatorUpper, GatorSupreme, NbBarMouthIsOpen, NbBarPriceOutMouth
Awesome / Accelerator oscillators AO, AOF, AC, ACD, SAO, Zone, ZLC, Saucer, StDevAO
Fractals Fractal, Fractal5/8/13/21/34/55, FractalBuy, FractalSell, FractalDimension, FractalInitBuySignal, FractalInitSellSignal
Divergent bars BDB (bullish), FDB (fractal divergent bar)
Distances distGLRL, distGLBL, distRLBL, distRLPrice, distGLPrice, distBLPrice
Divergence MPDivergence, DivergenceIndicator, DivergenceStrenght [sic], LabelDivergence, LabelAngulation
Other Squat, MFI, IsExtreme, BarType, StDevClose, IsEntryPoint, IsExitPoint
Range link PovTlidRange → SequencialPanoDatas
Scratch int1..int3, double1..double3, bool1..bool3, string1..string3

The scratch columns are a schema smell to remove. They appear in four tables (PanoIndicators, SequencialPanoDatas, Casans, PanoSegments) and exist because adding a column to a generated model was expensive. In a re-implementation use a typed extension mechanism (a JSON/document column with a declared schema, or a proper migration workflow). Carrying int1..int3 forward guarantees the same undocumented-meaning problem recurs.

Foreign key defect to fix: the original declares six separate foreign keys all on the single column PovTlid — pointing at Casans, PanoSegments, PanoSegments1, PDSPPrices, TradedSignalExamples, and TradedSignalExamples1 — plus a seventh on PovTlidRange to SequencialPanoDatas. One column cannot meaningfully reference six parents, and two of the six are duplicate constraints against the same target table under a 1-suffixed name.

Only the PDSPPrices reference (and the PovTlidRange one) is real; the rest are generator artifacts and should be dropped. If a genuine association to segments or cases is needed, model it with its own column or a join table.

Table: SequencialPanoDatas

Aggregate statistics over a range of bars rather than a single bar.

TABLE RangeStatistics
  povTlidRange      varchar(64)  NOT NULL  -- PRIMARY KEY
  instrument        varchar(16)  NOT NULL
  timeframe         varchar(3)   NOT NULL
  nbPeriods         int          NULL
  dtStart, dtEnd    datetime     NULL
  bdbAvgHeight      float        NULL
  stDevAO, stDevClose               float NOT NULL
  dist{GLRL,GLBL,RLBL,RLPrice,GLPrice,BLPrice}Max    float NOT NULL
  dist{...}Min                                        float NOT NULL
  dist{...}Stdev                                      float NOT NULL
  (+ scratch columns)

Range identity: povTlidRange extends the bar key format with a second timestamp — <instrument>_<tf>__<tlidFrom>__<tlidTo> (see BarTlider.MkPovTimerangedTlid). Same virtue as the bar key: a range is named by its content, so the same range computed twice collides rather than duplicates.

Note timeframe varchar(3) here versus varchar(4) in PDSPPrices — an inconsistency that truncates 4-character codes. Unify.

Table: Casans (Case Analyses)

A named study over a period, self-referencing to form a hierarchy.

TABLE CaseAnalyses
  cid                varchar(32)   NOT NULL   -- PRIMARY KEY
  label              varchar(160)  NOT NULL
  category           varchar(64)   NULL
  typeName           varchar(64)   NULL
  dtStart, dtEnd     datetime      NOT NULL
  stepsText          varchar(8000) NULL
  notes              varchar(8000) NULL
  binLabel           bit           NULL
  profitPerContract  float         NULL
  profitPips         float         NULL
  parentCasanCid     varchar(32)   NULL   -- FK → self (hierarchy)
  (+ scratch columns)

Table: PanoSegments

A labelled sub-sequence within a case analysis; also self-referencing.

TABLE Segments
  idug                varchar(32)   NOT NULL   -- PRIMARY KEY
  tagName             varchar(128)  NOT NULL
  transformType       varchar(128)  NOT NULL
  category            varchar(64)   NULL
  eventName           varchar(8000) NULL
  timeOrigin          int           NULL
  desired             bit           NULL       -- the intended vs actual flag
  isCasanPanoPoint    bit           NULL
  notes               varchar(8000) NULL
  parentSegmentIdug   varchar(32)   NULL   -- FK → self
  parentCid           varchar(32)   NOT NULL -- FK → CaseAnalyses

desired marks a segment as representing an intended outcome rather than an observed one — the structural-tension pattern that recurs across this platform.

Table: TradedSignalExamples

Annotated example signals, keyed by idug, linked to a case analysis via parentCid, carrying tagName, entryType, exitType, and notes. Used as training/reference material for signal recognition.

Views

The original defines 45 views. They fall into four families, three of which should not be reproduced.

Family 1 — Enrichment joins ✅ keep

vPDSPricesNProp   -- bars joined to their instrument's pipSize and precision
vPDSPPricesDt     -- (dt, instrument, timeframe) only: the cheap existence probe
vPDSPrices        -- bars plus a computed "instrument_timeframe" label

vPDSPPricesDt is genuinely load-bearing: it backs GetPeriodTimestamps, which backs the range fast-path in spec 72.

Family 2 — Incomplete-bar filters ✅ keep, but parameterize

vIsIncompleted__ALL    -- all bars still forming
vIsIncompleted_{M1,W1,H4,D1}   -- per-timeframe variants

The __ALL view is the useful one; the per-timeframe copies are the same query with a literal. Replace all five with one parameterized query.

Note the original predicate is WHERE IsIncompleted LIKE '1' — a string comparison against a bit column. Use a boolean comparison.

Family 3 — Per-instrument OHLC projections ❌ do not reproduce

vAUDUSD_H4, vAUDUSD_H1, vAUDUSD_W1, vAUDUSD_m5, vAUDUSD_m15, vAUDUSD_M1,
vEURUSD_*, vGBPUSD_*, vUSDCAD_*, ohlcvAUDUSD_*, vEURUSD_H4__202207, ...

Roughly 35 views, each hardcoding one instrument and one timeframe — some hardcoding a specific month. They exist because the tooling of the day made ad-hoc parameterized queries awkward. They are pure duplication and they rot: adding an instrument means adding seven views.

Replace with a single parameterized query or view function:

GetOHLC(instrument, timeframe, dtFrom, dtTo, priceSide)
   priceSide ∈ { ask, bid, mid, median }

Note the derivations the originals encode, because they are the real content:

Several also carry SELECT TOP (100) PERCENT ... ORDER BY 'Date'. That orders by a string literal, not by the column — the quotes make it a constant. The TOP (100) PERCENT prefix is a legacy trick to allow ORDER BY in a view, and it does not guarantee ordering anyway. Never rely on view-level ordering. Order in the query that consumes it.

Family 4 — Windowed indicator views ⚠️ instructive, do not keep as views

x__Indicator_AVG              -- 15-period centred moving average of the mid bar
ohlcvAUDUSD_D1_xAVG2206       -- three overlapping asymmetric moving averages

x__Indicator_AVG computes:

babar = (askH + askL + bidH + bidL) / 4
avg   = AVG(babar) OVER (ORDER BY dt ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING)

ohlcvAUDUSD_D1_xAVG2206 computes three of them with asymmetric windows — (5 preceding, 3 following), (8, 5), (13, 8). Those are Fibonacci-spaced lookback/lookahead pairs, and they are the SQL expression of the Alligator’s displaced moving averages.

Two things matter here:

  1. They are forward-looking. A window including following rows cannot be computed for the most recent bars and must never be used for live signalling without an explicit lag. This is exactly the displacement the Alligator indicator specifies, but in a view it is invisible and easy to misuse.
  2. They demonstrate that indicator computation was being migrated into the database. See spec 75 — that was a deliberate direction, and it is worth an explicit decision in the target architecture rather than an accident.

Keep the formulas. Compute them in the indicator layer (spec 05), where the displacement is explicit and testable.

Stored Procedures

PDSDb defines 24 procedures in four groups:

Group Procedures Disposition
Query sp_pov_select, sp_pov_get_last, sp_dt_pov_select, sp_pov_delete Fold into the data layer (spec 70)
Export sp_csv_avg_exporter*, sp_csv_nb_suffix*, sp_csv_dt_nb_suffix*, sp_csv_gz_nb_suffix, sp_csv_pov_exporter*, sp_ic_csv_pov_exporter See spec 75
Compute GetChartNormalized, sp_get_pov_norm_range See spec 75
Admin sp_adm_index_rebuild, sp_adm_index_organize, adm__chk__r__version, adm__ls__default_lib, adm_get_pandas_version, adm_get_sklearn_version, sp_ls_py_pack Operational; replace with platform tooling

sp_pov_get_last

SELECT TOP(1) <bar columns>
FROM PricePoints
WHERE instrument = @i AND timeframe = @tf
ORDER BY dt DESC

Straightforward and correct. It is the only procedure whose logic must be preserved verbatim in the data layer.

sp_pov_delete

Deletes a whole series. Destructive and unguarded — the repo contains several ad-hoc deletion scripts alongside it (debug220720__delete_*, delete__M1.sql, Delete__All__InstrumentProperty.sql) that were clearly used interactively.

Requirement: any series-deletion capability in the re-implementation must be explicit, audited, and require a confirmation token. The original’s ConfirmDangerousActionForm in the test project shows the author reached the same conclusion.

Index maintenance

sp_adm_index_rebuild / sp_adm_index_organize rebuild or reorganize the table’s indexes. The CLI exposes them as reorg and rebuild subcommands and optionally runs a reorg automatically at the end of a refresh (driven by a REORGINDEX environment setting).

This was necessary because the clustered key is a non-monotonic string — new bars for AUD/USD insert in the middle of the key order, fragmenting pages continuously. With the covering index specified above and a monotonic physical ordering, the maintenance pressure largely disappears.

Requirement: treat automatic index maintenance as a symptom to be designed out, not a feature to port. If the target store still needs it, schedule it as platform maintenance rather than as a step inside the data refresh.

Triggers

One trigger exists, in an incomplete state: lastUpdated on PDSPPrices AFTER UPDATE. It executes Python inside the database engine and appends to a file on disk. Both the create and alter scripts show a hardcoded literal ('NAS100') where the affected instrument should be, and the subprocess.call that would have notified downstream systems is commented out.

This was an experiment toward database-level change notification (spec 73) that was never finished.

Requirement: do not implement change notification as a database trigger that shells out. Use the application-level event contract in spec 73, or the target store’s native change-feed/logical-replication mechanism if one exists. The intent — “downstream systems learn that a series changed, without polling” — is right; the mechanism is not.

Deployment Shape

The original ran both databases in a single SQL Server 2019 container (codename ZEUS), with:

Container definitions, volume backup/restore scripts, and the attach/detach runbook are catalogued in README.md of this spec set.

Requirements carried forward:

  1. Data files, backups, and exports on separate mounts. The original put exports on the data volume, so an export loop could fill the database’s disk.
  2. Backups verified by restore, not just taken. The repo contains multiple backup files and a restore folder, which suggests this was practised.
  3. In-database scripting (Python/R) is a capability with real blast radius — enable it only if spec 75’s in-database compute is actually adopted.

Traceability

Object Source
Full schema export gia-mssql/PDSDb.script-221031.sql (UTF-16LE, 7,620 lines)
Earlier exports (for diffing) gia-mssql/PDSDb__scriptExport__{2208091244,220907,220922}.sql
Trigger experiment gia-mssql/PDSPPrices.Trigger-ON-lastUpdated__designing220919{,.altering}.sql
Index maintenance gia-mssql/adm__INDEX_{Rebuild,Reorg}.sql
Table creation fragments gia-mssql/cd__Bars__TableCreate.sql, gia-mssql/cd__POVData__TableCreate.sql
UTC validation gia-mssql/tests/220925-validate_UTC_fixed.sql
Known period defect gia-mssql/issue220810__Extra_Period_compared_to_FXTradeStation.sql
Backup/restore runbook gia-mssql/README.md