Build a stock transaction API using Python (FastAPI + Ariadne GraphQL) and PostgreSQL/MySQL.

Design a GraphQL API that supports the following queries and mutations:

1. Mutation: createTransaction(userId: Int!, stockSymbol: String!, shares: Int!, price: Float!): Transaction!

   • Adds a new stock transaction.

2. Query: transactions(userId: Int): [Transaction!]!

   • Retrieves all transactions (or transactions for a specific user if userId is provided).

3. Query: transaction(id: ID!): Transaction

   • Fetches a single transaction by ID.

4. Query: topStocks: [TopStock!]!

   • Returns the top 5 most traded stocks (by total shares) in the last 24 hours.

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    stock_symbol VARCHAR(10) NOT NULL,
    shares INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

mutation ($userId: Int!, $stockSymbol: String!, $shares: Int!, $price: Float!) {
  createTransaction(
    userId: $userId
    stockSymbol: $stockSymbol
    shares: $shares
    price: $price
  ) {
    id
    user_id
    stock_symbol
    shares
    price
    timestamp
  }
}

{
  "userId": 101,
  "stockSymbol": "AAPL",
  "shares": 10,
  "price": 150.5
}

{
  "data": {
    "createTransaction": {
      "message": "Transaction recorded successfully",
      "transaction_id": "207"
    }
  }
}

query ($userId: Int) {
  transactions(userId: $userId) {
    id
    user_id
    stock_symbol
    shares
    price
    timestamp
  }
}

{
  "userId": 101
}

{
  "data": {
    "transactions": [
      {
        "id": 1,
        "user_id": 101,
        "stock_symbol": "AAPL",
        "shares": 10,
        "price": 150.5,
        "timestamp": "2024-03-17T12:30:00"
      }
    ]
  }
}

query ($id: ID!) {
  transaction(id: $id) {
    id
    user_id
    stock_symbol
    shares
    price
    timestamp
  }
}

{
  "id": 1
}

{
  "data": {
    "transaction": {
      "id": 1,
      "user_id": 101,
      "stock_symbol": "AAPL",
      "shares": 10,
      "price": 150.5,
      "timestamp": "2024-03-17T12:30:00"
    }
  }
}

✅ Implement pagination for transaction retrieval (e.g., limit, offset).
✅ Use async programming (asyncpg) for better performance.
✅ Optimize SQL queries using indexes.

🚀 Flexible Queries – Clients can request only the fields they need.
🚀 Single Endpoint – Instead of multiple REST routes, one /graphql handles all queries & mutations.
🚀 Reduced Overfetching – No more extra data in responses.