ores.iam.session

1. Flags
2. Natural keys
3. Columns
4. SQL
- 4.1. Flags
- 4.2. Indexes
5. C++
6. See also

A user session: one login or service-account connection, recorded for analytics, session listing and end-time tracking. The table is a TimescaleDB hypertable partitioned by start_time with 7-day chunks (see projects/ores.sql/create/iam/iam_sessions_create.sql).

The table has no valid_from=/=valid_to, no GIST exclusion, no version column and no audit tail, and it keys on (id, start_time) rather than a single surrogate key – the partition column must sit in the primary key of a hypertable. The :current_state: and :hypertable: flags in the * SQL ** Flags drawer select exactly that shape. The compound key is what makes this the first entity in the estate whose key carries a timestamp; the mapper, entity and generator templates gained the timestamp key-column branches they were missing.

end_time is a nullable timestamp with time zone: a session that has not ended has no closing instant at all, so the column holds NULL and the domain and protocol types hold std::optional<time_point>. There is no empty-string spelling and no other sentinel for "still open"; absence is the representation, and the wire carries null. client_ip is text, not inet, so the richer reading of an IP address belongs to the consumer that needs it.

The hand-written domain struct also carried party_id, visible_party_ids and username. No column backs any of them; they are the denormalised, party-scoped shape of a session, and the rule that an entity describes its table puts them on a message instead. They are declared as message fields on session_view in ores.iam.session_messages.

The entity's canonical CRUD subjects are generated: session_registrar owns list/get/get-many/put/put-many/delete on iam.v1.sessions.*. The operation models declare a different set under the same prefix – iam.v1.ops.get_active_sessions and iam.v1.ops.get_session_samples – so the two coexist rather than compete, and no handler is suppressed here.

1. Flags

The row names the account a session belongs to, where it came from and how much it moved, so the generated reads are not open to every signed-in caller: the generated iam.v1.sessions.list, .get and .get_many handlers require iam::sessions:read before they serve anything, as every generated read requires its resource's read code.

:schema: public :product: ores :component: iam :profile: readonly-timeseries

2. Natural keys

3. Columns

3.1. id

Unique identifier for the session.

3.2. start_time

Timestamp when the session started (login time). It is the hypertable's partition column, so it is part of the primary key. A list may be ordered by it, so an account's sessions read newest first.

3.3. account_id

Foreign key referencing the associated account. A list may be filtered by it, so an account's page reads that account's sessions and no other.

3.4. end_time

Timestamp when the session ended (logout or disconnect). NULL while the session is still open, because an open session has no closing instant.

std::chrono::system_clock::now()

3.5. client_ip

Client IP address (IPv4 or IPv6).

boost::asio::ip::make_address("192.168.1.100")

3.6. client_identifier

Client identifier string from the handshake, typically the client application name.

std::string(faker::word::noun()) + " Client"

3.7. client_version_major

Client protocol version major number.

3.8. client_version_minor

Client protocol version minor number.

3.9. bytes_sent

Total bytes sent to the client during this session.

3.10. bytes_received

Total bytes received from the client during this session.

3.11. country_code

ISO 3166-1 alpha-2 country code from geolocation. Empty if geolocation is unavailable or the IP is private or localhost.

std::string("GB")

3.12. protocol

Protocol used for this session: binary or http.

std::string("binary")

4. SQL

4.1. Flags

4.2. Indexes

name	columns	unique	current_only	where_extra
tenant	tenant_id, start_time desc	false	false
account_id	account_id, start_time desc	false	false
active	account_id	false	false	end_time is null
country	country_code, start_time desc	false	false	country_code != ''
protocol	protocol, start_time desc	false	false

5. C++

5.1. Flags

5.2. Repository

5.3. Domain includes

#include <chrono>
#include <boost/asio/ip/address.hpp>
#include <boost/uuid/uuid.hpp>
#include <cstdint>
#include <optional>
#include <string>

5.4. Entity includes

#include <cstdint>
#include <string>

5.5. Conventions

5.6. Table display

column	header
id	ID (UUID)
account_id	Account
start_time	Start
end_time	End
client_ip	Client IP
client_identifier	Client
country_code	Country
protocol	Protocol
bytes_sent	Bytes Sent
bytes_received	Bytes Received

5.7. Presentation

The screen's declaration. A session is addressed by its id, which is the session's own identifier: the storage key is composite because the table is a hypertable partitioned by start_time, and a partition column has to be part of a primary key. That composite addresses a row for the store; it is not what an operator means by a session, and it is not what a path segment can carry.

The screen is read-only. A session is opened by signing in and closed by the service that ends it, so there is nothing for a person to write here; what the screen is for is seeing who is connected and from where.

5.7.1. Detail fields

field	label	widget	type	is_key	is_required	placeholder
id	ID	idEdit	line_edit	true	true	Enter the session id
account_id	Account	accountIdEdit	line_edit
start_time	Start	startTimeEdit	date
end_time	End	endTimeEdit	date
client_ip	Client IP	clientIpEdit	line_edit
client_identifier	Client	clientIdentifierEdit	line_edit
country_code	Country	countryCodeEdit	line_edit
protocol	Protocol	protocolEdit	line_edit

5.7.2. Columns

enum_name	field	header	type	width
AccountId	account_id	Account	string	300
StartTime	start_time	Start	timestamp	160
EndTime	end_time	End	timestamp	160
ClientIp	client_ip	Client IP	string	150
CountryCode	country_code	Country	string	90
Protocol	protocol	Protocol	string	110
BytesSent	bytes_sent	Bytes Sent	int	110
BytesReceived	bytes_received	Bytes Recv.	int	110

5.8. Paste blocks

5.8.1. repository_reads

The generated CRUD set reads by compound primary key and orders by it. The consumers on the session surface read by account, by active state, by session id alone (the logout path needs the start_time before it can address the row), and update end_time and the byte counters in place on the hot path. Those reads and the two in-place updates stay declared here, over the generated entity and mapper, rather than forcing the consumers onto full-row upserts.

#include "ores.platform/time/datetime.hpp"
#include <boost/lexical_cast.hpp>
#include <boost/uuid/uuid.hpp>
#include <boost/uuid/uuid_io.hpp>
#include <chrono>

std::optional<domain::session> read(context ctx, const boost::uuids::uuid& session_id);

void update_bytes(context ctx,
                  const boost::uuids::uuid& session_id,
                  const std::chrono::system_clock::time_point& start_time,
                  std::uint64_t bytes_sent,
                  std::uint64_t bytes_received);

void end_session(context ctx,
                 const boost::uuids::uuid& session_id,
                 const std::chrono::system_clock::time_point& start_time,
                 const std::chrono::system_clock::time_point& end_time,
                 std::uint64_t bytes_sent,
                 std::uint64_t bytes_received);

std::vector<domain::session> read_by_account(context ctx,
                                             const boost::uuids::uuid& account_id,
                                             std::uint32_t limit = 0,
                                             std::uint32_t offset = 0);

std::vector<domain::session> read_active_by_account(context ctx,
                                                    const boost::uuids::uuid& account_id);

std::uint32_t count_by_account(context ctx, const boost::uuids::uuid& account_id);

std::vector<domain::session> read_all_active(context ctx);

std::optional<domain::session>
session_repository::read(context ctx, const boost::uuids::uuid& session_id) {
    const auto session_id_str = boost::lexical_cast<std::string>(session_id);
    const auto query = query_builder::read<std::vector<session_entity>> |
                       query_builder::where("id"_c == session_id_str) | query_builder::limit(1);

    const auto sessions = query_builder::execute_read_query<session_entity, domain::session>(
        ctx,
        query,
        [](const auto& entities) { return session_mapper::map(entities); },
        lg(),
        "Reading session");
    if (sessions.empty())
        return std::nullopt;
    return sessions.front();
}

void session_repository::update_bytes(context ctx,
                                      const boost::uuids::uuid& session_id,
                                      const std::chrono::system_clock::time_point& start_time,
                                      std::uint64_t bytes_sent,
                                      std::uint64_t bytes_received) {
    query_builder::execute_parameterized_multi_column_query(
        ctx,
        std::string("update ") + session_entity::tablename +
            " set bytes_sent = $1::bigint, bytes_received = $2::bigint"
            " where id = $3::uuid and start_time = $4::timestamptz",
        {std::to_string(bytes_sent),
         std::to_string(bytes_received),
         boost::lexical_cast<std::string>(session_id),
         ores::platform::time::datetime::to_db_string(start_time)},
        lg(),
        "Updating session bytes");
}

void session_repository::end_session(context ctx,
                                     const boost::uuids::uuid& session_id,
                                     const std::chrono::system_clock::time_point& start_time,
                                     const std::chrono::system_clock::time_point& end_time,
                                     std::uint64_t bytes_sent,
                                     std::uint64_t bytes_received) {
    query_builder::execute_parameterized_multi_column_query(
        ctx,
        std::string("update ") + session_entity::tablename +
            " set end_time = $1::timestamptz, bytes_sent = $2::bigint,"
            " bytes_received = $3::bigint where id = $4::uuid and start_time = $5::timestamptz",
        {ores::platform::time::datetime::to_db_string(end_time),
         std::to_string(bytes_sent),
         std::to_string(bytes_received),
         boost::lexical_cast<std::string>(session_id),
         ores::platform::time::datetime::to_db_string(start_time)},
        lg(),
        "Ending session");
}

std::vector<domain::session> session_repository::read_by_account(context ctx,
                                                                 const boost::uuids::uuid& account_id,
                                                                 std::uint32_t limit_count,
                                                                 std::uint32_t offset_count) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);
    auto query = query_builder::read<std::vector<session_entity>> |
                 query_builder::where("account_id"_c == account_id_str) |
                 query_builder::order_by("start_time"_c.desc()) |
                 query_builder::offset(offset_count);
    if (limit_count > 0)
        query = std::move(query) | query_builder::limit(limit_count);

    return query_builder::execute_read_query<session_entity, domain::session>(
        ctx,
        query,
        [](const auto& entities) { return session_mapper::map(entities); },
        lg(),
        "Reading sessions by account");
}

std::vector<domain::session>
session_repository::read_active_by_account(context ctx, const boost::uuids::uuid& account_id) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);
    const auto query =
        query_builder::read<std::vector<session_entity>> |
        query_builder::where("account_id"_c == account_id_str && query_builder::is_null("end_time")) |
        query_builder::order_by("start_time"_c.desc());

    return query_builder::execute_read_query<session_entity, domain::session>(
        ctx,
        query,
        [](const auto& entities) { return session_mapper::map(entities); },
        lg(),
        "Reading active sessions by account");
}

std::uint32_t session_repository::count_by_account(context ctx,
                                                   const boost::uuids::uuid& account_id) {
    const auto account_id_str = boost::lexical_cast<std::string>(account_id);
    const auto query = query_builder::read<std::vector<session_entity>>;
    return query_builder::execute_count_query(
        ctx, query, "account_id"_c == account_id_str, lg(), "Counting sessions by account");
}

std::vector<domain::session> session_repository::read_all_active(context ctx) {
    const auto query = query_builder::read<std::vector<session_entity>> |
                       query_builder::where(query_builder::is_null("end_time")) |
                       query_builder::order_by("start_time"_c.desc());

    return query_builder::execute_read_query<session_entity, domain::session>(
        ctx,
        query,
        [](const auto& entities) { return session_mapper::map(entities); },
        lg(),
        "Reading all active sessions");
}

6. See also

ores.iam — component group overview.