Historical oracle price data

[Klapeyron]

Would be great to store in separate table historical data of oracle prices for each market

[codeautopilot[bot]]

Potential solution

To store historical oracle price data for each market, we need to create a new table in the database, define a corresponding TypeORM model, update the GraphQL schema to include a new type and query, implement a resolver to fetch the historical data, and modify the event processor to save historical price data. This ensures that we can track and query historical oracle prices efficiently.

How to implement

Step 1: Create the Historical Oracle Price Model

Create a new TypeORM model to represent the historical oracle price data.

File: src/model/generated/historicalOraclePrice.model.ts

import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn } from 'typeorm';

@Entity('historical_oracle_price')
export class HistoricalOraclePrice {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  marketId: string;

  @Column('decimal', { precision: 18, scale: 8 })
  price: number;

  @CreateDateColumn()
  timestamp: Date;
}

Step 2: Export the Model

Update the index file to export the newly created model.

File: src/model/index.ts

export * from "./generated";
export { HistoricalOraclePrice } from "./generated/historicalOraclePrice.model";

Step 3: Update the GraphQL Schema

Add a new type and query definition for historical oracle price data.

File: schema.graphql

type HistoricalOraclePrice @entity {
  id: ID! @unique @index
  marketId: String! @index
  price: BigDecimal!
  timestamp: DateTime!
}

type Query {
  historicalOraclePrices(marketId: ID!, startDate: DateTime, endDate: DateTime): [HistoricalOraclePrice!]!
}

Step 4: Implement the Resolver

Add a new resolver function to fetch historical oracle price data from the database.

File: src/server-extension/resolvers/oraclePriceResolver.ts

import { Arg, Field, ObjectType, Query, Resolver } from "type-graphql";
import { HistoricalOraclePrice } from "../../model/generated/historicalOraclePrice.model";
import { getRepository } from "typeorm";

@ObjectType()
export class HistoricalOraclePriceData {
  @Field(() => String, { nullable: false })
  price!: BigInt;

  @Field(() => Date, { nullable: false })
  timestamp!: Date;

  constructor(props: Partial<HistoricalOraclePriceData>) {
    Object.assign(this, props);
  }
}

@Resolver()
export class OraclePriceResolver {
  @Query(() => [HistoricalOraclePriceData])
  async getHistoricalOraclePrices(
    @Arg("marketId", { nullable: false }) marketId: string,
    @Arg("startDate", { nullable: true }) startDate?: Date,
    @Arg("endDate", { nullable: true }) endDate?: Date,
  ): Promise<HistoricalOraclePriceData[]> {
    if (marketId.length == 0) {
      throw new Error("MarketId is empty");
    }

    const query = getRepository(HistoricalOraclePrice)
      .createQueryBuilder("price")
      .where("price.marketId = :marketId", { marketId });

    if (startDate) {
      query.andWhere("price.timestamp >= :startDate", { startDate });
    }

    if (endDate) {
      query.andWhere("price.timestamp <= :endDate", { endDate });
    }

    const prices = await query.getMany();

    return prices.map(price => new HistoricalOraclePriceData(price));
  }
}

Step 5: Update the Event Processor

Modify the processor to insert historical oracle price data into the new table.

File: src/eventprocessor/market/latestOraclePriceProcessor.ts

import { EventProcessor } from "../eventProcessor";
import { Store } from "@subsquid/typeorm-store";
import { Market, HistoricalOraclePrice } from "../../model";
import { DataHandlerContext, Block, Event } from "@subsquid/substrate-processor";
import { oracle } from "../../types/events";
import { oracle as storage } from "../../types/storage";
import { BigDecimal } from "@subsquid/big-decimal";
import { USDC_DECIMALS } from "../../utils";

type PriceData = { [key: string]: Set<bigint> };

export class LatestOraclePriceProcessor implements EventProcessor {
  private blockData: PriceData = {};
  private blockNumber = 0;

  getHandledEventName(): string {
    return "Oracle.NewFeedData";
  }

  async process(
    ctx: DataHandlerContext<Store, any>,
    block: Block<any>,
    event: Event,
  ) {
    console.log("Latest oracle price event");
    const receivedEvent = oracle.newFeedData.v2;
    if (receivedEvent.is(event)) {
      const decodedEvent = receivedEvent.decode(event);
      if (block.header.height != this.blockNumber) {
        delete this.blockData[this.blockNumber];
        this.blockNumber = block.header.height;
        this.blockData[this.blockNumber] = new Set<bigint>();
      }

      for (const eventTicker of decodedEvent.values) {
        this.blockData[this.blockNumber].add(eventTicker[0]);
      }

      for await (const marketId of this.blockData[this.blockNumber]) {
        const marketPrice = await storage.values.v2.get(block.header, marketId);

        let market = await ctx.store.findOne(Market, {
          where: {
            id: marketId.toString(),
          },
        });
        if (market === undefined) {
          console.error(`Market with market Id ${marketId} does not exist.`);
          continue;
        }
        if (marketPrice === undefined) {
          console.error(
            `Price for market with market Id ${marketId} is not available.`,
          );
          continue;
        }

        const price = BigDecimal(marketPrice.value, USDC_DECIMALS);
        market.oraclePrice = price;
        await ctx.store.save(market);

        // Create and save historical oracle price entry
        const historicalPrice = new HistoricalOraclePrice();
        historicalPrice.marketId = market.id;
        historicalPrice.price = price;
        historicalPrice.timestamp = new Date(block.header.timestamp);
        await ctx.store.save(historicalPrice);
      }
    }
  }
}

Step 6: Update the Migration File

Add a new table definition for historical oracle price data.

File: db/migrations/1718277827116-Data.js

module.exports = class Data1718277827116 {
    name = 'Data1718277827116'

    async up(db) {
        await db.query(`CREATE TABLE "market" ("id" character varying NOT NULL, "ticker" text NOT NULL, "tick_size" numeric NOT NULL, "lifetime" numeric NOT NULL, "initial_margin" integer NOT NULL, "maintenance_margin" integer NOT NULL, "contract_unit" numeric NOT NULL, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "daily_volume" numeric NOT NULL, "oracle_price" numeric, "status" character varying(5) NOT NULL, CONSTRAINT "PK_1e9a2963edfd331d92018e3abac" PRIMARY KEY ("id"))`)
        await db.query(`CREATE TABLE "order" ("id" character varying NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "side" character varying(5) NOT NULL, "who" text, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "initial_quantity" numeric NOT NULL, "status" character varying(23) NOT NULL, "type" jsonb NOT NULL, "market_id" character varying, CONSTRAINT "PK_1031171c13130102495201e3e20" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_d91cc35ada00c918781b7f0599" ON "order" ("market_id") `)
        await db.query(`CREATE TABLE "position" ("id" character varying NOT NULL, "create_price" numeric NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "quantity_left" numeric NOT NULL, "long" text NOT NULL, "short" text NOT NULL, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "status" character varying(6), "market_id" character varying, CONSTRAINT "PK_b7f483581562b4dc62ae1a5b7e2" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_d744886149158961e1b796182f" ON "position" ("market_id") `)
        await db.query(`CREATE TABLE "aggregated_orders_by_price" ("id" character varying NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "side" character varying(5) NOT NULL, "market_id" character varying, CONSTRAINT "PK_43d8c277d4b42f0ecd9e93d1d76" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_6a3c7cf051dee6be40f560f2f0" ON "aggregated_orders_by_price" ("market_id") `)
        await db.query(`CREATE TABLE "withdraw" ("id" character varying NOT NULL, "amount" numeric NOT NULL, "user" text NOT NULL, "status" character varying(9) NOT NULL, CONSTRAINT "PK_5c172f81689173f75bf5906ef22" PRIMARY KEY ("id"))`)
        await db.query(`CREATE TABLE "market_settlements" ("id" character varying NOT NULL, "amount" numeric NOT NULL, "user" text NOT NULL, "type" character varying(8) NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "market_id" character varying, CONSTRAINT "PK_e29485c52f72700921fc2eb736f" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_b5f2240a59c8d1b72ec029d3d1" ON "market_settlements" ("user") `)
        await db.query(`CREATE INDEX "IDX_7576b8515e52f177ca4797d594" ON "market_settlements" ("market_id") `)
        await db.query(`CREATE TABLE "user_balance" ("id" character varying NOT NULL, "user" text NOT NULL, "balance_change" numeric NOT NULL, "market_id" character varying, CONSTRAINT "PK_f3edf5a1907e7b430421b9c2ddd" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_0f7286bb3c5c88d8b5a7612b11" ON "user_balance" ("user") `)
        await db.query(`CREATE INDEX "IDX_8927a4646227f7ad15ec1264db" ON "user_balance" ("balance_change") `)
        await db.query(`CREATE INDEX "IDX_99b139cd3e9dadefb8263133d7" ON "user_balance" ("market_id") `)
        await db.query(`CREATE TABLE "general_leaderboard" ("id" character varying NOT NULL, "balance_change" numeric NOT NULL, CONSTRAINT "PK_c5c71acd0600bd847910441dadd" PRIMARY KEY ("id"))`)
        await db.query(`CREATE INDEX "IDX_3da0cfd74db44b12fe9013b691" ON "general_leaderboard" ("balance_change") `)
        await db.query(`ALTER TABLE "order" ADD CONSTRAINT "FK_d91cc35ada00c918781b7f0599d" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`)
        await db.query(`ALTER TABLE "position" ADD CONSTRAINT "FK_d744886149158961e1b796182f8" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`)
        await db.query(`ALTER TABLE "aggregated_orders_by_price" ADD CONSTRAINT "FK_6a3c7cf051dee6be40f560f2f07" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`)
        await db.query(`ALTER TABLE "market_settlements" ADD CONSTRAINT "FK_7576b8515e52f177ca4797d594f" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`)
        await db.query(`ALTER TABLE "user_balance" ADD CONSTRAINT "FK_99b139cd3e9dadefb8263133d7c" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`)

        // Add the new table for historical oracle price data
        await db.query(`CREATE TABLE "historical_oracle_price" (
            "id" SERIAL PRIMARY KEY,
            "market_id" character varying NOT NULL,
            "price" numeric NOT NULL,
            "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL,
            CONSTRAINT "FK_market" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE CASCADE
        )`)
    }

    async down(db) {
        await db.query(`DROP TABLE "market"`)
        await db.query(`DROP TABLE "order"`)
        await db.query(`DROP INDEX "public"."IDX_d91cc35ada00c918781b7f0599"`)
        await db.query(`DROP TABLE "position"`)
        await db.query(`DROP INDEX "public"."IDX_d744886149158961e1b796182f"`)
        await db.query(`DROP TABLE "aggregated_orders_by_price"`)
        await db.query(`DROP INDEX "public"."IDX_6a3c7cf051dee6be40f560f2f0"`)
        await db.query(`DROP TABLE "withdraw"`)
        await db.query(`DROP TABLE "market_settlements"`)
        await db.query(`DROP INDEX "public"."IDX_b5f2240a59c8d1b72ec029d3d1"`)
        await db.query(`DROP INDEX "public"."IDX_7576b8515e52f177ca4797d594"`)
        await db.query(`DROP TABLE "user_balance"`)
        await db.query(`DROP INDEX "public"."IDX_0f7286bb3c5c88d8b5a7612b11"`)
        await db.query(`DROP INDEX "public"."IDX_8927a4646227f7ad15ec1264db"`)
        await db.query(`DROP INDEX "public"."IDX_99b139cd3e9dadefb8263133d7"`)
        await db.query(`DROP TABLE "general_leaderboard"`)
        await db.query(`DROP INDEX "public"."IDX_3da0cfd74db44b12fe9013b691"`)
        await db.query(`ALTER TABLE "order" DROP CONSTRAINT "FK_d91cc35ada00c918781b7f0599d"`)
        await db.query(`ALTER TABLE "position" DROP CONSTRAINT "FK_d744886149158961e1b796182f8"`)
        await db.query(`ALTER TABLE "aggregated_orders_by_price" DROP CONSTRAINT "FK_6a3c7cf051dee6be40f560f2f07"`)
        await db.query(`ALTER TABLE "market_settlements" DROP CONSTRAINT "FK_7576b8515e52f177ca4797d594f"`)
        await db.query(`ALTER TABLE "user_balance" DROP CONSTRAINT "FK_99b139cd3e9dadefb8263133d7c"`)

        // Drop the new table for historical oracle price data
        await db.query(`DROP TABLE "historical_oracle_price"`)
    }
}

Explanation:

• up method: Adds the new table historical_oracle_price with columns id, market_id, price, and timestamp. The market_id column references the market table.
• down method: Drops the historical_oracle_price table if the migration is rolled back.

This change ensures that historical oracle price data is stored in a separate table, as requested.

Click here to create a Pull Request with the proposed solution

Files used for this task:

Changes on src/model/index.ts

To address the task for the file `src/model/index.ts`, you need to export the newly created historical oracle price model. This will make the model available for use throughout the application. Here's a step-by-step plan: 1. **Create the Historical Oracle Price Model**: Ensure that the new model for historical oracle price data is created in `src/model/generated/historicalOraclePrice.model.ts`. 2. **Export the Model**: Modify `src/model/index.ts` to export the newly created model. Assuming the new model is named `HistoricalOraclePrice`, the updated `src/model/index.ts` file should look like this: ```typescript export * from "./generated"; export { HistoricalOraclePrice } from "./generated/historicalOraclePrice.model"; ``` This change ensures that the `HistoricalOraclePrice` model is exported and can be used in other parts of the application. If the model is already being exported through `export * from "./generated";`, then no additional changes are needed. However, explicitly exporting it can improve code clarity and maintainability.

Changes on src/model/generated/historicalOraclePrice.model.ts

To address the task of creating a new TypeORM model for the historical oracle price data table, you need to define the structure of the table in this file. Here is a concrete proposal for the implementation: 1. **Define the Entity**: Create a new TypeORM entity that represents the historical oracle price data table. 2. **Specify Columns**: Define the necessary columns such as `id`, `market`, `price`, `timestamp`, etc. 3. **Add Decorators**: Use TypeORM decorators to specify the column types and constraints. Here is an example implementation for the `historicalOraclePrice.model.ts` file: ```typescript import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn } from 'typeorm'; @Entity('historical_oracle_price') export class HistoricalOraclePrice { @PrimaryGeneratedColumn() id: number; @Column() market: string; @Column('decimal', { precision: 18, scale: 8 }) price: number; @CreateDateColumn() timestamp: Date; } ``` ### Explanation: - **Entity Decorator**: `@Entity('historical_oracle_price')` specifies the table name in the database. - **Primary Key**: `@PrimaryGeneratedColumn()` defines an auto-incrementing primary key. - **Market Column**: `@Column()` defines a column for the market identifier. - **Price Column**: `@Column('decimal', { precision: 18, scale: 8 })` defines a column for the price with high precision. - **Timestamp Column**: `@CreateDateColumn()` automatically sets the timestamp when a new record is created. This model will be used by TypeORM to interact with the `historical_oracle_price` table in the database. Make sure to synchronize this model with the database schema by running the necessary migrations. If you have any further questions or need additional modifications, feel free to ask!

Changes on schema.graphql

To address the task of adding a new type and query definition for historical oracle price data in the `schema.graphql` file, you can follow these steps: 1. **Define a New Type for Historical Oracle Price Data:** - Add a new type `HistoricalOraclePrice` to represent the historical oracle price data. - Include fields such as `id`, `market`, `price`, `timestamp`, and any other relevant fields. 2. **Add a Query Definition:** - Add a query definition to fetch historical oracle price data. This can be a query that returns a list of `HistoricalOraclePrice` entries, possibly filtered by market or date range. Here's how you can modify the `schema.graphql` file: ```graphql enum PositionStatus { OPEN CLOSED } enum OrderStatus { ACTIVE COMPLETED CANCELLED AUTOMATICALLY_MODIFIED AUTOMATICALLY_CANCELLED } enum OrderSide { SHORT LONG } enum WidthdrawStatus { APPROVED CANCELLED REQUESTED } enum MarketStatus { OPEN CLOSE } type OpeningOrder { type: String! } type ClosingOrder { type: String! value: BigInt! } union OrderType = OpeningOrder | ClosingOrder enum TransferType { INGOING OUTGOING } type Market @entity { id: ID! @unique @index ticker: String! tickSize: BigDecimal! lifetime: BigInt! initialMargin: Int! maintenanceMargin: Int! contractUnit: BigDecimal! blockHeight: BigInt! timestamp: DateTime! dailyVolume: BigInt! oraclePrice: BigDecimal status: MarketStatus! } type Order @entity { id: ID! @unique @index market: Market! price: BigDecimal! quantity: BigInt! side: OrderSide! who: String blockHeight: BigInt! timestamp: DateTime! initialQuantity: BigInt! status: OrderStatus! type: OrderType! } type Position @entity { id: ID! market: Market! createPrice: BigDecimal! price: BigDecimal! quantity: BigInt! quantityLeft: BigInt! long: String! short: String! blockHeight: BigInt! timestamp: DateTime! status: PositionStatus } type AggregatedOrdersByPrice @entity { id: ID! price: BigDecimal! quantity: BigInt! market: Market! @index side: OrderSide! } type Withdraw @entity { id: ID! amount: BigInt! user: String! status: WidthdrawStatus! } type MarketSettlements @entity { id: ID! amount: BigDecimal! user: String! @index market: Market! type: TransferType! timestamp: DateTime! } type UserBalance @entity { id: ID! user: String! @index balanceChange: BigDecimal! @index market: Market! } type GeneralLeaderboard @entity { id: ID! @index balanceChange: BigDecimal! @index } # New type for historical oracle price data type HistoricalOraclePrice @entity { id: ID! @unique @index market: Market! @index price: BigDecimal! timestamp: DateTime! } # Query to fetch historical oracle price data type Query { historicalOraclePrices(marketId: ID!, startDate: DateTime, endDate: DateTime): [HistoricalOraclePrice!]! } ``` ### Explanation: - **HistoricalOraclePrice Type:** This new type includes fields for `id`, `market`, `price`, and `timestamp`. - **Query Definition:** The `historicalOraclePrices` query allows fetching historical oracle prices for a specific market within an optional date range (`startDate` and `endDate`). This modification will enable the GraphQL API to support querying historical oracle price data, which can then be implemented in the resolver to fetch data from the database.

Changes on src/server-extension/resolvers/oraclePriceResolver.ts

To address the task of adding a new resolver function to fetch historical oracle price data from the database, you will need to extend the existing `OraclePriceResolver` class. Here is a step-by-step guide on how to achieve this: 1. **Define a new ObjectType for Historical Oracle Price Data:** - This will represent the structure of the historical oracle price data. 2. **Add a new Query to the Resolver:** - This query will fetch the historical oracle price data based on the market ID and possibly a date range. 3. **Implement the Logic to Fetch Data from the Database:** - Use the appropriate database model to query the historical data. Here is the updated code for `src/server-extension/resolvers/oraclePriceResolver.ts`: ```typescript import { Arg, Field, ObjectType, Query, Resolver } from "type-graphql"; import { OraclePriceProvider } from "../../utils"; import { HistoricalOraclePrice } from "../../model/generated/historicalOraclePrice.model"; // Import the new model import { getRepository } from "typeorm"; // Import TypeORM's getRepository @ObjectType() export class LatestOraclePrice { @Field(() => String, { nullable: false }) price!: BigInt; constructor(props: Partial) { Object.assign(this, props); } } @ObjectType() export class HistoricalOraclePriceData { @Field(() => String, { nullable: false }) price!: BigInt; @Field(() => Date, { nullable: false }) timestamp!: Date; constructor(props: Partial) { Object.assign(this, props); } } @Resolver() export class OraclePriceResolver { @Query(() => LatestOraclePrice) async getLatestOraclePrice( @Arg("marketId", { nullable: false }) marketId: string, ): Promise { if (marketId.length == 0) { throw new Error("MarketId is empty"); } return new LatestOraclePrice({ price: await OraclePriceProvider.getLatestOraclePriceForMarketId(marketId), }); } @Query(() => [HistoricalOraclePriceData]) async getHistoricalOraclePrices( @Arg("marketId", { nullable: false }) marketId: string, @Arg("startDate", { nullable: true }) startDate?: Date, @Arg("endDate", { nullable: true }) endDate?: Date, ): Promise { if (marketId.length == 0) { throw new Error("MarketId is empty"); } const query = getRepository(HistoricalOraclePrice) .createQueryBuilder("price") .where("price.marketId = :marketId", { marketId }); if (startDate) { query.andWhere("price.timestamp >= :startDate", { startDate }); } if (endDate) { query.andWhere("price.timestamp <= :endDate", { endDate }); } const prices = await query.getMany(); return prices.map(price => new HistoricalOraclePriceData(price)); } } ``` ### Explanation: 1. **HistoricalOraclePriceData ObjectType:** - This defines the structure of the historical oracle price data with fields for `price` and `timestamp`. 2. **getHistoricalOraclePrices Query:** - This query accepts `marketId`, `startDate`, and `endDate` as arguments. - It uses TypeORM's `getRepository` to query the `HistoricalOraclePrice` model. - The query filters data based on the provided `marketId`, and optionally `startDate` and `endDate`. - The results are mapped to instances of `HistoricalOraclePriceData` and returned. This implementation will allow you to fetch historical oracle price data for a specific market, optionally within a specified date range.

Changes on src/eventprocessor/market/latestOraclePriceProcessor.ts

To address the task of updating the processor to insert historical oracle price data into the new table, we need to modify the `LatestOraclePriceProcessor` class to include logic for saving historical price data. Here's a step-by-step plan and the corresponding code changes: ### Step-by-Step Plan 1. **Import the Historical Oracle Price Model**: Import the newly created model for historical oracle price data. 2. **Create a New Historical Oracle Price Entry**: For each market price update, create a new entry in the historical oracle price table. 3. **Save the Historical Oracle Price Entry**: Save the new historical oracle price entry to the database. ### Code Changes Here is the updated code for `src/eventprocessor/market/latestOraclePriceProcessor.ts`: ```typescript import { EventProcessor } from "../eventProcessor"; import { Store } from "@subsquid/typeorm-store"; import { Market, HistoricalOraclePrice } from "../../model"; // Import HistoricalOraclePrice model import { DataHandlerContext, Block, Event, } from "@subsquid/substrate-processor"; import { oracle } from "../../types/events"; import { oracle as storage } from "../../types/storage"; import { BigDecimal } from "@subsquid/big-decimal"; import { USDC_DECIMALS } from "../../utils"; type PriceData = { [key: string]: Set }; export class LatestOraclePriceProcessor implements EventProcessor { private blockData: PriceData = {}; private blockNumber = 0; getHandledEventName(): string { return "Oracle.NewFeedData"; } async process( ctx: DataHandlerContext, block: Block, event: Event, ) { console.log("Latest oracle price event"); const receivedEvent = oracle.newFeedData.v2; if (receivedEvent.is(event)) { const decodedEvent = receivedEvent.decode(event); if (block.header.height != this.blockNumber) { delete this.blockData[this.blockNumber]; this.blockNumber = block.header.height; this.blockData[this.blockNumber] = new Set(); } for (const eventTicker of decodedEvent.values) { this.blockData[this.blockNumber].add(eventTicker[0]); } for await (const marketId of this.blockData[this.blockNumber]) { const marketPrice = await storage.values.v2.get(block.header, marketId); let market = await ctx.store.findOne(Market, { where: { id: marketId.toString(), }, }); if (market === undefined) { console.error(`Market with market Id ${marketId} does not exist.`); continue; } if (marketPrice === undefined) { console.error( `Price for market with market Id ${marketId} is not available.`, ); continue; } const price = BigDecimal(marketPrice.value, USDC_DECIMALS); market.oraclePrice = price; await ctx.store.save(market); // Create and save historical oracle price entry const historicalPrice = new HistoricalOraclePrice(); historicalPrice.marketId = market.id; historicalPrice.price = price; historicalPrice.timestamp = new Date(block.header.timestamp); await ctx.store.save(historicalPrice); } } } } ``` ### Explanation 1. **Import Statement**: We import the `HistoricalOraclePrice` model at the beginning of the file. 2. **Historical Oracle Price Entry**: After updating the `market.oraclePrice`, we create a new instance of `HistoricalOraclePrice` and set its properties (`marketId`, `price`, and `timestamp`). 3. **Save Historical Entry**: We save the new historical oracle price entry to the database using `ctx.store.save(historicalPrice)`. These changes ensure that every time the oracle price is updated, a corresponding historical record is created and stored in the new table.

Changes on db/migrations/1718277827116-Data.js

To address the task of adding a new table definition for historical oracle price data, you need to modify the migration file `db/migrations/1718277827116-Data.js`. Here's a step-by-step guide on how to do it: 1. **Define the new table in the `up` method**: - Add a new table named `historical_oracle_price` with the necessary columns to store historical oracle price data. 2. **Define the `down` method**: - Ensure that the `down` method includes the logic to drop the newly created table if the migration is rolled back. Here's the updated code for the migration file: ```javascript module.exports = class Data1718277827116 { name = 'Data1718277827116' async up(db) { await db.query(`CREATE TABLE "market" ("id" character varying NOT NULL, "ticker" text NOT NULL, "tick_size" numeric NOT NULL, "lifetime" numeric NOT NULL, "initial_margin" integer NOT NULL, "maintenance_margin" integer NOT NULL, "contract_unit" numeric NOT NULL, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "daily_volume" numeric NOT NULL, "oracle_price" numeric, "status" character varying(5) NOT NULL, CONSTRAINT "PK_1e9a2963edfd331d92018e3abac" PRIMARY KEY ("id"))`) await db.query(`CREATE TABLE "order" ("id" character varying NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "side" character varying(5) NOT NULL, "who" text, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "initial_quantity" numeric NOT NULL, "status" character varying(23) NOT NULL, "type" jsonb NOT NULL, "market_id" character varying, CONSTRAINT "PK_1031171c13130102495201e3e20" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_d91cc35ada00c918781b7f0599" ON "order" ("market_id") `) await db.query(`CREATE TABLE "position" ("id" character varying NOT NULL, "create_price" numeric NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "quantity_left" numeric NOT NULL, "long" text NOT NULL, "short" text NOT NULL, "block_height" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "status" character varying(6), "market_id" character varying, CONSTRAINT "PK_b7f483581562b4dc62ae1a5b7e2" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_d744886149158961e1b796182f" ON "position" ("market_id") `) await db.query(`CREATE TABLE "aggregated_orders_by_price" ("id" character varying NOT NULL, "price" numeric NOT NULL, "quantity" numeric NOT NULL, "side" character varying(5) NOT NULL, "market_id" character varying, CONSTRAINT "PK_43d8c277d4b42f0ecd9e93d1d76" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_6a3c7cf051dee6be40f560f2f0" ON "aggregated_orders_by_price" ("market_id") `) await db.query(`CREATE TABLE "withdraw" ("id" character varying NOT NULL, "amount" numeric NOT NULL, "user" text NOT NULL, "status" character varying(9) NOT NULL, CONSTRAINT "PK_5c172f81689173f75bf5906ef22" PRIMARY KEY ("id"))`) await db.query(`CREATE TABLE "market_settlements" ("id" character varying NOT NULL, "amount" numeric NOT NULL, "user" text NOT NULL, "type" character varying(8) NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, "market_id" character varying, CONSTRAINT "PK_e29485c52f72700921fc2eb736f" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_b5f2240a59c8d1b72ec029d3d1" ON "market_settlements" ("user") `) await db.query(`CREATE INDEX "IDX_7576b8515e52f177ca4797d594" ON "market_settlements" ("market_id") `) await db.query(`CREATE TABLE "user_balance" ("id" character varying NOT NULL, "user" text NOT NULL, "balance_change" numeric NOT NULL, "market_id" character varying, CONSTRAINT "PK_f3edf5a1907e7b430421b9c2ddd" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_0f7286bb3c5c88d8b5a7612b11" ON "user_balance" ("user") `) await db.query(`CREATE INDEX "IDX_8927a4646227f7ad15ec1264db" ON "user_balance" ("balance_change") `) await db.query(`CREATE INDEX "IDX_99b139cd3e9dadefb8263133d7" ON "user_balance" ("market_id") `) await db.query(`CREATE TABLE "general_leaderboard" ("id" character varying NOT NULL, "balance_change" numeric NOT NULL, CONSTRAINT "PK_c5c71acd0600bd847910441dadd" PRIMARY KEY ("id"))`) await db.query(`CREATE INDEX "IDX_3da0cfd74db44b12fe9013b691" ON "general_leaderboard" ("balance_change") `) await db.query(`ALTER TABLE "order" ADD CONSTRAINT "FK_d91cc35ada00c918781b7f0599d" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`) await db.query(`ALTER TABLE "position" ADD CONSTRAINT "FK_d744886149158961e1b796182f8" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`) await db.query(`ALTER TABLE "aggregated_orders_by_price" ADD CONSTRAINT "FK_6a3c7cf051dee6be40f560f2f07" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`) await db.query(`ALTER TABLE "market_settlements" ADD CONSTRAINT "FK_7576b8515e52f177ca4797d594f" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`) await db.query(`ALTER TABLE "user_balance" ADD CONSTRAINT "FK_99b139cd3e9dadefb8263133d7c" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE NO ACTION ON UPDATE NO ACTION`) // Add the new table for historical oracle price data await db.query(`CREATE TABLE "historical_oracle_price" ( "id" SERIAL PRIMARY KEY, "market_id" character varying NOT NULL, "price" numeric NOT NULL, "timestamp" TIMESTAMP WITH TIME ZONE NOT NULL, CONSTRAINT "FK_market" FOREIGN KEY ("market_id") REFERENCES "market"("id") ON DELETE CASCADE )`) } async down(db) { await db.query(`DROP TABLE "market"`) await db.query(`DROP TABLE "order"`) await db.query(`DROP INDEX "public"."IDX_d91cc35ada00c918781b7f0599"`) await db.query(`DROP TABLE "position"`) await db.query(`DROP INDEX "public"."IDX_d744886149158961e1b796182f"`) await db.query(`DROP TABLE "aggregated_orders_by_price"`) await db.query(`DROP INDEX "public"."IDX_6a3c7cf051dee6be40f560f2f0"`) await db.query(`DROP TABLE "withdraw"`) await db.query(`DROP TABLE "market_settlements"`) await db.query(`DROP INDEX "public"."IDX_b5f2240a59c8d1b72ec029d3d1"`) await db.query(`DROP INDEX "public"."IDX_7576b8515e52f177ca4797d594"`) await db.query(`DROP TABLE "user_balance"`) await db.query(`DROP INDEX "public"."IDX_0f7286bb3c5c88d8b5a7612b11"`) await db.query(`DROP INDEX "public"."IDX_8927a4646227f7ad15ec1264db"`) await db.query(`DROP INDEX "public"."IDX_99b139cd3e9dadefb8263133d7"`) await db.query(`DROP TABLE "general_leaderboard"`) await db.query(`DROP INDEX "public"."IDX_3da0cfd74db44b12fe9013b691"`) await db.query(`ALTER TABLE "order" DROP CONSTRAINT "FK_d91cc35ada00c918781b7f0599d"`) await db.query(`ALTER TABLE "position" DROP CONSTRAINT "FK_d744886149158961e1b796182f8"`) await db.query(`ALTER TABLE "aggregated_orders_by_price" DROP CONSTRAINT "FK_6a3c7cf051dee6be40f560f2f07"`) await db.query(`ALTER TABLE "market_settlements" DROP CONSTRAINT "FK_7576b8515e52f177ca4797d594f"`) await db.query(`ALTER TABLE "user_balance" DROP CONSTRAINT "FK_99b139cd3e9dadefb8263133d7c"`) // Drop the new table for historical oracle price data await db.query(`DROP TABLE "historical_oracle_price"`) } } ``` ### Explanation: - **`up` method**: Adds the new table `historical_oracle_price` with columns `id`, `market_id`, `price`, and `timestamp`. The `market_id` column references the `market` table. - **`down` method**: Drops the `historical_oracle_price` table if the migration is rolled back. This change ensures that historical oracle price data is stored in a separate table, as requested.

_Disclaimer: This comment was entirely generated using AI. Be aware that the information provided may be incorrect._ Current plan usage: 0.00% **Have feedback or need help?** [Discord](https://discord.gg/r72ykfvyx7) [Documentation](https://docs.codeautopilot.com/) [support@codeautopilot.com](mailto:support@codeautopilot.com)